Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
Thread: SQL Question
03-09-2009, 04:53 PM #1
- Join Date
- Mar 2009
- Thanked 0 Times in 0 Posts
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]')";
03-09-2009, 05:00 PM #2
- Join Date
- Dec 2005
- Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
- 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.)
03-10-2009, 12:11 AM #3
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:
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.Code:INSERT INTO checkout (full_name, user_idnum, date, book) SELECT full_name, user_idnum, NOW(), book FROM otherDB.otherTable WHERE user_idnum = 7731;
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.
Or, *probably* in PHP terms:Code:INSERT INTO checkout (full_name, user_idnum, date, book) SELECT full_name, 7731, NOW(), book FROM otherDB.tableOfBooks WHERE book = 'BS78133125'
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.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';";
Not a PHP user, either, so forgive my PHP syntax error(s).
03-10-2009, 12:13 AM #4
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 #5
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- Thanked 148 Times in 139 Posts
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.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?
03-10-2009, 01:16 AM #6
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.