CRC CRC - 3 months ago 5
MySQL Question

PHP Arrays insert into MySQL Table

newbie with PHP and MySQL here. I've looked through other articles here to try and piece together the code that I'm after but struggling to get the content of two arrays inserted into two columns within a MySQL table.

The MySQL table is structured into three columns as follows -

|-------|-------|-------|
| id | date | value |
|-------|-------|-------|


id is primary key set with auto_increment.
date is of date datatype.
value is of decimal(5,2) datatype.

I have two PHP arrays
$dates
and
$values
which contain the following -

echo '<pre>'; print_r($dates); echo '</pre>';
Array
(
[0] => 2015
[1] => 2014
[2] => 2013
[3] => 2012
[4] => 2011
[5] => 2010
[6] => 2009
[7] => 2008
[8] => 2007
[9] => 2006
[10] => 2005
[11] => 2004
[12] => 2003
[13] => 2002
[14] => 2001
[15] => 2000
[16] => 1999
[17] => 1998
[18] => 1997
[19] => 1996
[20] => 1995
[21] => 1994
[22] => 1993
[23] => 1992
[24] => 1991
[25] => 1990
[26] => 1989
[27] => 1988
[28] => 1987
)

echo '<pre>'; print_r($values); echo '</pre>';
Array
(
[0] => 52.32
[1] => 98.97
[2] => 108.56
[3] => 111.63
[4] => 111.26
[5] => 79.61
[6] => 61.74
[7] => 96.94
[8] => 72.44
[9] => 65.16
[10] => 54.57
[11] => 38.26
[12] => 28.85
[13] => 24.99
[14] => 24.46
[15] => 28.66
[16] => 17.9
[17] => 12.76
[18] => 19.11
[19] => 20.64
[20] => 17.02
[21] => 15.86
[22] => 17.01
[23] => 19.32
[24] => 20.04
[25] => 23.76
[26] => 18.23
[27] => 14.91
[28] => 18.53
)


I've serialized the data into two new variables prior to my insert query -

$mydates = serialize($dates);
$myvalues = serialize($values);


My insert statement is then as follows -

$query = "INSERT INTO eia(date,value) VALUES ('$mydates','$myvalues')";


with some error handling -

if ($conn->query($query)) {
$msg = ...Sucessfully Entered!
} else {
$msg = ...Error Entering!
}
if (isset($msg)){
echo $msg;
}
if (!$conn->query("INSERT INTO eia(date,value) VALUES ('$mydates','$myvalues')")) {
printf("Errormessage: %s\n", $conn->error);
}


Then finally close the connection -

mysqli_close($conn);


When this runs I receive the 'Successfully Entered' message but on inspecting the MySQL table I just get two rows with the following -

id | date | value
1 |{null}| 0.00
2 |{null}| 0.00


Any ideas how I can get the contents of these arrays added into each column i.e.

id | date | value
1 | 2015 | 52.32
2 | 2014 | 98.97
...


Thank you in advance.

Answer

You don't need serialize here

if ($conn->query($sql)) {
   $msg = ...Sucessfully Entered!
} else {
    $msg = ...Error Entering!
}
if (isset($msg)){
  echo $msg;
}

$sql = 'INSERT INTO eia_brent(eiaDate,eiaValue) VALUES';
foreach ($dates as $index => $date) {
    $sql .= "($date, ".$values[$index]."),";
}
$sql = trim($sql,',');

if (!$conn->query($sql)) {
     printf("Errormessage: %s\n", $conn->error);
}