Search from more than one table

[franwpd]

The following code works if there is only one table to search from. However, I need it to search from multiple tables because I have a script that the client uses to upload tables so I need it to be automatic.

(Another script takes care of this
$data = Table1, Table2, etc)

<php>

$sql = "SELECT * FROM $data 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.");

<php>
<p align="center"><font face="Trebuchet MS, Arial, Helvetica" size="2">Your
search for "<?echo $var;?>" has returned <?echo @mysql_num_rows($sql_result);?>
result(s).</font></p>

The error I get is:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /.../html/results_name.php on line 145
Results

So...my question is... can this script be modified to select from multiple tables, or does it have to be altered and how?

Please keep in mind that I have not been doing this for very long ;-)

[franwpd]

Actually, I have something that I think will work but I still have problems with implode. Can someone help me write this?

implode the following...

$data.lastname LIKE '%$trimmed%' AND $data.lastname LIKE '%$trimmed%' ...etc

Any help will be much appreciated ;-)

[drawmack]

(Another script takes care of this
$data = Table1, Table2, etc)

$data_ary = explods(', ',$data);
$sql = array();
foreach($data_ary as $table) {
    $sql[] = "SELECT * FROM $data WHERE lastname LIKE '%$trimmed%'";
} //end foreach

foreach($sql as $cur_sql) {
    $ numrows = 0;
    $numresults=mysql_query($cur_sql);
    $numrows+=mysql_num_rows($numresults);
} //end foreach

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;
  }

foreach($sql as $cur_sql) {
    $cur_sql .= " limit $s,$limit";

$sql_result = mysql_query($cur_sql,$connection)
or die("Couldn't execute the query.");
?>
              <p align="center"><font face="Trebuchet MS, Arial, Helvetica" size="2">Your 
                search for "<?echo $var;?>" has returned <?echo @mysql_num_rows($sql_result);?> 
                result(s).</font></p>
<?php
} //end foreach

[dnast]

Wouldn't it work with just...

$sql = "SELECT * FROM $data WHERE lastname LIKE '%$trimmed%'";
$result = mysql_query($sql);

...even without all of those loops and counting all of the results since that one SQL statement will search all tables in $data?

[drawmack]

Originally posted by dnast
Wouldn't it work with just...

$sql = "SELECT * FROM $data WHERE lastname LIKE '%$trimmed%'";
$result = mysql_query($sql);

...even without all of those loops and counting all of the results since that one SQL statement will search all tables in $data? [/B]

yeah but it's going to return one massive array as well.