steve Kim steve Kim - 1 month ago 5
MySQL Question

PHP Query multiple search

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

For example:

  1. (Most important): name

  2. area

  3. color

  4. shape

  5. (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?


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.