Page 1 of 1
Variables in a select statement?
Posted: Tue Jul 31, 2007 9:56 am
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!
Posted: Tue Jul 31, 2007 9:59 am
by Begby
Have you tried using variables in a select statment? Maybe you should try it and see what happens.
Posted: Tue Jul 31, 2007 10:26 am
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.
Posted: Tue Jul 31, 2007 10:28 am
by John Cartwright
Yes you can..
"Select Statements" are simply strings passed to *_query()
Posted: Tue Jul 31, 2007 10:46 am
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'
Posted: Tue Jul 31, 2007 10:47 am
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?
Posted: Tue Jul 31, 2007 10:57 am
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";
i alwsya is brackets
Posted: Tue Jul 31, 2007 11:05 am
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.
Re: i alwsya is brackets
Posted: Tue Jul 31, 2007 1:10 pm
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.
Posted: Tue Jul 31, 2007 1:23 pm
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?
Posted: Tue Jul 31, 2007 2:00 pm
by chull
In this case Yes. However the number could increase or decrease depending on what information they need from the database.
Posted: Wed Aug 01, 2007 7:09 am
by superdezign
Have you tried echoing $searchdb to the screen after putting the variables in it...?
Posted: Wed Aug 01, 2007 8:06 am
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.