PHP Search Returning all Results

[patk570]

Hello everyone,

I have a search form that i am working on, and I have a huge array of checkboxes that I am using so people can refine the search down to just 1 or 2 things if they want to. In the search.php page, i have an implode function to return the array down to a string so it can be passed through the query. But now something strange has happened, it return everything because it is searching for a Comma(,) and a Space and returning all the results on the search page...

I would like to know if there is a way to take

$var = mysql_real_escape_string(implode(', ', $_REQUEST['q']));

and make it for friendly for the query maybe using a foreach statement or something else to handle the array of checkboxes that I have.

Here is the rest of the code i have for the php search and I will attach the checkbox array incase I made a mistake on that.

Thanks everyone in advance for your help.

Patrick.

<?php
$limit = 27;

//get the search variable from URL
$var = mysql_real_escape_string(implode(', ', $_REQUEST['q']));

//get pagination
$s = mysql_real_escape_string($_REQUEST['s']);

//set keyword character limit
if(strlen($var) < 3){
    $resultmsg =  "<p>Search Error</p><p>Keywords with less then three characters are omitted...</p>" ;
}
//trim whitespace from the stored variable
$trimmed = trim($var);
$trimmed1 = trim($var);
//separate key-phrases into keywords
$trimmed_array = explode(" ",$trimmed);
$trimmed_array1 = explode(" ",$trimmed1);

// check for an empty string and display a message.
if ($trimmed == "") {
    $resultmsg =  "<p>Search Error</p><p>Please enter a search...</p>" ;
}

// check for a search parameter
if (!isset($var)){
    $resultmsg =  "<p>Search Error</p><p>We don't seem to have a search parameter! </p>" ;
}
    // Build SQL Query for each keyword entered

foreach ($trimmed_array as $trimm){

  //If MATCH query doesn't return any results due to how it works do a search using LIKE
 if($row_num_links_main < 1){
    $query = "SELECT title, id, keywords, catagories, emurl FROM libvid WHERE keywords LIKE '%$trimm%' OR catagories LIKE '%$trimm%' OR title LIKE '%$trimm%' IS NOT null ORDER BY title DESC";
    $numresults=mysql_query ($query);
    $row_num_links_main1 =mysql_num_rows ($numresults);
 }
 // next determine if 's' has been passed to script, if not use 0.
 // 's' is a variable that gets set as we navigate the search result pages.
 if (empty($s)) {
     $s=0;
 }

  // now let's get results.
  $query .= " LIMIT $s,$limit" ;
  $numresults = mysql_query ($query)  or die ( "Couldn't execute query" );
  $row= mysql_fetch_array ($numresults);

  //store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result.
  do{
      $adid_array[] = $row[ 'title' ];
  }while( $row= mysql_fetch_array($numresults));
} //end foreach

//Display a message if no results found
if($row_num_links_main == 0 && $row_num_links_main1 == 0){
    $resultmsg = "<p>Search results for: ". $trimmed."</p><p>Sorry, your search returned zero results </p>" ;
}

//delete duplicate record id's from the array. To do this we will use array_unique function
$tmparr = array_unique($adid_array);
$i=0;
foreach ($tmparr as $v) {
   $newarr[$i] = $v;
   $i++;
}

//total result
$row_num_links_main = $row_num_links_main + $row_num_links_main1;

// now you can display the results returned. But first we will display the search form on the top of the page
   echo'<form class="mysearch" action="search.php" method="get">
      <p>
        <input name="q[]" id="tags" />
      <input title="Search" name="search" alt="Search" class="" type="submit" value="Search" /><br />
      <a href="library.php"><font size="-2">Search other catagories</font></a>
      </p>
    </form>';

// display an error or, what the person searched
if( isset ($resultmsg)){
    echo $resultmsg;
}else{
    echo "<p>Search results for: <strong>" . $var."</strong></p>";
 print '<table border="0" width="125%">'; 
 print '<tr>';
$col=0;
    foreach($newarr as $value){
 $col++;
    // EDIT HERE and specify your table and field unique ID for the SQL query
   $query_value = "SELECT * FROM libvid WHERE title = '".$value."'";
    $num_value=mysql_query ($query_value);
    $row_linkcat= mysql_fetch_array ($num_value);
    $row_num_links= mysql_num_rows ($num_value);

   //create summary of the long text. For example if the field2 is your full text grab only first 130 characters of it for the result
    $introcontent = strip_tags($row_linkcat['title']);
    $introcontent = substr($introcontent, 0, 130)."...";

    //now let's make the keywods bold. To do that we will use preg_replace function.

    //Replace field
      $title = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" , $row_linkcat['title']);
      $desc = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" , $introcontent);
      $link = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" ,  $row_linkcat['emurl']);
      $cata = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" ,  $row_linkcat['catagories']);
      $instr = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" ,  $row_linkcat['instructions']);
      $id = preg_replace ( "'($var)'si" , "<strong>\\1</strong>" ,  $row_linkcat['id']);

        foreach($trimmed_array as $trimm){
            if($trimm != 'b' ){
                $title = preg_replace( "'($trimm)'si" ,  "<strong>\\1</strong>" , $title);
                $desc = preg_replace( "'($trimm)'si" , "<strong>\\1</strong>" , $desc);
                $link = preg_replace( "'($trimm)'si" ,  "<strong>\\1</strong>" , $link);
                $cata = preg_replace( "'($trimm)'si" ,  "<strong>\\1</strong>" , $cata);
                $instr = preg_replace( "'($trimm)'si" ,  "<strong>\\1</strong>" , $instr);
                $id = preg_replace( "'($trimm)'si" ,  "<strong>\\1</strong>" , $id);


            }//end highlight
        }//end foreach $trimmed_array

    print '<td width="170px" height="130px"><font size="-3"><strong>'.$title.'</strong></font><br><a href="#'.$title.'" onClick="javascript:loadwindow(\'popup.php?id=' . $id . '\')"><img src=http://i3.ytimg.com/vi/'.$link.'/0.jpg width="162" height="110" /></a><br /><font size="-3">'.$cata.'</font></td>';
    if($col % 3 ==0) print '</tr><tr>';

        }  //end foreach $newarr
 print '</tr></table>';
    if($row_num_links_main > $limit){
    // next we need to do the links to other search result pages
        if ($s >=1) { // do not display previous link if 's' is '0'
            $prevs=($s-$limit);
            echo '<div align="left" class="search_previous"><a href="'.$PHP_SELF.'?s='.$prevs.'&q[]='.$var.'">Previous Page</a>
            </div>';
                    }
    // check to see if last page
        $slimit =$s+$limit;
        if (!($slimit >= $row_num_links_main) && $row_num_links_main!=1) {
            // not last page so display next link
            $n=$s+$limit;
            echo '<div align="right" class="search_next"><a href="'.$PHP_SELF.'?s='.$n.'&q[]='.$var.'">Next Page</a>
            </div>';

        }
    }//end if $row_num_links_main > $limit
}//end if search result
?>

and checkbox array:

<form autocomplete="off" action="search.php"  method="post">
  <table width="100%" border="0" cellpadding="5" cellspacing="5">
    <tr><td><label><input type="checkbox" name="q[]" value="Trapezius" />Trapezius</label></td>
    <td><label><input type="checkbox" name="q[]"  value="Neck" />Neck</label></td>
    <td><label><input type="checkbox" name="q[]" value="Medicine Ball" />Medicine Ball</label></td></tr>
    <tr><td><label><input type="checkbox" name="q[]" value="Stability Ball" />Stability Ball</label></td>
    <td><label><input type="checkbox" name="q[]" value="Shoulders" />Shoulders</label></td>
    <td><label><input type="checkbox" name="q[]" value="Chest" />Chest</label></td></tr>
    <tr><td><label><input type="checkbox" name="q[]" value="Glutes" />Glutes</label></td>
    <td><label><input type="checkbox" name="q[]" value="Abdominals" />Abdominals</label></td>
    <td><label><input type="checkbox" name="q[]" value="General Exercise" />General Exercise</label></td></tr>
    <tr><th colspan="3">&nbsp;</th></tr>
    <tr><td><div align="center">Back</div></td><td><div align="center">Arms</div></td><td><div align="center">Legs</div></td></tr>
    <tr><td><label><input name="q[]" type="checkbox" value="Lower Back" />Lower Back</label></td>
    <td><label><input type="checkbox" name="q[]" value="Forearms" />Forearms</label></td><td><label><input type="checkbox" name="q[]" value="Calves" />Calves</label></td></tr>
    <tr><td><label><input type="checkbox" name="q[]" value="Upper Back" />Upper Back</label></td>
    <td><label><input type="checkbox" name="q[]" value="Triceps"  />Triceps</label></td><td><label><input type="checkbox" name="q[]" value="Hamstrings" />Hamstrings</label></td></tr>
    <tr><td><label><input type="checkbox" name="q[]" value="Warm"  />Warm Up</label></td><td><label><input name="q[]"  type="checkbox" value="Biceps" />Biceps</label></td><td><label><input name="q[]" type="checkbox" value="Quadriceps" />Quadriceps</label></td></tr>
    <tr><th colspan="3">&nbsp;</th></tr>
    <tr><td><label><input type="checkbox" name="q[]" value="Resistance Bands" />Resistance Bands</label></td>
    <td><label><input type="checkbox" name="q[]" value="Barbell" />Barbell</label></td><td><label><input type="checkbox" name="q[]" value="Cardio" />Cardio</label></td></tr>
    <tr><td><label><input type="checkbox" name="q[]" value="Olympic Lifts" />Olympic Lifts</label></td><td><label><input name="q[]" type="checkbox" value="Hammer Strength" />Hammer Strength</label></td><td><label><input type="checkbox" name="q[]" value="Dumbbell" />Dumbbell</label></td></tr>
    <tr><td><label><input name="q[]" type="checkbox" value="Stretches" />Stretches</label></td><td><label><input type="checkbox" name="q[]" value="Full Body" />Full Body</label></td><td><label><input name="q[]"  type="checkbox" value="Machines" />Machines</label></td></tr>
    <tr><td><label><input name="q[]" type="checkbox" value="Body Weight" />Body Weight</label></td><td><label><input name="q[]" type="checkbox" value="Core" />Core</label></td><td><input type="submit"  value="Submit" /></td></tr>
    </table>
     </form>

[minitauros]

Damn that is a whole lot of code man. Would you be so kind to specify the lines that you would like us to look at? ^^

By the way, on a side note, using $_REQUEST is considered unsafe/risky, as it may get defined by $_COOKIE, $_GET and $_POST values, which means it leaves room for malicious injections and stuff plus you might end up using the wrong data (if you have the same key in a $_GET, $_POST and $_COOKIE).

[patk570]

Quoted Text Damn that is a whole lot of code man. Would you be so kind to specify the lines that you would like us to look at? By the way, on a side note, using $_REQUEST is considered unsafe/risky, as it may get defined by $_COOKIE, $_GET and $_POST values, which means it leaves room for malicious injections and stuff plus you might end up using the wrong data (if you have the same key in a $_GET, $_POST and $_COOKIE).
Here^^

It is line 5, that is where i am getting the array of q and turning it into a $var.

[minitauros]

So exactly what is wrong with your $var at the current moment? Are there comma's where you don't want them? Or are there values that you do not want to be included in $var?

[patk570]

Quoted Text Damn that is a whole lot of code man. Would you be so kind to specify the lines that you would like us to look at? By the way, on a side note, using $_REQUEST is considered unsafe/risky, as it may get defined by $_COOKIE, $_GET and $_POST values, which means it leaves room for malicious injections and stuff plus you might end up using the wrong data (if you have the same key in a $_GET, $_POST and $_COOKIE).
Here^^

It is line 5, that is where i am getting the array of q and turning it into a $var.

[patk570]

What it is doing when i get the results back from the database is it is returning basically EVERYTHING from the database because it is searching for the "," and the space as well. I have tried using

$var = str_replace(",", "",$var); 

and it did get rid of the commas, but now i need to get rid of the whitespace between the variables, So instead of using what is on Line 5 of the code, i am wondering if ther eis a a way to use a foreach statement to fix it and still return results on both a single word and multiple words.

[minitauros]

Well, if you want to search for a match with multiple words, you should probably use WHERE ... LIKE %...% OR ... LIKE %...% (for example).

So, in your example:

WHERE (keywords LIKE '%$word1%' OR catagories LIKE '%$word1%' OR title LIKE '%$word1%')
OR (keywords LIKE '%$word2%' OR catagories LIKE '%$word2%' OR title LIKE '%$word2%')

etc.

To remove the comma's, simply pass an empty parameter to your implode function :). E.g. implode('', $array).

If you need more info, could you please post the exact query that you are using to search for results? So not your code, but the actual query itself, echo'd? :)

[patk570]

What it is doing when i get the results back from the database is it is returning basically EVERYTHING from the database because it is searching for the "," and the space as well. I have tried using

$var = str_replace(",", "",$var); 

and it did get rid of the commas, but now i need to get rid of the whitespace between the variables, So instead of using what is on Line 5 of the code, i am wondering if ther eis a a way to use a foreach statement to fix it and still return results on both a single word and multiple words.

[patk570]

Hello Minitauros,

here is my echo statement for $query

SELECT title, id, keywords, catagories, emurl FROM libvid WHERE keywords LIKE '%Trapezius%' OR catagories LIKE '%Trapezius%' OR title LIKE '%Trapezius%' IS NOT null ORDER BY title DESCSELECT title, id, keywords, catagories, emurl FROM libvid WHERE keywords LIKE '%Abdominals%' OR catagories LIKE '%Abdominals%' OR title LIKE '%Abdominals%' IS NOT null ORDER BY title DESCSELECT title, id, keywords, catagories, emurl FROM libvid WHERE keywords LIKE '%Triceps%' OR catagories LIKE '%Triceps%' OR title LIKE '%Triceps%' IS NOT null ORDER BY title DESC

[minitauros]

Ah I see three queries there, am I right? And does each of those queries return ALL rows from the table specified? Because there seems to be nothing wrong with your queries, although I've personally never seen "IS NOT null" being used at the end of a WHERE statement, but I cannot say with certainty that that is incorrect syntax. You could try and see what happens if you don't add that to the end of your WHERE statement.

If I were you, I wouldn't use so many separate queries. Instead, I would slightly modify my WHERE part to include all the WHERE...LIKE... parts. For example WHERE (... LIKE first word) OR (... LIKE second word) OR (... LIKE third word) etc. Is that clear enough? :)

[patk570]

I kinda do, If you want to see the example, I have it hosted on my website, its still being constructed and i left the debugging on,

http://www.patrickspcrepair.com/exercise

Click on exercise library and you can choose the things you want...

Let me know what you find or need..

[minitauros]

Ah now we're going somehwere. You could try the following code. If anything is unclear to you, let me know :).

<?php
$search_terms = $_REQUEST['q'];

// Make sure we can do a safe input by applying mysql_real_escape_string.
// Check out the PHP documentation if you want to learn more about array_map().
$search_terms = array_map('mysql_real_escape_string', $search_terms);

if($search_terms)
{
    foreach($search_term as $term)
    {
        // Let's build the WHERE part of the query.

        $where_parts .= '(';
        $where_parts .= ' keywords LIKE "%' . $term . '%" ';
        $where_parts .= ' OR catagories LIKE "%' . $term . '%" '; // For your info, it's "categories", not "catagories" ^^.
        $where_parts .= ' title LIKE "%' . $term . '%" ';
        $where_parts .= ')';
    }

    // Let's implode the where parts to create an actual where clause.
    $where = implode(' OR ', $where_parts); // Change to AND if you want, of course :).

    // Your possible new query could be as follows:
    $q = 'SELECT title, 
            id, 
            keywords, 
            catagories, 
            emurl 
        FROM libvid
        WHERE ' . $where . '
        ORDER BY title DESC';
}
else
{
    //* No search was done.
}

[patk570]

Im trying to place that in there there but it is erroring out on me:
Parse error: syntax error, unexpected $end in /homepages/36/d301143136/htdocs/PCRJOOMLA/exercise/search.php on line 327

I am working on getting it on there.

[minitauros]

Ouch, that sounds like the file has not yet finished uploading? :) Or is there something wrong on line 327?

[patk570]

its there, you can see what its doing now.

[minitauros]

Oh that looks like a typo ^^. Have you checked out what's on line 156? It says an invalid argument is supplied for the foreach() loop, which is correct, cause we're passing $search_term to it instead of $search_terms (with an "s" at the end). If you correct that, I think it should work :).

[patk570]

the only things that its missing is for it to show $var= something, I just noticed that it was omitted?

[minitauros]

Yea I may not have used the same variable names as you have. You might want to check the code I provided and change the variable names to whatever variable names you want to use.

[ndomba]

How can i read a php file from directory and change the variables of that file to a random numbers