PHP Query multiple search

Question

For my php sql search query, I have 5 different search criteria (from most important to least).

For example:

(Most important): name
area
color
shape
(least important): time

For query, I have the following:

$ajax_data = $_POST['data']; 
$name      = $ajax_data [0]; 
$area      = $ajax_data [1]; 
$color     = $ajax_data [2]; 
$shape     = $ajax_data [3]; 
$time      = $ajax_data [4]; 

$option  = "WHERE name = '$name' AND area = '$area' AND color = '$color' AND shape = '$shape' AND time = '$time'"; //Not complete
$query   = "SELECT * FROM $table $option limit 10;

Here is what I am trying to do.

Using the 5 criteria (name, area, color, shape and time), I want to do a query search to find most related data, meaning it should match the search criteria for the best results.

For example, following search criteria was sent via ajax:

Ajax search criteria: Earth, Canada, White, Square, Today

Then, I want to do a query search for the identical criteria.

However, there are occasions where the identical data do not exist.

In the scenario where it doesn't, I want to remove the least important criteria and do another search and so on till I have 10 results.

Any suggestions how I can write the query for this?

Obviously he doesn't know PDO for asking the question here. Don't need to be condescending. — HEYHEY
– HEYHEY, Commented Sep 22, 2016 at 13:12

James Scott · Accepted Answer · 2016-09-22 14:17:30Z

1

Could you try an approach like this where you build up a numeric field in the table that keeps track of the number of matches, then order descending on this field and apply the LIMIT at then results?

Apologies for the pseudocode, it should be enough to give you the idea.

$option  = "WHERE name = '$name' AND area = '$area' AND color = '$color' AND shape = '$shape' AND time = '$time'"; //Not complete
$order = "ORDER BY matchQuality DESC";


$query   = "SELECT *, IF(name = '$name', 1, 0) + IF(area = '$area', 1, 0) + IF(color = '$color', 1, 0) + IF(shape = '$shape', 1, 0) + IF(time = '$time', 1, 0) matchQuality FROM $table $option $order limit 10;";

Let me know if you have any further requirements.

Regards,

James

answered Sep 22, 2016 at 14:17

James Scott

1,0821 gold badge10 silver badges19 bronze badges

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

2 Comments

Steve Kim Over a year ago

Thank you James! I got the idea and will give it a try! Appreciate it!

James Scott Over a year ago

Pleasure Steve!

Collectives™ on Stack Overflow

PHP Query multiple search

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related