Simple MySQL Search Query
Posted by Nessa | Posted in Uncategorized | Posted on 04-07-2007
8
If you use MySQL to keep a ton of records, it might be nice to be able to search for the particular entry you’re looking for via a simple form on your site. To set this up, we’ll make two scripts — one being the form itself, the other being script that executes the MySQL query.
In this example I created a simple form to query a database to look a person’s last name from a database column in an ‘addressbook’ database. First, we need to create the form. This is just a simple html file with a single input field:
<html>
<body>
<h4>Enter Last Name:</h4>
<form action="query.php" method="post">
Server: <input name="lastName" type="text" />
<input type="submit" value="Submit" />
</form>
</body>
</html>
Here i named the form field ‘lastName’, which will be the variable passed on to the php script and returned later on, and that the form action is set to ‘query.php’, which is the name of the script processing the form. Next, create a file called query.php:
In the first section we need to define a few database variables to allow the script to connect to the database:
<?php
// Make a MySQL Connection
$dbhost = "localhost";
$dbname = "database_name";
$dbuser = "database_user";
$dbpass = "password";
Next, we need to define the posted variable ‘lastName’, which we created in the form to allow that variable to pass into this script. If you have register_globals turned on (which is a BAD idea), you don’t need to do this.
$lastName = $_POST['lastName'];
Now for actual query itself. The syntax you use to search a database table is as follows:
SELECT <what info> FROM <table> WHERE <column>='<search term'>
So in that case, I want to select everything from the ‘names’ table where the last name is equal to what I search for, denoted by the variable ‘lastName’
$query = "SELECT * FROM names WHERE lastname='$lastName'";
Similarly if you wanted to search two tables in one query you can just use the UNION command like so:
$query = "SELECT * FROM name WHERE lastname='$lastName' UNION SELECT * FROM morenames WHERE lastName='$lastName'
Now that all that crap is defined, create the database link:
$dblink = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $dblink);
?>
Now you can echo the results back into an array (in case there is more than one entry):
<h2> Query Results for <?php echo($lastName); ?> : </h2>
<?php
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['lastName']. " ", $row['firstName'];
echo "<br />";
}
?>
To explain above, the query is run against the database and the results are fetched as an array. The row(s) contained the search terms are then displayed to the screen based on the colums specified, which in this case are ‘firstName’ and ‘lastName’
In case you’re on the slower end, here’s the entire query.php script:
<?php
// Make a MySQL Connection
$dbhost = "localhost";
$dbname = "database_name";
$dbuser = "database_user";
$dbpass = "password";
$lastName = $_POST['lastName'];
$query = "SELECT * FROM names WHERE lastname='$lastName'";
$dblink = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname, $dblink);
?>
<h2> Query Results for <?php echo($lastName); ?> : </h2>
<?php
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['lastName']. " ", $row['firstName'];
echo "<br />";
}
?>
This is a simple script derived from the cPanel process that generates full account backups, only in PHP form. This makes the task of backing up your site easier and more reliable by allowing you to generate and maintain your own full site backups.
