How can one split an array to insert it into another for an SQL query?

Question

I have the array $total as depicted with pint_r:

Array
(
[0] => 1,2,3,4,5,6,7,8,9,10,11,12,1
[1] => 13,14,15,16,17,18,19,20,21,22,23,24,1
[2] => 25,26,27,28,29,30,31,32,33,34,35,36,1
[3] => 37,38,39,40,41,42,43,44,45,46,47,48,1
[4] => 49,50,51,52,53,54,55,56,57,58,59,60,1
[5] => 61,62,63,64,65,66,67,68,69,70,71,72,1
[6] => 73,74,75,76,77,78,79,80,81,82,83,84,1
[7] => 85,86,87,88,89,90,91,92,93,94,95,96,1
[8] => 97,98,99,100,101,102,103,104,105,106,107,108,1
[9] => 109,110,111,112,113,114,115,116,117,118,119,120,1
[10] => 121,122,123,124,125,126,127,128,129,130,131,132,1
[11] => 133,134,135,136,137,138,139,140,141,142,143,144,1
[12] => 145,146,147,148,149,150,151,152,153,154,155,156,1
[13] => 157,158,159,160,161,162,163,164,165,166,167,168,1
[14] => 169,170,171,172,173,174,175,176,177,178,179,180,1
[15] => 181,182,183,184,185,186,187,188,189,190,191,192,1
[16] => 193,194,195,196,197,198,199,200,201,202,203,204,1
[17] => 205,206,207,208,209,210,211,212,213,214,215,216,1
[18] => 217,218,219,220,221,222,223,224,225,226,227,228,1
[19] => 229,230,231,232,233,234,235,236,237,238,239,240,1
[20] => 241,242,243,244,245,246,247,248,249,250,251,252,1
[21] => 253,254,255,256,257,258,259,260,261,262,263,264,1
[22] => 265,266,267,268,269,270,271,272,273,274,275,276,1
[23] => 277,278,279,280,281,282,283,284,285,286,287,288,1
)

What my goal is, is to make an SQL insert query that inputs each of these numbers into a MYSQL table, each number 2 a row. Ending in a table with 288 rows labeled 1-288 in the column PortNUmbers (please ignore the trailing 1, it seems to have taged along for the ride when i ran sort()) so this is my SQL:

$sqlinsert='
    INSERT INTO '.$tbl_name.'
    (PortNumber) VALUES (1),(2),(3)etc..';

pretty simple, i would just explode it put it in a while loop and then implode for the query, however I need the values to be inserted in in a rather odd order, like so:

(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(145),(146),(147),(148),(149),(150),(151),(152),(153),(154),(155),(156)

and so on following that same pattern until the end is met. Any one have any ideas as to how i could achieve this?

Achieve what? The pattern for sorting? You haven't really specified what this pattern is. pow(12,x)+y for x=0..n and y=1..12? — dev-null-dweller
– dev-null-dweller, Commented Jan 22, 2013 at 22:54
Why don't you insert the data in you table with a while loop and then sort the data how you like it when you select data from the database. I am not sure but is reason behind this that you want to use an ID column with auto_increment and to have the "correct" order? Like so, ID 1 has value 1 ... ID 12 has value 12 and ID 13 has value 145... — Mr. Radical
– Mr. Radical, Commented Jan 22, 2013 at 23:03

Bill Karwin · Accepted Answer · 2013-01-22 22:59:25Z

2

SQL tables have no implicit order. You shouldn't rely on the order you insert values being the order in which they will be returned when you query them. You must use ORDER BY to have a dependable order for a query result.

If you need to record the order in which you input the data, you need another column.

For example:

CREATE TABLE TblName (
  id INT AUTO_INCREMENT PRIMARY KEY,
  PortNUmber INT NOT NULL
);

INSERT INTO TblName (PortNumber) VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
...etc...

The auto-increment will preserve the order in which you inserted the values, and then you can insert the values in a rather odd order instead of numerical order however you wish.

answered Jan 22, 2013 at 22:59

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick Over a year ago

So basically pull them out of the table based on auto id in increments of 12? I cant believe i didnt think of that. +1 for my retardation.

Collectives™ on Stack Overflow

How can one split an array to insert it into another for an SQL query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related