Using arrays to insert data into mysql

[knowram]

I am sure this is a newbe question but I have been scratching my head on this one for a while and am looking for some advice. I have 2 arrays

array 1
Array
(
    [0] => PID
    [1] => Action
    [2] => Name
    [3] => Product Name
    [4] => Description
    [5] => Phone Protocol
    [6] => Product Model ID
    [7] => Host Name Prefix
    [8] => Maximum Number of Lines
    [9] => Maximum Number of Speed Dials
    [10] => Has a Screen
    [11] => Screen is Color
    [12] => Maximum Number of Buttons
    [13] => Maximum Number of Softkeys
    [14] => Maximum Number of Calls Waiting
    [15] => Default Maximum Number of Calls Waiting
    [16] => Maximum Busy Trigger
    [17] => Default Busy Trigger
    [18] => Maximum No-Answer Ring Duration
    [19] => Default No-Answer Ring Duration
    [20] => Maximum Number of Busy Lamp Fields
    [21] => Expansion Module Enabled
)
array2
Array
(
    [0] => 1
    [1] => I
    [2] => 7960
    [3] => Cisco 7960
    [4] => Cisco 7960
    [5] => SCCP
    [6] => 7
    [7] => SEP
    [8] => 6
    [9] => 99
    [10] => Y
    [11] => N
    [12] => 6
    [13] => 0
    [14] => 200
    [15] => 4
    [16] => 200
    [17] => 2
    [18] => 300
    [19] => 12
    [20] => 99
    [21] => Y
)

 

The first array is all the table headers and the second is the data that I would like to add to the table. I have been trying to come up with some way to use a loop or something to build an insert statement but haven't been able to come up with anything. Does anyone know any tricks? Thanks for the help.

[Jax2]

for ($i = 0; $i < 21; $i++) {
              $sql = "insert into **tablename** (**cell name**) values ( '$**value**[$i]')";
                 $result = mysql_query($sql ,$db);
              }

 

Mess around with that a bit... create one for the first array value and one for the second right after. It will loop 21 times (as you had 21 items) ...

 

 

[knowram]

for ($i = 0; $i < 21; $i++) {
              $sql = "insert into **tablename** (**cell name**) values ( '$**value**[$i]')";
                 $result = mysql_query($sql ,$db);
              }

 

Mess around with that a bit... create one for the first array value and one for the second right after. It will loop 21 times (as you had 21 items) ...

 

 

 

Wouldn't that insert a new row each loop?

[Jax2]

Ahh, misunderstood what you were trying to do, sorry. Guess that won't work after all :/

[knowram]

Ahh, misunderstood what you were trying to do, sorry. Guess that won't work after all :/

 

Yeah my first thought was to do something like that. An insert the first time around the loop and then an update each time after that but I am hoping there is an easier way.

[Tazerenix]

// create table with first array

$createtbl = "CREATE TABLE **tablename**
(
";
foreach($array1 as $header)
{
if ($header == "PID") $createtbl .= "`" . $header . "` AUTO_INCREMENT NOT NULL,";
else $createtbl .= "`" . $header . "`,";
}
$createtbl .= "PRIMARY KEY(`PID`)
)";

if (!mysql_query($createtbl, $conn))
{
echo mysql_error();
}

// insert data with second array

$insertdata = "INSERT INTO **tablename** (";
foreach($array1 as $columns)
{
if ($columns == "PID") break; // Dont need to insert the first value
else $insertdata .= "`" . $columns . "`,";
}
$insertdata .=") VALUES (";
foreach($array2 as $count => $data)
{
if ($count == 0) break; // dont insert id
else $insertdata .= "`" . $data . "`,";
}
$insertdata ,= ")";

if (!mysql_query($insertdata, $conn))
{
echo mysql_error();
}

// Everything should have worked if no mysql errors were shown 

 

If that doesnt work just echo $createtbl and $insertdata and edit them with notepad then enter query manually

 

The code above will probably fail tho, i havent tested it

[knowram]

sorry should have added that this is an existing table and I just want to add a new row to it with the data in the second array.

[Tazerenix]

then just use the second half of that script, ignore the $createtbl part. You'll still need both arrays tho

[knowram]

Thanks Tazerenix

 

After a little tweaking I got your script to work perfectly

[Tazerenix]

no problems