insert array into mysql based on key value

Question

I have searched for a simliar solution to my problem, but I am struggling to find it so please excuse me if this has already been asked. I have my php code as such

foreach($data['cells'] as $row) {
    print_r($row);
}

which produces

Array
(
    [1] => ASSET ID
    [2] => SERIAL IN CAPS
    [3] => COMPANYASSETTAG
    [4] => DATE RCVD
    [5] => MFR
    [6] => Type
    [7] => MODEL
    [8] => PRINTER MODEL COMMENTS
    [9] => PART NUMBER
    [10] => R ID
    [11] => GRADE
    [12] => PRICE
    [13] => COLOR CAPABLE (Color or Monochrome)
    [14] => COSMETICALLY ACCEPTABLE (Yes or No)
    [15] => PRINTERCABLEINCLUDED (Yes or No)
    [16] => PRINTER TECHNOLOGY (Laser, Inkjet, 4-1 Laser, 3-1 Laser, 4-1 Ink Jet, 3-1     Ink Jet, Dot Matrix, Plotter, Solid Ink, Thermal)
    [17] => DUPLEX (Yes or No)
    [18] => MULTIFUNCTION (Yes or No)
    [19] => COMMENT  (reason)
    [20] => COSMETICS COMMENT
    [21] => PURCHASE ORDER # (Trailer #)
    [22] => WAYBILL#
)
Array
(
    [1] => CNGYF04230
    [2] => CNGYF04230
    [3] => MISSING
    [4] => 28/12/2012
    [5] => Hewlett Packard
    [6] => Multi-Function Printers
    [8] => 4345X
    [9] => Q3943A
    [11] => G
    [13] => Monochrome
    [14] => Yes
    [15] => No
    [16] => Laser
    [17] => Yes
    [18] => Yes
    [21] => TRDS293
    [22] => HM693800
)

As you can see the key's in the second array are missing ones compared to the above array. These correspond to the column. The reason some are missing in the second is because those fields did not contain any data. I want to be able to insert these into the appropriate fields in the table in mysql based on the keys.

For instance

foreach($data['cells'] as $row) {
    //print_r($row);
    $sql = "INSERT INTO table (asset_id,serial_in-caps...) VALUES ('$row[0]','$row[1]'...)";
}

You should *never* generate your query string this way. http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php — Michael - sqlbot, Dec 31 '12 at 23:19

atreju · Accepted Answer · 2013-01-01T01:13:04.777

2

As you have numbers as keys in your array, you can loop through your array with a for-loop:

$keys = array();
foreach($data["cells"] as $row) {
    if(empty($keys)) {
        $keys = $row;
        continue;
    }

    $rowValues = array();
    for($i = 1; $i < count($keys); $i++) {
        if(isset($row[$i]))
            $rowValues[] = $row[$i];
        else
            $rowValues[] = "NULL"; //(or another standard value)
    }
    /* continue with the variable $rowValues */
}

I hope it helps...

edited Jan 01 '13 at 01:13

answered Dec 31 '12 at 22:56

atreju

477
4
14

This for($i = 0; $i < count($keys); $i++) { should be for($i = 1; $i <= count($keys); $i++) { or it will produce an empty field in the beginning – Cesar Bielich Jan 01 '13 at 01:15

score 1 · Answer 2 · answered Dec 31 '12 at 22:53

1

I just found the solution.

foreach($data['cells'] as $row) {
    //print_r($row);
    foreach($row as $key => $value) {
        echo "$key is at $value<br>";
    }
}

answered Dec 31 '12 at 22:53

Cesar Bielich

4,754
9
39
81

score 0 · Answer 3 · answered Jan 01 '13 at 01:05

0

@atreju

Your code was perfect, just cleaned up a few missing characters :)

$keys = array();
foreach($data["cells"] as $row) {
    if(empty($keys)) {
        $keys = $row;
        continue;
    }

    $rowValues = array();
    for($i = 1; $i <= count($keys); $i++) {
        if(isset($row[$i]))
            $rowValues[] = $row[$i];
        else
            $rowValues[] = "NULL"; //(or another standard value)
        }
    /* continue with the variable $rowValues */
}

answered Jan 01 '13 at 01:05

Cesar Bielich

4,754
9
39
81

I think the ´<= count($keys)´ should only be ´< count($keys)´, since the last element of an array has the index one smaller than the length of the array. In fact, you have a very special array since it starts with key 1 and not (as usual) key 0 – atreju Jan 01 '13 at 01:16
now that you mention that, I need to find out why it is starting with 1 and not 0 :) thanks – Cesar Bielich Jan 01 '13 at 02:49

insert array into mysql based on key value

3 Answers3