The following warnings occurred:
Warning [2] Undefined variable $unreadreports - Line: 26 - File: global.php(961) : eval()'d code PHP 8.1.2-1ubuntu2.14 (Linux)
File Line Function
/global.php(961) : eval()'d code 26 errorHandler->error
/global.php 961 eval
/printthread.php 16 require_once



UserSpice
$db->query not returning records - Printable Version

+- UserSpice (https://userspice.com/forums)
+-- Forum: Support Center (https://userspice.com/forums/forumdisplay.php?fid=23)
+--- Forum: UserSpice 4.3 and Below (https://userspice.com/forums/forumdisplay.php?fid=26)
+--- Thread: $db->query not returning records (/showthread.php?tid=789)

$db->query not returning records - eer3 - 10-10-2017

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



$db->query not returning records - eer3 - 10-10-2017

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

Code:
$db = DB::getInstance();


$db->query not returning records - Brandin - 10-11-2017

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

$db->query not returning records - corey_hitt@multiverse.software - 10-16-2017

@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:

Quote:DB Object ( [_pdoBig GrinB:private] => PDO Object ( ) [_queryBig GrinB:private] => PDOStatement Object ( [queryString] => SELECT * FROM apps_project WHERE token = oimevmvlaeccuspb0dtzq6y3xrig4jwt; ) [_errorBig GrinB:private] => 1 [_resultsBig GrinB:private] => Array ( ) [_resultsArrayBig GrinB:private] => Array ( ) [_countBig GrinB:private] => 1 [_lastIdBig GrinB:private] => 20 [_queryCountBig GrinB:private] => 9 )

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

I'm guessing from "[_errorBig GrinB: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?

$db->query not returning records - Brandin - 10-16-2017

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

$db->query not returning records - karsen - 10-16-2017

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.

$db->query not returning records - corey_hitt@multiverse.software - 10-18-2017

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.

$db->query not returning records - Brandin - 10-18-2017

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

Ex. array($whatever1));