Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6

Thread: SQL Question

  1. #1
    New Coder
    Join Date
    Mar 2009
    Posts
    25
    Thanks
    3
    Thanked 0 Times in 0 Posts

    SQL Question

    I was wondering if you could select a value from another database and then insert it into another database. In my case I would like to:
    SELECT full_name FROM users and have the value output in the script query below.
    PHP Code:
     $query "INSERT INTO checkout (full_name, user_idnum, date, book) VALUES ('[full_name value]', '$_POST[user_idnum]', 'NOW()', '$_POST[bookisbn]')"
    Is this possible?
    Thanks,
    Eric

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    That looks like PHP code so I'll assume you're using PHP... from what I can remember about this, you can't access two different databases in one query. However, since you're using PHP anyway, it's a fairly trivial matter to fetch from one DB and then insert into another DB using the values you fetched. (You can connect to multiple databases at once; just select the appropriate database using mysql_select_db() or mysqli_select_db() before you run each query.)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Fumigator: Why would the use of PHP matter???

    The query is passed to the DB engine, so if the DB engine allows cross DB queries, how would PHP even be aware of what happened?

    With MySQL, cross DB stuff is trivial. Example:
    Code:
    INSERT INTO checkout (full_name, user_idnum, date, book) 
    SELECT full_name, user_idnum, NOW(), book 
    FROM otherDB.otherTable 
    WHERE user_idnum = 7731;
    That's probably not exactly what he wants, because that would copy *ALL* books associated with user_idnum 7731 from otherDB.otherTable to the checkout table in the current DB.

    But I would think that this format could be adapted to his needs, without any necessity for first pulling the values from the otherDB and then using them in a separate INSERT.

    ************

    Added:

    Looks to me like he only wants to copy full_name, for a given book by ISBN. So likely the table in the other DB does *NOT* have any user_idnum.

    So:
    Code:
    INSERT INTO checkout (full_name, user_idnum, date, book) 
    SELECT full_name, 7731, NOW(), book 
    FROM otherDB.tableOfBooks 
    WHERE book = 'BS78133125'
    Or, *probably* in PHP terms:
    Code:
    $uid = $_POST[user_idnum];
    $isbn = $_POST[bookisbn];
    $query = "INSERT INTO checkout (full_name, user_idnum, date, book) "
           . "SELECT full_name, '$uid', NOW(), book " 
           . "FROM otherDB.tableOfBooks "
           . "WHERE book = '$isbn';";
    If user_idnum in checkout table is a NUMERIC field, then there should NOT be apostrophes aroung '$uid'. Ditto for book field, but I suspect it is okay. Definitely NOT '...' around the NOW() function call.

    Not a PHP user, either, so forgive my PHP syntax error(s).

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    And by the by, I did stuff like this in JSP (Java) code all the time, when we were building a multi-tiered system. The "backend" DB and the "frontend" DB were separate and then we had an "integration" layer that used syntax like this to copy appropriate pieces of content from backend to frontend. Worked like a charm with MySQL 5.1.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    The query is passed to the DB engine, so if the DB engine allows cross DB queries, how would PHP even be aware of what happened?
    I think Fumigator was thinking of multiple logins rather than just multiple databases. If each database has a different connection/login then it is possible that you can't do it with php.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Oh, sure. The DB user doing the query must have at least read permissions to the second DB. But that's true no matter what server environment: JSP, PHP, ASP, ASP.NET. You would need two separate connections in that case, of course.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •