Search entire table? PHP MySQL

Question

I have made the following search script but can only search one table column when querying the database:

$query = "select * from explore where site_name like '%".$searchterm."%'";

I would like to know how I can search the entire table(explore). Also, I would need to fix this line of code:

echo "$num_found. ".($row['site_name'])." <br />";

One last thing that is bugging me is when I push the submit button on a different page I always displays the message "Please enter a search term." even when I enter in something?

Thanks for any help, here is the entire script if needed:

<?php
// Set variables from form.
$searchterm  = $_POST['searchterm'];
trim ($searchterm);

// Check if search term was entered.
if (!$serachterm)
{
    echo "Please enter a search term.";
}
// Add slashes to search term.
if (!get_magic_quotes_gpc())
{
    $searchterm = addcslashes($searchterm);
}

// Connects to database.
@ $dbconn = new mysqli('localhost', 'root', 'root', 'ajax_demo');
if (mysqli_connect_errno())
{
    echo "Could not connect to database. Please try again later.";
    exit;
}
// Query the database.
$query = "select * from explore where site_name like '%".$searchterm."%'";
$result = $dbconn->query($query);

// Number of rows found.
$num_results = $result->num_rows;
echo "Found: ".$num_results."</p>";

// Loops through results.
for ($i=0; $i <$num_results; $i++)
{
    $num_found = $i + 1;
    $row = $result->fetch_assoc();
    echo "$num_found. ".($row['site_name'])." <br />";
}

// Escape database.
$result->free();
$dbconn->close();
?>

Solution 1

Contrary to other answers, I think you want to use "OR" in your query, not "AND":

$query = "select * from explore where site_name like '%".$searchterm."%' or other_column like '%".$searchterm."%'";

Replace other_column with the name of a second column. You can keep repeating the part I added for each of your columns.

Note: this is assuming that your variable $searchterm has already been escaped for the database, for example with $mysqli->real_escape_string($searchterm);. Always ensure that is the case, or better yet use parameterised queries.

Similarly when outputting your variables like $row['site_name'] always make sure you escape them for HTML, for example using htmlspecialchars($row['site_name']).

One last thing that is bugging me is when I push the submit button on a different page I always displays the message "Please enter a search term." even when I enter in something?

Make sure that both forms use the same method (post in your example). The <form> tag should have the attribute method="post".

Also, what is wrong with the line of code you mentioned? Is there an error? It should work as far as I can tell.

Solution 2

A UNION query will provide results in a more optimized fashion than simply using OR. Please note that utilizing LIKE in such a manner will not allow you to utilize any indexes you may have on your table. You can use the following to provide a more optimized query at the expense of losing a few possible results:

$query = "SELECT * FROM explore WHERE site_name LIKE '".$searchterm."%'
          UNION
          SELECT * FROM explore WHERE other_field LIKE '".$searchterm."%'
          UNION
          SELECT * FROM explore WHERE third_field LIKE '".$searchterm."%'";

This query is probably as fast as you're going to get without using FULLTEXT searching. The downside, however, is that you can only match strings beginning with the searchterm.

Solution 3

To search other columns of table you need to add conditions to your sql

$query = "select * from explore where site_name like '%".$searchterm."%' or other_column like '%".$searchterm."%'";

But if you don't know that I would strongly advise going through some sql tutorial...

Also I didn't see anything wrong with this line

echo "$num_found. ".($row['site_name'])." <br />";

What error message are you getting?