0

I have 2 tables:

1)'table'
    'id' - int autoincremented
    'name' - string

2) 'table2'
     'id' - int
     'info' - string

Lets, say the last row in table has id=20

After I insert 5 empty rows to table, last row has id=25

INSERT INTO `table` (`name`)
VALUES ((""),(""),(""),(""),(""))

1) First thing I need is to add 5 rows with same id-s and empty info-s to table2, right after they were added to table. Something like that:

INSERT INTO `table2` (`id`,`info`)
VALUES ((21,""),(22,""),(23,""),(24,""),(25,""))

Can I do it using one statement?

2) Second thing I need is to extract these values to a PHP variable, like array or string (21,22,23,24,25), so that it could be used for further actions. Is it possible at all?

Blaine_
  • 306
  • 3
  • 11

2 Answers2

1

1- you cant insert in two tables with one statment. you need two statments.

2- and if you using Mysqli or pdo you already have the option to bind parameters to variables.

edit>

    $stmt->store_result();
    $stmt->bind_result($id); ---------> here you will get $id as variable of those ids
echo_Me
  • 37,078
  • 5
  • 58
  • 78
1

  1. Not in one statement, but in one block of statements that can be encapsulated in an atomic operation. You can make the insert statements dependent on one another by placing them into a single transaction.

    More information: http://dev.mysql.com/doc/refman/5.0/en/commit.html

  2. The solution to your second problem will likely involve placing that transaction, along with some logic to return the extracted values to your application code, into a stored routine.

    More information: http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html

  • Thanks for the reply. Could you please provide an example of the transanction, basing on simple information provided in the question? – Blaine_ Jan 17 '14 at 23:14
  • I'm afraid I can't do any better than the official MySQL guide. You can find a thorough set of examples here: http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples –  Jan 17 '14 at 23:35