将两个查询合并为一个需要第一个查询中的值的查询

Question

I have two SQL queries:

$res1 = mysql_query("SELECT * FROM `table1` WHERE `user`='user'");
$i = mysql_fetch_assoc($res1);    
$value1 = $i['num'];

And:

$res2 = mysql_query("INSERT INTO `table2` (`name`,`num`) VALUE ('name','$value1')");

How to combine the two SQL queries into one? I need to get the value of the variable from of a second query for the first request. Is it possible?

Answer 1

This query with a subquery should do the trick nicely for you:

insert into table 2 (`name`, `num`) 
    values ('name', (select `num` from table1 where `user`='user'));

This assumes that you aren't using anything but the num from your second query as it doesn't seem to be used in your original code.

Edit: Also, I see that this is an insert, not just a select statement, but you might well benefit from reading a rather lengthy question and answer I put up which covers off multiple tables and SQL covering unions, joins, subqueries and a bunch more stuff.