Re: Dynamic Queries
| From: | Matt McClanahan | Date: | Mon, 27 Nov 2000 23:24:33 +0000 |
| Subject: | Re: Dynamic Queries | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-27496@lists.php.net to get a copy of this message | ||
On Mon, 27 Nov 2000, Jamie Jackson wrote:
> Hello,
>
> Newbie question. What's the best, most elegant way to write dynamic
> queries? Keep in mind my background is in ColdFusion (and I'm using
> PHP3 and MySQL).
>
> Obviously, this is NOT the way to do it, but it'll give you an idea of
> what I'm up to:
>
> $str_sql = "
> select *
> from pers_list2
> WHERE
> if (isset($fname)) {
> fname = '$fname'
> }
> if (isset($fname) && isset($lname)) {
> AND
> }
> if (isset($lname)) {
> lname = '$lname'
> }
> order by lname
> ";
You're on the right track. However the conditionals need to be outside of
the quotes. The use of .= concatenates onto the existing value of a
variable. Also I moved the 'where' into a condition, so that the query
functions properly if neither $fname nor $lname are set.
$sql = "select * from pers_list2";
if (isset($fname) || isset($lname))
$sql .= ' WHERE ';
if (isset($fname))
$sql .= "fname = '$fname'";
if (isset($fname) && isset($lname))
$sql .= ' AND ';
if (isset($lname))
$sql .= "lname='$lname'";
$sql .= ' ORDER BY lname';
Matt