Re: Need help with PHP & MySQL

Lisa

Suggestion:

When developing an sql statement in this manner, instead of banging it
against the database and getting not too helpful errors, instead just write
it out to your screen:

Then you can see it visually. Once you feel confident that the syntax is
correct, then bang away again at the database.

You can also copy the sql string off the web page and run that directly in
mySQL, though I'm not sure the errors returned are any better there; with
some database products you can make good headway that way because they
return error messages more helpful in their own interface than via the web.

Mike

> When you have multiples like that, you need to first check whether there
> should even by a WHERE in the query.  If all the clauses are empty, then
the
> WHERE stands alone and that is a BAD thing.
>
> Try this:
>
> $where_clause=Array();
>
> if (isset($alpha)&&!empty($alpha)) $where_clause[] =" Alpha='$alpha' ";
> if (isset($county)&&!empty($county)) $where_clause[] =" County='$country'
";
> if (isset($cuisine)&&!empty($cuisine)) $where_clause[] ="
Cuisine='$cuisine'
> ";
> if (isset($price)&&!empty($price)) $where_clause[] =" Price='$price' ";
>
> if (sizeof($where_clause)) {
>  $final_where_clause=" WHERE ".join(" AND ",$where_clause);
> }
>
> $result=mysql_query(
>  "SELECT *
>   FROM Restaurants
>   $final_where_clause
>   ORDER BY Name
>  ");
>
> I didn't test it, again, of course, so watch for my typos!
>
>
> D
>
>
>
> ----- Original Message -----
> From: "Lisa Bradshaw" <zibbler(at)web-design-cs.com>
>
>
> Okay, I'm getting closer. I used your suggestion and it worked great, so I
> took it a step further to include the other elements in the form, then it
> didn't work :-(   I get a warning that says: Warning: Supplied argument is
> not a valid MySQL result resource. Here's the code I used. Maybe you can
> spot something I did wrong?
>
>
> if (isset($alpha)&&!empty($alpha)) $alpha_clause=" AND Alpha='$alpha' ";
> else $alpha_clause='';
>
> if (isset($county)&&!empty($county)) $county_clause="County='$county' ";
> else $county_clause='';
>
> if (isset($cuisine)&&!empty($cuisine)) $cuisine_clause=" AND
> Cuisine='$cuisine' ";
> else $cuisine_clause='';
>
> if (isset($price)&&!empty($price)) $price_clause=" AND Price='$price' ";
> else $price_clause='';
>
> $result=mysql_query("SELECT * FROM
> Restaurants WHERE $county_clause $alpha_clause $cuisine_clause
> $price_clause ORDER BY Name");
>
>

HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA