PHP and MySQL Database Search Multiple Tables At One Time

Question

I am trying to make the form search by ID, firstname, and lastname. I want the user to type in either one in the search field and get the results from the database.

Here is the actual form I am using:

<form action="form.php" method="post"> 
<input type="text" name="term" />
<input type="submit" value="Submit" /> 
</form>

And here is the form.php

<?php
$db_hostname = 'localhost';
$db_username = 'test';
$db_password = 'test';
$db_database = 'test';

// Database Connection String
$con = mysql_connect($db_hostname,$db_username,$db_password);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db($db_database, $con);
?>

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>SpeedZone Data Search</title>
        <style type="text/css">
table { border-collapse:collapse; }
table td, table th { border:1px solid black;padding:5px; }
tr:nth-child(even) {background: #ffffff}
tr:nth-child(odd) {background: #ff0000}
</style>
    </head>
    <body>
<form action="form.php" method="post">  
<input type="text" name="term" />
<input type="submit" value="Search" />  
</form>  
<?php
if (!empty($_REQUEST['term'])) {

$term = mysql_real_escape_string($_REQUEST['term']);     

$sql = "SELECT * FROM rcrentals WHERE firstname LIKE '%".$term."%' or lastname LIKE '%".$term."%' or id = ".$term;
$r_query = mysql_query($sql); 

echo "<table border='1' cellpadding='5'>";
echo "<tr> <th>ID</th> <th>First Name</th> <th>Last Name</th> <th>Address</th> <th>City</th> <th>State</th> <th>Zip</th> <th>Phone</th> <th>DL</th> <th>Email</th> <th>Car and Controller</th> <th></th> <th></th></tr>";

// loop through results of database query, displaying them in the table
        while ($row = mysql_fetch_array($r_query)){

                // echo out the contents of each row into a table
                echo "<tr>";
                echo '<td>' . $row['id'] . '</td>';
                echo '<td>' . $row['firstname'] . '</td>';
                echo '<td>' . $row['lastname'] . '</td>';
                echo '<td>' . $row['address'] . '</td>';
                echo '<td>' . $row['city'] . '</td>';
                echo '<td>' . $row['st'] . '</td>';
                echo '<td>' . $row['zip'] . '</td>';
                echo '<td>' . $row['phone'] . '</td>';
                echo '<td>' . $row['dl'] . '</td>';
                echo '<td>' . $row['email'] . '</td>';
                echo '<td>' . $row['carcont'] . '</td>';
                echo '<td><a href="edit.php?id=' . $row['id'] . '">Edit</a></td>';
                // echo '<td><a href="delete.php?id=' . $row['id'] . '">Delete</a></td>';
                echo '<td><a href="delete.php?id=' . $row['id'] . '" onclick="return confirm(\'Confirm?\')">Delete</a></td>';
                echo "</tr>"; 
        } 

        // close table>
        echo "</table>"; 

}
?>
    </body>
</html>

Where I currently have this: It is only searching the ID. I want to be able to type in the ID, or the firstname, or the lastname, or first and last if possible.

if (!empty($_REQUEST['term'])) {

$term = mysql_real_escape_string($_REQUEST['term']);     

$sql = "SELECT * FROM rcrentals WHERE firstname LIKE '%".$term."%' or lastname LIKE '%".$term."%' or id = ".$term;

I think there are a few things I will need to change but I am confused and have lost myself in it and cannot solve it. Please help.

Barmar · Accepted Answer · 2013-06-20 01:43:28Z

1

You need to put quotes around $term when you compare it with id. Otherwise, you'll get a syntax error if it's not a number.

$sql = "SELECT * FROM rcrentals WHERE firstname LIKE '%".$term."%' or lastname LIKE '%".$term."%' or id = '".$term."'";

Also, this assumes you don't use 0 as an id. When a number is compared to a string, the string is converted to a number, and all non-numeric strings get converted to 0 and they'll match that id. If that's a problem, you should check whether $term is a number first. If it's not a number, use a query that doesn't include the id check:

$sql = "SELECT * FROM rcrentals WHERE firstname LIKE '%$term%' or lastname LIKE '%$term%'";
if (is_numeric($term)) {
  $sql .= " or id = $term";
}

answered Jun 20, 2013 at 1:43

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user2503303 Over a year ago

GENIUS! Thank you Barmar! Solved my problem if I am searching one term at a time. What if I want to search two terms at a time? Like firstname and lastname in the box? Right now it comes back empty.

Barmar Over a year ago

Do you mean they should be able to enter "John Smith" in the box, and it should check for each word in either field?

Barmar Over a year ago

Try using full-text searching instead of LIKE.

Barmar Over a year ago

Is the MySQL documentation on full-text search hard to understand?

user2503303 Over a year ago

I only know LIKE and OR - I am not sure about full-text searching.

|

user11 · Accepted Answer · 2013-06-20 01:47:48Z

0

Your query looks coorect to me. Try using braces to separate out OR conditions

$sql = "SELECT * FROM rcrentals WHERE firstname LIKE '%".$term."%' or (lastname LIKE '%".$term."%') or (id = ".$term);

answered Jun 20, 2013 at 1:47

user11

3312 gold badges3 silver badges12 bronze badges

1 Comment

Barmar Over a year ago

Parentheses are only necessary if you need to override precedence of multiple conditions, you don't need them around each condition.

Collectives™ on Stack Overflow

PHP and MySQL Database Search Multiple Tables At One Time

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related