How to add a Where clause to this query

Worker Joined: Dec 03, 2005 Posts: 153

hi all

I have a query like below, this will list all the users:

Code:

$select = 'select user_id, uname, user_item, user_userbid, from '.$prefix.'_bid ';

I use this code to show the results.

Code:

$result = $db->sql_query($select.$sort.$limit ) or die();

I like to add a WHERE Clause, like below: ($biddersname is the member who is logged in)

Code:

$select = 'select user_id, uname, user_item, user_userbid, from '.$prefix.'_bid WHERE user_userbid = '.$biddersname.' ';

When I use the above code it does not list any info, if I use the below code it works

Code:

$select = 'select user_id, uname, user_item, user_userbid, from '.$prefix.'_bid WHERE user_userbid="demouser" ';

How can I add the WHERE clause so it only shows info for that user. I have searched and tried other ways to do it, but just can't seam to get it working.

Cheers Scorp

Posted: Tue Oct 04, 2011 7:13 am

Try this... since the query ends in a variable... you don't need to end the query with .' ';

Code:

$select = 'select user_id, uname, user_item, user_userbid, from '.$prefix.'_bid WHERE user_userbid = '.$biddersname;

if you're trying to stay consistent...

Code:

$select = 'select user_id, uname, user_item, user_userbid, from '.$prefix.'_bid WHERE user_userbid ="'.$biddersname.'";

Try one of those and see if it works for you. If neither of those work... you need to check the variable and ensure it is set with the username.

For a method of error checking... use

Code:

or die(mysql_error());

on the end of the query to output errors from mysql.

Posted: Tue Oct 04, 2011 7:18 am

With single quotes PHP interprets your $select variable literally ... that is to say it is looking for literally .$biddersname rather than the value of the variable $biddername. You need to do something like this:

$select = 'select user_id, uname, user_item, user_userbid, from '.$prefix.'_bid WHERE user_userbid = \' '.$biddersname. '\' ';

the \ character will escape one of the single quotes.

In terms of general process:

1. you can always echo out the value of $select by saying

echo $select . <br />;

and look at it to see if it is syntactically correct.

2. You can also look for similar code in the distribution. You will see plenty of examples.

(I didn't test the code I posted btw so good luck).

Posted: Wed Oct 05, 2011 6:37 am

Hi All

Cheers for the fast reply.

fkelly:

I tried this before

Code:

WHERE user_userbid = \' '.$biddersname. '\' ';

but got a blank screen, as I have seen this code used before.

killing-hours:

Code:

where user_userbid ="'.$biddersname.'" ';

works just find.

Cheers to the both of you for your help and advise.

Scorp

Posted: Wed Oct 05, 2011 7:11 am

As a side note... you really ought to capitalize your queries keywords.

I.e.

Code:

$select = 'SELECT user_id, uname, user_item, user_userbid, FROM '.$prefix.'_bid WHERE user_userbid ="'.$biddersname.'";

Just a good practice.

Posted: Wed Oct 05, 2011 7:30 am

IF we are talking good practices then user_id, uname, user_item and user_userbid should be enclosed in ` (tic marks?) as `user_id` etc. Just in case they happen to be MYSQL reserved words this prevents misinterpretation.

I don't see why the code I posted wouldn't work or would result in a blank screen. I don't like to just let these things pass. Quite honestly if this happened to me I would echo out the value of $select and then die the program and study it until I figured out what was wrong. Or I'd use our MYSQL error logging and see what MYSQL thought was wrong with the statement. Or I'd copy the generated SQL over into a PHPMYADMIN screen and run a query using it and see what PHPMYADMIN thought.

Posted: Wed Oct 05, 2011 7:36 am

fkelly,

I agree... although I don't generally practice the (tics?) but I guess I should. Sad

I also don't see why your code wouldn't work. I use it in my work tracker all over the place it it works fine for me.

Posted: Wed Oct 05, 2011 1:37 pm

Hi All

Fkelly when i try the following:

Code:

$select = 'SELECT user_id, uname, user_item, user_userbid, from '.$prefix.'_bid WHERE user_userbid = \' '.$biddersname. '\' ';

and then

Code:

echo "$select . <br />";

I get the following on the screen

Code:

SELECT user_id, uname, user_item, user_userbid, from nuke_bid WHERE user_userbid = ' demouser' .

However, I should have said, the results dont show, its just blank (NOT a black screen, my wrong)

When I try

Code:

$select = 'SELECT user_id, uname, user_item, user_userbid, from '.$prefix.'_bid WHERE user_userbid = "'.$biddersname.'" ';

the results show, now, I did notice on the output of

Code:

echo "$select . <br />";

the space after the first ' and the word demouser

Code:

' demouser'

I removed the spaces like so

Code:

user_userbid = \''.$biddersname.'\'

and now it shows the results.

The (tic marks?) should they be used on all querys?

Cheers scorp

Posted: Wed Oct 05, 2011 2:55 pm

Cheers to you Scorp ... you solved the problem. Yes, I can see that the extra space was causing the problem because ' demouser" != 'demouser'.

I don't know if we have a RN standard on the tic marks. My understanding is that they prevent any possible "collision" with MYSQL reserved words. Unless you have these reserved words committed to memory it is probably safest to use the tics. For instance, do you know off the top whether 'user' is a MYSQL reserved word? It would seem to me it could be. So saying SELECT `user` would be safe whereas SELECT user might not be. It also is a good way of signaling intent and thus making your program more maintainable.

This is not implemented consistently throughout RN code, in fact it's rather rare. And going back through working code and putting them would not be high on my priority list. Still, I think it's a good idea to use them in new code or code you are actively working on.

Posted: Fri Oct 07, 2011 10:27 am

I would definitely recommend using back-ticks. Just as a scary thought, time, text and timestamp are very commonly used for database fields but are actually reserved words. Luckily mySQL allows them to be used (usually) but come the day when they change their minds......
So always follow best practice when you can.