Results 1 to 10 of 10
Related
-
Generating reports from databases Forum: CGI Perl Forum
Replies: 2 -
databases Forum: Databases
Replies: 1 -
Shopping carts and databases Forum: Website Scripts Forum
Replies: 1 -
Shopping carts and databases Forum: HTML Forum
Replies: 0 -
Changing Databases??? Forum: Web Hosting Forum
Replies: 1
-
04-05-2006, 10:13 PM #1
Query Several Databases From Search Field
Code:<? 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.
-
04-05-2006, 10:34 PM #2
Re: Query Several Databases From Search Field
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.Music Around The World - Collecting tips, trade
and want lists, album reviews, & more
-
04-06-2006, 03:21 AM #3
Re: Query Several Databases From Search Field
Originally Posted by asterixx
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 ...
-
04-06-2006, 08:29 AM #4
Re: Query Several Databases From Search Field
Here's an updated version:
Code:<?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>'; } } ?>
Code:'SHOW TABLES'
Code:'SELECT * FROM TABLE1 LEFT JOIN TABLE2 USING zip'
PHP Code: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
Thanks in advance!
-
04-06-2006, 08:58 AM #5
Re: Query Several Databases From Search Field
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.
Music Around The World - Collecting tips, trade
and want lists, album reviews, & more
-
04-06-2006, 12:17 PM #6
Re: Query Several Databases From Search Field
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?
-
04-08-2006, 10:52 PM #7
Re: Query Several Databases From Search Field
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.
Music Around The World - Collecting tips, trade
and want lists, album reviews, & more
-
04-10-2006, 06:36 AM #8
Re: Query Several Databases From Search Field
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
-
07-29-2006, 06:47 AM #9
Re: Query Several Databases From Search Field
Originally Posted by asterixx
Code:'SELECT * FROM TABLE1 LEFT JOIN TABLE2 USING (zip)'
-
07-29-2006, 07:30 PM #10
Re: Query Several Databases From Search Field
Hi asterixx,
Are you looking to do something like this?
Code: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;
I got most of the thinking for this from the Twins Project found in Ch 6 of the online MySQL documentation.
hth,
U
Cloudjiffy- PaaS for Developers
10-05-2020, 12:30 AM in Web Hosting Forum