| View previous topic :: View next topic |
| Author |
Message |
Vlad
Apprentice
Joined: 09 Apr 2002
Posts: 264
Location: San Diego, California
|
 Posted: Wed Jul 30, 2003 8:39 am Post subject: Performing multiple mysql queries with PHP |
 |
|
Alright, I'm trying to search a database with several columns. Each column has one string from a fixed set of strings. I'm trying to allow users to submit values from a form with drop down menus. Each menu has all the possible elements of one column (so one menu per column) and the user simply selects which options to query for. I want the "default" option (unselected) to return all of the rows.
So, if a user has the options (column "Animal) [Unselected, Dog, Cat, Horse, Cow] and the options (column "Color") [Unselected, Grey, Brown, Black, White] and selects [Unselected] and [Grey], the query will return all rows for which Color=Grey.
Interestingly enough, if I were to do this from the mysql command line...
| Code: |
mysql>SELECT * FROM database WHERE Animal=IFNULL("Dog",Animal) AND Color=IFNULL("Grey",Color);
|
It would return all rows that have both Dog in the Animal column AND Grey in the Color column. Translating this to mysql is another story -
| Code: |
$query = "SELECT * FROM database WHERE Animal=IFNULL(\"$Animal\",Animal) AND Color=IFNULL(\"$Color\",Color)
|
Returns nothing.
I've debugged this by verifying that print "$Animal"; returns the correct variable the user has submitted. So if the user submits Dog, print "Animal". will print Dog. I realize what I'm doing is probably a syntax error...I've tried doing various combinations of ", ', `, to no effect. But it *does* work on the command line, so there must be a way to make it work with PHP.
Vlad |
|
| Back to top |
|
 |
Celtis
l33t
Joined: 05 Jul 2003
Posts: 737
|
| Posted: Wed Jul 30, 2003 8:48 am Post subject: |
|
|
Just out of interest, what client API version do you get for mysql from a phpinfo(); ? I'm wondering if you have the 3.23 API's installed and they don't support a query like that.
Just a stab in the dark! |
|
| Back to top |
|
|
Vlad
Apprentice
Joined: 09 Apr 2002
Posts: 264
Location: San Diego, California
|
| Posted: Wed Jul 30, 2003 4:07 pm Post subject: |
|
|
In the mysql section it lists...
Client API version 4.0.13
And some basic server information (if it will help):
Server Version: Apache/2.0.47 (Gentoo/Linux) mod_perl/1.99_09 Perl/v5.8.0 mod_ssl/2.0.47 OpenSSL/0.9.6j PHP/4.3.2 |
|
| Back to top |
|
|
Haro
n00b
Joined: 17 May 2003
Posts: 71
Location: West Bend, WI
|
| Posted: Wed Jul 30, 2003 5:13 pm Post subject: |
|
|
I don't think you need the dblquotes at all because the information posted from the form is already a string (obviously).
Also, it's good practice to use $_POST['formfield'] when retrieving information from a form where method="post".
| Code: | | $query = "SELECT * FROM `database` WHERE `Animal`=IFNULL({$_POST['Animal']},`Animal`) AND `Color`=IFNULL({$_POST['Color']},`Color`)"; |
Also, try printing out the query and error, if exists.
| Code: | $query = "SELECT * FROM `database` WHERE `Animal`=IFNULL({$_POST['Animal']},`Animal`) AND `Color`=IFNULL({$_POST['Color']},`Color`)";
$query = mysql_query($query);
if(!$query) {
echo $query."<br><br>."mysql_error();
} |
|
|
| Back to top |
|
|
lisa
Retired Dev
Joined: 01 Jun 2003
Posts: 273
Location: York, UK again! Horray!
|
| Posted: Wed Jul 30, 2003 7:20 pm Post subject: |
|
|
SELECT .... WHERE color='Grey' AND Animal='dog' ?
_________________
Distcc guide
Visit my website
I maintain Distcc, Ccache, Memcached, and some others (i think) |
|
| Back to top |
|
|
Vlad
Apprentice
Joined: 09 Apr 2002
Posts: 264
Location: San Diego, California
|
| Posted: Wed Jul 30, 2003 11:37 pm Post subject: |
|
|
| Code: |
SELECT .... WHERE color='Grey' AND Animal='dog'
|
That's what I want to do; however; the script sometimes passes one (or multiple) null arguments from the form (ie, the default for a drop down menu) - I want these null arguments to translate as anything, except you can't do...
| Code: |
SELECT ... WHERE Color=* AND Animal='Dog'
|
since regular expressions aren't recognized without the REGEX command, and I can't figure out how to tie that into what I want to do for the life of me.
If Color is null, I want it to match ALL rows. Since only some animals in the Animal column are Dog's, it should only return animal's which are Dog's when you don't select a Color and only select an Animal.
If a user doesn't select *any* options, then all arguments return null (ie, if I do an if($argument) will return false) - but I can't match that to "anything". |
|
| Back to top |
|
|
lisa
Retired Dev
Joined: 01 Jun 2003
Posts: 273
Location: York, UK again! Horray!
|
| Posted: Wed Jul 30, 2003 11:49 pm Post subject: |
|
|
| Vlad wrote: | | If Color is null, I want it to match ALL rows. Since only some animals in the Animal column are Dog's, it should only return animal's which are Dog's when you don't select a Color and only select an Animal. |
So don't pass the color...
| Code: |
if (is_null($_POST['Color'])) {
...
SELECT ... WHERE Animal='Dog'
...
}
else {
...
}
|
You should see how I build a query for a search engine.  There's 4 or 5 functions to it based on several form inputs!
_________________
Distcc guide
Visit my website
I maintain Distcc, Ccache, Memcached, and some others (i think) |
|
| Back to top |
|
|
Vlad
Apprentice
Joined: 09 Apr 2002
Posts: 264
Location: San Diego, California
|
| Posted: Thu Jul 31, 2003 1:37 am Post subject: |
|
|
| But how can I not pass one variable and still pass all the others? |
|
| Back to top |
|
|
superjaded
l33t
Joined: 05 Jul 2002
Posts: 802
|
| Posted: Thu Jul 31, 2003 2:49 am Post subject: |
|
|
| Vlad wrote: | | But how can I not pass one variable and still pass all the others? |
This is usually how I set up my queries:
| Code: |
if ($_POST['animal'] != NULL) { $cond.=" animal='".$_POST['animal']."'";}
if ($_POST['color'] != NULL) { $cond.=($cond?' and':'')." color='".$_POST['color']."''";}
$que="select * from animals".($cond?' where '.$cond':'');
|
and if all went well, you could echo back the $que value and it should print something to the effect of: "select * from animals where animal='dog'"
(I hope my code was somewhat understandable  ) |
|
| Back to top |
|
|
Vlad
Apprentice
Joined: 09 Apr 2002
Posts: 264
Location: San Diego, California
|
| Posted: Thu Jul 31, 2003 4:56 am Post subject: |
|
|
It's...complicated. But will it work with, say, 7 conditions? (I don't even want to imagine how ugly that is going to be...).
Working with php/mysql has given me a new appreciation for what an absolute pain it is to create a workable database search engine.... |
|
| Back to top |
|
|
To
Veteran
Joined: 12 Apr 2003
Posts: 1145
Location: Coimbra, Portugal
|
| Posted: Thu Jul 31, 2003 9:10 am Post subject: |
|
|
It can work with any conditions you need/want. I'll take superjaded example and do it other way;)
| Code: |
if ($_POST['animal'] != NULL) { $cond.=" animal='".$_POST['animal']."' AND";}
if ($_POST['color'] != NULL) { $cond.=" color='".$_POST['color']."'' AND";}
if ($_POST['whatever'] != NULL) { $cond.=" whatever='".$_POST['whatever']."'' AND";}
$cond = substr($cond, 0, -4);
$que="select * from animals".($cond?' where '.$cond':''); |
Just my way.
Tó
_________________
------------------------------------------------
Linux Gandalf 3.2.35-grsec
Gentoo Base System version 2.2
------------------------------------------------ |
|
| Back to top |
|
|
Vlad
Apprentice
Joined: 09 Apr 2002
Posts: 264
Location: San Diego, California
|
| Posted: Thu Jul 31, 2003 9:10 pm Post subject: |
|
|
Tó,
Ahh, that's what I was looking for, a way to "build" the query. However, php hiccups on
| Code: |
$que="select * from animals".($cond?' where '.$cond':'');
|
with a parse error. Any idea what might be wrong? I'm going to try to debug it in the mean time...thank you for your input! |
|
| Back to top |
|
|
Vlad
Apprentice
Joined: 09 Apr 2002
Posts: 264
Location: San Diego, California
|
| Posted: Thu Jul 31, 2003 9:30 pm Post subject: |
|
|
Nevermind, I got it working perfectly using what Tó posted, minus the $que line - I rebuilt it using if statements instead of your fancy one liner =P. Thanks to everyone who offered feedback - I really appreciate all your help!
(and if you know what happened to the syntax in that $que line...feel free to respond, I'm always happy to shrink my code!)
Thanks again,
Vlad |
|
| Back to top |
|
|
superjaded
l33t
Joined: 05 Jul 2002
Posts: 802
|
| Posted: Sat Aug 02, 2003 2:53 am Post subject: |
|
|
Woops.
I had a stray single quote on that line, should actually read:
| Code: | | $que="select * from animals".($cond?' where '.$cond:''); |
Notice how there's no longer a ' before the colon now. |
|
| Back to top |
|
|
|
Portage & Programming
