473,372 Members | 1,357 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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

Jul 17 '05 #1
7 3521
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
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

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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Robert Scheer | last post by:
Hi. I have a stored procedure on a Oracle 8.1.6 database that generates a dynamic sql statement. This stored procedure has an output parameter that needs to return a count from a view. I can...
4
by: jrefactors | last post by:
I want to distinguish between static SQL, dynamic SQL, and embedded SQL, but couldn't find too much useful resources in the web. For example, if we put SQL statements (SELECT, INSERT, UPDATE,...
2
by: klh | last post by:
We use DB2 Connect v 7.2 FP7 in Windows NT hitting a OS/390 DB2 v7.1 database. We have a Websphere (java) application that issues dynamic SQL. Most of the time when we issue dynamic SQL SELECT...
1
by: Arijit Chatterjee | last post by:
Dear Faculties, I have a query on this statement.. =============================== CREATE PROCEDURE Check_Manage ( ) DYNAMIC RESULT SETS 1 ============================== I want to know the...
0
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list...
4
by: Brian Shannon | last post by:
I have 3 combo boxes and two date text boxes on a .aspx page. The user can fill in any of the 5 controls or none to filter a datagrid. I was hoping someone could explain how to efficiently build...
13
by: salad | last post by:
Operating in A97. I didn't receive much of a response conserning Pivot tables in Access. Pivot tables are nice, but a CrossTab will work for me too. Using a Pivot table, one is actually...
1
by: john | last post by:
I'm trying to build a LINQ expression that will use a dynamic construction of a LIKE statement in the WHERE clause, it would look something like this in SQL: WHERE TaskGroup Like "*00*" OR...
0
by: Jay Douglas | last post by:
Hello, I've found some posts on creating dynamic WHERE clauses in LINQ to SQL using predicate builders and extending lamda expressions. One issue with these posts is all the examples only use a...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.