PHP - Search Problems

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

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 = "&nbsp;";
  $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>";  
?>

Hi,

i have the following problem. I use a script to enter data in a mysql database and for now i only have three variable...

Quote

<h2>Invoeren</h2>
<form action="insert.php" method="post" class="cmxform">
  <fieldset>
   <legend>Gegevens</legend>
      <table border="2">
      <tr>
         <td>1. </td>
         <td><label for="merk">merk:</label></td>
         <td><input id="merk" name="merk" /></td>
      </tr>
      <tr>
         <td>2. </td>
         <td><label for="cpu">cpu:</label></td>
         <td><select name="cpu" id="cpu" />
            <option value="">Maakt niet uit</option>
            <option value="amd">amd</option>
            <option value="intel">intel</option>
         </select></td>
      </tr>
      <tr>
         <td>3. </td>
         <td><label for="hdd">hdd:</label></td>
         <td><select name="hdd" id="hdd" />
            <option value="">Maakt niet uit</option>
            <option value="160GB">160GB</option>
            <option value="320GB">320GB</option>
            <option value="500GB">500GB</option>
         </select></td>
      </tr>
   </table>
    <input type="submit" value="Verstuur" />
</fieldset> 
</form>



Data goes in to the database correctly but now i use the following search script...

Quote


<h2>zoeken</h2>
<form action="search.php" method="post" class="cmxform">
  <fieldset>
      <legend>Gegevens</legend>
      <table border="2">
      <tr>
         <td>1. </td>
         <td><label for="merk">merk:</label></td>
         <td><input id="merk" name="merk" /></td>
      </tr>
      <tr>
         <td>2. </td>
         <td><label for="cpu">cpu:</label></td>
         <td><select name="cpu" id="cpu" />
            <option value="0">Maakt niet uit</option>
            <option value="amd">amd</option>
            <option value="intel">intel</option>
         </select></td>
      </tr>
      <tr>
         <td>3. </td>
         <td><label for="hdd">hdd:</label></td>
         <td><select name="hdd" id="hdd" />
            <option value="0">Maakt niet uit</option>
            <option value="160GB">160GB</option>
            <option value="320GB">320GB</option>
            <option value="500GB">500GB</option>
         </select></td>
      </tr>
   </table>
    <input type="submit" value="Verstuur" />
  </fieldset>
</form>



The values go correct to the search script because i checked this with an echo function. The search.php loks like this

Quote

<?php
    $con = mysql_connect("localhost","admin","t1tan1um");
    if (!$con)
    {
    die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("model", $con);
   
   $qry = "SELECT * FROM eigenschappen WHERE 1=1";
      if (isset($_POST['merk']) && $_POST['merk'] != 0) {
      $qry .= " AND merk='" . mysql_real_escape_string($_POST['merk']). "'";
      }
      if (isset($_POST['cpu']) && $_POST['cpu'] != 0) {
      $qry .= " AND cpu='" . mysql_real_escape_string($_POST['cpu']). "'";
      }
      if (isset($_POST['hdd']) && $_POST['hdd'] != 0) {
      $qry .= " AND hdd='" . mysql_real_escape_string($_POST['hdd']). "'";
      }
                  
      $result = mysql_query($qry) or die(mysql_error()); // lelijke foutmelding, zelf mooier maken of $result door een if statement halen
      
      while($row = mysql_fetch_array($result))
      
      {
      echo $row['merk'] . " " . $row['cpu'] . " " . $row['hdd'];
      echo "<br />";
      }
?>



When i search for hdd wich is last in de search list the query only returns the records wich contain that value. When the condition has to be for example Intel cpu AND 500GB it returns everyting with 500GB but also every kind of CPU.

Anyone?

Hi, I'm very new to PHP and have been working on a small project that is a search engine connected to a mysql database.  I can't seem to display the results from the search across multiple pages, when I click the Next button it just brings me to a blank page with no content except for my search form on top.  I have a feeling that my problem lies in how I'm passing variables from one page to another as I realize that the URL of page 2 doesn't have the users search anymore.  What do I need to change to get multiple pages working?
Code: [Select]
<?php
if(isset($_GET['submit'])){
if(isset($_GET['name'])){
if(preg_match("/^[  a-zA-Z0-9]+/", $_GET['name'])){
$name=$_GET['name'];

// Connect to database
$db=mysql_connect("localhost", "root", "1234") or die ('Cannot connect: ' . mysql_error());
// Select database
$mydb=mysql_select_db("mydb");

// Check page num, if no page num set to 1
if(isset($_GET['pagenum'])){
$pagenum = $_GET['pagenum'];
}
else{
$pagenum = 1;
}

// Query the database table
$sql="SELECT  id, ProductName, Price FROM testdb WHERE ProductName LIKE '%" . $name ."%'";

// Run query 
$result=mysql_query($sql);

// Check to see if any results were found, if not, tell user
$resultnum = mysql_num_rows($result);

// Number of results per page
$page_rows = 4;

// Page number of previous page
$last = ceil($resultnum/$page_rows);

// Make sure page number isn't below 1 or above maximum
if($pagenum < 1){
$pagenum = 1;
}
elseif($pagenum > $last){
$pagenum = $last;
}

// Set range to display in query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; 

$sql2 = "SELECT  id, ProductName, Price FROM testdb WHERE ProductName LIKE '%" . $name ."%' $max";

$data_p = mysql_query($sql2);

if($resultnum == 0){
echo "<p class='p1'>No results found for query: " . $name . "</p>";
}

else{

echo "<table class='sortable'>";
echo "<thead>";
echo "<tr>";
echo "<th>" . "Model Number" . "</th>";
echo "<th>" . "Product" . "</th>";
echo "<th>" . "Price" . "</th>";
echo "</tr>";
echo "</thead>";

// Loop through results
$bg = '#9D9D9D';
while($row=mysql_fetch_array($data_p)){
$bg = ($bg=='#9D9D9D' ? '#C7C7C7' : '#9D9D9D');
$ProductName=$row['ProductName'];
$Price=$row['Price'];
$ID=$row['id'];

// Display results of array
echo "<tr bgcolor='" .$bg . "'>";
echo "<td width='30%', align='center'>" . " " . "</td>";
echo "<td width='60%', align='center'>" . "<a href=\"search.php?id=$ID\">" . $ProductName . "</a></td>";
echo "<td width='10%', align='center'>" . $Price . "</td>\n";
echo "</tr>";
echo "<tr><td></td></tr>";
}
echo "</table>";

echo "<p>";
echo " --Page $pagenum of $last-- </p>";

if($pagenum == 1){
}
else{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";
echo " ";
$previous = $pagenum-1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";
}

if ($pagenum == $last){
}
else{
$next = $pagenum+1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
echo " ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";
}
}
}
else{
echo "<p class='p2'>Please enter a query.</p>";
}
}
}
?>
The search form looks like this...
Code: [Select]
<form  method="get" action="search.php"  id="searchform">
      <input  type="text" name="name" size = "42">
      <input  type="submit" name="submit" value="Search" STYLE="font-family: Arial, Helvetica, sans-serif; font-weight: bold;">
    </form>
Thanks.

Is there a way to search PHP Coding Help for problems with passwords scripts? In other words I would like to see every thread where the user is having problems getting his password script to work. You know what I get if I search on just password.

Thanks

Curtis

Hello,

I adapted the pagination script from the tutorial (great instruction and detail) from this site and it works perfectly. However, I added a search engine to it and it works like it should. The problem comes in with the following SQL query:
Code: [Select]
$sql = "SELECT COUNT(*) FROM qa";

If i modify it to fit my query, the number at the bottom of the page disappears and the > >> links go to blank pages. Also all queried rows show on one page and doesn't stop at ten. How do i modify my pagination script with a search engine so people can query their results without 'breaking' the pagination script??