CODEWITHSUNDEEP
phpmysql
Saint Robson
Saint Robson

Reputation: 5525

MySQL : Insert Data With Single Query, But Auto Increment Number On Non-Auto Increment Column

I have this SQL query :

INSERT INTO db1.outbox (DestinationNumber, TextDecoded)
SELECT User.CellPhone, '$SMSMessage' as TextDecoded
FROM db2.User
WHERE User.PurchaseDate BETWEEN 2012-01-01 AND 2012-01-31

it does multiple rows insertion to 'outbox' table. and I can easily get the ID number of first row by using this query :

SELECT LAST_INSERT_ID()

and let's say I have 532 as result from SELECT LAST_INSERT_ID() and I have 34 rows inserted.

how to use this 532 as initial number for other table named 'outbox_multipart' insertion and having it auto increment so the result will be like this :

+------+----------------+----------+----------------------------------+
| ID   | phonenumber    | outboxID | message                          |
+------+----------------+---------------------------------------------+
|                      ......                                         |
| 1025 | 555-123456     | 532      | part 2 : hello there!            |
| 1026 | 555-999999     | 533      | part 2 : hello there!            |
| 1027 | 555-888888     | 534      | part 2 : hello there!            |
|                                                                     |
|               ...... 34 rows inserted here .......                  |
|                                                                     |
+------+----------------+---------------------------------------------+

please note that outboxID column isn't auto increment column. but it must have auto increment number from 532 + 34 rows = 566.

Upvotes: 0

Views: 1139

Answers (2)

FajitaNachos
FajitaNachos

Reputation: 1020

What about using a for loop combined with number of affected rows in mysql. If you are using PDO(which I reccommend) you can use this http://php.net/manual/en/pdostatement.rowcount.php

So it would look something like this. 

<?php 

 $dbh = new pdo (your_database_connection_details);
 $stmt = dbh->prepare() //do your initial insert into the outbox table
 $stmt->bindParams() //bind your parameters
 $stmt->execute();

 $out_box_id =  dbh->lastInsertId();
 $row_count = dbh->rowCount();

for(x=0;x<$row_count;x++){   
  //insert $out_box_id into the outboxID column
  $stmt->execute();
  $out_box_id = $out_box_id + 1;
 }

So this should grab the last inserted id of your first insert and set the $out_box_id to it. Then you can insert $out_box_id inside your for loop. The loop with run however many times the number of rows inserted was and then at the end of each loop it will increment the out_box_id by one.

Upvotes: 0

GautamD31
GautamD31

Reputation: 28763

Try with this

ALTER TABLE outbox_multipart AUTO_INCREMENT = 532;

it will increments from the 532,

and remember that the "outboxID" should also be in auto increment initially

Upvotes: 2

Related Questions