Simple MySQL Search Query
Posted by Nessa | Posted in code,mysql,php,scripts,tutorials | Posted on July 4, 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 />";
}
?>
No related posts.
this is also the #1 cause of SQL Injection vulerabilities in php web applications, so please, by all means, sprinkle this code throughout every last bit of your website.. :) — or you could fix it by simply changing the line:
$lastName = $_POST[’lastName’];
to
$lastName = ereg_replace(“[^a-z0-9A-Z]“, “”, $_POST[’lastName’] );
granted this is not the best practice and not the best action, however, it will save you alot of grief if you are doing this on some type of publicly accessible page.
DOH! Actually, I’d prefer to just turn on register globals and not use $var = $_POST at all =D
Thanks for pointing that out…you just made me realize how lazy of a coder I am, and I’ve now lost all ambition and hope in life.
hehehe
Handy Dandy
glad I could be of help.. :) I aim to please… anyhow, the real problem is that people will copy and paste said code into their application and leave themselves open to attack, I am sure you are aware of the security issues, but the people who are googling for someone to solve their code problems may not be… :) anyhow, I am sure I will criticize more of your code at some point in time *G*
You can always go criticize mine too if you want — although I haven’t updated my blog since January (looks like I am the lazy one….)
you could even do this to protect against sql injection:
$lastName = mysql_real_escape_string($_POST[’lastName’]);
taken from php.net:
mysql_real_escape_string() calls MySQL’s library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ‘, ” and \x1a.
hmm… I am having some problems with this. When i search using the code above I only recieve one result. And that’s if I have more than one persons with same lastname. Example: I know I have 2 persons with the lastname: “Planitzer”. But when I search on the name only one is showing. Another problem is that this one person is the only one I can find. I have another person named “Jensen”. But the search function can’t find him and is showing no results.
Maybe it’s just me and my coding.. I’m still a new one in this area :)
hiiiiii
I would also recommend creating a blacklist of banned characters – % is a big one as it is the SQL wildcard. Enter that as your last name and you will receive all the database results. ;)