Variables in a select statement?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
chull
Forum Newbie
Posts: 9
Joined: Thu Jul 26, 2007 11:45 am

Variables in a select statement?

Post by chull »

I'm trying to write a page for users to create custom reports. They make their selections as to what columns, sorting, and selection criteria they want. I then pass these selections to another page that will display the report for them. Can you use variables in a select statement? i.e. $query="SELECT $c1, $c2, $c3,... FROM client WHERE lastname=... ORDER BY $s1, $s2, $s3... Can this be done? And how would you write the code? If this won't work do you have any suggestions.

Thanks!
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

Have you tried using variables in a select statment? Maybe you should try it and see what happens.
chull
Forum Newbie
Posts: 9
Joined: Thu Jul 26, 2007 11:45 am

Post by chull »

Yes, I did try them and they didn't seem to work. So I'm not sure whether you can use them or if my statement was written incorrectly.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Yes you can..

"Select Statements" are simply strings passed to *_query()
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

chull wrote:Yes, I did try them and they didn't seem to work. So I'm not sure whether you can use them or if my statement was written incorrectly.
Run the sql query outside of PHP, and make sure that it actually works.

If it does, make sure that your quoting is correct in the SQL, ie $int vs. '$string'
User avatar
boo
Forum Commoner
Posts: 42
Joined: Mon Jul 02, 2007 11:30 am
Location: NY

Post by boo »

chull wrote:Yes, I did try them and they didn't seem to work. So I'm not sure whether you can use them or if my statement was written incorrectly.
What did you code look like when you tried to do this?
chull
Forum Newbie
Posts: 9
Joined: Thu Jul 26, 2007 11:45 am

Post by chull »

Here's what the code looks like.

$searchdb = "SELECT $c1, $c2, $c3, $c4 FROM client WHERE lastname=$w1 ORDER BY $s1, $s2, $s3";
User avatar
yacahuma
Forum Regular
Posts: 870
Joined: Sun Jul 01, 2007 7:11 am

i alwsya is brackets

Post by yacahuma »

just in case i like to always use bracket in a statement like that

Code: Select all

$searchdb = "SELECT {$c1},{$c2}, {$c3}, {$c4} FROM client WHERE lastname="{$w1}" ORDER BY {$s1}, {$s2}, {$s3}";
I think your lastname need to be quoted or any other character for that matter.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Re: i alwsya is brackets

Post by Begby »

yacahuma wrote:

Code: Select all

$searchdb = "SELECT {$c1},{$c2}, {$c3}, {$c4} FROM client WHERE lastname="{$w1}" ORDER BY {$s1}, {$s2}, {$s3}";

It should be single quotes.

To the OP, one good idea is to echo your SQL statment instead of trying to run it. You can then look at the statement to see if it appears correct. That will give you some more info.
User avatar
boo
Forum Commoner
Posts: 42
Joined: Mon Jul 02, 2007 11:30 am
Location: NY

Post by boo »

chull wrote:Here's what the code looks like.

$searchdb = "SELECT $c1, $c2, $c3, $c4 FROM client WHERE lastname=$w1 ORDER BY $s1, $s2, $s3";
So in this case you will always require 4 fields to select and 4 fields to order by?
chull
Forum Newbie
Posts: 9
Joined: Thu Jul 26, 2007 11:45 am

Post by chull »

In this case Yes. However the number could increase or decrease depending on what information they need from the database.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Have you tried echoing $searchdb to the screen after putting the variables in it...?
User avatar
boo
Forum Commoner
Posts: 42
Joined: Mon Jul 02, 2007 11:30 am
Location: NY

Post by boo »

chull wrote:In this case Yes. However the number could increase or decrease depending on what information they need from the database.
Well in that case you will need to do a little more building of the SQL statement. It will not be as easy as just passing in 4 values since one call could have 4 and the next could have 5 or 3. So you will need to have your sql something like this

Code: Select all

Select $selectfields
From client 
Where lastname=$w1 
Order by $orderfields
You will need to build $selectfields and $orderfields using what the user selects.
Post Reply