Query Several Databases From Search Field

[Asterixx]

<?
mysql_connect("localhost","usr","pw"); 

mysql_select_db("my_db"); 

$search=$_POST["search"];

$result = mysql_query("SELECT * FROM my_table WHERE zip LIKE '%$search%'");

//grab all the content
while($r=mysql_fetch_array($result))
{	

   $zip=$r["zip"];
   $contact=$r["contact"];
   $address=$r["address"];

   echo "$zip <br> $contact <br> $address <br>";
}
?>

This is a simple thing to more advanced users. I need to query more than just "my_table" to get the results; the other tables I need to query hold the exact same info as "my_table". I tried delimiting with comas, as in "my_table, my_othertable, my_moretable", but that brought up a SQL error.

Thanks a bunch in advance,
Stephan.

[devinemke]

store all of the table names in an array (or get them via a SHOW TABLES query) and loop through with your SELECT query.

[Asterixx]

I thought mysql_query was the way to go, instead of SHOW TABLES.

Would you mind helping a bit more? I understand that I'm supposed to learn how to do this for myself and not be handed the answer, but a bit more guidance would be appreciated. 🙂

I tried editing the code (see below) but I get teh famous "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/veter69/public_html/test/search.php on line 11"

<?
mysql_connect("localhost","usr","pw"); 

mysql_select_db("db"); 

$search=$_POST["search"];

$result = mysql_query("SELECT * FROM '$array' WHERE zip LIKE '%$search%' ORDER BY zip DESC");
$array = array(db_1, db_2);

while($row=mysql_fetch_array($result))
{	

   $zip=$r["zip"];
   $contact=$r["contact"];
   $address=$r["address"];

   echo "$zip <br> $contact <br> $address <br>";
}
?>

[devinemke]

<?php
mysql_connect('localhost', 'veter69_dataentr', 'klf$$521pw') or exit(mysql_error());
mysql_select_db('veter69_resources') or exit(mysql_error());

$result = mysql_query('SHOW TABLES') or exit(mysql_error());
while ($row = mysql_fetch_row($result))
{
	$result_select = mysql_query("SELECT * FROM " . $row[0] . " WHERE zip LIKE '%" . $_POST['search'] . "%' ORDER BY zip DESC") or exit(mysql_error());
	$num_rows = mysql_num_rows($result_select);
	if ($num_rows)
	{
		echo '<b>' . $num_rows . ' found in ' . $row[0] . '</b><br>';
		while ($row_select = mysql_fetch_assoc($result_select))
		{
			echo $row_select['zip'] . '<br>';
			echo $row_select['contact'] . '<br>';
			echo $row_select['address'] . '<br>';
		}
	}
	else
	{
		echo '<b>no records found in ' . $row[0] . '</b><br>';
	}
}
?>

[Asterixx]

Very cool but that displays all the tables.

I've tried to limit the query to a couple of tables (as an example) by replacing

'SHOW TABLES'

with

"SELECT * FROM TABLE1 LEFT JOIN TABLE2 USING zip"

but that gave me the following error

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'zip' at line 1

What does that mean? I don't see 'zip' on the first line... 🙁

[devinemke]

Asterixx wrote:

I've tried to limit the query to a couple of tables...

then don't bother with the SHOW TABLES query, just store all of the table names you want in an array.

[Roger_Ramjet]

I think I understand what is going on here. Forget the query you are using and the error it is giving: wrong query form to begin with. You should be using a UNION QUERY for this.

$sql = "SELECT * FROM my_table WHERE zip LIKE '%$search%'
     UNION
     SELECT * FROM next_table WHERE zip LIKE '%$search%'
      UNION
     SELECT * FROM other_table WHERE zip LIKE '%$search%'";

Now that is going to fetch the data you want. You can modify it to identify the table the row came from, the sort order, etc. You can add many more unions if you need to query more tables.

[Asterixx]

Thanks, I'll give it a try! 🙂