Multi Field Search

[wrichards8]

I am trying to create a multi-field search based on the Database Searching tutorial. I am holding the field names in an array to make it easier for myself. The code I have is as follows

function database_search_user_cond($connection, $term, $start_page, $per_page) /* This function will search the database when the user has run a search */
{
	$start_page	 	= (int)($start_page - 1) * $per_page;
	$per_page 		= (int)$per_page;
	$search				= array();
	$i							= 1;
	$fields 				= array("title", "year", "director", "classification", "storyline", "genres", "actors");
	$term = trim($connection->real_escape_string($term));
	$keyword = preg_split("#/[\s]+/#", $term);
	foreach($keyword as $value)
	{
		foreach($fields as $row)
		{
			$search[] = "`{$row}` LiKE '%{$value}%'";
		}
	}
	$clause	= implode(" OR ", $search);
	$base 	= "SELECT  `title`, `year`, `director`, `movie_code`, `poster_name` FROM `movie_list` WHERE {$clause} LiMiT {$start_page}, {$per_page}";
	$query = $connection->query($base);
	while($results = $query->fetch_assoc())
	{
		$search[$i]["title"] 			= "{$results["title"]} ({$results["year"]})";
		$search[$i]["director"] 	= $results["director"];
		$search[$i]["code"] 		= $results["movie_code"];
		$search[$i]["poster"] 		= $results["poster_name"]; $i++;
	}
	return $base;

The reason I am outputting the value of $base is to test it. My issue is that what is returned for $term is exactly what is input. So, for example, if we enter "lost boys" we get

SELECT `title`, `year`, `director`, `movie_code`, `poster_name` FROM `movie_list` WHERE `title` LiKE '%lost boys%' OR `year` LiKE '%lost boys%' OR `director` LiKE '%lost boys%' OR `classification` LiKE '%lost boys%' OR `storyline` LiKE '%lost boys%' OR `genres` LiKE '%lost boys%' OR `actors` LiKE '%lost boys%' LiMiT 0, 3

when I am really looking for it to do something like

SELECT `title`, `year`, `director`, `movie_code`, `poster_name` FROM `movie_list` WHERE `title` LiKE '%lost%' OR `year` LiKE '%lost%' OR `director` LiKE '%lost%' OR `classification` LiKE '%lost%' OR `storyline` LiKE '%lost%' OR `genres` LiKE '%lost%' OR `actors` LiKE '%lost%' OR `title` LiKE '%boys%' OR `year` LiKE '%boys%' OR `director` LiKE '%boys%' OR `classification` LiKE '%boys%' OR `storyline` LiKE '%boys%' OR `genres` LiKE '%boys%' OR `actors` LiKE '%boys%' LiMiT 0, 3

so that it will match either keyword on any field.

[ScTech]

It looks like your preg_split() may be a little messed up. I'm not very good with regex. Why not just use:

<?php
$keyword = explode(" ", $term);
?>

If it's an extra spaces issue you're worrying about, which it looks like it may be, you can use trim() to remove extra whitespace