Bug in db query->count? - Printable Version

The following warnings occurred:

Warning [2] Undefined variable $unreadreports - Line: 26 - File: global.php(961) : eval()'d code PHP 8.2.25 (Linux)

File	Line	Function
/global.php(961) : eval()'d code	26	errorHandler->error
/global.php	961	eval
/printthread.php	16	require_once

Bug in db query->count? - 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: Bug in db query->count? (/showthread.php?tid=388)

Bug in db query->count? - Trioxin - 12-10-2016

The following code..

<pre>

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

        $poolsQ = $db->query("SELECT pool_id, wormhole_id FROM pools WHERE user_id = ?",$currentUser->id);

        $poolsC = $poolsQ->count();

</pre>

returns a value of 1 even though the table

Code:
pools

is empty. I was expecting it to return 0.

Bug in db query->count? - Trioxin - 12-10-2016

Am I thinking about this wrong? I'm used to using mysqli.

Bug in db query->count? - Trioxin - 12-10-2016

guess I'll use db->get

Bug in db query->count? - dan - 12-10-2016

In your query it should be =?",array($user->data()->id));

Or you could use your current user variable but the other one is already done for you.

Bug in db query->count? - Trioxin - 12-10-2016

I got it all worked out using get. Thanks. As for the userid I have $user-data assigned to $currentUser. I wen't ahead with: $poolsO = $db->get('pools',['user_id','=',$currentUser->id]);

Bug in db query->count? - dan - 12-10-2016

Ahh that works too. I never use get. Idk why.... I always forget about that one. Glad it is working. If you ever do need to do a raw query, just passing all the variables in at the end as an array will fix your problems. Binding variables like that helps prevent certain sql hacks.

Bug in db query->count? - Trioxin - 12-12-2016

How can I do multiple conditions like this using AND? Looking at the docs here (https://userspice.com/documentation-db-class-2/) I tried using db-query once again...
$poolQ = $db->query("SELECT * FROM pools WHERE id = ?",$poolID);
$poolO = $poolQ->results();
print_r($poolO);

and once again what I got was results from the

Code:
users

table even though my query specified the

Code:
pools

table. I'm not sure how I should setup an AND with db->get. Also, you said passing my input as an array rather than how I did in the example in this post will clean the variable from injection?

Thanks

Bug in db query->count? - Trioxin - 12-12-2016

Oh I see now. It HAS to be an array when binding because of how the db class is programmed. If not, it uses a previously selected table from some offshoot of init.php. That's all I can think of as far as why it's choosing the

Code:
users

table even when I'm trying to select a different one. Might as well just use an array for all bindings but the documentation doesn't say anything about that and in fact shows an example of searching a user-defined table without passing an array to query as a second parameter.

Bug in db query->count? - mudmin - 12-12-2016

Yep. You're correct. That's one of the weird things about using the singleton method of db connection. If you establish a new connection to the db (name/pw/etc) every time you make a query/page, bad programming can cause you to make too many connections to the db. The way we call db::getInstance in the header, you will always have a single connection, no more, no less.

So that means, that your previous query is still in memory if there is one and if it fails, it may deliver results from that query.

Either way, best practice (using our method, mysqli, or just PDO in general), you want to properly bind your variables in an array to make absolutely SURE that the db is only searching that particular column for your info (and that someone isn't using a form to get up to mischief in your database). Note that if you are not getting outside information (ie variables) you can just pass the info in as normal ("SELECT * FROM users where id=1") works as you would expect.

I need to update the documentation. Especially the db documentation. I wrote that stuff when I barely understood the classes myself.