dynamic where clause

I am having a problem using a dynamic where clause. I have a feeling
that I am overlooking something very simple, although I can't seem to
figure it out.

The error i'm getting is: You have an error in your SQL syntax. Check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'ORDER BY full_name ASC LIMIT 0,25'

Any help would be greatly appreciated.

here is my code:

$ssn = $_POST['ssn'];
$state = $_POST['state'];
$lastname = $_POST['lastname'];
$firstname = $_POST['firstname'];

$whereclause .= ($ssn)?" and ssn='$ssn'":"";
$whereclause .= ($state)?" and state='$state'":"";
$whereclause .= ($firstname)?" and firstname like '%$firstname%'":"";
$whereclause .= ($lastname)?" and lastname like '%$lastname%'":"";

$where = 'WHERE'.substr($whereclause, 4);

// Define sql to list customers
$sql_customers = "SELECT customers.*,
date_format(customers.create_date, '%m/%d/%Y') as create_date,
concat(customers.lastname,', ',customers.firstname) as
full_name,
products.description,
lookupclientstatus.status
FROM customers
INNER JOIN products ON
customers.product_id=products.product_id
INNER JOIN lookupclientstatus ON
customers.status_id=lookupclientstatus.status_id
$where
ORDER BY full_name ASC
LIMIT $from,$max_results
";

diroddi wrote:
> $whereclause .= ($ssn)?" and ssn='$ssn'":"";
> $whereclause .= ($state)?" and state='$state'":"";
> $whereclause .= ($firstname)?" and firstname like '%$firstname%'":"";
> $whereclause .= ($lastname)?" and lastname like '%$lastname%'":"";
>
> $where = 'WHERE'.substr($whereclause, 4);

You should try outputting your SQL statement to see what it looks like.
If you did, you would see something that the first term of your
WHERE clause begins with "and". (WHERE and ssn=.....) You need to keep
track of whether or not you have added a term to the WHERE clause and
only include the "and" for terms other than the first.

NM

--
convert uppercase WORDS to single keystrokes to reply

diroddi wrote:

> I am having a problem using a dynamic where clause.

So you have an SQL question, not a PHP question.

> $ssn = $_POST['ssn'];
> $state = $_POST['state'];
> $lastname = $_POST['lastname'];
> $firstname = $_POST['firstname'];
>
> $whereclause .= ($ssn)?" and ssn='$ssn'":"";
> $whereclause .= ($state)?" and state='$state'":"";
> $whereclause .= ($firstname)?" and firstname like '%$firstname%'":"";
> $whereclause .= ($lastname)?" and lastname like '%$lastname%'":"";

Uhm... that's all you check? Major security issue here!

> $where = 'WHERE'.substr($whereclause, 4);

read the documentation on implode, that's probably a better way to get
your "and"'s.

Also, what if $whereclause is empty?

> // Define sql to list customers
> $sql_customers = "SELECT customers.*,
> date_format(customers.create_date, '%m/%d/%Y') as create_date,
> concat(customers.lastname,', ',customers.firstname) as
> full_name,
> products.description,
> lookupclientstatus.status
> FROM customers
> INNER JOIN products ON
> customers.product_id=products.product_id
> INNER JOIN lookupclientstatus ON
> customers.status_id=lookupclientstatus.status_id
> $where
> ORDER BY full_name ASC
> LIMIT $from,$max_results
> ";

Do an echo $sql_customers and check this. Otherwise, feed this directly
to mysql (guessing you use that one) and see what line it reports.

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html

diroddi wrote (in part):
> The error i'm getting is: You have an error in your SQL syntax. Check
> the manual that corresponds to your MySQL server version for the
right
> syntax to use near 'ORDER BY full_name ASC LIMIT 0,25'
>
> Any help would be greatly appreciated.
>
> here is my code:
>
> $ssn = $_POST['ssn'];
> $state = $_POST['state'];
> $lastname = $_POST['lastname'];
> $firstname = $_POST['firstname'];
>
> $whereclause .= ($ssn)?" and ssn='$ssn'":"";
> $whereclause .= ($state)?" and state='$state'":"";
> $whereclause .= ($firstname)?" and firstname like '%$firstname%'":"";
> $whereclause .= ($lastname)?" and lastname like '%$lastname%'":"";
>
> $where = 'WHERE'.substr($whereclause, 4);
>
> // Define sql to list customers
> $sql_customers = "SELECT customers.*,
> date_format(customers.create_date, '%m/%d/%Y') as create_date,
> concat(customers.lastname,', ',customers.firstname) as
> full_name,
> products.description,
> lookupclientstatus.status
> FROM customers
> INNER JOIN products ON
> customers.product_id=products.product_id
> INNER JOIN lookupclientstatus ON
> customers.status_id=lookupclientstatus.status_id
> $where
> ORDER BY full_name ASC
> LIMIT $from,$max_results
> ";

Have you tried printing out your query before you execute it? SQL is
telling you that you have an error in your query. What does it look
like?

Ken

News Me wrote:
> diroddi wrote:
>
>> $whereclause .= ($ssn)?" and ssn='$ssn'":"";
>> $whereclause .= ($state)?" and state='$state'":"";
>> $whereclause .= ($firstname)?" and firstname like '%$firstname%'":"";
>> $whereclause .= ($lastname)?" and lastname like '%$lastname%'":"";
>>
>> $where = 'WHERE'.substr($whereclause, 4);
>
>
> You should try outputting your SQL statement to see what it looks like.
> If you did, you would see something that the first term of your WHERE
> clause begins with "and". (WHERE and ssn=.....) You need to keep track
> of whether or not you have added a term to the WHERE clause and only
> include the "and" for terms other than the first.
>
> NM
>

Whoops! Missed the "substr". NEVER MIND!

NM

--
convert uppercase WORDS to single keystrokes to reply

News Me wrote:
> diroddi wrote:
>
>> $whereclause .= ($ssn)?" and ssn='$ssn'":"";
>> $whereclause .= ($state)?" and state='$state'":"";
>> $whereclause .= ($firstname)?" and firstname like '%$firstname%'":"";
>> $whereclause .= ($lastname)?" and lastname like '%$lastname%'":"";
>>
>> $where = 'WHERE'.substr($whereclause, 4);
>
>
> You should try outputting your SQL statement to see what it looks like.
> If you did, you would see something that the first term of your WHERE
> clause begins with "and". (WHERE and ssn=.....) You need to keep track
> of whether or not you have added a term to the WHERE clause and only
> include the "and" for terms other than the first.
>
> NM

Even easier, do something like this: assuming that $ssn, $state,
$firstname and $slastname all have values:

$where_items[] = "ssn='$ssn'";
$where_items[] = "state='$state'";
$where_items[] = "firstname like '%$firstname%'";
$where_items[] = "lastname like '%$lastname%'";

$where = 'WHERE ' . join(' AND ', $where_items);

JP

--
Sorry, <devnull@cauce.org> is a spam trap.
Real e-mail address unavailable. 5000+ spams per month.

Thanks for the fast responses everybody.

Turns out that it was the empty $where. Hers's what I added to correct
it.

$tmp_where = 'WHERE '.substr($whereclause, 4);

if($tmp_where=="WHERE ")
{
$where="";
}
else
{
where=$tmp_where;
}

I am a novice experimenting with different things. If there is a better
way (and I'm sure there is) to use variables
to build SQL where clauses I really want to know. Also, if anybody
knows of better place to post on MySQL I really need to know.

I'm off to read about cleaning my variables from harm, intentional or
otherwise. I'm going to start with addslashes() and stripslashes(). Any
other ideas?

Thanks again

diroddi wrote:
> Turns out that it was the empty $where. Hers's what I added to correct
> it.
>
> $tmp_where = 'WHERE '.substr($whereclause, 4);
>
> if($tmp_where=="WHERE ")
> {
> $where="";
> }
> else
> {
> where=$tmp_where;
> }

Ugly hack:

You know $whereclause is either empty or " and x1=y1[ and x2=y2[ ...]]";
if you do

'WHERE 1' . $whereclause

you get something like

'WHERE 1' or
'WHERE 1 and id=6 and cat=4'

so you can get rid of the $tmp_where and substr() call and do

$sql_customers = "SELECT ...
...
WHERE 1$whereclause
ORDER BY ...";


Please don't hit me! I said it was an ugly hack :-)

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!