When i would search for the keywords that i have specified in my database it will return everything from my database not just the corresponding links that have the keywords attached to the link. here is my code
<?php
$q = $_GET['q'];
$terms = explode(" ", $q);
$query = "SELECT * FROM search ";
foreach ($terms as $each){
$i=0;
$i++;
if ($i == 1)
$query .= "keywords LIKE '%$each%' ";
else
$query .= "OR keywords LIKE '%$each%' ";
}
//connect
mysql_connect("localhost", "root", "");
mysql_select_db("search");
$query = mysql_query("SELECT * FROM search");
$numrows = mysql_num_rows($query);
if ($numrows > 0){
while($row = mysql_fetch_assoc($query)){
$id = $row['id'];
$title = $row['title'];
$description = $row['description'];
$keywords = $row['keywords'];
$link = $row['link'];
echo "<h3><a href='$link'>$title</a></h3><h4>$link</h4>$description<br /><br />";
}
}
else
echo "<b>No Results Found</b><br><br>Suggestions:<br>
Make sure all words are spelled correctly.<br>
Try different keywords.<br>
Try more general keywords.";
//disconnect
mysql_close();
?>
<?php
$q = $_GET['q'];
$terms = explode(" ", $q);
//connect
mysql_connect("localhost", "root", "");
mysql_select_db("search");
$query = "SELECT * FROM search ";
$i=1;
foreach ($terms as $each){
if ($i == 1) {
$query .= "WHERE ";
$query .= "keywords LIKE '" . mysql_real_escape_string("%" . $each . "%") . "' ";
} else {
$query .= "OR keywords LIKE '" . mysql_real_escape_string("%" . $each . "%") . "' ";
}
$i++;
}
$query = mysql_query($query);
$numrows = mysql_num_rows($query);
if ($numrows > 0){
while($row = mysql_fetch_assoc($query)){
$id = $row['id'];
$title = $row['title'];
$description = $row['description'];
$keywords = $row['keywords'];
$link = $row['link'];
echo "<h3><a href='$link'>$title</a></h3><h4>$link</h4>$description<br /><br />";
}
} else {
echo "<b>No Results Found</b><br><br>Suggestions:<br>
Make sure all words are spelled correctly.<br>
Try different keywords.<br>
Try more general keywords.";
}
//disconnect
mysql_close();
?>
Fixes:
1) Removed second $query that was being defined. It selected all rows.
2) Moved initial $i declaration. It was being set back to 0 each loop.
3) Added WHERE
4) Moved $i++ after the if statement and set initial $i to 1.
5) Added mysql_real_escape_string so that data is escaped properly.
Recommendations:
I highly recommend taking a look at MySQLi (http://us2.php.net/mysqli) or PDO (http://us3.php.net/pdo)
Please let me know if this works or if you need further assistance.
A first sight, i see a couple of errors.
$i=0;
$i++;
if ($i == 1)
$i Will ALWAYS be one are. you might want to move $i = 0; BEFORE the foreach
$query = mysql_query("SELECT * FROM search");
You build a query, but in the end you're not using it. you probably want to do : $query = mysql_query($query); instead. ( and also for code clarity using a different variable name for the output ? ) .
mysql_query is deprecated. Useless you're in a hurry, check PDO
First, you're missing the WHERE keyword before the conditions. So it should be:
foreach ($terms as $i => $each){
$each = mysql_real_escape_string($each); // Prevent SQL injection
if ($i == 0)
$query .= "WHERE keywords LIKE '%$each%' ";
else
$query .= "OR keywords LIKE '%$each%' ";
}
You don't need to increment your own counter variable, you can use the array indexes from $terms.
Second, after all that work to create $query, you're not using it. You wrote:
$query = mysql_query("SELECT * FROM search");
That should be:
$query = mysql_query($query);
BTW, it's generally a bad idea to reuse variables like that, it gets confusing when you use the same variable for different things. I suggest you call the second $query something like $results.
Change this line
$query .= "keywords LIKE '%$each%' ";
By
$query .= " Where keywords LIKE '%$each%' ";
And also cnhange this line
$query = mysql_query("SELECT * FROM search");
By
$query = mysql_query($query);