multi-table search

Please help a nubee?

[frannie96]

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.

[ergophobe]

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

That's because it is ambiguous (which lastname?). Try this:

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

That said, your tables look identical (perhaps just for this example). Why are there two tables?

Tom

[ExpLarry]

as ergophobe said - why two identical tables?

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

This is bad - apart from lastname being ambiguous, it will result in a "cartesian join", which will give you all the results of the table not specified in the WHERE clause.

You may be looking for a UNION join
SELECT * FROM table1 where lastname LIKE ...
UNION
SELECT * FROM table2 where lastname LIKE ...

the mysql online docs whill have further infos.

[ergophobe]

Ooops! Yes my query is certinaly not what you want. Too hasty. Like he said, you'll get a Cartesian join which you definitely don't want.

If you need to use UNION and you are running MySQL 3.x, this explains how:

[jinxidoru.com...]

Tom

[frannie96]

The reason for multiple "alike" tables is because the person uploading the database times out over 3000. So I made a script that uploads 3000 at a time. That worked great. Now I have to let people search for certain names. I figured if I could get at least the last name to work then I could go on to let them search for last name, first name, and state in one query. First things first...I can't get the lastname to search...thus my question about the code. I have tried many versions:

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

SELECT * FROM table1 where lastname LIKE ...
UNION
SELECT * FROM table2 where lastname LIKE ...

SELECT * FROM table1
inner join ON lastname (joblogs.jobID=projects.jobID)
inner join ON TASKS (joblogs.jobID=tasks.taskID)
inner join ON ARCHIVE (joblogs.jobID=archive.archID)
WHERE userID='$userID'

SELECT * FROM table1
inner join ON archive_id (table1.lastname=table2.lastname)
WHERE lastname LIKE '%$trimmed%'";

None of these work. This this an impossible task?

[ergophobe]

The reason for multiple "alike" tables is because the person uploading the database times out over 3000

That would thus require that the table be uploaded in several chunks, but it should not be put into multiple tables. The way to solve your problem is to add those records onto the existing table 3000 at a time, not to create X tables with 3000 records (what would your strategy be if he timed out after 2?).

None of these work. This this an impossible task?

Not in the least. If you can't use UNION, all you need to do is this:

$tables = array("table1", "table2", "table3");
$hits = array();
$i = 0;

foreach ($tables as $table){
$sql = "SELECT * FROM $table WHERE lastname LIKE '%$trimmed%'";
$rslt = mysql_query($sql);
while ($person = mysql_fetch_assoc($rslt)) {
$hits[$i] = $person;
$i++;
}

The array person now has all hits on all tables.

[ergophobe]

To put all records into the same table, go to your client interface (the myslq client in the shell, PhpMyAdmin or whatever) and just do a INSERT SELECT.

All of your tables have the same structure. Create a new, empty table with that strucure. Then:

INSERT INTO new_table SELECT * FROM table1, table2, table3, table4;

Now change your script so that when records are uploaded, they are inserted into new_table.

[frannie96]

Thank you! I finally got it working the way I want. This is what it looks like now:

<php

$tables = array("3000A", "6000A", "9000A");
$hits = array();
$i = 0;

foreach ($tables as $table){

$var = @$_GET['query1'] ;
$trimmed1 = trim($var);
$limit=10;

$var = @$_GET['query2'] ;
$trimmed2 = trim($var);

$var = @$_GET['query3'] ;
$trimmed3 = trim($var);

if ($trimmed1 == "")
{
echo "<div align=center><br><br>Please enter a search word.<br><br></div>";
exit;
}

if ($trimmed1 == "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;
}

$sql = "SELECT * FROM $table WHERE lastname LIKE '%$trimmed1%'
AND firstname LIKE '%$trimmed2%' AND state LIKE '%$trimmed3%'";
$rslt = mysql_query($sql);

while ($row = mysql_fetch_assoc($rslt)) {
$hits[$i] = $row;

php>

<tr align="center">
<td><font face="Trebuchet MS, Arial, Helvetica" size="2"><? echo "$row[archive_id]";?></font> </td>
<td><font face="Trebuchet MS, Arial, Helvetica" size="2"><? echo "$row[lastname]";?></font> </td>
<td><font face="Trebuchet MS, Arial, Helvetica" size="2"><? echo "$row[firstname]";?></font> </td>
<td><font face="Trebuchet MS, Arial, Helvetica" size="2"><? echo "$row[state]";?></font> </td>
<td><font face="Trebuchet MS, Arial, Helvetica" size="2"><? echo "$row[newspaper]";?></font></td>
<td><font face="Trebuchet MS, Arial, Helvetica" size="2"><? echo "$row[type]";?></font>
<td><font face="Trebuchet MS, Arial, Helvetica" size="2"><? echo "$row[date]";?></font></td>
</tr>

It may be a bit crude but it works ;-) Thanks a million!

[ergophobe]

Glad to help.

You still should switch to one table when you get a chance.

Have fun!

Tom