Jump to content

Inserting an array into mysql database


terungwa
Go to solution Solved by Ch0cu3r,

Recommended Posts

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 by terungwa
Link to comment
Share on other sites

  • Solution

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);
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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);
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.