Layout DB: Search Function

ID: PHP - 12
Date Added: 2006-07-15
Category: PHP
Difficulty: Hard
Author: Rei

Getting Started

The search engine that we will be creating will be like the one that I have on my layouts page. This is another complicated PHP/MySQL tutorial, and you are bound to come across errors along the way.

PHPMyAdmin

We are going to use the same table from our database as our layout database tutorial, which is layout. Perhaps this is your first time working with the database, thus you do not have the table rows filled in, therefore, dump this code into PhpMyAdmin

CREATE TABLE `layout` (
`id` INT(3) NOT NULL AUTO_INCREMENT,
`title` VARCHAR(50) NOT NULL,
`creator` VARCHAR(255) NOT NULL,
`size` VARCHAR(10) NOT NULL,
`type` VARCHAR(50) NOT NULL,
`date` date NOT NULL default '0000-00-00',
`link` VARCHAR(255) NOT NULL,
PRIMARY KEY (id));
) TYPE=MyISAM AUTO_INCREMENT=1 ;

Syntax Explanation

The code above will create the following rows in our database table:

id with the maximum of 3 characters; AUTO_INCREMENT allows MySQL to auto generate integers in ascending order (1, 2, 3, and so forth).
title with the maximum of 50 characters.
creator with the mazimum of 225 characters.
size with the maximum of 10 characters.
type with the maximum of 50 characters (Div, tables, iframes...)
date will be created to insert the current date in the order of: YYYY-MM-DD, and the default value (If you forgot to insert the date), is 0000-00-00.
link with the maximum of 255 characters.
id is our pirmary key so that MySQL will know how to order our rows, and in this case, order by id.

layout.php page

Now we will create our search form, and on this form, you can decide on what search matches will match. For example, if you wish to be able to let your visitors search the database depending upon the series, date added, and so forth.

<form action="search.php" method="POST">
<table width="100%" border="0" colspan="2" 
cellspadding="0">
<tr><td>Title</td><td><input type="text" 
name="search" id="search" size="30">
</td></tr>
<tr><td> </td><td><input type="submit" 
name="submit" value="Submit!">  
<input type="reset" name="reset" 
value="Clear!"></td></tr>
</table>

The above code will give us the following form:

Enter Keyword

There are THREE important elements in the form that we must know about.

search.php is our PHP script that will be doing the search.
name="search" is our element that will be matched with the rows in our table.
id="search" is our element that will be matched with the rows in our table.

search.php page

Create a page to show your results, I called mine search.php. Dump in the following PHP code


<?php
// Fill in your database information here
$hostname='localhost';
$user=''; // Your MySQL username.
$pass=''; // Your MySQL password
$dbase='';// Your database name.

$connection = mysql_connect("$hostname" , "$user" , "$pass"
) or die ("Can't connect to MySQL");

$db = mysql_select_db($dbase , $connection
) or die ("Can't select database.");

// Searching for matching results...
$search = $_POST["search"];
$query="SELECT * FROM `layout`
WHERE `title` LIKE '%$search%' OR `type` LIKE '%$search%' 
OR `creator` LIKE '%$search%' ORDER BY `id`";

// If query fails:
        $result= mysql_query($query
) or die
("Cannot connect to the database: " . mysql_error());
        $found=mysql_num_rows($result); 
// back slash are required. Do not edit!
echo "You have searched for \"$search\", 
and there are $found match(es) found:";
echo"<ul type="circle">";
while ($row=mysql_fetch_array($result))
{extract($row);
echo"<li><b>#$id:</b> $title by $creator and is a 
$type layout.</li>";
}
echo"</ul>";
?>

It's a long code, but I'll explain everything here:

database information you should be quite familiar with inserting the database information: login password, username, and database name.
// Searching for matching results... - MySQL will attempt to find suitable matches that match the keyword entered.
// If query fails: - if the script cannot execute the search command, an error message will be given.
WHERE `title` LIKE '%$search%' OR `type` LIKE '%$search%' OR `creator` LIKE '%$search%' mySQL will attempt to find the suitable matches that contains a part of the keyword in the title, type, and creator rows.
echo"You have searched for \"$search\", and there are $found match(es) found: if there are matches found, it will be outputted in the format set.

« back · clear ¦ reload · forward »

Site