dynamic where clause

[diroddi]

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
";

[News Me]

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

[John Bokma]

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

[Ken Robinson]

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]

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

[Jan Pieter Kunst]

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, <de*****@cauce.org> is a spam trap.
Real e-mail address unavailable. 5000+ spams per month.

[diroddi]

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

[Pedro Graca]

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!