Using PHP to Search and Display Data in a MySQL Database
June 25th, 2007This tutorial will explain how to search a MySQL database using PHP. If you plan on using PHP & MySQL this is a core concept.
To start, create a new database using PHPMyAdmin and call it “test400″. Then execute this SQL code:
CREATE TABLE `news` (
`id` int(11) NOT NULL auto_increment,
`title` text NOT NULL,
`message` text NOT NULL,
`author` text NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
INSERT INTO `news` (title,message,author) VALUES (
'This Is A Test Title',
'This is a test message',
'BrendenWilson.com'
);
This SQL code will create a new table in your ‘test400′ database called news. It will create an ID field, a title field, a message field and an author field.
Here is the entire script. Paste this into your PHP file.
//connect to mysql
mysql_connect("localhost","username","password");
mysql_select_db("test400");
$search=$_POST["search"];
$result = mysql_query("SELECT * FROM news WHERE message LIKE '%$search%'");
while($row=mysql_fetch_array($result))
{
$title=$row["title"];
$message=$row["message"];
$who=$row["who"];
$date=$row["date"];
$time=$r["time"];
$id=$r["id"];
echo "$title
$message
$who
$date | $time
“;
}
?>
This code will allow you to search text in the ‘message’ fields of the database. Your page should look something like this:
Lets go over the different segments of PHP code.
The first segment is pretty standard. It makes a connection to the database using a username and password. When the connection is made, the database is selected.
mysql_connect("localhost","username","password");
mysql_select_db("test400");
If the user submitted a search query, then we take the search string and put it into a variable. If the user searched for “laptop”, then the $search variable will be, “laptop”.
$search=$_POST["search"];
Next we have the MySQL query. We select all of the data from the ‘messages’ field in the ‘news’ table that has anything related to what was is going to be searched.
$result = mysql_query("SELECT * FROM news WHERE message LIKE '%$search%'");
We then take our search results and display them on the page. The while function repeats the actions inside the braces{} until their is no more data left. The MySQL Fetch Array makes the data from the MySQL database usable. In the while loop the information from the database is put into variables (e.x. title=$r[”title”];). The data is then formated accordingly.
while($r=mysql_fetch_array($result))
{
//the format is $variable = $r["nameofmysqlcolumn"];
//modify these to match your mysql table columns
$title=$r["title"];
$message=$r["message"];
$who=$r["who"];
$date=$r["date"];
$time=$r["time"];
$id=$r["id"];
//displays the row
echo "$title
$message
$who
$date | $time
“;
}
Let me know if this tutorial helped you out or not by leaving me a comment.
This tutorial was created as a way to learn concepts. Do not use this on your website. It is insecure.
Good Tutorial. Worked for me.
I clicked the ad to give you a few tip cents!
So how do you sort the results by most relevancy?
What order would you want them sorted in?
how would one search all rows on the table and not just the row “message” ?
Very good, and can be easily developed further.
How do you sort the results via a particular field? eg I’m setting up a flight database with flights to a particular destination. I want people to search for a destination and have flights sorted by cheapest price. How would i acheive this? And how can i custimize the design of the outputted results?
Hi Brenden,
is it possible to have the matching element highlighted?
finaly a search function there works. Thanks! ads clicked…
Hey thanks a lot!I’m doing a web design course here in Ireland nd i’ve been tryinh to get my head around this stuff.
Really helped!
$date=$row[”date”];
$time=$r[”time”];
$id=$r[”id”];
You either use $r or $row… Currently in the first example $r isnt defined… only $row
Shane