View Full Version : SQL Question

03-09-2009, 04:53 PM
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.

$query = "INSERT INTO checkout (full_name, user_idnum, date, book) VALUES ('[full_name value]', '$_POST[user_idnum]', 'NOW()', '$_POST[bookisbn]')";

Is this possible?

03-09-2009, 05:00 PM
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.)

Old Pedant
03-10-2009, 12:11 AM
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:

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.



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.


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:

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

Old Pedant
03-10-2009, 12:13 AM
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.

03-10-2009, 12:26 AM
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.

Old Pedant
03-10-2009, 01:16 AM
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.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum