2

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?

Jared Farrish
  • 48,585
  • 17
  • 95
  • 104
John Smith
  • 107
  • 1
  • 10
  • 1
    If you can run multiple statements, you can put a `;` at the end of each statement. I think PDO or `mysqli_` can do that (if allowed), but `mysql_` (note, no `i`) cannot (which you should STOP USING as it's deprecated and old and no longer relevant). – Jared Farrish Sep 29 '12 at 22:39
  • Please, considere using PDO, as it is more secure and have a bunch of helper methods. – Ricardo Souza Sep 29 '12 at 22:41
  • Oh, then you should use php to have the data already and not use SQL. php does processing NOT SQL. Get the data with your php server then give it to SQL. Yes, SQL CAN, but its not wise, its is where it will bottleneck. – Case Sep 29 '12 at 22:43

3 Answers3

4

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.

Community
  • 1
  • 1
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
4

Can you use the INSERT INTO ... SELECT ... syntax?

E.g. something like:

INSERT INTO table2 (name, num)
SELECT 'name', num
FROM table1
WHERE user='user'

(untested so please forgive any minor errors here...)

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208
0

Scrap this.

The best answer is to have PHP have the data that MYSQL is going to need. Doing it any other way will cause bottlenecks and is inefficient.

Case
  • 4,244
  • 5
  • 35
  • 53
  • Depends heavily on database structure. It's like teaching a beginning Math Class. Well 2 + 2 = 4, Well not really, because what does equals really mean. I have no idea how often this is going to be called, but if its frequently, than it shouldn't be handled by sql. However, if you are going to do it the method your posted Fluffeh is best. – Case Sep 29 '12 at 22:46