PHP insert a data array into a mysql table

Question

I have an array in php that contains some data, example:

Array
(
    [nome] => Nome
    [sobrenome] => Sobrenome
    [genero] => 1
    [email] => [email protected]
)

And I would like to insert this data into a mysql table more automatically. At the moment that Gero this array, the process is already done so that it is in accordance with the table, that is, my table is composed by columns nome, sobrenome, genero and email.

So instead of doing a query like this:

INSER INTO tabela (nome, sobrenome, genero, email) VALUES ('$nome', ...)

I wonder if there is any method to automate this execution based on the data array I have.

I thought I’d run some process to treat this array as follows:

foreach ($data as $key => $value){
    $keys   = $keys.$key.",";
    $values = $values.$values.",";
}
$keys   = substr($keys, 0,-1);
$values = substr($values, 0,-1);

INSER INTO tabela ('$keys') VALUES ('$values');

But I do not know if it is the most appropriate/ideal and/or if there is a "correct" method to perform this task.

Answer 1

Well this is not by far the best solution, I’m only giving you a north, but the ideal would be to have value escapes to avoid SQL Injection, so this is only a reduced form. ah! I have not tested.

$data = array(
    'nome' => 'Nome',
    'sobrenome' => 'Sobrenome',
    'genero' => 1,
    'email' => '[email protected]'
);

$sql = sprintf(
    "INSERT INTO tabela (%s) VALUES ('%s');",
    implode(',', array_keys($data)),
    implode("','", array_values($data))
);

Answer 2

At oscommerce has a function that does just what you need and even has some cool features, just adapt in your project or analyze the code and do something similar:

// função principal
function tep_db_perform($table, $data, $action = 'insert', $parameters = '', $link = 'db_link') {
    reset($data);
    if ($action == 'insert') {
      $query = 'insert into ' . $table . ' (';
      while (list($columns, ) = each($data)) {
        $query .= $columns . ', ';
      }
      $query = substr($query, 0, -2) . ') values (';
      reset($data);
      while (list(, $value) = each($data)) {
        switch ((string)$value) {
          case 'now()':
            $query .= 'now(), ';
            break;
          case 'null':
            $query .= 'null, ';
            break;
          default:
            $query .= '\'' . tep_db_input($value) . '\', ';
            break;
        }
      }
      $query = substr($query, 0, -2) . ')';
    } elseif ($action == 'update') {
      $query = 'update ' . $table . ' set ';
      while (list($columns, $value) = each($data)) {
        switch ((string)$value) {
          case 'now()':
            $query .= $columns . ' = now(), ';
            break;
          case 'null':
            $query .= $columns .= ' = null, ';
            break;
          default:
            $query .= $columns . ' = \'' . tep_db_input($value) . '\', ';
            break;
        }
      }
      $query = substr($query, 0, -2) . ' where ' . $parameters;
    }
    return tep_db_query($query, $link);
}

// Funções chamadas
function tep_db_query($query, $link = 'db_link') {
    global $$link;
    $result = mysqli_query($$link, $query) or tep_db_error($query, mysqli_errno($$link), mysqli_error($$link));
    return $result;
}

function tep_db_input($string, $link = 'db_link') {
    global $$link;
    return mysqli_real_escape_string($$link, $string);
}

function tep_db_error($query, $errno, $error) { 
    die('<font color="#000000"><strong>' . $errno . ' - ' . $error . '<br /><br />' . $query . '<br /><br /><small><font color="#ff0000">[TEP STOP]</font></small><br /><br /></strong></font>');
}

Utilizing:

$data_array = array(
    'nome' => 'Nome',
    'sobrenome' => 'Sobrenome',
    'genero' => 1,
    'email' => '[email protected]',
    'fone' => 'null',
    'data' => 'now()');

// insert
tep_db_perform('clientes', $data_array);

// update
tep_db_perform('clientes', $data_array, 'update', "cliente_id = '1'");