Insert keys from other tables

[Darkness Soul]

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 (¹):
[code]$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]
code idea (²):
[code]$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 );[/code]
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

[fenway]

Well, you can do something like:

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

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+):

[code]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 )[/code]

Or perhaps this (also UNTESTED):

[code]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';[/code]