[PHP] Search and retrieve/extract data from a MySql Database - WORKS ;)

So for the past two days i have been trying to implement a simple search on a MYSQL database and retrieve the "table data" if the search query matches any word in a database and i couldnt find ONE single site which would allow me to do this exactly. After wasting many hours, i figured it out myself :) So here the low down on how you can too search and extract specified data from a MYSQL database.

First things first.. as a beginner you should be able to know what is MYSQL and at least have created a database and a table in MYSQL and have accessed PHPmyAdmin.


TUTORIAL:

Lets say there are 3 coloumns in your MYSQL database table named "fname", "lname" and "info" with respect to a person's FIRSTNAME, LAST NAME and his INFORMATION. So if you want to search for a particular "first name" and extract his details, i.e., Fname, Lname and INFO. Then here is how you do it.



First you would need a form(HTML) to enter the data to search in the MYSQL Database and a PHP form to process it.

(Download link to a sample search form in HTML at the bottom of this post), below is the PHP code the process the input from the HTML search form



------------------------------searchit.php------------------------------------


//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","database_user","password");

//select which database you want to edit
mysql_select_db("database_name");

$search=$_POST["search"];

//get the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search
$result = mysql_query("SELECT * FROM tablename WHERE fname LIKE '%$search%'");

//grab all the content
while($r=mysql_fetch_array($result))
{
//the format is $variable = $r["nameofmysqlcolumn"];
//modify these to match your mysql table columns

$fname=$r["fname"];
$lname=$r["lname"];
$info=$r["info"];

//display the row
echo "$fname
$lname
$info
";
}
?>

------------------------------searchit.php------------------------------------



Now save the above PHP code as searchit.php and make sure you enter the database details. The most important code in this above script is

$result = mysql_query("SELECT * FROM tablename WHERE fname LIKE '%$search%'");

So make sure you enter the correct name of the table(tablename) in the above code. Also if you want to search for other coloumns other than the fname(FIRSTNAME) then just change the coloumn name (in the above code we have used fname) So if you want to sort out users using last names then just change fname to lname in the above PHP code.

Thats it.. Simple search in MYSQL using PHP :)

DOWNLOAD the files (search.html and searchit.php) from here:
http://www.mediafire.com/?sharekey=12fd75b0c6e2e35091b20cc0d07ba4d20fad65698c0aef0b

If you have any doubts leave a comment and i will get back to you.