CODEWITHSUNDEEP
phpmysqlsearchphpmyadmin
FoamyMedia
FoamyMedia

Reputation: 496

Search function on website php sql

I have a simple search box on my website which for now only produces results based on certain criteria being met.

if(isset($_GET['action'])&&isset($_GET['searchbox'])) {
    if($_GET['searchbox']!='') {
        $result = mysql_query("SELECT * FROM `restaurants` WHERE `restaurant` LIKE '%{$_GET['searchbox']}%'");  // RESULT IS FIND EVERYTHING THAT MATCHES SEARCH FROM TABLE //
    } else {
        $result = mysql_query("SELECT * FROM `restaurants`");
    }
}

<form action="search.php" id="searchform" method="get" class="searchbox-container">
    <input type="text" id="searchbox" name="searchbox" class="searchbox" />
    <input type="submit" class="searchbox-btn" value="Search" />
    <input type="hidden" name="action" value="search" />

What i want is for the search box to return a result from the database from all table data not just specific data such as restaurant names.

I also want it to work so that if someone searches for "sto" then results such as "stoke" will show up, so the search doesnt have to be exactly what is in the database.

excuse the code if it is wrong or out of date, this was taken from a university tutorial which is a little old.

Upvotes: 0

Views: 4430

Answers (1)

Nick Cardoso
Nick Cardoso

Reputation: 21763

There's no way to just search everything in a database using MySql, you'll still need to choose which columns to search in which tables. You can search different data with different queries then join the results in PHP or do some kind of JOIN. If you're not careful though you'll end up with something entirely inefficient.

One more note - this should already match partial words as you have it - the %'s around your search term in the LIKE clause means 'any characters in this position'

To search more fields in your restaurant, you could start with the following (if you have a slogan for instance):

$result = mysql_query("SELECT * FROM `restaurants` WHERE `restaurant` LIKE '%{$_GET['searchbox']}%' OR `slogan` LIKE '%{$_GET['searchbox']}%'");

You should not be injecting the user input directly into the SQL though, it leaves you open to attack!

To search more fields in other tables using a JOIN, you could start with the following (if you have a food table for instance):

$result = mysql_query("SELECT `restaurants`.* FROM `restaurants` LEFT JOIN `food` ON `food`.`restaurant_id` = `restaurant`.`id` WHERE `restaurant` LIKE '%{$_GET['searchbox']}%' OR `food` LIKE '%{$_GET['searchbox']}%'");

You can look at these two pages if you need help with what I've just given you:

http://dev.mysql.com/doc/refman/5.0/en/select.html

http://dev.mysql.com/doc/refman/5.0/en/join.html

Upvotes: 3

Related Questions