Quick tip: mysql select

Here’s another quick tip. This time its about mysql select. Yup its just the basic mysql select but often times there are certain characters that we overlook.

Using close and open parenthesis in select queries can actually limit the results. Or give you the result that you require.

For example, we need to select all the customers which has addresses and names which matches the given conditions:

SELECT * FROM customers 
WHERE Customer_Name 
LIKE '%{$search_param}%'
OR Customer_Address LIKE '%{$search_param}%'

That’s just a simple select statement. What we did was to supply the php variable $search_param into the query.A record which matches with the given criteria will be outputted in the results.

But what if we have a delete functionality which doesn’t actually delete the given record. But instead, it just sets its status to 0. And every record which has a status of 0 must not be seen in the results whenever a user tries to search.

Normally, you would do it this way:

SELECT * FROM customers WHERE Status = 1 
AND Customer_Name LIKE '%{$search_param}%' 
OR Customer_Address LIKE '%{$search_param}%'

But guess what, it doesn’t work even if it’s a valid sql statement. It just doesn’t do what its supposed to do.

What we have to do is to add a parenthesis which will enclosed the criteria which has secondary importance.

SELECT * FROM customers 
WHERE Status = 1 
AND (Customer_Name LIKE '%{$search_param}%' 
OR Customer_Address LIKE '%{$search_param}%')

Just remember the order of operations, but backwards. Because what is inside the parenthesis will be the least important.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s