views:

286

answers:

7

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();
?>
A: 

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.

Brad
A: 

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 />";
munch
This is just wrong all over the place... unless I'm mistaken he doesn't want to get every row, he wants to search the whole table (i.e. different columns). And your "code correction" is not really improved - for one thing, now there isn't the period after `$num_found`. It's mean to come out as `1. Site Name<br />` AFAIK.
DisgruntledGoat
Good catch on the line, I missed the '.' in his code. It's now fixed. However, I'm not trying to make any assumptions about what he means when he says, "I would like to know how I can search the entire table(explore)". I suppose I gave a smart alec response because the question was vague, but that doesn't make it wrong. It answers what he asked...
munch
But I don't see where `echo $num_found.".".($row['site_name'])." <br />";` improves over `echo "$num_found." .($row['site_name'])." <br />";` . If I am not complete wrong both is correct.
Felix Kling
@Felix, `echo "$num_found."` will not show the value of the variable, but rather will show the text '$num_found.'. If it was written `echo "{$num_found}."` , with the curly brackets around it or the concatenated way I wrote it above, the output would show the value of the variable though
munch
Is it because of the dot? Because normally, PHP parses strings enclosed in `" "` and substitutes variables. You were correct if the string was inclosed in single quotes.
Felix Kling
@Felix, you've just taught me something. I had to go back to the php manual to check, but it appears I picked up some faulty information somewhere along my php journey. Probably from someone here :P His echo should work. Is it considered bad practice to do it without any separators? Because I know there are times when it won't work, eg. with variable `$var`, `$varstring` doesn't work while `{$var}string` does.
munch
I think PHP looks for the longest sequence that is a valid variable, so `$varstring` is valid but for the string `$var,string` only `$var` is a valid variable (`$var,` is not). Parsing the string for variables is considered to be slower but I don't think that it is significantly slower. I think it is more a matter of readably, sometimes putting everything between `" "` is just easier ;)
Felix Kling
+1  A: 

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?

Adrian Serafin
I think it should be "OR" instead of "AND"
Adrian
you;re right, my bad ;)
Adrian Serafin
A: 

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%';
streetparade
You do realize you can comment on people's questions, this is hardly an answer.
TravisO
A: 

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).

Felix Kling
-1 This is a comment, not an answer
Adrian
If he has copied and pasted his code, it is the answer to his question: "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?"
Felix Kling
Felix Kling
+2  A: 

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.

DisgruntledGoat
This query will take a serious hit on performance as his row count increases.
cballou
+2  A: 

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.

cballou
+1 because answer is ok but I would go with DisgruntledGoat's option: (with OR instead of AND) `$query = "select * from explore where site_name like '%".$searchterm."%'` or other_column like '%".$searchterm."%'" There is a chance MySql will do more than one full table scan...
Adrian
@Adrian - The point of my answer is that it would utilize an INDEX as opposed to all other answers. This would prevent full table scans as well as providing faster results by not utilizing an OR query.
cballou