PHP/MySQL field Search Parser

mjurmann · February 8, 2007

Hello there. I looked on the forums, and have been looking online, but can't seem to find anything. Can someone point me in the right direction - I'm looking to have a Search field in which someone can enter search keywords using "+" or "AND" , separated by commans, (but that is not necessary right now, I only need a basic search paraser), and then have a particular field in a MySQL DB searched using that/those keywords. Please help!

trq · February 8, 2007

It would be easy enough to do. Just explode the text on ' + ' and ' and ' if they exist, then rebuild your query using the new array of words.

mjurmann · February 8, 2007

I really appreciate the quick response, but I'm somewhat new at this. I'm not sure what "explode the text" means. Is there any sample code you could spit out to me to get me started?

mjurmann · February 8, 2007

Can someone please help me here?

papaface · February 8, 2007

http://uk2.php.net/explode

mjurmann · February 9, 2007

Alright - so I attempted to create a query using explode..but I'm havig issues. It seems that when I submit a search term with the form, it finds the searched keyword in the mySQL field, but its outputting "S" for everything - the title, thumbnail URL, ID, section, etc...I don't know where "S" is coming from since I'm searching for "rfl". Help would be greatly appreciated.

Here is my code:


if ($_POST['search'] == null) {

echo "AH";

}

else {

// Get the search variable from URL
$var = $_POST['search'];
//trim whitespace from the stored variable
  $trimmed = trim($var); 
//separate key-phrases into keywords
  $trimmed_array = explode(",",$trimmed);
  echo $trimmed_array;
foreach ($trimmed_array as $trimm){
  $query = "SELECT * FROM videos WHERE tags LIKE \"%$trimm%\" OR title LIKE  \"%$trimm%\"  ORDER BY date_posted 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);

// 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)) {echo "ytttt";
$s=0;
}

// now let's get results.


$query .= " LIMIT $s,$limit" ;
$numresults = mysql_query ($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{
//EDIT HERE and specify your field name that is primary key
  $adid_array[] = $row[ 'id' ];
  }while( $row= @mysql_fetch_array($numresults));
} //end foreach


if($row_num_links_main == 0 && $row_set_num == 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++;
   }


// now you can display the results returned. But first we will display the search form on the top of the page
?>

<?php
// display what the person searched for.
if( isset ($resultmsg)){
  echo $resultmsg;
  exit();
}else{
  echo "Search results for: " . $var;
}

foreach($newarr as $value){

// EDIT HERE and specify your table and field names for the SQL query
$query_value = "SELECT * FROM videos WHERE id = '$value'";
$num_value=mysql_query ($query_value);
$row_linkcat= mysql_fetch_array ($num_value);
$row_num_links= mysql_num_rows ($num_value);

//now let's make the keywods bold. To do that we will use preg_replace function.
//EDIT parts of the lines below that have fields names like $row_linkcat[ 'field1' ]
//This script assumes you are searching only 3 fields. If you are searching more fileds make sure that add appropriate line. 
  $titlehigh = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'tags' ] );
  $linkhigh = preg_replace ( "'($var)'si" , "<b>\\1</b>" , $row_linkcat[ 'title' ] );

foreach($trimmed_array as $trimm){
    if($trimm != 'b' ){
//IF you added more fields to search make sure to add them below as well.
        $titlehigh = preg_replace( "'($trimm)'si" ,  "<b>\\1</b>" , $titlehigh);
        $linkhigh = preg_replace( "'($trimm)'si" , "<b>\\1</b>" , $linkhigh);
     }
//end highlight
}
?>

<?php echo $titlehigh; ?><br>
<?php echo $linkhigh; ?><br>
<?php echo $linkhigh; ?>


<?php
}   //end foreach $trimmed_array 
   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'><a href='$PHP_SELF?s=$prevs&q=$var&catid=$catid'>Previous " .$limit. "</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'><a href='$PHP_SELF?s=$n&q=$var&catid=$catid'>Next " .$limit. "</a></div>";
        }
    }
}  //end foreach $newarr
?>

mjurmann · February 9, 2007

I can't figure out why I'm getting "S" for everything...

mjurmann · February 9, 2007

Now I'm getting this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/mjurmann/www/temp/gameargusmedia/htms/video-player.php on line 153

$query .= " LIMIT $s,$limit" ;
$numresults = mysql_query ($query);
$row= mysql_fetch_array ($numresults);
echo $numresults;

I have no idea where to even begin here, help!

mjurmann · February 9, 2007

I'd be willing to pay someone if they can help me solve this issue - i'm sure its something small i'm overlooking. thanks!

mjurmann · February 11, 2007

Here is my code: like I said, when I'm searching, I'm echoing out the number of results, and its correct, but the information coming back is simply "S" for everything. My link URL, my image URL, my description...I have no idea why.

if ($_POST['search'] == null) {


}

else {

// Get the search variable from URL
$var = $_POST['search'];
//trim whitespace from the stored variable
  $trimmed = trim($var); 
//separate key-phrases into keywords
  $trimmed_array = explode(",",$trimmed);
  echo $trimmed_array;
foreach ($trimmed_array as $trimm){
  $query = "SELECT * FROM videos WHERE tags LIKE \"%$trimm%\" OR title LIKE  \"%$trimm%\"  ORDER BY date_posted 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);
echo $row_num_links_main;

// 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)) {echo "ytttt";
$s=0;
}

// now let's get results.


$numresults = mysql_query ($query);
$row= mysql_fetch_array ($numresults);
echo $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{
//EDIT HERE and specify your field name that is primary key
  $adid_array[] = $row[ 'id' ];
  }while( $row= @mysql_fetch_array($numresults));
} //end foreach


if($row_num_links_main == 0 && $row_set_num == 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++;
   }


// now you can display the results returned. But first we will display the search form on the top of the page
?>

<?php
if( isset ($resultmsg)){
  echo $resultmsg;
  exit();
}else{
}

}
?>

Jessica · February 11, 2007

Can you link to a sample url you'd use for it?

I simplified the code down a lot to try to find the problem. For now, run this and paste what it outputs, or please provide a sample link to the page and run this code on it.

<?
$var = $_POST['search'];
$trimmed = trim($var); 
$trimmed_array = explode(",",$trimmed);
print_r($trimmed_array);
foreach ($trimmed_array as $trimm){
$query = "SELECT * FROM videos WHERE tags LIKE '%$trimm%' OR title LIKE '%$trimm%' ORDER BY date_posted DESC" ; 
$rs = mysql_query ($query) OR DIE(mysql_error());
$row_num_links_main = mysql_num_rows($rs);
echo $row_num_links_main;
while($row= mysql_fetch_array ($numresults)){
	print_r($row);
}
}
?>

mjurmann · February 11, 2007

im doing it now now, give me one moment please, thank you SO MUCH

mjurmann · February 11, 2007

I entered it exactly as you gave it to me and here is what I got back

Array ( [0] => rfl ) 2
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/mjurmann/www/temp/gameargusmedia/htms/video-player.php on line 136

Jessica · February 11, 2007

Woops, I made a mistake. Change the line to

while($row= mysql_fetch_array ($rs)){

mjurmann · February 11, 2007

Array ( [0] => rfl ) 2Array ( [0] => 30 [id] => 30 [1] => 20070209014606 [date_posted] => 20070209014606 [2] => Thumbnail Upload Test 2b [title] => Thumbnail Upload Test 2b [3] => Matt Jurmann [author] => Matt Jurmann [4] => Test [article] => Test [5] => 1 [authorId] => 1 [6] => Sony [section] => Sony [7] => shambles.flv [videoURL] => shambles.flv [8] => 0 [height] => 0 [9] => 0 [width] => 0 [10] => standard [aspectRatio] => standard [11] => 29_thumb_karisa.jpg [thumbURL] => 29_thumb_karisa.jpg [12] => rfl [tags] => rfl ) Array ( [0] => 29 [id] => 29 [1] => 20070209004856 [date_posted] => 20070209004856 [2] => Thumbnail Upload Test 2 [title] => Thumbnail Upload Test 2 [3] => Matt Jurmann [author] => Matt Jurmann [4] => Test [article] => Test [5] => 1 [authorId] => 1 [6] => Sony [section] => Sony [7] => shambles.flv [videoURL] => shambles.flv [8] => 0 [height] => 0 [9] => 0 [width] => 0 [10] => standard [aspectRatio] => standard [11] => 29_thumb_karisa.jpg [thumbURL] => 29_thumb_karisa.jpg [12] => rfl [tags] => rfl )

mjurmann · February 11, 2007

So it looks like it has all of the correct information - I can't understand why S is coming out for everything then

Here is my code where I'm trying to echo out the results:

	<?php if ($_POST['search'] == null) {

	}

	else { echo "<div id=\"results_text\"><span class=\"results\">Results:</span>";
		   echo "</div>";
		   echo "<ul>";
		   echo "<li><span class=\"content_results_left\">";
		   
		   if ($query['thumbURL'] == null) {

			echo "<img src=\"../images/default_video.jpg\" width=\"40\" height=\"40\" border=\"1\" alt=\"GameArgus Thumb\"/>";

			}

			else {

			echo "<img src=\"../cms/htms/staff_videos/thumbs/".$query['thumbURL']."\" height=\"40\" width=\"40\" alt=\"GameArgus Thumb\" border=\"2\" />"; }








		    echo "</span><span class=\"content_results_right\"><a href=\"video-player.php?id=".$query['id']."&&section=".$query['section']."\"/>\"".$query['thumbURL']."</a>\"</span><span class=\"content_results_linebreak\"><img src=\"../images/newreleases_line.jpg\" width=\"142\" height=\"5\" /></span></li>";
		    echo "</ul>";

	}?>

Jessica · February 11, 2007

Also, You can skip this:

//delete duplicate record id's from the array. To do this we will use array_unique function

By adding DISTINCT into the query

http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

Change if ($_POST['search'] == null) {

to

if(isset($_POST['search'])){

You're using $query, you should be using $row now.

Like:

echo '<img src="../cms/htms/staff_videos/thumbs/'.$row['thumbURL'].'" height="40" width="40" alt="GameArgus Thumb" border="2" />';

$query is the sql string - $row is the current row you're on. S was the first letter (at the 0 spot) of $query, and you were essentially getting it.

mjurmann · February 11, 2007

Alright - no more errors. Two questions:

1) Where do we add "DISTINCT" exactly

2) I'm not getting any information populating the query:

	
	<?php if ($_POST['search'] == null) {

	}

	else { echo "<div id=\"results_text\"><span class=\"results\">Results:</span>";
		   echo "</div>";
		   echo "<ul>";
		   echo "<li><span class=\"content_results_left\">";
		   
		   if ($row['thumbURL'] == null) {

			echo "<img src=\"../images/default_video.jpg\" width=\"40\" height=\"40\" border=\"1\" alt=\"GameArgus Thumb\"/>";

			}

			else {

			echo "<img src=\"../cms/htms/staff_videos/thumbs/".$row['thumbURL']."\" height=\"40\" width=\"40\" alt=\"GameArgus Thumb\" border=\"2\" />"; }








		    echo "</span><span class=\"content_results_right\"><a href=\"video-player.php?id=".$row['id']."&&section=".$row['section']."\"/>\"".$row['thumbURL']."</a>\"</span><span class=\"content_results_linebreak\"><img src=\"../images/newreleases_line.jpg\" width=\"142\" height=\"5\" /></span></li>";
		    echo "</ul>";

	}?>

Jessica · February 11, 2007

You didn't post your new query.

It should be something like

SELECT DISTINCT(*) FROM ...

mjurmann · February 11, 2007

You didn't post your new query.

I did, but I'm not getting the fields populated. I sent you a PM with the link to the page so you can see what I'm talking about...

Jessica · February 11, 2007

I see plenty of non-default thumbnails there.

When you do print_r($row), that shows you what was returned from the db.

The code you posted when you asked about where to put distinct does not show the query, just some HTML output.

Instead of trying to print an image try just printing $row['thumbURL'] and seeing what it's set to.

mjurmann · February 11, 2007

I see plenty of non-default thumbnails there.

When you do print_r($row), that shows you what was returned from the db.

The code you posted when you asked about where to put distinct does not show the query, just some HTML output.

Instead of trying to print an image try just printing $row['thumbURL'] and seeing what it's set to.

Do you see these non-default thumbnails in the bottom section with the Search Videos, or up at the top in one of the tall, narrow columns? They should be showing up at the bottom, but I don't see them. Just a default thumb or an eyeball.

mjurmann · February 11, 2007

thank you so much. you are my hero of the day!!!

mjurmann · February 11, 2007

Not sure if you're still around ,but I'm trying to repeat that region for all results, but I can't get it to work with the mysql_fetch_array, its giving me this error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/mjurmann/www/temp/gameargusmedia/htms/video-player.php on line 376

Here is the code:

do {
		   
		   echo "<li><span class=\"content_results_left\">";
		   
		   if ($row['thumbURL'] == null) {

			echo "<img src=\"../images/default_video.jpg\" width=\"40\" height=\"40\" border=\"1\" alt=\"GameArgus Thumb\"/>";

			}

			else {

			echo "<img src=\"../cms/htms/staff_videos/thumbs/".$row['thumbURL']."\" height=\"40\" width=\"40\" alt=\"GameArgus Thumb\" border=\"2\" />"; }








		    echo "</span><span class=\"content_results_right\"><a href=\"video-player.php?id=".$row['id']."&&section=".$row['section']."\"/>\"".$row['title']."</a>\"</span><span class=\"content_results_linebreak\"><img src=\"../images/newreleases_line.jpg\" width=\"142\" height=\"5\" /></span></li>";

			} while ($row = mysql_fetch_array($query));

		    echo "</ul>";

	}?>

Jessica · February 11, 2007

On the mysql_query() you need to have that OR die(mysql_error()); part - that error indicates something failed in your query.

(sorry to bump an old post, but I went to bed last night without finishing up here )

Personally, I think the do while syntax is much less readable then the simple while() - you're putting your crucial part way at the end.

Sign In

PHP/MySQL field Search Parser

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information