Creating a dynamic search query with PHP and MySQL

Question

I'm trying to create a dynamic search query, based on the user input.

Requirements:

A user could fill in none, some, or all fields.
The query searches in a table for a record that matches all the requirements.

Now I have done my research, and I found out multiple ways on doing this. But none of them work, and if they do, they are far from practical.

Attempt:

At the moment I'm creating a query like this:

SELECT * 
FROM assignments 
WHERE (id = $id OR id = '') 
  AND (field1 = $field1 OR field1 = '')

This query works, but only if you fill in all the fields.

I got this from a stackoverflow article, that I can't find anymore, that said:

If the user has filled in an input field it will check the first rule "id = $input" and if the user hasn't specified any input it will check for "id = '' " and when it checks for that, it will just return everything. Because it escapes the empty search rule.

But as you might already know, it doesnt work..

How would you suggest me to approach this?

Why not creating a dynamic query with PHP on your requirements? For Example an Array with all filter elements which build up your query before search? — derdida
– derdida, Commented Aug 11, 2014 at 20:23
are you using pdo or mysqli? you should be preparing your statements so this is handled properly for you... — Cheruvian
– Cheruvian, Commented Aug 11, 2014 at 20:28
See stackoverflow.com/questions/18940229/… and stackoverflow.com/questions/21691675/… — Barmar
– Barmar, Commented Aug 11, 2014 at 20:32

Scott Miller · Accepted Answer · 2014-08-11 20:36:13Z

13

Try getting all of the post vars and looping through them to see if they are valid, and then build your query

<?php
$id = $_POST[id];
$field1 = $_POST[field1];
$field2 = $_POST[field2];
$field3 = $_POST[field3];

$whereArr = array();
if($id != "") $whereArr[] = "id = {$id}";
if($field1 != "") $whereArr[] = "field1 = {$field1}";
if($field2 != "") $whereArr[] = "field2 = {$field2}";
if($field3 != "") $whereArr[] = "field3 = {$field3}";

$whereStr = implode(" AND ", $whereArr);

$query = "Select * from assignments WHERE {$whereStr}";

Something like that should handle what you need

answered Aug 11, 2014 at 20:36

Scott Miller

3322 silver badges14 bronze badges

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

1 Comment

andy Over a year ago

Thank you. All answers have been helpfull, but yours did the trick.

Daniel · Accepted Answer · 2014-08-11 20:28:07Z

2

You should start with a string like yours up to the WHERE statement, then after that you loop through all the fields the user wants to search with and add them to an array, then use the PHP function "implode" to glue the fields together with an AND statement as "glue".

Now add on the glued string to the startquery and voila!

I'd give example but on phone atm!

answered Aug 11, 2014 at 20:28

Daniel

1281 silver badge8 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:33:32Z

0

Building the query dynamically based on the responses is definitely a must. But another nice feature that allows users to find results based on even partial responses is using a MySQL REGEXP query. So for instance, if they wanted to find "maverick" in a Top Gun database, a query REGEXP = 'mav' | 'rick' would return results. This brings your search much closer to the search engine functionality that users are accustomed to.

Here's a REGEXP example, simplified.

edited May 23, 2017 at 12:33

CommunityBot

11 silver badge

answered Aug 11, 2014 at 20:31

bpeterson76

12.9k6 gold badges51 silver badges82 bronze badges

Collectives™ on Stack Overflow

Creating a dynamic search query with PHP and MySQL

Requirements:

Attempt:

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Requirements:

Attempt:

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related