Member Avatar for empoor

Hi. I tried looking for this problem on Google, and found a lot, but nothing that actually seems to work. I basically have a search script that works fine when using one keyword, but when one uses more than that, it returns every single row per keyword. So a search for "hello kitty" doesn't look for "hello" and "kitty", but for "hello" and then another time for "kitty".

I know there's something wrong with my code, but, what? And how can I solve this issue.

// get the search variable from URL
$var = @$_GET['q'] ;
// trim whitespace from the stored variable
$trimmed = trim($var);  
// separate key-phrases into keywords
$trimmed_array = explode(" ",$trimmed);

// build SQL Query for each keyword entered 
foreach ($trimmed_array as $trimm)
{
	
	$query = "SELECT * FROM (articles LEFT JOIN authors ON articles.author_id = authors.author_id) WHERE text LIKE '%$trimm%' ORDER BY issue DESC" ;

	// and then the queries and everything; with which nothing's wrong

}

Can someone help me?

Edited by empoor because: clarification
  • 3 Contributors
  • 4 Replies
  • 223 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by dasatti

Dani AI

Generated

As discovered, the root cause was running a separate query per token instead of building one query that requires all tokens (or one full-text query). That pattern produces duplicate results, extra looping, and fragile escaping. pointed out exploding terms; showed building the WHERE incrementally — both are on the right track, but there are a few common improvements and pitfalls to avoid.

A compact, safe pattern using PDO and a single query (AND between tokens) — tokenise on any whitespace, dedupe tokens, build one WHERE with placeholders, and use DISTINCT to avoid duplicate rows from the join:

$q = trim($_GET['q'] ?? '');
$tokens = preg_split('/\s+/', $q, -1, PREG_SPLIT_NO_EMPTY);
$tokens = array_values(array_unique($tokens)); // remove empties and duplicates

$where = $params = [];
foreach ($tokens as $t) {
    $where[] = "(articles.text LIKE ? OR articles.page_title LIKE ? OR authors.author LIKE ?)";
    $params[] = "%$t%"; $params[] = "%$t%"; $params[] = "%$t%";
}

$sql = "SELECT DISTINCT articles.* FROM articles LEFT JOIN authors ON articles.author_id = authors.author_id"
     . (count($where) ? " WHERE " . implode(" AND ", $where) : "")
     . " ORDER BY issue DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

Notes and tips:

  • Use prepared statements (not addslashes/htmlspecialchars) to prevent SQL injection.
  • Use preg_split('/\s+/') instead of explode(' ') to avoid empty tokens on multiple spaces.
  • Use DISTINCT or GROUP BY articles.page_id to eliminate duplicates caused by joins.
  • For better performance and richer behavior (phrase search, stemming, required words) add a FULLTEXT index and use MATCH(...) AGAINST('+term1 +term2' IN BOOLEAN MODE); beware of stopwords and minimum-word-length.
  • Leading wildcards (LIKE '%term%') prevent index use and can be slow on large tables — consider full-text or an external search engine (Sphinx/Elastic) for serious search features.

Check small syntax issues (for example the string-concatenation operator is .=, not . =) and handle quoted phrases separately if you need exact-phrase matching.

Recommended Answers

All 4 Replies

Member Avatar for empoor

Explode on spaces and use ORs for the extra terms. http://php.net/explode

I'm already using explode to separate terms. And shouldn't it be "AND", 'cause I want the results to match all the results. It's basically already doing an "OR" search, which results in duplicate search results showing.

Member Avatar for empoor

I have solved the issue myself by trying to fix things with experimentation.

My first problem was that the code I used to remove duplicate entries from the search array was basically looping twice. So, I edited the source to keep it from looping twice.

do
		{
			$adid_array[] = $row['page_id'];
		}
	
	while($row = mysql_fetch_array($numresults));
$tmparr = array_unique($adid_array); 
	$i = 0; 
	foreach ($tmparr as $newarr_v)
		{ 
			$newarr[$i] = $newarr_v; 
			$i++; 
		}

And then I use newarr to execute the search query (with foreach).

Secondly, because this is my first experience with building a search page, I used stock code and didn't notice that it was erroneously building a query for each keyword entered, instead of a query that incorporates all keywords (if multiple). So I removed

foreach ($search_array as $search_keyword)

, and wrote a little code to separate each keyword with AND.

$var = addslashes(htmlspecialchars($_GET['q'])) ;
// trim whitespace from the stored variable
$trimmed = trim($var);  
// separate key-phrases into keywords
$trimmed_array = explode(" ",$trimmed);
// count keywords
$trimm_total = count($trimmed_array);
$i = 0;
$searchstring = '';

// looping to get the search string
foreach ($trimmed_array as $trimm)
{ 
	if ($i != 0 and $i != $wordcount)
	{ 
		$searchstring .= " AND ";
	} 

	$searchstring .= "text LIKE '%$trimm%' OR page_title LIKE '%$trimm%' OR author LIKE '%$trimm%'"; 
	
	// incrementing the value 
	$i = $i + 1; 
}

And now it finally works.

Edited by empoor because: clarification
Member Avatar for dasatti

Try this

// get the search variable from URL
$var = @$_GET['q'] ;
// trim whitespace from the stored variable
$trimmed = trim($var);  
// separate key-phrases into keywords
$trimmed_array = explode(" ",$trimmed);

// build SQL Query for each keyword entered
$query = "SELECT * FROM (articles LEFT JOIN authors ON articles.author_id = authors.author_id) WHERE";
 
$first = true;
foreach ($trimmed_array as $trimm)
{
                if(!$first)
	{
	    $query . = " AND ";
	}
                 else
                 {
                     $first = false;
                 }
	
	 
	$query = $query . " text LIKE '%$trimm%' " ;
 
	// and then the queries and everything; with which nothing's wrong
 
}
$query = $query. " ORDER BY issue DESC";
Edited by dasatti because: n/a
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.