Inserting array into mysql

Question

I have my array displayed like it but I cant seems to be able to save it into database. So when I do var_dump($result); while $result is my array then the following content is displayed

array
0 => 
array
  'Credit Weighting' => string '5' (length=1)
  'Teaching Period(s)' => string 'Teaching Periods 1 and 2.' (length=25)
  'No. of Students' => string '-.' (length=2)
1 => 
array
  'Credit Weighting' => string '5' (length=1)
  'Teaching Period(s)' => string 'Teaching Periods 1 and 2.' (length=25)
  'No. of Students' => string '-.' (length=2)
2 => 
array
  'Credit Weighting' => string '10' (length=2)
  'Teaching Period(s)' => string 'Teaching Periods 1 and 2.' (length=25)
  'No. of Students' => string '-.' (length=2)

And below is my PDO query to save the above content into mysql but nothing is happening.What am I doing wrong please ??

$result = array();  
$placeholder = array();
$values = "?, ?, ?";
foreach ($result as $array){
$placeholder[] = $value;

        $result[] = $array['CreditWeighting'];
        $result[] = $array['TeachingPeriod'];
        $result[] = $array['NoofStudents'];         
} 

$sql = "INSERT INTO data_array_copy (CreditWeighting,TeachingPeriod,NoofStudents)
                             VALUES (". implode('), (', $placeholder) . ")"; 
$stmt = $conn->prepare($sql);
$stmt->execute($result);

You need to serialize your data. This link will help you http://stackoverflow.com/questions/1978438/save-php-array-to-mysql — DrinkJavaCodeJava, Jun 30 '12 at 00:13
@redelman431 Thanks for the reply and link,I'll give that a go and see how that goes,thanks alot. — user1444442, Jun 30 '12 at 00:42

Lawrence Cherone · Answer 1 · 2012-06-30T00:27:32.050

Try This:

<?php 
//If your array is:
/*
$result = array(0=>array('Credit Weighting'=>'5',
                         'Teaching Period(s)'=>'Teaching Periods 1 and 2.',
                         'No. of Students'=> '-.'),
                1=>array('Credit Weighting'=>'5',
                         'Teaching Period(s)'=>'Teaching Periods 1 and 2.',
                         'No. of Students'=> '-.'),
                2=>array('Credit Weighting'=>'10',
                         'Teaching Period(s)'=>'Teaching Periods 1 and 2.',
                         'No. of Students'=> '-.'));
*/
//The query
$sql = "INSERT INTO data_array_copy (CreditWeighting,TeachingPeriod,NoofStudents)
               VALUES (?,?,?)"; 
//Prepare the query
$stmt = $conn->prepare($sql);

//Loop through the $result array
foreach ($result as $array){
    //Bind and execute the values to the prepared query
    $stmt->bindParam(1, $array['Credit Weighting']);
    $stmt->bindParam(2, $array['Teaching Period(s)']);
    $stmt->bindParam(3, $array['No. of Students']);
    $stmt->execute($result);
}
?>

There are other methods to bind params at http://php.net/manual/en/pdo.prepared-statements.php but until your familiar with the ins and outs make it as readable as possible. This way you can or other devs can see whats going on without var_dump'ing arrays to get key names ect. :)

Thanks for the reply.I tried the sample code you wrote above and nothings is happening.No error coming up.Maybe I should echo my query and see maybe any value is actually in the query before it's executed and saved into database. — user1444442, Jun 30 '12 at 00:40
enable `error_reporting(E_ALL)` also make sure your PDO connection is reporting errors and connected ect. The `?` in `$sql` wont get replaced, the query is emulated. Check the link to the manual I added and also there is http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html which has some good examples. hope it helps — Lawrence Cherone, Jun 30 '12 at 00:47
Hey,I do have `ini_set('display_errors',1);` and `error_reporting(E_ALL);` at the top of my page.Thanks for the link to the manual.I'll have a look at it and try to figure out what's wrong.Cheers. — user1444442, Jun 30 '12 at 00:59

score -1 · Accepted Answer · answered Jun 30 '12 at 01:51

Ok just in case there is anyone out there that might need to know a easier way this could be done. Here is the solution that works well.

$result = array();  
foreach($result as $snode) { 

 foreach($snode as $key => &$val) {
  $val = mysql_real_escape_string($val);      
 }

$query = sprintf("INSERT INTO data_array_copy (
        CreditWeighting,
        TeachingPeriod,
        NoofStudents) 
                    VALUES ('%s')",implode("','",$snode));

mysql_query($query) or die (mysql_error());
echo $query. '<br />'; 
}

This works great !!

Inserting array into mysql

2 Answers2