$db->query not returning records

[eer3]

I have a local Userspice install and database queries work fine. I tried to create a brand new, clean install in my hosted environment and the installation works, logins work, everything looks good. But, when trying to query data (for example the users table), no actual results are ever returned. I have listed a sample of php that I am using to test with and provided the results from the failing - and the successful - servers. These are both untouched installations (no modifications to any files or database, except to dump the users query). What is the next step that I can take to troubleshoot what the issue is with the hosted environment? Great product by the way!

Here's the php:
<pre>

Code:

<?php
    $users = $db->query("SELECT * FROM USERS");
    dnd($users);
?>

</pre>


Here's the result on the failing server:

object(DB)#2 (8) {
["_pdo":"DB":private]=>
object(PDO)#3 (0) {
}
["_query":"DB":private]=>
object(PDOStatement)#6 (1) {
["queryString"]=>
string(19) "SELECT * FROM USERS"
}
["_error":"DB":private]=>
bool(true)
["_results":"DB":private]=>
array(1) {
[0]=>
object(stdClass)#9 (3) {
["id"]=>
string(2) "47"
["page"]=>
string(22) "users/project_root.php"
["private"]=>
string(1) "0"
}
}
["_resultsArray":"DB":private]=>
array(1) {
[0]=>
array(3) {
["id"]=>
string(2) "47"
["page"]=>
string(22) "users/project_root.php"
["private"]=>
string(1) "0"
}
}
["_count":"DB":private]=>
int(1)
["_lastId":"DB":private]=>
string(1) "0"
["_queryCount":"DB":private]=>
int(11)
}


Here's the result on the successful server:
C:\wamp64\www\demo\users\helpers\helpers.php:216:
object(DB)[2]
private '_pdo' =>
object(PDO)[3]
private '_query' =>
object(PDOStatement)[6]
public 'queryString' => string 'SELECT * FROM USERS' (length=19)
private '_error' => boolean false
private '_results' =>
array (size=2)
0 =>
object(stdClass)[8]
public 'id' => string '1' (length=1)
public 'email' => string 'userspicephp@gmail.com' (length=22)
public 'username' => string 'admin' (length=5)
public 'password' => string '$2y$12$1v06jm2KMOXuuo3qP7erTuTIJFOnzhpds1Moa8BadnUUeX0RV3ex.' (length=60)
public 'fname' => string 'Dan' (length=3)
public 'lname' => string 'Hoover' (length=6)
public 'permissions' => string '1' (length=1)
public 'logins' => string '47' (length=2)
public 'account_owner' => string '1' (length=1)
public 'account_id' => string '0' (length=1)
public 'company' => string 'UserSpice' (length=9)
public 'stripe_cust_id' => string '' (length=0)
public 'billing_phone' => string '' (length=0)
public 'billing_srt1' => string '' (length=0)
public 'billing_srt2' => string '' (length=0)
public 'billing_city' => string '' (length=0)
public 'billing_state' => string '' (length=0)
public 'billing_zip_code' => string '' (length=0)
public 'join_date' => string '2016-01-01 00:00:00' (length=19)
public 'last_login' => string '2017-10-10 22:10:10' (length=19)
public 'email_verified' => string '1' (length=1)
public 'vericode' => string '322418' (length=6)
public 'title' => string '' (length=0)
public 'active' => string '0' (length=1)
public 'custom1' => string '' (length=0)
public 'custom2' => string '' (length=0)
public 'custom3' => string '' (length=0)
public 'custom4' => string '' (length=0)
public 'custom5' => string '' (length=0)
public 'oauth_provider' => string '' (length=0)
public 'oauth_uid' => string '' (length=0)
public 'gender' => string '' (length=0)
public 'locale' => string '' (length=0)
public 'gpluslink' => string '' (length=0)
public 'picture' => string '' (length=0)
public 'created' => string '0000-00-00 00:00:00' (length=19)
public 'modified' => string '1899-11-30 00:00:00' (length=19)
public 'fb_uid' => string '' (length=0)
public 'un_changed' => string '0' (length=1)
1 =>
object(stdClass)[10]
public 'id' => string '2' (length=1)
public 'email' => string 'noreply@userspice.com' (length=21)
public 'username' => string 'user' (length=4)
public 'password' => string '$2y$12$HZa0/d7evKvuHO8I3U8Ff.pOjJqsGTZqlX8qURratzP./EvWetbkK' (length=60)
public 'fname' => string 'Sample' (length=6)
public 'lname' => string 'User' (length=4)
public 'permissions' => string '1' (length=1)
public 'logins' => string '5' (length=1)
public 'account_owner' => string '1' (length=1)
public 'account_id' => string '0' (length=1)
public 'company' => string 'none' (length=4)
public 'stripe_cust_id' => string '' (length=0)
public 'billing_phone' => string '' (length=0)
public 'billing_srt1' => string '' (length=0)
public 'billing_srt2' => string '' (length=0)
public 'billing_city' => string '' (length=0)
public 'billing_state' => string '' (length=0)
public 'billing_zip_code' => string '' (length=0)
public 'join_date' => string '2016-01-02 00:00:00' (length=19)
public 'last_login' => string '2017-02-20 12:14:10' (length=19)
public 'email_verified' => string '1' (length=1)
public 'vericode' => string '970748' (length=6)
public 'title' => string '' (length=0)
public 'active' => string '1' (length=1)
public 'custom1' => string '' (length=0)
public 'custom2' => string '' (length=0)
public 'custom3' => string '' (length=0)
public 'custom4' => string '' (length=0)
public 'custom5' => string '' (length=0)
public 'oauth_provider' => string '' (length=0)
public 'oauth_uid' => string '' (length=0)
public 'gender' => string '' (length=0)
public 'locale' => string '' (length=0)
public 'gpluslink' => string '' (length=0)
public 'picture' => string '' (length=0)
public 'created' => string '0000-00-00 00:00:00' (length=19)
public 'modified' => string '0000-00-00 00:00:00' (length=19)
public 'fb_uid' => string '' (length=0)
public 'un_changed' => string '0' (length=1)
private '_resultsArray' =>
array (size=2)
0 =>
array (size=39)
'id' => string '1' (length=1)
'email' => string 'userspicephp@gmail.com' (length=22)
'username' => string 'admin' (length=5)
'password' => string '$2y$12$1v06jm2KMOXuuo3qP7erTuTIJFOnzhpds1Moa8BadnUUeX0RV3ex.' (length=60)
'fname' => string 'Dan' (length=3)
'lname' => string 'Hoover' (length=6)
'permissions' => string '1' (length=1)
'logins' => string '47' (length=2)
'account_owner' => string '1' (length=1)
'account_id' => string '0' (length=1)
'company' => string 'UserSpice' (length=9)
'stripe_cust_id' => string '' (length=0)
'billing_phone' => string '' (length=0)
'billing_srt1' => string '' (length=0)
'billing_srt2' => string '' (length=0)
'billing_city' => string '' (length=0)
'billing_state' => string '' (length=0)
'billing_zip_code' => string '' (length=0)
'join_date' => string '2016-01-01 00:00:00' (length=19)
'last_login' => string '2017-10-10 22:10:10' (length=19)
'email_verified' => string '1' (length=1)
'vericode' => string '322418' (length=6)
'title' => string '' (length=0)
'active' => string '0' (length=1)
'custom1' => string '' (length=0)
'custom2' => string '' (length=0)
'custom3' => string '' (length=0)
'custom4' => string '' (length=0)
'custom5' => string '' (length=0)
'oauth_provider' => string '' (length=0)
'oauth_uid' => string '' (length=0)
'gender' => string '' (length=0)
'locale' => string '' (length=0)
'gpluslink' => string '' (length=0)
'picture' => string '' (length=0)
'created' => string '0000-00-00 00:00:00' (length=19)
'modified' => string '1899-11-30 00:00:00' (length=19)
'fb_uid' => string '' (length=0)
'un_changed' => string '0' (length=1)
1 =>
array (size=39)
'id' => string '2' (length=1)
'email' => string 'noreply@userspice.com' (length=21)
'username' => string 'user' (length=4)
'password' => string '$2y$12$HZa0/d7evKvuHO8I3U8Ff.pOjJqsGTZqlX8qURratzP./EvWetbkK' (length=60)
'fname' => string 'Sample' (length=6)
'lname' => string 'User' (length=4)
'permissions' => string '1' (length=1)
'logins' => string '5' (length=1)
'account_owner' => string '1' (length=1)
'account_id' => string '0' (length=1)
'company' => string 'none' (length=4)
'stripe_cust_id' => string '' (length=0)
'billing_phone' => string '' (length=0)
'billing_srt1' => string '' (length=0)
'billing_srt2' => string '' (length=0)
'billing_city' => string '' (length=0)
'billing_state' => string '' (length=0)
'billing_zip_code' => string '' (length=0)
'join_date' => string '2016-01-02 00:00:00' (length=19)
'last_login' => string '2017-02-20 12:14:10' (length=19)
'email_verified' => string '1' (length=1)
'vericode' => string '970748' (length=6)
'title' => string '' (length=0)
'active' => string '1' (length=1)
'custom1' => string '' (length=0)
'custom2' => string '' (length=0)
'custom3' => string '' (length=0)
'custom4' => string '' (length=0)
'custom5' => string '' (length=0)
'oauth_provider' => string '' (length=0)
'oauth_uid' => string '' (length=0)
'gender' => string '' (length=0)
'locale' => string '' (length=0)
'gpluslink' => string '' (length=0)
'picture' => string '' (length=0)
'created' => string '0000-00-00 00:00:00' (length=19)
'modified' => string '0000-00-00 00:00:00' (length=19)
'fb_uid' => string '' (length=0)
'un_changed' => string '0' (length=1)
private '_count' => int 2
private '_lastId' => string '0' (length=1)
private '_queryCount' => int 11

[eer3]

Ok... not sure why, but on the hosted server I had to explicitly make a call to open the DB

Code:

$db = DB::getInstance();

[Brandin]

That and technically $users is just the query...you should be dumping $users->results() or $users->first().

[corey_hitt@multiverse.software]

@neverforget98 and @eer3,

I'm running into a similar issue. I've created another table in my userspice database and I'm trying to query data from it using the same format as the queries already in US.

In a previous section I'm generating a 32-digit random token ($project_token), along with name and description, which are inserted into an auto-incrementing table.

<pre>

Code:

$p_create=array('project-token'=>$project_token,'project-name'=>$_POST['project-name'],'project-description'=>$_POST['project-description']);
$db->insert('apps_project',$p_create);

</pre>


Now I'm trying to query the table to find my project by that token

<pre>

Code:

$project_get = $db->query("SELECT * FROM apps_project WHERE token = ".$project_token.";");
$project_get_id = $project_get->results();
$this_id = $project_get_id->id;

</pre>


When I print_r($project_get), this is the result:

DB Object ( [_pdoB:private] => PDO Object ( ) [_queryB:private] => PDOStatement Object ( [queryString] => SELECT * FROM apps_project WHERE token = oimevmvlaeccuspb0dtzq6y3xrig4jwt; ) [_errorB:private] => 1 [_resultsB:private] => Array ( ) [_resultsArrayB:private] => Array ( ) [_countB:private] => 1 [_lastIdB:private] => 20 [_queryCountB:private] => 9 )

print_r($project_get_id) just returns an empty "Array()"

I'm guessing from "[_errorB:private] => 1" that my sql query has generated an error, and returned no data.

So is it possible to use the built-in DB class/functions, or will that not work for data outside of the US installation?

[Brandin]

Two things.

You should be binding your variables.

Code:

$project_get = $db->query("SELECT * FROM apps_project WHERE token = ?",($project_token));

Second thing, you are making $project_get_id a full array, not just one result. If you want one line, use

Code:

first()

instead of results(). If you want to use

Code:

results()

and obtain more than one line, you will need to foreach.

Code:

foreach ($project_get->results() as $project_get_id) { $this_id = $project_get_id->id; }

Note this is going to overwrite the $this_id variable if you use it outside of the foreach

[karsen]

Brandin is definitely correct about binding, but I want to chime in to your original problem. Your sql was invalid and so the $db object wasn't able to get the rows.

If you look at the queryString property of your print_r(), the query you sent the database was:

Code:

SELECT * FROM apps_project WHERE token = oimevmvlaeccuspb0dtzq6y3xrig4jwt;

You'd need to add quotes around your token, to make it:

Code:

SELECT * FROM apps_project WHERE token = 'oimevmvlaeccuspb0dtzq6y3xrig4jwt';

When you bind your variables the code knows to add in your quotes for you though as well as helping prevent SQL injection, so it's definitely recommended to bind them any time you use non-static values.

[corey_hitt@multiverse.software]

I ended up having to do:

Code:

$db->query("SELECT * FROM apps_project WHERE token = '".$project_token."'");

Doing:

Code:

$db->query("SELECT * FROM apps_project WHERE token = '?'",($project_token));

would not work, or any other variation of binding the variable (quotes, no quotes, etc.). It didn't give an error, but it returned no results.

[Brandin]

Sorry about that I messed up the code. Before your brackets around the binded variables you have to define the array...

Ex. array($whatever1));