52 users online (1 members and 51 guests)  


  Results 1 to 10 of 10

Related

  1. Generating reports from databases    Forum: CGI Perl Forum
    Replies: 2
  2. databases    Forum: Databases
    Replies: 1
  3. Shopping carts and databases    Forum: Website Scripts Forum
    Replies: 1
  4. Shopping carts and databases    Forum: HTML Forum
    Replies: 0
  5. Changing Databases???    Forum: Web Hosting Forum
    Replies: 1
  1. #1
    asterixx's Avatar
    New User

    Status
    Offline
    Join Date
    Apr 2006
    Posts
    3

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

  2. #2
    vinyl-junkie's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    721

    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

  3. #3
    DeadMeatGF's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    381

    Re: Query Several Databases From Search Field

    Quote Originally Posted by asterixx
    ...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 ...

  4. #4
    asterixx's Avatar
    New User

    Status
    Offline
    Join Date
    Apr 2006
    Posts
    3

    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>'; 
        } 
    } 
    ?>
    The "problem" with this one is that all databases are queried. I've tried to change
    Code:
     
    'SHOW TABLES'
    with
    Code:
     
    '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
    PHP Code:
    You have an error in your SQL syntaxCheck 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!

  5. #5
    vinyl-junkie's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    721

    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

  6. #6
    asterixx's Avatar
    New User

    Status
    Offline
    Join Date
    Apr 2006
    Posts
    3

    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?

  7. #7
    vinyl-junkie's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    721

    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

  8. #8
    DeadMeatGF's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    381

    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

  9. #9
    DeadMeatGF's Avatar
    Moderator

    Status
    Offline
    Join Date
    Sep 2005
    Posts
    381

    Re: Query Several Databases From Search Field

    Quote Originally Posted by asterixx
    Code:
    'SELECT * FROM TABLE1 LEFT JOIN TABLE2 USING zip'
    gave me the following error
    PHP Code:
    You have an error in your SQL syntaxCheck 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
    Code:
     
    'SELECT * FROM TABLE1 LEFT JOIN TABLE2 USING (zip)'

  10. #10
    uthus's Avatar
    Senior Member

    Status
    Offline
    Join Date
    Feb 2006
    Location
    Ft Hood
    Posts
    75

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



Tags for this Thread