0

Let's straight to the problem... I have this table, named 'phonenumber' :

+-------------+
| phonenumber |
+-------------+
| 555-123456  |
| 555-999999  |
| 555-888888  |
+-------------+

now, I want to send text message to those numbers by inserting message into this table, named 'outbox' :

+------+----------------+---------------------------------------------+
| ID   | phonenumber    | message                                     |
+------+----------------+---------------------------------------------+
|                        ......                                       |
| 321  | 555-123456     | part 1 : lorem ipsum dolor sit amet         |
| 322  | 555-999999     | part 1 : lorem ipsum dolor sit amet         |
| 323  | 555-888888     | part 1 : lorem ipsum dolor sit amet         |
+------+----------------+---------------------------------------------+

it's easy to do that. this SQL can do that job :

INSERT INTO outbox (phonenumber, message)
SELECT phonenumber, '$SMSMessage' as message
FROM phonenumber
WHERE purchasedate BETWEEN 2012-01-01 AND 2012-01-31;

the problem rise when text message more than 160 characters. it need to be splited and in this case, the second part of splited message must be stored on other table named 'outbox_multipart' and keep the ID number from 'outbox' so that the table will look like this : 

+------+----------------+----------+----------------------------------+
| ID   | phonenumber    | outboxID | message                          |
+------+----------------+---------------------------------------------+
|                      ......                                         |
| 1025 | 555-123456     | 321      | part 2 : hello there!            |
| 1026 | 555-999999     | 322      | part 2 : hello there!            |
| 1027 | 555-888888     | 323      | part 2 : hello there!            |
+------+----------------+---------------------------------------------+

please note : outbox.ID relates with outbox_multipart.outboxID.

and I have this PHP loop to insert the text message :

foreach ($CutMessage as $index => $SMSMessage) 
{
if ($index == 0) {
**INSERT FIRST PART OF MESSAGE TO OUTBOX TABLE**
}else{
**INSERT SECOND / OTHER PART OF MESSAGE TO OUTBOX_MULTIPART TABLE**
}       
}

how to correctly done this with PHP? thank you.

Saint Robson
  • 5,211
  • 15
  • 63
  • 106

1 Answers1

1

Use mysqli_insert_id() to get the ID assigned in the last INSERT statement.

Barmar
  • 596,455
  • 48
  • 393
  • 495
  • if you're using mysqli_insert_id(), you'll get only the last one. in my case, I have SQL query with this condition : WHERE purchasedate BETWEEN 2012-01-01 AND 2012-01-31; which mean, there will be multiple rows to be inserted at once. now, the problem is... I need to get those IDs to be inserted to another table (outbox_multipart). any idea how to do it? – Saint Robson Sep 27 '12 at 02:26
  • Instead of inserting them all at once, use a SELECT query to get the data, then construct individual INSERT statements. – Barmar Sep 27 '12 at 02:35
  • I have 1,000+ rows of phone number. by selecting and inserting one-by-one, means I will have 1,000+ SQL queries to run. I don't think that's the best idea, Barmar. what if, we just collect 'the first ID' from multiple rows inserted using single INSERT query, then we 'auto increment' as many as number of rows inserted. is that possible? because I have this info : http://stackoverflow.com/questions/4637367/mysql-last-insert-id-used-with-multiple-records-insert-statement – Saint Robson Sep 27 '12 at 02:42
  • How will you know which ID goes with which phone number? You'll need to do a query like `SELECT id, phonenumber FROM outbox WHERE id > $lastID`, then use that to construct the INSERTs for outbox_multipart. – Barmar Sep 27 '12 at 03:02
  • it doesn't matter, Barmar. because all recipient on outbox_multipart will receive same message. and all recipient's phone number on both outbox and outbox_multipart table are same too. what I need is 'outbox_multipart.OutboxID' to have same *exactly as 'outbox.ID' every time that INSERT query executed. – Saint Robson Sep 27 '12 at 03:58
  • In that case it sounds like your solution should work. However, see http://stackoverflow.com/questions/8481421/does-a-mysql-multi-row-insert-grab-sequential-autoincrement-ids regarding guarantees of sequential IDs in multi-row inserts. – Barmar Sep 27 '12 at 14:13