Inserting an array into mysql database

[terungwa]

After validating and filtering user input, my validateInput() function method returns an array of filtered input stored in a variable ($filtered).

 

The Print_r () PHP function returns the array as shown below:

 $filtered = Array (
  [LocalGovArea] => town 
  [Ward] => mak
  [PollingUnit] => imato 
  [RegVoters] => 9 
  [AccreditedVoters] => 9 
  [VotersOnQueue] => 9 
  [BallotIssued] => 9 
  [BallotUnused] => 9 
  [BallotDamaged] => 9 
  [BallotRejected] => 9 
  [aacn] => 9 
  [acpn] => 9 
  [alp] => 9 
  [onpp] => 9 
  [apuga] => 9 
  [aps] => 9 
  [arp] => 9 
  [cpc] => 9 
  [dpp] => 9 
  [lpdn] => 9 
  [ncp] => 9 
  [ntp] => 9 
  [apidp] => 9 
  [rpn] => 9 
  [unupp] => 9 
  [nap] => 9 
  [atp] => 9 
  [aln] => 9 
  [pmp] => 9 
  [ad] => 9 
  [da] => 9 
  [aa] => 9 
  [ppn] => 9 
  [pac] => 9 ) 

The array key represents my mysql table columns headings, while the key values represent the column values.

This is the insert query I attempted to use to transfer the array values into the database, shown below:

Insert.php

if (!$missing && !$errors) 
  {
   include 'includes/Connection.inc.php';
   // Everything passed validation.
   // The validated input is stored in $filtered.
  if ($filtered)
  {
 	foreach ($filtered as $key => $values)
      {   
   	$sql = 'INSERT INTO tblforensic('.$key.')VALUES('.$values.')';
	 $result = $mysqli->query($sql);
      }
	if(!$result)
	{
	    //something went wrong, display the error
	   echo 'An error occured while inserting your data. Please try again later.<br /><br />' . $mysqli->error;
	}
	else
	{
		//retrieve the id of the freshly created topic for usage in the posts query
		$id = $mysqli->insert_id;
		$success[]='successfully posted';
	}
   }
}

Thus far, the insert query treats each array $key/$values pair as a new row. THe insert query thus creates 34 rows using each $key/$values pair; rather than insert the array into one row.

 

I would appreciate help on how to insert values from this array into the database.

 

NOTE: If it helps, my table scheme is shown below:

--
-- Table structure for table `tblforensic`
--

CREATE TABLE IF NOT EXISTS `tblforensic` (
  `ForensicId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `LocalGovArea` varchar(60) NOT NULL,
  `Ward` varchar(60) NOT NULL,
  `PollingUnit` varchar(60) DEFAULT NULL,
  `RegVoters` int(10) unsigned DEFAULT NULL,
  `AccreditedVoters` int(11) DEFAULT NULL,
  `VotersOnQueue` int(11) DEFAULT NULL,
  `BallotIssued` int(11) DEFAULT NULL,
  `BallotUnused` int(11) DEFAULT NULL,
  `BallotDamaged` int(11) DEFAULT NULL,
  `BallotRejected` int(11) DEFAULT NULL,
  `aacn` int(11) DEFAULT NULL,
  `acpn` int(11) DEFAULT NULL,
  `alp` int(11) DEFAULT NULL,
  `onpp` int(11) DEFAULT NULL,
  `apuga` int(11) DEFAULT NULL,
  `aps` int(11) DEFAULT NULL,
  `arp` int(11) DEFAULT NULL,
  `cpc` int(11) DEFAULT NULL,
  `dpp` int(11) DEFAULT NULL,
  `lpdn` int(11) DEFAULT NULL,
  `ncp` int(11) DEFAULT NULL,
  `ntp` int(11) DEFAULT NULL,
  `apidp` int(11) DEFAULT NULL,
  `rpn` int(11) DEFAULT NULL,
  `unupp` int(11) DEFAULT NULL,
  `nap` int(11) DEFAULT NULL,
  `atp` int(11) DEFAULT NULL,
  `aln` int(11) DEFAULT NULL,
  `pmp` int(11) DEFAULT NULL,
  `ad` int(11) DEFAULT NULL,
  `da` int(11) DEFAULT NULL,
  `aa` int(11) DEFAULT NULL,
  `ppn` int(11) DEFAULT NULL,
  `pac` int(11) DEFAULT NULL,
  PRIMARY KEY (`ForensicId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Edited May 6, 2014 by terungwa

[Ch0cu3r]

You don't need the foreach loop.

 

You'd use array_keys to get the keys and then use implode to build the column list. You'd then do a similar thing for values, whereby you'd use array_values to get the values and again use implode to build the values

function smart_quote($val) {

   if(is_string($val))
        $val = "'$val'"; // add quotes to string values
   }
 
   return $val;
}

$sql = 'INSERT INTO tblforensic (`';
$sql .= implode('`, `', array_keys($filtered));            // build column list
$sql .= '`) VALUES ('. implode(',' array_map('smart_quote', array_values($values))) .')'; // build value list

$result = $mysqli->query($sql);

[ginerjm]

Use your cleverness to take that array and build the necessary strings to create the proper query statement, instead of just building a (decidedly incorrect) query statement that did the wrong thing.  Instead of a single column and a single value, build strings that represent what you need.

[Psycho]

Something to consider: If you were to use PDO instead of mysqli_ you can create a prepared statement and pass the entire array to the statement. You do need to specify the named indexes in the prepared statement, which you would have to write out in the code or do it programatically as Ch0cu3r did above. But, you can then create multiple records by passing the arrays.

 

Example

 

//Arrays with values to insert
$record1 = array('name' => 'John Smith', 'addr' => '123 Main Street', 'city' => 'New York');
$record2 = array('name' => 'Jane Doe', 'addr' => '425 Elm Street', 'city' => 'Los Angeles');
$record3 = array('name' => 'Alex Williams', 'addr' => '623 Delta Ave', 'city' => 'Austin');
 
//Create database handle
$dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
 
//Create prepared statement handle
$sth = $dbh->prepare("INSERT INTO my_table (name, addr, city) value (:name, :addr, :city)");
 
//Insert records using the arrays
$sth->execute($record1);
$sth->execute($record2);
$sth->execute($record3);