Search entire table? PHP MySQL

Question

Hello, 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();
?>

Answer 1

Just add 'AND column = "condition"' to the WHERE clause of your query.

Be careful with adding lots of LIKE % conditions as these can be very slow especially if using a front wild card. This causes the RDBMS to search every row. You can optimize if you use an index on the column and only a trailing wildcard.

Answer 2

You are searching the whole table, just limiting the results to those where the site_name like '%".$searchterm."%'. If you want to search everything from that table, you need to remove the WHERE clause

Here's the corrected line. You had a few too many quotes in it.

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

Answer 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?

Answer 4

your code is very bugy for sql injection first do do this

$searchterm = htmlspecialchars($searchterm);
trim($searchterm);

next

$query = mysql_real_escape_string($query);

finaly your search looks like this

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

Answer 5

Regarding displaying the message, you have a typo in your code:

// Check if search term was entered.
if (!$serachterm)

should be:

// Check if search term was entered.
if (!$searchterm)

In the code you have written, !$serachterm always evaluates to true because you never declared a variable $seracherm (note the typo).

Answer 6

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.

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?

Regarding this - 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.

Answer 7

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.