Hi, I'm having two issues with my Search engine for my website. It is created to search through my database (it doesn't crawl through pages). The first problem I'm having is when I type in for example:
Super Mario Bros
and there is a row in my table with a field called Super Mario Brothers
this row is not included as a successful result to the search. I need to somehow modify my code to search every word in every table cell in the database.
So another example. I have 5 rows, each with one cell, named as follows:
One
Two
Three
Four
Five
If I was to type in the search box:
One Two Three Four Five
it should display all rows (it obviously doesn't do that right now lol)
The second issue has to do with my sql query.
It looks like this:
$query = "select * from sheets where artist like \"%$trimmed%\" OR title like \"%$trimmed%\"
order by artist";
$numresults=mysql_query($query);I need this query to search in the columns artist and title (like it is doing above) AS WELL as search only those rows that have an active status set to 'yes'.
I tried to type something like:
$query = "select * from sheets where active='yes' && artist like \"%$trimmed%\" OR title like \"%$trimmed%\"
order by artist";but this obviously causes problems. How do i require the query to include rows that are active, but have it look through artist OR title as well?
Here is my code below. Any insight appreciated. Will be working on it til someone is available. Thanks
<?php
include_once('inc/functions.php');
// Get the search variable from URL
$var = @mysql_safe($_GET['q']) ;
$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=10000;
// check for an empty string and display a message.
if ($trimmed == "")
{
$error = "<tr><td colspan='2' style='text-align: center; border-style: solid; border-color: #f43636; background-color: #fe6a6a;'><strong>Type In A Sheet To Search For</strong></td></tr>";
}
// check for a search parameter
if (!isset($var))
{
$error = "<tr><td colspan='2' style='text-align: center; border-style: solid; border-color: #f43636; background-color: #fe6a6a;'><strong>Type In A Sheet To Search For</strong></td></tr>";
}
// Build SQL Query
$query = "select * from sheets where artist like \"%$trimmed%\" OR title like \"%$trimmed%\"
order by artist";
$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
// If we have no results, DISPLAY ERROR LETTING USER KNOW THE SHEET WAS NOT FOUND. INCLUDE LINK TO ALLOW THEM TO REQUEST SHEET
if ($numrows == 0)
{
// If search was not found
$error = "<tr><td colspan='2' style='text-align: center; border-style: solid; border-color: #f43636; background-color: #fe6a6a;'><strong>Unfortunately that sheet was not found, however, please request it by clicking below</strong></td></tr><tr><td colspan='2' style='text-align: center; border-left-style: solid; border-bottom-style: solid; border-right-style: solid; border-color: #f43636; background-color: #f5f5f5;'><a href='request.php'>Request A Sheet Here</a></td></tr>";
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}
// get results
$query .= " limit $s, $limit";
$result = mysql_query($query) or die("Couldn't execute query");
$search = " ";
$break = "<br />";
if($var!=""){
$search = "Search:";
$break = "";
}
?>
<br /><div id='headsearch'></div>
<div style="width: 210px; margin-left: auto; margin-right: auto; text-align: center;">
<form name="form" action="search.php" method="get">
<div style="float: left;"><input type="text" name="q" /></div>
<div style="float: right;"><input type="image" src="img/search.png" alt="Search" name="Submit" value="Search" /></div>
</form>
</div>
<?php
// display what the person searched for
echo "<center><div style='width: 210px; margin-left: auto; margin-right: auto; text-align: center;'>$search <span style='color: #6aa504; margin-left; auto; margin-right: auto;'>" . stripslashes($var) . "</span></div></center>";
?>
<?php
// begin to show results set
$count = 1 + $s ;
$greenboxleft = "greenboxleft";
$greenboxright = "greenboxright";
$grayboxleft = "grayboxleft";
$grayboxright = "grayboxright";
$colorvalue = 0;
echo "$break<table width='700px' align='center' style='border-collapse:separate;
border-spacing:0px;'><th style='background-color: #93DB70; border-bottom-style: solid; border-color: #6aa504;'>Artist</th><th style='background-color: #93DB70; border-bottom-style: solid; border-color: #6aa504;'>Title</th>";
if($error==""){
// now you can display the results returned
while ($row= mysql_fetch_array($result)) {
$artist = $row["artist"];
$title = $row["title"];
if(($colorvalue%2)==0){
$styleleft = $greenboxleft;
$styleright = $greenboxright;
}
else{
$styleleft = $grayboxleft;
$styleright = $grayboxright;
}
echo "<tr>";
echo "<td align='center' width='350px' id='$styleleft'><div id='songsboxleft'><strong>". ucwords($row['artist']). "</strong></div></td>";
echo "<td align='center' width='350px' id='$styleright'><div id='songsboxright'><a target='_blank' name='downloadclick' href='download.php?sheet=".$row['url']."&artist=".$row['artist']."&title=".$row['title']."'>" .ucwords($row['title']). "</a></div></td>";
echo "</tr>";
$colorvalue++;
}
}
else{
echo $error;
}
echo "</table>";
?>