Build insert query from array MySQL and PHP

Question

I'm writing a small web service in PHP and I'm having a bit of trouble getting my head around the following scenario.

My plan is to be able to send an array of data to a function, which would then build a query for MySQL to run. In the array I plan for the key to be the column name, and the value to be the value going in to the columns.. i.e.

$myData = array('userName'=>'foo','passWord'=>'bar');
$myClass = new users();

$myClass->addUser($myData);

Then, in my function I currently have:

function addUser($usrData){
   foreach($usrData as $col => $val){

      // This is where I'm stuck..

   }
}

Basically, I am wondering how I can separate the keys and values so that my query would become:

INSERT INTO `myTable` (`userName`,`passWord`) VALUES ('foo','bar');

I know I could probably do something like:

function addUser($usrData){
   foreach($usrData as $col => $val){
      $cols .= "," . $col;
      $values .= ",'".$val."'";
   }
}

But I thought there might be a more elegant solution.

This is probably something really simple, but I have never came across this before, so I would be grateful for any help and direction..

Thanks,

Dave

Answer 1

Try this:

function addUser($usrData) {
   $count = 0;
   $fields = '';

   foreach($usrData as $col => $val) {
      if ($count++ != 0) $fields .= ', ';
      $col = mysql_real_escape_string($col);
      $val = mysql_real_escape_string($val);
      $fields .= "`$col` = $val";
   }

   $query = "INSERT INTO `myTable` SET $fields;";
}