How should i do the search?

[matthewlesh]

Ok, i'm working on a site and i am trying to add a live search feature, i have got the ajax etc done but now the mysql / php is killing me.

 

The situation is i have about 5-7 columns of information i am trying to search some long text some a word some an ID corresponding to an ID in its type... (i.e. i have a "city" column with a number within which is the same number as the city ID within another table).

 

Anyway i want a person to be able to enter " Steve Los Angles" and for steve to be inside the "name" column (or any column) and Los Angles to represent to the city column (which is just an ID going to a different table)

 

Sounds a little confusing i know.. but have gotten far enough to use the "explode" function and then loop around using the LIKE method all the columns i want it to search... The problem is that rows are repeating each time with a different city.

 

It doesn't worry me how efficient it works its more a matter of getting it working.

 

Any help would be GREAT

[laffin]

and how do u differentiate a name from a city?

and how do u concatenate a 2+ word name/city?

 

 

[soycharliente]

Have multiple input boxes that would correspond to different columns.

This is very basic.

 

<?php
$name = $_POST['name'];
$city = $_POST['city'];
$something = $_POST['something'];

$sql = "SELECT * FROM `table` JOIN `cities` ON table.cityId=cities.id";
$sql .= (isset($name)) ? " WHERE `name` LIKE '%$name%'" : "";
$sql .= (isset($city)) ? " WHERE `city` LIKE '%$city%'" : "";
$sql .= (isset($something)) ? " WHERE `something` LIKE '%$something%'" : "";

$errors = FALSE;
if (empty($name) && empty($city) && empty($something))
{
$errors = TRUE;
// kick out error because they didn't fill in anything.
// or just return everything. up to you.
}
else
{
$result = mysql_query($sql) OR DIE ("ERROR:<br />$sql<br />".mysql_error());
// loop through and display the results.
}
?>

<?php
if (!$errors) // no errors means show the form
{
?>
<form action="thispage.php" method="post">
<?php
echo ($errors) ? "<p>Enter something for at least one of the search fields.</p>" : "";
?>
<input type="text" name="name" value="" />
<input type="text" name="city" value="" />
<input type="text" name="something" value="" />

<input type="submit" name="Submit" value="Submit" />
</form>
<?php
}
else
{
// otherwise display search results
}
?>

 

Sidenote. How do you backtick properly when writing table.field ? Like `table`.`field`?