• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Website Search using PHP MYSQL

Hi,

Im looking to build a website search that searches a variety of database fields in multiple tables.
I have a main category table and a sub category table, both stores main page content and descriptions, names etc. When someone types in computer for example, i want the selected fields in both tables to be searched and then return the results to the search.php page.
Example of Database Tables

Category
-----------
category_id
category_name
category_content

Sub Category
sub_category_id
sub_category_name
sub_category_content

HTML Form for Search on index.php
----------------------------------
<form  method="get" action="search.php" id="search-form">
<label for="search">Website Search</label>
<input name="search" type="text" value="" />
<input type="submit" name="submit" value="Search" id="search-button">
</form>

Open in new window

0
JiveMedia
Asked:
JiveMedia
2 Solutions
 
asafadisCommented:
Try this:

$sql = "
    SELECT
        category_id, category_name, category_content
    FROM
        Category
    WHERE
        category_id LIKE '{$_POST['search']}'
        OR category_name LIKE '{$_POST['search']}'
        OR category_content LIKE '{$_POST['search']}'

    UNION

    SELECT
        sub_category_id, sub_category_name, sub_category_content
    FROM
        `Sub Category`
    WHERE
        sub_category_id LIKE '{$_POST['search']}'
        OR sub_category_name LIKE '{$_POST['search']}'
        OR sub_category_content LIKE '{$_POST['search']}'
";

Open in new window

0
 
JiveMediaAuthor Commented:
Thanks for the comment :)

Im using dreamweaver cs4, do i put this info into the recordset advanced sql dialog box?
Sorry, still fairly new to sql.
0
 
jaxstormCommented:
you've got two pages - the original form and then another page which is search.php

Create the form as you've detailed above, then for the search form do something like this

On the search.php add the following
//gets the value from the form
$searchterm = $_GET['search'];


$sql = "SELECT category_id, category_name, category_content FROM Category WHERE category_id LIKE '$searchterm' OR category_name LIKE '$searchterm' OR category_content LIKE '$searchterm'
UNION
SELECT sub_category_id, sub_category_name, sub_category_content FROM `Sub Category` WHERE sub_category_id LIKE '$searchterm' OR sub_category_name LIKE '$searchterm' OR sub_category_content LIKE '$searchterm'";

//perform the query
$result = mysql_query($sql);

//put the query results into an array
$row = mysql_fetch_assoc($result);

//you can now print the results by echoing the array values like so
foreach ($row as $searchresults) {
//feel free to change this with any HTML you want, this is just an example
echo '<p>' . $searchresults . '</p>'
}

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jaxstormCommented:
Sorry, missed a ; from the code, change line 18 to read
echo '<p>' . $searchresults . '</p>';

Open in new window

0
 
innotionentCommented:
In addition. It's possible that you might want to add the % wildcard to your LIKE. That way you can match partial results.

example:
select * from table where foo like 'bar'
Returns only results where it matches bar

Select * from table where foo like '%bar%'
Returns results where bar is in a word.

0
 
JiveMediaAuthor Commented:
Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now