Inserting PHP array into MYSQL

Question

I have a html table that is converted to JSON and sent over via AJAX to PHP. I've decoded the JSON object and now have a PHP array. I'd like to insert this array or rather the contents of it into a MySQL database.

I'm confused on some of the methodologies out there. what is the ideal method to prevent SQL injection? I see a number of methods that look like so;

 foreach ($array as $key => $value) 
    {
        $price = $array["price"][$key];
        ...................................
    }

this doesn't seem to work, or at least I can't get php to print or echo out what $price is.

I've tried this:

     foreach ($array as $key => $value) 
         {
           $price = $value["price"];
          ...................................
         }

This i can get echo or print_r to display the value.

First question is: what is the purpose of $key here in terms of inserting these values into MySQ? Second is: why doesn't the first one return the expected result, like that of the second code snippet? echo should display the value of $price? Lastly, from this assuming both methods are valid, an insert statement like below is correct for actually pushing the data into MySQL?

       $sql = mysql_query("insert into Daily_Requests values ('','$price','$item','$etc...','$etc....')");

Regards

EDIT

Here is my JSON:

[{"Price":"5000","Manufacturer":"Newton","Model":"84x26x10 43u","Model_info":"Newton 84x26x10 43u","Type":"Rack","Height_in":"83.97637795","Height_mm":"2133","Width_in":"25.98425197","Width_mm":"660","Description":"Newton 84x26x10 43u","Depth_in":"10","Depth_mm":"254","Mount_Type":"Floor Mount","Rack_UNITS":"43","Rack_INSIDE_HEIGHT_mm":"1911","Rack_INSIDE_WIDTH_mm":"584","Rack_INSIDE_DEPTH_mm":"","ASSET_TYPE":"Rack","Phases":"","Status":"","Date":"2017-01-11","Submitted":"","Image File / Web Info":"","Site":"Orlando"}]

Answer 1

Once you enter the loop

foreach ($array as $key => $value) {
    #loop entered here after open

    #each value of the array is accessible

    #end loop here before close
     }

With $key being the index value of the array as an integer and $value as the actual variable holding the value at that index. $array inside the loop is the same as outside loop. $value represents the slice of that array.

once you load the data into a new variable as $price = $value["price"] it is available until the close of the loop and resets to the new value when looped over again.

So your SQL statement out of the loop will contain the last values held by $array.

Answer 2

Whenever you have doubts about how to access or loop throuhg the elements of an array, use var_dump to visualize its structure.

I see you have an array of objects and I understand that each object is a row. So:

foreach($array as $object) {
    //Here you have each object in $object
    //$object is {"Price":"5000","Manufacturer":"Newton",...}

    //Lets build the query to insert this row. I'm gonna do this with a query string as you're already using mysql_query, but as suggested you should migrate to PDO.

    $query='';

    foreach($object as $key=>$value) {
        //To answer your $key question, here $key are Price, Manufacturer, Model, etc and $value are the correspondig values for each key

        if($query) $query.=','; //add the comma if it's not the fist var
        $query.='`'.mysql_real_escape_string($key).'`=\' '.mysql_real_escape_string($value).'\'';
    }

    $query='insert into Daily_Requests set '.$query;

    mysql_query($query);
}