Query Results Issue

[theOtherGuy]

Now I have a database set up like the following example:

 

id (int(11)) | name (varchar(20)) | food (varchar(20)) | fastfood (varchar(3)) | pricerange (int(11))

 

1              | McDonalds              | American              | Yes                            | 1

2              | Outback                  | BBQ                      | No                            | 2

3              | Mortons                  | Steakhouse          | No                              | 3

 

 

Now I have a user form set up like the following:

 

<form action="index.php" method="GET" id="sheet">

 

    <select name="name1" id="name1">

        <option value="%">No Preference</option>

        <option value="outback">Outback</option>

        <option value="mcdonalds">McDonalds</option>

        <option value="mortons">Mortons</option>

    </select>

 

    <select name="food1" id="food1">

        <option value="%">No Preference</option>

        <option value="american">American</option>

        <option value="bbq">BBQ</option>

        <option value="steakhouse">Steakhouse</option>

    </select>

 

    <select name="fast" id="fast">

        <option value="%">No Preference</option>

        <option value="yes">Yes</option>

        <option value="no">No</option>

    </select>

 

    <select name="price" id="price">

        <option value="%">No Preference</option>

        <option value="1">Cheap</option>

        <option value="2">Middle Ground</option>

        <option value="3">Expensive</option>

    </select>

 

    <input type="submit" value="Submit" id="submit"></input>

    <input type="clear" value="Clear" id="Clear"<input>

 

</form>

 

Now when I run the following query if not all the fields match, I will get no result. How can I manipulate the query to display a result based on best match when I have INT's & VARCHARS involved?

 

$query = "SELECT * FROM .$usertable WHERE $name LIKE '$name' AND $food LIKE '$food1' AND $fastfood LIKE '$fast' AND $pricerange LIKE '$price' ";

 

Fulltext search doesn't work because of the INT and 'OR' will not display the results based on the best match. Let's say the user selected 3 of the 4 options such as: BBQ | Yes | 2

 

Now the best match in that case would be Outback, however because it is trying to match up fast food as well, it will return no result. And if there are even more options it gets even more complicated

 

If anyone could shed some light on a solution for this problem I would be extremely appreciative. Thanks

[ignace]

Fulltext search doesn't work

 

That's because FULLTEXT search requires 2 things: 1) a FULLTEXT index 2) the special syntax MATCH(col) AGAINST(val)

 

Now the best match in that case would be Outback, however because it is trying to match up fast food as well, it will return no result. And if there are even more options it gets even more complicated

 

That's because you are using AND, if you replace it with OR it will return all rows that give a match. This will put Outback in the result but not at the top. To do this you will need to rank each row and sort the result by that rank.

 

Something like (untested):

 

SELECT *, (IF($name LIKE '$name', 1, 0) + IF($food LIKE '$food1', 1, 0) + ..) AS rank
..
WHERE $name LIKE '$name' OR $food LIKE '$food1' OR $fastfood LIKE '$fast' OR $pricerange LIKE '$price'
ORDER BY rank DESC

 

A more extensive/correct answer can be found here: http://stackoverflow.com/a/2108493