PHP insert an array of data into a mysql table [duplicate]

Question

PHP insert an array of data into a mysql table [duplicate]

Navigation

#1 by (0 votes)
#2 by (0 votes)

-1

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

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

And I would like to insert this data into a mySql table more automatically. When I generate this array, the process is already done so that it conforms to the table, that is, my table is made up of columns nome , sobrenome , genero and email . >

So, instead of doing a query like this:

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

I would like to know if there is any method of automating this execution based on the array of data I have.

I thought of executing 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 suitable / ideal and / or if there is a "correct" method of performing this task.

php mysql sql

asked by anonymous 03.09.2016 / 00:23

2 answers

Project in Android studio does not run for lack of libs How to insert multiple images in a single post inside a CPT?

score 0 · Answer 1

Well this is by no means the best solution, I'm only giving you a north, but the ideal would be to have leaks in value 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))
);

score 0 · Answer 2

In osCommerce has a function that does exactly what you need and has even some pretty 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>');
}

Usage:

$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'");