How to filter by multiple fields in MySQL/PHP

Question

I'm writing a filter/sorting feature for an application right now that will have text fields above each column. As the user types in each field, requests will be sent to the back-end for sorting. Since there are going to be around 6 text fields, I was wondering if there's a better way to sort instead of using if statements to check for each variable, and writing specific queries if say all fields were entered, just one, or just two fields, etc.

Seems like there would be a lot of if statements. Is there a more intuitive way of accomplishing this?

Thanks!

Sounds more like you're trying to FILTER results based on what's typed, not just sorting. Sorting would just switch between ascending/descending and wouldn't require typing (unless you're making your users type out "ascending" or "descending". — Marc B
– Marc B, Commented Nov 14, 2011 at 15:28
Yeah I realized this after the first two responses. Haha, whoops... brain fart. — ThinkingInBits
– ThinkingInBits, Commented Nov 14, 2011 at 15:30
Tim S... I don't see how it's possibly dangerous when the jQuery call is sending the data to a very specific controller which filters ALL user input. I will be using LIKE queries to accomplish this... — ThinkingInBits
– ThinkingInBits, Commented Nov 14, 2011 at 16:06

Brad · Accepted Answer · 2011-11-14 15:34:09Z

3

Any initial data manipulation, such as sorting, is usually done by the database engine.

Put an ORDER BY clause in there, unless you have a specific reason the sorting needs done in the application itself.

Edit: You now say that you want to filter the data instead. I would still do this at the database level. There is no sense in sending a huge dataset to PHP, just for PHP to have to wade through it and filter out data there. In most cases, doing this within MySQL will be far more efficient than what you can build in PHP.

edited Nov 14, 2011 at 15:34

answered Nov 14, 2011 at 15:26

Brad

164k57 gold badges380 silver badges559 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ThinkingInBits Over a year ago

Brad, I don't think you understand my question. Have you ever seen the way flex sorts data? I'm trying to accomplish something similar to PHP. Above each table column for First Name, Last Name, Address, Phone Number, etc will be a text box. These fields are all optional so they may enter a telephone number, but no first name, etc. And the queries are fairly complex joins.

ThinkingInBits Over a year ago

I should have used the term filter rather than sort.

TehShrike Over a year ago

@ThinkingInBits: Brad is right, an operation like this is best suited for the database level. No matter how complex the JOINs may seem to you, the database can work with them much more quickly than any code that you can write will be able to do.

ThinkingInBits Over a year ago

I was referring to the code that actually determines which query to run... right now I'm doing it like if($first_name && $address && $phone) { do this complex join } else if ($first_name && $address && !$phone) { do this complex join } etc etc.

Your Common Sense · Accepted Answer · 2011-11-14 16:24:02Z

Since there are going to be around 6 text fields, I was wondering if there's a better way to sort instead of using if statements to check for each variable

Definitely NO.

First, nothing wrong in using several if's in order.
Trust me - I myself being a huge fan of reducing repetitions of code, but consider these manually written blocks being the best solution.
Next, although there can be a way to wrap these condition ns some loop, most of time different conditions require different treatment.

however, in your next statements you are wrong:

and writing specific queries

you need only one query

Seems like there would be a lot of if statements.

why? no more than number of fields you have.

here goes a complete example of custom search query building code:

$w     = array();
$where = '';

if (!empty($_GET['rooms']))     $w[]="rooms='".mesc($_GET['rooms'])."'";
if (!empty($_GET['space']))     $w[]="space='".mesc($_GET['space'])."'";
if (!empty($_GET['max_price'])) $w[]="price < '".mesc($_GET['max_price'])."'";

if (count($w)) $where="WHERE ".implode(' AND ',$w);
$query="select * from table $where";

the only fields filled by the user going to the query.

the ordering is going to be pretty the same way.

mesc is an abbreviation for the mysql_real_escape_string or any other applicable database-specific string escaping function

Senad Meškin · Accepted Answer · 2011-11-14 15:54:12Z

0

select * from Users

order by Creadted desc, Name asc, LastName desc, Status asc

And your records will be sorted by order from query.

First by Created desc, then by Name asc and so on.

But from your question I can see that you are searching for filtering results.

So to filter by multiple fileds just append your where, or if you are using any ORM you can do it through object methods.

But if its simple you can do it this way

$query = "";
foreach($_POST['grid_fields'] as $key => $value)
{
   if(strlen($query) > 0)
         $query .= ' and '
   $query .= sprintf(" %s LIKE '%s' ", mysql_real_escape_string($key), '%' .mysql_real_escape_string($value) .'%');  
}
if(strlen($query) > 0)
   $original_query .= ' where ' . $query;

this could help you to achieve your result.

edited Nov 14, 2011 at 15:54

answered Nov 14, 2011 at 15:28

Senad Meškin

13.8k4 gold badges39 silver badges55 bronze badges

5 Comments

ThinkingInBits Over a year ago

Sorry. I said sort, but I meant filter

Marc B Over a year ago

Nice sql injection vulnerability via the $key value... ANY data coming from the user must be escaped if it's used in a query, and that includes form field names.

Your Common Sense Over a year ago

sorry, but you keep your injection intact.

Senad Meškin Over a year ago

I don't see how is that. @Col Shrapnel

Senad Meškin Over a year ago

don't behave like that, every parameter sent by user is escaped, I don't see SQL injection, and you need to learn how to behave, if you find error, you should write like Marc B.

Peter Molnar · Accepted Answer · 2011-11-14 15:30:56Z

0

No. You cannot avoid the testing operations when sorting the set, as you have to compare the elements in the set in same way. The vehicle for this is an if statement.

answered Nov 14, 2011 at 15:30

Peter Molnar

1212 silver badges8 bronze badges

Comments

Nonym · Accepted Answer · 2011-11-14 15:47:40Z

0

Could you take a look at this?

WHERE (ifnull(@filter1, 1) = 1 or columnFilter1 = @filter1)
  and (ifnull(@filter2, 1) = 1 or columnFilter2 = @filter2)
  and (ifnull(@filter3, 1) = 1 or columnFilter3 = @filter3)
  and (ifnull(@filter4, 1) = 1 or columnFilter4 = @filter4)
  and (ifnull(@filter5, 1) = 1 or columnFilter5 = @filter5)
  and (ifnull(@filter6, 1) = 1 or columnFilter6 = @filter6)

Please let me know if I'm misunderstanding your question.. It's not like an IF statement batch, and is pretty lengthy, but what do you think?

answered Nov 14, 2011 at 15:47

Nonym

6,2971 gold badge28 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to filter by multiple fields in MySQL/PHP

5 Answers 5

4 Comments

Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related