Php Mysql Insert From Array

Last Updated: Apr. 5th 2022 at 2:06am

I first wrote this article in 2009 when CodeIgniter’s active record class did most of the db work for you. I don’t use code CodeIgniter any more as I’ve created my own php framework. Now in 2021 I find myself again looking for a simple database helper because the mysqli packages in composer do not support php8.

Here is my simple php class helper for mysqli.

/**
*  Creates an sql string from an associate array
*  $table in the db table name
*  $array is the array
*  $insert is optional.  allows a user to use an update
*  V1.0
*  Last Updated Oct 4. 2009
**/
function _arrayToSql($table, $array, $insert = "INSERT INTO") {

  //Check if user wants to insert or update
  if ($insert != "UPDATE") {
    $insert = "INSERT INTO";
  }

  $columns = array();
  $data = array();

  foreach ( $array as $key => $value) {
    $columns[] = $key;
    if ($value != "") {
      $data[] = "'" . $value . "'";
    } else {
      $data[] = "NULL";
    }

    //TODO: ensure no commas are in the values
  }

  $cols = implode(",",$columns);
  $values = implode(",",$data);

$sql = <<<EOSQL
  $insert `$table`
  ($cols)
  VALUES
  ($values)
EOSQL;
      return $sql;

}
//End _arrayToSql()

Reference