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

[syntax=php]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;[/syntax]
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
[syntax=text]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[/syntax] when I am really looking for it to do something like [syntax=text]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[/syntax] 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:
[syntax=php]<?php
$keyword = explode(" ", $term);
?>[/syntax]
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