Custom Search Engine.

Question

patk570 42 Newbie Poster

10 Years Ago

Hello everyone, I have a website I am working on. On this site, I have a form that when you search for just one term, say "legs", it is supposed to pull up everything that is "legs", but not "legs" with "stability ball". My form uses the search term q[] as its name. I will post that in a bit. So what I it to do is search the DB that I have called libvid, to pull the main content like "legs" then when you search additional terms like "stability ball", or "Shoulders" with "Resistance Bands" or even "Glutes", "legs" and "chest".

Here is my form code:

    <form>
    <table border="0" cellpadding="5" cellspacing="5">
        <tr>
            <th colspan="3"> <a href="javascript:void();" onclick="javascript:checkAll('test', true);">Check all exercises</a> | 

<a href="javascript:void();" onclick="javascript:checkAll('test', false);">Uncheck all exercises</a>

            </th>
        </tr>
                <tr>
            <th> </th>
            <th> </th>
            <th> </th>
        </tr>
    </table>
</form>
<form name="test" action="search.php" method="post">
    <table border="0" cellpadding="5" cellspacing="5">

        <tr>
            <th><u>Legs</u></th>
            <th><u>Back</u></th>
            <th><u>Arms</u></th>
        </tr>
        <tr>
            <td>
                <label>
                    <input name="q[]" value="Quadriceps" type="checkbox" />
                    Quadriceps
                </label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Upper Back" type="checkbox" />
                    Upper Back
                </label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Biceps" type="checkbox" />
                    Biceps
                </label>
            </td>
        </tr>
        <tr>
            <td>
                <label>
                    <input name="q[]" value="Hamstrings" type="checkbox" />Hamstrings</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Lower Back" type="checkbox" />Lower Back</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Triceps" type="checkbox" />Triceps</label>
            </td>
        </tr>
        <tr>
            <td>
                <label>
                    <input name="q[]" value="Calves" type="checkbox" />Calves</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Trapezius" type="checkbox" />Trapezius</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Forarms" type="checkbox" />Forarms</label>
            </td>
        </tr>
        <tr>
            <td colspan="3"> </td>
        </tr>
        <tr>
            <td>
                <label>
                    <input name="q[]" value="Chest" type="checkbox" />Chest</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Shoulders" type="checkbox" />Shoulders</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Olympic Lifts" type="checkbox" />Olympic Lifts</label>
            </td>
        </tr>
        <tr>
            <td>
                <label>
                    <input name="q[]" value="Abdominals" type="checkbox" />Abdominal (Abs)</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Core" type="checkbox" />Core</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Combo/Complex Movements" type="checkbox" />Combo/Complex Movements</label>
            </td>
        </tr>
        <tr>
            <td>
                <label>
                    <input name="q[]" value="Glutes" type="checkbox" />Glutes</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Neck" type="checkbox" />Neck</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Body Weight" type="checkbox" />Body Weight</label>
            </td>
        </tr>
        <tr>
            <th colspan="3"><u>Additonal Features</u></th>
        </tr>
        <tr>
            <td>
                <label>
                    <input name="q[]" value="Barbell" type="checkbox" />Barbell</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Hammer Strength" type="checkbox" />Hammer Strength</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Stability Ball" type="checkbox" />Stability Ball</label>
            </td>
        </tr>
        <tr>
            <td>
                <label>
                    <input name="q[]" value="Dumbbell" type="checkbox" />Dumbbell</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Resistance Bands" type="checkbox" />Resistance Bands</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Medicine Ball" type="checkbox" />Medicine Ball</label>
            </td>
        </tr>
        <tr>
            <td>
                <label>
                    <input name="q[]" value="Machines" type="checkbox" />Machines</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Suspension" type="checkbox" />Suspension</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Stretches" type="checkbox" />Stretches</label>
            </td>
        </tr>
        <tr>
            <td>
                <label>
                    <input name="q[]" value="Rehab Exercises" type="checkbox" />Rehab Exercises</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="BOSU, Dyna Disc, Wobbleboard" type="checkbox" />BOSU, Dyna Disc, Wobbleboard</label>
            </td>
            <td>
                <label>
                    <input name="q[]" value="Cardio" type="checkbox" />Cardio</label>
            </td>
        </tr>
        <tr>
            <td colspan="3" align="right"><input type="submit" class="button" value="Show Exercises" /></td>
        </tr>
    </table>
</form>

and the search page script:

<?php
    include 'includes/config.inc.php';



//specify how many results to display per page
$limit = 29;

//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);
$trimmed2 = $var;


//separate key-phrases into keywords
$trimmed_array = explode(" ",$trimmed);
$trimmed_array1 = explode(" ",$trimmed1);
$trimmed_array2 = $trimmed2;


// 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){

    $query = "SELECT * , MATCH (title, catagories) AGAINST ('".$trimm."') AS score FROM libvid WHERE MATCH (title, catagories) AGAINST ('+".                $trimm."') ORDER BY score DESC";
 // Execute the query to  get number of rows that contain search kewords
    $numresults=mysql_query ($query);
    $row_num_links_main = mysql_num_rows($numresults);

  //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 * FROM libvid WHERE keywords LIKE '%$trimm%' OR catagories LIKE '%$trimm%' OR title LIKE '%$trimm%' 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="120%">'; 
 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 class="imgb" style="border: 2px solid black; border-radius: 30px; -moz-border-radius: 30px;-khtml-border-radius: 30px; -webkit-border-radius: 30px;" src=http://i3.ytimg.com/vi/'.$link.'/0.jpg width="162" height="110" />
          </a><br />
          <a class="addlink" href="#" onClick="getWorkoutList(' . $id . ',' . $uid . ');">Add to Saved Workouts</a>
          <div id="list-' . $id . '" class="workout-list" style="display:none;"></div>
        </td>';
    if($col % 3 ==0) print '</tr><tr>';

        }  //end foreach $newarr
 print '</tr></table>';

}//end if search result
?>

and the pageination:

<?php
        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 '<a class="alignleft" href="'.$PHP_SELF.'?s='.$prevs.'&q[]='.$var.'"><img class="imgb" src="images/dumbellprev.png" height="100px" width="100px" /></a>
            ';
                    }
    // 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 '<a class="alignright" href="'.$PHP_SELF.'?s='.$n.'&q[]='.$var.'"><img class="imgb" src="images/dumbellnext.png" height="100px" width="100px" /></a>
            ';

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

I think i may need to have the code rewritten , but if there is a quick way to fix it so i can do the additional features and such that would be great.

custom php search-engine

2 Contributors
16 Replies
390 Views
1 Week Discussion Span
Latest Post 10 Years Ago Latest Post by patk570

All 16 Replies

pritaeas 2,194 ¯\_(ツ)_/¯

10 Years Ago

How is your data organized? Are you using FK's or linked tables for "legs" and "stability ball"?

pritaeas 2,194 ¯\_(ツ)_/¯

10 Years Ago

Can you show how a row of data looks like? How it looks in the database table.

pritaeas 2,194 ¯\_(ツ)_/¯

10 Years Ago

A LIKE on the keywords column may turn up unwanted hits. You may want to have a look at FIND_IN_SET(). Perhaps it can narrow you search more easily. Is there a reason why the categories and keywords are not linked tables?

pritaeas 2,194 ¯\_(ツ)_/¯

10 Years Ago

Kind of. That's a full-text search. It may match more than you want, but that's hard to control.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

patk570 42 Newbie Poster · Answer 1 · 2013-08-11T02:05:55+00:00

I have one database, so in the database i have the table that is libvid, it has the rows catagories, keywords, and title. I removed the one for title, but seems to still pull up titles in the search. I dont know what is going on with this.

here is my new query string..

$query = "SELECT * FROM libvid WHERE keywords LIKE '%$trimm%' OR catagories LIKE '%$trimm%' ORDER BY id DESC";

patk570 42 Newbie Poster · Answer 2 · 2013-08-11T15:48:48+00:00

Here is a link to it.

http://patrickspcrepair.com/images/db.PNG

patk570 42 Newbie Poster · Answer 3 · 2013-08-13T02:47:41+00:00

not sure, what would the benefit of that be? I am new to programming and such. How would i link the tables and why should the keywords and such be in different categories ?

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 4 · 2013-08-13T07:47:15+00:00

Assume you have two categories in there, e.g. "powerlift squat", and "squat" (taken from keywords I know). In your code (with the LIKE) when you search for "squat", "powerlift squat" is also returned. This might not always be what you want. If you create a linked table, this would be easier to fix.

Apart from that, the FIND_IN_SET function can also do this correctly in your case. Just look it up in the manual.

patk570 42 Newbie Poster · Answer 5 · 2013-08-13T13:45:23+00:00

patk570 42 Newbie Poster

10 Years Ago

Awesome, I will try that today.

Thanks.

patk570 42 Newbie Poster · Answer 6 · 2013-08-13T14:01:17+00:00

Now I do have this search string in my code as well:

$query = "SELECT * , MATCH (title, catagories) AGAINST ('".$trimm."') AS score FROM libvid WHERE MATCH (title, catagories) AGAINST ('+".                $trimm."') ORDER BY score DESC";
 // Execute the query to  get number of rows that contain search kewords
    $numresults=mysql_query ($query);
    $row_num_links_main = mysql_num_rows($numresults);

doesnt that do the same type of search as the one you suggested?

patk570 42 Newbie Poster · Answer 7 · 2013-08-13T15:29:53+00:00

So i ammended the code to reflect this:

if($row_num_links_main < 1){
    $query = "SELECT * FROM libvid WHERE FIND_IN_SET(%$trimm%) ORDER BY id ASC";
    $numresults=mysql_query ($query);
    $row_num_links_main1 = mysql_num_rows($numresults);

but it will not execute query. did i do it right?

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 8 · 2013-08-13T16:04:26+00:00

did i do it right?

No. See the manual. It takes two parameters, unlike the LIKE, so the percent signs are out of place too. In your case it would probably be:

SELECT * FROM libvid WHERE FIND_IN_SET('$trimm', categories) ORDER BY id ASC

patk570 42 Newbie Poster · Answer 9 · 2013-08-13T19:03:35+00:00

So now I have another issue. When I do a search with multiple terms like Neck and Trapezius i get this: http://www.patrickspcrepair.com/images/2-search.PNG which is not everything, but when i do just trapezius on its own, i get: http://www.patrickspcrepair.com/images/1-search.PNG.

pritaeas 2,194 ¯\_(ツ)_/¯ Moderator Featured Poster · Answer 10 · 2013-08-13T19:25:06+00:00

Yes. I said it works differently. Searching for two terms also requires two FIND_IN_SET's.

patk570 42 Newbie Poster · Answer 11 · 2013-08-13T20:14:21+00:00

patk570 42 Newbie Poster

10 Years Ago

gotcha..

patk570 42 Newbie Poster · Answer 12 · 2013-08-22T03:31:51+00:00

Hello @pritaes,

I took your suggestion on linking the databases together. I now have a field in libvid that has an array of numbers, like 1,20,28,30 I now need to figure out how to get the data from libvid's array and match it with the categories table in the database.

Would something like this work?

SELECT libvid.categories, categories.id
FROM categories, libvid
WHERE libvid.id = libvid.categories

or is that structure wrong?

Custom Search Engine.

Recommended Answers Collapse Answers

All 16 Replies

Recommended Answers