Member Avatar for MDGM

Hi all,

Iv looked at quite a lot of forums and cannot get the code to work properly for searching multiple search terms. I have tried exploding the search string and then doing a search similar to this:

WHERE fullname LIKE '%$1%' OR fullname LIKE '%$2%'

but it has not worked so I need a quick point in the right direction! Below is the code I am currently using which searches for a record that contains the whole phrase (e.g. john smith, NOT john or smith which is what I want.)

//connect to db
include('../../php/database/connect.php');

$q = $_GET['q'];
$q = strtolower($q);
$q = stripslashes($q);
$q = strip_tags($q);

$query  = "SELECT * FROM products WHERE fullname LIKE '%".$q."%' ";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo $row['fullname'];
}

Thanks in advance!! :)

  • 2 Contributors
  • 3 Replies
  • 183 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by MDGM

Dani AI

Generated

Quick summary: — splitting the query into terms and building an OR list is fine for an “any term” search, but the code posted is vulnerable to SQL injection and will grow slow as the term count and table size increase. ’s question about OR vs AND is important — this example below implements OR. Use prepared statements (PDO or mysqli) and limit token length/count; consider full‑text search for better performance.

Example (PDO, dynamic, safe, ranks matches):

// $pdo = new PDO(...);

$raw = trim(mb_strtolower($_GET['q'] ?? ''));
$tokens = preg_split('/\s+/', $raw, -1, PREG_SPLIT_NO_EMPTY);
$tokens = array_values(array_filter(array_unique($tokens), function($t){ return mb_strlen($t) >= 2; })); // drop tiny tokens

if (empty($tokens)) { exit; }

$where = $scoreParts = $params = [];
foreach ($tokens as $i => $t) {
  $ph = ":t{$i}";
  $where[] = "fullname LIKE {$ph}";
  $scoreParts[] = "(fullname LIKE {$ph})";
  $params[$ph] = "%{$t}%";
}

$sql = "SELECT *, (" . implode(' + ', $scoreParts) . ") AS score
        FROM products
        WHERE " . implode(' OR ', $where) . "
        ORDER BY score DESC
        LIMIT 100";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo htmlspecialchars($row['fullname'], ENT_QUOTES, 'UTF-8') . \"\n\";
}

Practical notes and troubleshooting

  • Avoid leading wildcards (%term%) if you need index use; term% can use an index. For general substring matching, expect full table scans.
  • For larger datasets use MySQL full‑text (MATCH ... AGAINST) or a dedicated search engine (Elasticsearch, Sphinx). Full‑text gives relevance and is far faster, but watch for stopwords and minimum word length in your MySQL version.
  • Sanitize output (htmlspecialchars) to avoid XSS. Limit number of tokens (e.g., first 8) to avoid huge queries.
  • If you need AND instead of OR, change OR to AND or use boolean full‑text with +term.

Recommended Answers

All 3 Replies

Member Avatar for MDGM

I want to return all results with EITHER john OR smith in.

Also the query needs to be dynamic, i.e. accept more than 1 / 2 words.

Thanks :)

Member Avatar for MDGM

Sorted. its probs not the most efficient code but it works. :)

$q = $_GET['q'];

//bit of sanitizing
$qtrim = strtolower($q);
$qtrim = stripslashes($q);
$qtrim = strip_tags($q);

//split values by whitespace into an array, then for each array member stick them together with "OR columnname LIKE '%$arrayvalue%' "
$qtrim = explode(' ',$q);
foreach($qtrim as $value)
	{
		$qtrim = $qtrim."OR fullname LIKE '%".$value."%' ";
	}
//we now have a string which has the following value:
//OR fullname LIKE '%".$value1."%' OR fullname LIKE '%".$value2."%' 

//we now need to remove the first OR, so the string looks like this:
//fullname LIKE '%".$value1."%' OR fullname LIKE '%".$value2."%' 
$qtrim = substr($qtrim,2);

//then we use the string in a mysql query
$query  = "SELECT * FROM products WHERE ".$qtrim;

//execute query
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
      echo $row['fullname'];
}
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.