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%'");
 
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.

[vinyl-junkie]

Welcome to the forum, asterixx.

You cannot query multiple databases in the same query. However, you can run one query against multiple tables that are in the same database. If your tables are all in the same database and are laid out the same, you can use UNION to join all the queries together into a single query.

[DeadMeatGF]

...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.

I'm pretty sure you don't mean that all the tables hold the same data - that would be daft!
Nip over to the mySQL site and use the search for the version you have installed - as VJ said, look for "UNION" and also the "JOIN" command could come in handy.
Also, the syntax in mySQL is table.field not table,field which is why you're getting the errors ... however that is for use (as far as I know) in results, not in queries ...

[asterixx]

Here's an updated version:

 
<?php 
mysql_connect('localhost', 'usr', 'pw') or exit(mysql_error()); 
mysql_select_db('db') 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>'; 
    } 
} 
?>

The "problem" with this one is that all databases are queried. I've tried to change

 
'SHOW TABLES'

with

 
'SELECT * FROM TABLE1 LEFT JOIN TABLE2 USING zip'

in order to query only a couple of tables (for now) 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 

Do you know why that is? How can I query the tables I want to query and not all of them?
Thanks in advance!

[vinyl-junkie]

Check out what the manual says on syntax. I don't exactly what you're trying to do or I would post some sample code.

[asterixx]

Looking at the manual, which I've been doing for quite a while, doesn't give an answer to the error message.
I'm trying to query several tables, not all, within a determined database. The form holds only one field (zip) which is passed on to search.php (above).
I want to be able to search the "selected" tables for the zip code entered in the form and display the contact information for that specific zip code. Does that clarify what I'm trying to do?

[vinyl-junkie]

If the information you're trying to retrieve all comes from different databases, you'll have to do separate database connections and separate queries for each database you want to retrieve data from.

[DeadMeatGF]

I think you need joins for that.
I've not got time to dig up code just now, but until I (or someone else) gets back, look up JOIN syntax and examples at mysql.com

[DeadMeatGF]

'SELECT * FROM TABLE1 LEFT JOIN TABLE2 USING zip'

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 

Do you know why that is?

I've read back over this post, and browsing through the mySQL documentation, it might be as simple as adding brackets

 
'SELECT * FROM TABLE1 LEFT JOIN TABLE2 USING (zip)'

[uthus]

Hi asterixx,

Are you looking to do something like this?

select C.Charge, I.Invoice_Desc, P.Proj_Desc from Charge as C, Invoice as I, Project as P where C.Charge_ID=50 and C.Invoice_ID=I.Invoice_ID and C.Project_ID=P.Project_ID;

This is from a db that I'm developing to do my invoicing. It will return in this instance 1 and only 1 result.

I got most of the thinking for this from the Twins Project found in Ch 6 of the online MySQL documentation.

hth,

U