By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,983 Members | 2,035 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,983 IT Pros & Developers. It's quick & easy.

dynamic where clause

P: n/a
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
";

Jul 17 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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
Jul 17 '05 #2

P: n/a
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
Jul 17 '05 #3

P: n/a

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

Jul 17 '05 #4

P: n/a
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
Jul 17 '05 #5

P: n/a
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.
Jul 17 '05 #6

P: n/a
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

Jul 17 '05 #7

P: n/a
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!
Jul 17 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.