milti-table search

[franwpd]

I have several tables. They look similar to the one below:

table1
+---------------|---------------|----------------|----------|---------+
| archive_id | lastname | firstname | state | etc... |
+---------------|---------------|---------------|-----------|---------+
| 100 | Jones | George | WV | etc... |
| 101 | Doe | John | IN | etc... |
+---------------|---------------|----------------|----------|----------+

table2
+---------------|---------------|----------------|----------|---------+
| archive_id | lastname | firstname | state | etc... |
+---------------|---------------|----------------|----------|---------+
| 100 | Frito | Lay | NY | etc... |
| 101 | Doe | Jane | NE | etc... |
+---------------|---------------|----------------|----------|----------+

I want to run a search through all the tables

<?
$var = @$_GET['query'] ;
$trimmed = trim($var);

$limit=10;

if ($trimmed == "")

{
echo "<div align=center><br><br>Please enter a search word.<br><br></div>";

exit;

}

if ($trimmed == "Type Surname Here")

{
echo "<div align=center><br><br>Please enter a search word.<br><br></div>";

exit;

}

if (!isset($var))

{
echo "<br><br>We do not seem to have a search parameter!<br><br>";

exit;
}

//Here is where my problem begins.

//This works if I only had one table:
//$sql = "SELECT * FROM table1 WHERE lastname
// LIKE '%$trimmed%'";

// but this doesn't:

$sql = "SELECT * FROM table1, table2 WHERE lastname
LIKE '%$trimmed%'";

$numresults=mysql_query($sql);
$numrows=mysql_num_rows($numresults);

if ($numrows == 0)

{

echo "<b>Results</b>";
echo "<p align=center>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results.</p>";

exit;

}

if (empty($s)) {
$s=0;
}

$sql .= " limit $s,$limit";

$sql_result = mysql_query($sql,$connection)
or die("Couldn't execute the query.");

?>

I have looked through many forums but they don't seem to work with what I am trying to accomplish. Please understand that I am new to this and need lots of guidance.

[PallaviDalvi]

am really bad at SQL queries but i think the query shud be like

SELECT * FROM table1, table2 WHERE table1.lastname LIKE '%$trimmed%' and table2.lastname LIKE '%$trimmed%'

moreover when u say it doesnt work... what exactly happens??

[bubblenut]

There's no join so they should be two seperate queries. I don't understand why you have two tables both with exactly the same structure, that's kinda going against the very principles of relational databases. The query provided above will return a lot of rows because of the fact there is no join. For every row in table1 which matches a row with the details for that row in table1 and every different row in table2 will be output and the same back for each row in table2 which matches. I'm not very good at explaining things like this (hence the last sentance not making sense) but here's some numbers to explain it further. Let's say there are a total of 10 rows in table1 and 10 rows in table2 (keep it simple) and let's say that 5 rows in table1 match and 5 rows in table2. 100 rows would be returned! (510 + 510). If there were 100 rows in each table and still only five matches in each there'd be 1000 rows returned!!:eek:

HTH
Bubble