Jump to content


Photo

Insert keys from other tables


  • Please log in to reply
1 reply to this topic

#1 Darkness Soul

Darkness Soul
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts
  • LocationBrazil; São Paulo.

Posted 02 May 2006 - 03:23 PM

Hi,

I have a question about syntax.. I want to use an insert or update with select inside it.. I have it work¹ but want to make it faster with this idea²..

code working (¹):
$sport_q = mysql_query ( 'SELECT id FROM tbSports WHERE name="climb" LIMIT 1' , $conn );
$sport_f = mysql_fecht_array ( $sport_q );
$sport   = $sport_f['id'];

$other_q = mysql_query ( 'SELECT id FROM tbOther WHERE name="movies" LIMIT 1' , $conn );
$other_f = mysql_fecht_array ( $other_q );
$other   = $other_f['id'];

$write_q = mysql_query ( "INSERT INTO tbIds ( idsport , idother , iduser ) VALUES ( $sport , $other , $iduser )" , $conn );
code idea (²):
$sport_q = 'SELECT id FROM tbSports WHERE name="climb" LIMIT 1';
$other_q = 'SELECT id FROM tbOther WHERE name="movies" LIMIT 1';
$write_q = mysql_query ( "INSERT INTO tbIds ( idsport , idother , iduser ) VALUES ( $sport_q , $other_q , $iduser )" , $conn );
I'm doing it because i have about eight selects inside my insert/update, and the file have about 25000 lines.. so, I want to make it import faster..

Thank you.

D.Soul
(If something is wrong, please tell me. I'm learning this language. Thank you)

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 May 2006 - 07:03 PM

Well, you can do something like:

INSERT INTO table1 ( a, b, c ) SELECT d, e, f FROM table2 WHERE ....

But in your case, because each inserted value is from a different table, even translating this into subqueries wouldn't make it "faster", since the server has to do the same amount of work; although you'd save the PHP overhead back & forth.

You can test it yourself with the following code (UNTESTED, 4.1+):

INSERT INTO tbIds ( idsport , idother , iduser ) VALUES ( (SELECT id FROM tbSports WHERE name="climb" LIMIT 1) , (SELECT id FROM tbOther WHERE name="movies" LIMIT 1) , $iduser )

Or perhaps this (also UNTESTED):

INSERT INTO tbIds ( idsport , idother , iduser ) SELECT (SELECT id FROM tbSports WHERE name="climb" LIMIT 1) AS idsport, (SELECT id FROM tbOther WHERE name="movies" LIMIT 1) AS idother, '$iduser';

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users