[Resolved] trouble structuring mysql-query

chaza

Lets say I have three input fields.

day
month
year

I want to be able to search with all of them or any combination of them. If I do:
SELECT * from my_table WHERE day='$day' and month='$month' and year='$year'

if all fields are populated it gives me the answer, but if one of the input fields is undefined then I get no answer.
If I change the 'and' to 'or' that won't work because if I put tuesday and may it will give all entries with tuesday and all entries with may instead of all entries with tuesday and may.

tekky

so take advantage of PHP and exlude the values you dont want to use in your search if they are null/not set....

chaza

how do you go about doing that?

tekky

although you should try writing this on your own... here you go... I'm being generous

$sql = 'some sql statement';
$where = 'WHERE ';
if ($day) { $where .= 'day='.$day;
if ($month) {$where .= (($day) ? ' and' : '') . ' month='.$month;
if ($year) { $where .= (($day || $month) ? ' and' : '') . ' year='.$year;
$sql .= $where;
$result = mysql_query($sql);

njm

Chaza,

One solution would be:

$sql = "SELECT * FROM my_table WHERE";
if ($day) $sql .= " day = '$day' ";
if ($day && ($month || $year)) $sql .=" AND ";
if ($month) $sql .=" month = '$month'";
if ($year && $month) $sql .= " AND ";
if ($year) $sql .=" year = '$year'";
if (!$day && !$month && !$year) $sql .=" 1";

Not very pretty but works.

hth, njm

chaza

thanks for your replies.
I don't quite understand what is happening here.
Can you comment it out in order for me to learn what is going on.

Thanks

tekky

$sql = 'some sql statement'; //start a query
$where = 'WHERE '; //start a where clause
if ($day) { $where .= 'day='.$day; //if day is set... append day=value
if ($month) {$where .= (($day) ? ' and' : '') . ' month='.$month; //if month set and day set, append ' and month=value' otherwise just append month=value
if ($year) { $where .= (($day || $month) ? ' and' : '') . ' year='.$year; //if day or month is set append ' and year=value' otherwise append just 'year=value'
$sql .= $where; //combine the where clause with the sql query
$result = mysql_query($sql); //make the query happen

the (($day) ? ' and' : '') is an alternate form of an if statement that fits inline really nicely with variable strings....

basically this is if ($day) insert into this string ' and' else insert into this string ''

chaza

so in 'some sql statement' would I just put what njm suggested :
SELECT * FROM my_table WHERE ?

or do I need to define day, month , year?

tekky

some sql statement would be

SELECT * FROM yourtable

chaza

for some reason it's giving me a parse error on line 44 ( the ending php bracket ( ?> ))

tekky

Originally posted by chaza
for some reason it's giving me a parse error on line 44 ( the ending php bracket ( ?> ))

those usually mean the line above it has an error.... whats above your last line?

chaza

while ($data = mysql_fetch_object($query)) {
print $data->bigpic.'<br />';
}

tekky

that line looks ok.... dunno

chaza

Thanks for your help.
I got it working now.

chaza

Acutally, no I havn't!!!

The plot thickens... I really have five input fields in one form.

this is how I have it:

$sql = "SELECT * FROM gallery WHERE";
if ($captor) $sql .= " captor = '$captor'";
if ($captor && ($month || $year || $type || $weight)) $sql .= " AND ";
if ($month) $sql .=" month = '$month'";
if ($month && ($year || $captor || $type || $weight)) $sql .= " AND ";
if ($year) $sql .=" year = '$year'";
if ($year && ($month || $captor || $type || $weight)) $sql .= " AND ";
if ($type) $sql .=" type = '$type'";
if ($type && ($month || $captor || $year || $weight)) $sql .= " AND ";
if ($weight) $sql .=" pounds >= '$weight'";
if (!$captor && !$month && !$year && !$weight && !$type) $sql .="0";
$query = mysql_query($sql);

each input works on it's own but when two or more are submitted the script fails.