[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Multiple keywords %LIKE% match myslq query problem

Posted on 2009-05-02
7
Medium Priority
?
450 Views
Last Modified: 2013-12-12
I am trying to get the script below to output the query results like this:

the dark knight batman
the batman joker
the batman gotham
the batman
superman vs batman
superman batman

However when I run this script the output results are as so:

the dark knight batman
the dark knight batman
the batman joker
the batman joker
the batman gotham
the batman gotham
the batman
the batman
superman vs batman
superman vs batman
superman batman
superman batman

Can anyone point me into the right direction on how to remove the duplicate result?

Thanks!
<?php
$hostname_logon = "localhost" ;   
$database_logon = "dbname" ;  
$username_logon = "username" ;  
$password_logon = "password" ;   
//open database connection
 $connections = mysql_connect($hostname_logon, $username_logon, $password_logon) or die ( "Unabale to connect to the database" );
 //select database
 mysql_select_db($database_logon) or die ( "Unable to select database!" );
 
//specify how many results to display per page
$limit = 10;
 
// Get the search variable from URL
  $var = "batman forever" ;
//trim whitespace from the stored variable
  $trimmed = trim($var); 
//separate key-phrases into keywords
  $trimmed_array = explode(" ",$trimmed);
 
// 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){
     
// EDIT HERE and specify your table and field names for the SQL query
     $query = "SELECT * FROM popular_searches2 WHERE query LIKE \"%$trimm%\" ORDER BY query 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);
 
      // now let's get results.
      $query .= " LIMIT $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{
 //EDIT HERE and specify your field name that is primary key
          $adid_array[] = $row[ 'query' ];
      }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++;
   }
 
// 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 popular_searches2 WHERE query = '$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 =  $row_linkcat[ 'query' ];
 
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);
     }
//end highlight
 
?>
<?php echo $titlehigh; ?><br>
 
<?php
}   //end foreach $trimmed_array 
 
}  //end foreach $newarr
?>

Open in new window

0
Comment
Question by:derek2277
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 4

Expert Comment

by:Fugas
ID: 24289054
Hi,

you should use only one query with condition build from the string array WHERE query LIKE "%$trimmed[0]%" OR query LIKE "%$trimmed[1]%" OR query LIKE "%$trimmed[2]%"
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24289182
Try this:
<?php
$hostname_logon = "localhost" ;   
$database_logon = "dbname" ;  
$username_logon = "username" ;  
$password_logon = "password" ;   
//open database connection
 $connections = mysql_connect($hostname_logon, $username_logon, $password_logon) or die ( "Unabale to connect to the database" );
 //select database
 mysql_select_db($database_logon) or die ( "Unable to select database!" );
 
//specify how many results to display per page
$limit = 10;
 
// Get the search variable from URL
  $var = "batman forever" ;
//trim whitespace from the stored variable
  $trimmed = trim($var); 
//separate key-phrases into keywords
  $trimmed_array = explode(" ",$trimmed);
 
// 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){
     
// EDIT HERE and specify your table and field names for the SQL query
     $query = "SELECT * FROM popular_searches2 WHERE query LIKE \"%$trimm%\" ORDER BY query 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);
 
      // now let's get results.
      $query .= " LIMIT $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{
      //EDIT HERE and specify your field name that is primary key
          $adid_array[] = $row[ 'query' ];
      }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++;
   }
 
// 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 popular_searches2 WHERE query = '$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 =  $row_linkcat[ 'query' ];
 
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);
     }
//end highlight
 
$atitlehigh[] = $titlehigh;
 
}   //end foreach $trimmed_array 
 
}  //end foreach $newarr
 
// get unique values only
$atitlehigh = array_unique($atitlehigh);
foreach ($atitlehigh as $value){
	echo $value . '<br />';
}
?>

Open in new window

0
 

Author Comment

by:derek2277
ID: 24292248
I have been playing around with this and I now can get mysql to return results for each keyword.  The only problem is that it seems to run the query each time and then return 10 results for each separate keyword.  Does anyone know how to condense the results?

The output for the keywords "batman forever" returns 20 results instead of 10:

batman default
batman
batman defaults
batman logo
pink batman
batman the dark knight
batman dark knight
batman friendster
batman begins
batman the dark knight the dark knight
always and forever
forever 21
forever
forever the sickest kids
forever love
flashy diamonds are forever
love forever
forever and always
forever yours
plain yellow background stars forever

Anyone know how to have this return 10 results from the output only?

Thanks,
// Get the search variable from URL
  $var = "batman forever" ;
//trim whitespace from the stored variable
  $trimmed = trim($var); 
//separate key-phrases into keywords
  $trimmed_array = explode(" ",$trimmed);
 
// 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){
 
	$result = mysql_query("SELECT * FROM popular_searches2 WHERE query LIKE '%$trimm%' ORDER BY count DESC LIMIT 10") or die(mysql_error());  
	
	while($row = mysql_fetch_array($result)){
		echo $row['query'];
		echo "<br />";
	}
}
?>

Open in new window

0
Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24292754
Since you are LOOPING the query (in this case you are running two querys) you will get a maximum of 20. Instead if you use one execution (with or statements) you can limit the result quantity by 10.

// Get the search variable from URL
  $var = "batman forever" ;
//trim whitespace from the stored variable
  $trimmed = trim($var); 
//separate key-phrases into keywords
  $trimmed_array = explode(" ",$trimmed);
 
// 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 ONE SQL Query for keyword(s) entered
 
$i=0
foreach ($trimmed_array as $trimm){
 if ($i==0){
  $conditions = " query LIKE '%";
 else {
  $conditions = " OR query LIKE '%";
 }
 $conditions .= "$trimm%' ";
 $i++;
}
 
$result = mysql_query("SELECT * FROM popular_searches2 
WHERE $conditions 
ORDER BY count DESC LIMIT 10") or die(mysql_error());  
        
while($row = mysql_fetch_array($result)){
 echo $row['query'];
 echo "<br />";
)

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24292760
Fixed error. Use this instead :)
// Get the search variable from URL
  $var = "batman forever" ;
//trim whitespace from the stored variable
  $trimmed = trim($var); 
//separate key-phrases into keywords
  $trimmed_array = explode(" ",$trimmed);
 
// 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 ONE SQL Query for keyword(s) entered
 
$i=0
foreach ($trimmed_array as $trimm){
 if ($i==0){
  $conditions = " query LIKE '%";
 } else {
  $conditions = " OR query LIKE '%";
 }
 $conditions .= "$trimm%' ";
 $i++;
}
 
$result = mysql_query("SELECT * FROM popular_searches2 
WHERE $conditions 
ORDER BY count DESC LIMIT 10") or die(mysql_error());  
        
while($row = mysql_fetch_array($result)){
 echo $row['query'];
 echo "<br />";
)

Open in new window

0
 
LVL 23

Assisted Solution

by:Tony McCreath
Tony McCreath earned 150 total points
ID: 24293399
You may find the MySQL full text searches more suitable to what you want. You don't need to split up your words, the search is indexed so a lot faster and it can return results ordered by relevance.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
0
 
LVL 19

Accepted Solution

by:
NerdsOfTech earned 600 total points
ID: 24293598
I concur -- as relevancy, in this case, is highly important.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hey fellow admins! This time, I have a little fairy tale for you. As many tales do, it starts boring and then gets pretty gory. I hope you like it. TL;DR: It is about an important security matter, you should read it if you run or administer Windows …
IF you are either unfamiliar with rootkits, or want to know more about them, read on ....
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question