SQL Conundrum - Dev Shed

Dev Shed Forums Sponsor:

February 7th, 2012, 07:29 PM

suboc-1

Registered User

Join Date: Feb 2012

Posts: 1

Time spent in forums: 26 m 20 sec
Reputation Power: 0

SQL Conundrum

I don't know a lot about sql except what I've read on the w3c website. So if anyone could point me in the right direction here it would be much appreciated.

I have just started using php within the last week because I need to have a database (MySQL) that will hold attributes of different species of snakes that can be searched from a website. Anyway, I have a checklist of colors that the person needs to click on and only the database needs to be queried based on the colors selected (each snake has a y or n for the different color columns). Something like this:

Code:

SELECT * 
FROM  `tx_snakes` 
WHERE  `browns` = y
AND  `blacks` = y
AND  `yellows` = y
AND  `pinks` = y
AND  `whites` = y

But because its a checklist I have to use PHP variables. The code below illustrates what I need to do. It s a weird mix of php and sql that I know won't work but I wrote to show what I'm trying to do - only have the and statement if the color variable is equal to checked.

Code:

$sql_command = "SELECT * FROM  `tx_snakes` 
if ($cbrown = = 'checked') { WHERE  `browns` = y }
if ($cblack = = 'checked') { AND  `blacks` = y }
if ($ctan = = 'checked') { AND  `tans` = y }";

If anyone could help a general idiot/php and sql noob it would be appreciated.

February 7th, 2012, 07:53 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 25,046

Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829

you're on the right track

three suggestions --

first, string literals have to be enclosed in quotes, so it's 'y' not y

second, ditch the unnecessary backticks

finally, start your WHERE clause with WHERE 1=1, and then you can AND whichever of the additional conditions apply

Code:

$sql_command = "SELECT * FROM tx_snakes WHERE 1=1" 
if ($cbrown == 'checked') { $sql_command += " AND browns = 'y'" }
if ($cblack == 'checked') { $sql_command += " AND blacks = 'y'" }
if ($ctan   == 'checked') { $sql_command += " AND tans   = 'y'" }

disclaimer: i don't do php and this isn't the php forum

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Viewing: Dev Shed Forums > Databases > MySQL Help > SQL Conundrum

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump