Easier way to insert a long array into an INSERT query?

Question

I'm trying to insert this array into a database:

Array ( [0] => 1 [1] => 2376 [2] => 1804229942 53 [3] => 99 [4] => 120510105 5525 [5] => 99 [6] => 21581237 622 [7] => 99 [8] => 46612404 3 [9] => 99 [10] => 174284497 8 [11] => 99 [12] => 200000000 922 [13] => 99 [14] => 13641206 5 [15] => 99 [16] => 118438707 15 [17] => 99 [18] => 200000000 23333 [19] => 99 [20] => 13444999 9 [21] => 99 [22] => 200000000 17591 [23] => 99 [24] => 13067574 7 [25] => 99 [26] => 200000000 2 [27] => 99 [28] => 200000000 140 [29] => 99 [30] => 17959240 2087 [31] => 99 [32] => 13272978 9 [33] => 99 [34] => 53265829 2236 [35] => 99 [36] => 13194322 101 [37] => 99 [38] => 43703220 48 [39] => 99 [40] => 38083976 47 [41] => 99 [42] => 38458116 184 [43] => 99 [44] => 22217059 5267 [45] => 99 [46] => 13396733 101 [47] => 99 [48] => 15724235 903 [49] => 99 [50] => 13373505 -1 [51] => -1 -1 [52] => -1 -1 [53] => -1 -1 [54] => -1 -1 [55] => -1 )

I have all the corresponding fields, but my query is going to be huge. I was wondering if there was some trick to bypassing having to type $array[1], $array[2], etc. 75x.

mysql_query("INSERT INTO `gotskills_clantrack`.`track_users` (`id`, `user_id`, `clan_id`, `date`, `skill1rank`, `skill1lvl`, `skill1xp`, `skill2rank`, `skill2lvl`, `skill2xp`, `skill3rank`, `skill3lvl`, `skill3xp`, `skill4rank`, `skill4lvl`, `skill4xp`, `skill5rank`, `skill5lvl`, `skill5xp`, `skill6rank`, `skill6lvl`, `skill6xp`, `skill7rank`, `skill7lvl`, `skill7xp`, `skill8rank`, `skill8lvl`, `skill8xp`, `skill9rank`, `skill9lvl`, `skill9xp`, `skill10rank`, `skill10lvl`, `skill10xp`, `skill11rank`, `skill11lvl`, `skill11xp`, `skill12rank`, `skill12lvl`, `skill12xp`, `skill13rank`, `skill13lvl`, `skill13xp`, `skill14rank`, `skill14lvl`, `skill14xp`, `skill15rank`, `skill15lvl`, `skill15xp`, `skill16rank`, `skill16lvl`, `skill16xp`, `skill17rank`, `skill17lvl`, `skill17xp`, `skill18rank`, `skill18lvl`, `skill18xp`, `skill19rank`, `skill19lvl`, `skill19xp`, `skill20rank`, `skill20lvl`, `skill20xp`, `skill21rank`, `skill21lvl`, `skill21xp`, `skill22rank`, `skill22lvl`, `skill22xp`, `skill23rank`, `skill23lvl`, `skill23xp`, `skill24rank`, `skill24lvl`, `skill24xp`, `skill25rank`, `skill25lvl`, `skill25xp`) 
VALUES(1,1,now(), array_values_here)") or die(mysql_error());

---

Thank you, najmeddine. That worked flawlessly. :)

Answer 1

If you are inserting all the columns (not skipping any) you can omit the column list. However, this is not considered good practice as it makes things more difficult to debug.

Answer 2

if you want to bypass typing $array[1], $array[2]... you can user a FOR to build you query in a string

if your query is to big and mysql will fail to run it:
you can insert just 10 fields, then return the inserted ID of your inserted row
then update that row with the next 10 fields until you finish
do not forget to make all your columns to accept null values

Answer 3

Hi,

You can do the opposite: A few fields in the table holding strings with separators (some kind of CSV in each field).

You can take the advantage that everything you want to save is a number.

In example:

Table.field1 := 'V01:1V02:2376V031804229942'
Table.field2 := 'V45:10V46:175'
...

Regards.

Answer 4

    $query = sprintf("INSERT INTO `gotskills_clantrack`.`track_users` (`id`, `user_id`, `clan_id`, `date`, `skill1rank`, `skill1lvl`, `skill1xp`, `skill2rank`, `skill2lvl`, `skill2xp`, `skill3rank`, `skill3lvl`, `skill3xp`, `skill4rank`, `skill4lvl`, `skill4xp`, `skill5rank`, `skill5lvl`, `skill5xp`, `skill6rank`, `skill6lvl`, `skill6xp`, `skill7rank`, `skill7lvl`, `skill7xp`, `skill8rank`, `skill8lvl`, `skill8xp`, `skill9rank`, `skill9lvl`, `skill9xp`, `skill10rank`, `skill10lvl`, `skill10xp`, `skill11rank`, `skill11lvl`, `skill11xp`, `skill12rank`, `skill12lvl`, `skill12xp`, `skill13rank`, `skill13lvl`, `skill13xp`, `skill14rank`, `skill14lvl`, `skill14xp`, `skill15rank`, `skill15lvl`, `skill15xp`, `skill16rank`, `skill16lvl`, `skill16xp`, `skill17rank`, `skill17lvl`, `skill17xp`, `skill18rank`, `skill18lvl`, `skill18xp`, `skill19rank`, `skill19lvl`, `skill19xp`, `skill20rank`, `skill20lvl`, `skill20xp`, `skill21rank`, `skill21lvl`, `skill21xp`, `skill22rank`, `skill22lvl`, `skill22xp`, `skill23rank`, `skill23lvl`, `skill23xp`, `skill24rank`, `skill24lvl`, `skill24xp`, `skill25rank`, `skill25lvl`, `skill25xp`) 
    VALUES(1,1,now(), %s)", implode(", ", $array_values_here))

    $result = mysql_query($query);