Solved - Save more than one array in Mysql

Asked

Viewed 56 times

-3

I’m getting arrays and would like to save them in the database mysql and I’m not getting it, could you help me?

Follow what I’m getting and how I’d like it to stay:

$nomemorador = $_POST['nomemorador'];
$parentesco = $_POST['parentesco'];
$nascimento = $_POST['dataNascimento'];

All three are arrays! I need to enter in the database as follows:

INSERT INTO moradores (nome, parentesco, nascimento) VALUES ($nomemorador, $parentesco, $nascimento)

All I could do was walk through one: foreach ($nomemorador as $registro):echo $registro."
";endforeach; I keep trying different ways here and I haven’t gotten it yet!

php mysql
  • 1

    If they are arrays, you have to go through this array to perform the Inserts one by one (or a multiple Insert). Can you tell us what your last attempt was and what the result was?

  • 1

    Actually what I did, was just go through the array, I couldn’t put them together to do the rest! foreach ($addresser as $record): echo $record." <br>"; endforeach;

  • 1

    Put it there in the question, please.

  • @Moisespequeno you can do INSERT with strings? These values received through $_POST are all arrays?

  • Yeah. Yeah, they’re all arrays!

2 answers

3


To join the values of the three arrays you can use the function array_map. It takes as its first argument a function that will be called for each array value and will return a new array with all returns from that function. It also allows, in addition to the function, to pass as many arrays as needed as argument.

<?php

$nomes = ['João', 'Maria', 'Afonso', 'Ana'];
$parentescos = ['Pai', 'Filha', 'Avô', 'Avó'];
$nascimentos = ['01/01/980', '01/02/1999', '01/03/1930', '01/04/1934'];

function format($nome, $parentesco, $nascimento) {
  return "('{$nome}', '{$parentesco}', '{$nascimento}')";
}

$valores = array_map("format", $nomes, $parentescos, $nascimentos);

print_r($valores);
/**
Array
(
    [0] => ('João', 'Pai', '01/01/980')
    [1] => ('Maria', 'Filha', '01/02/1999')
    [2] => ('Afonso', 'Avô', '01/03/1930')
    [3] => ('Ana', 'Avó', '01/04/1934')
)
*/

Done that, just make one join and build your query:

$query = sprintf("INSERT INTO moradores (nome, parentesco, nascimento) VALUES %s", join(', ', $valores));

echo $query;
// INSERT INTO moradores (nome, parentesco, nascimento) VALUES ('João', 'Pai', '01/01/980'), ('Maria', 'Filha', '01/02/1999'), ('Afonso', 'Avô', '01/03/1930'), ('Ana', 'Avó', '01/04/1934')

  • This one worked! Thank you very much! Can you ask me another question? If I want to put a normal value, without being array together I can? Ex: INSERT INTO residents (name, kinship, birth, code) VALUES ('João', 'Pai', '01/01/980', '1'), ('Maria', 'Filha', '01/02/1999', '1'), ('Afonso', 'Avô', '01/03/1930', '1'), ('Ana', 'Avó', '01/04/1934', '1')

  • Yes, within the function format you can do this.

3

A simple example of Pivot (exchange row by column), already generating SQL:

$n = ['João', 'Maria', 'Afonso', 'Ana'];
$p = ['Pai', 'Filha', 'Avô', 'Avó'];
$d = ['01/01/980', '01/02/1999', '01/03/1930', '01/04/1934'];
$q = 'INSERT INTO moradores (nome, parentesco, nascimento) VALUES ';

for ($i = 0; $i < count($n); ++$i) $q.= ($i?',':'')."('{$n[$i]}','{$p[$i]}','{$d[$i]}')";

echo $q;

Exit:

INSERT INTO moradores (nome, parentesco, nascimento) VALUES ('João','Pai','01/01/980'),
('Maria','Filha','01/02/1999'),('Afonso','Avô','01/03/1930'),('Ana','Avó','01/04/1934')

See working on IDEONE.


Important: This is just a kickoff.

Things to fix:

  • Missing the proper escape from the strings (the code will break with quotes in the middle, and is subject to a severe SQL injection)
  • Empty array test is missing
  • Equal length check of arrays missing.

At the end will be more with this face (the example is for mysqli, have to adapt):

for ($i = 0; $i < count($nomes); ++$i) {
   $nome       = mysqli_real_escape_string( $con, $nomes[$i] );
   $parentesco = mysqli_real_escape_string( $con, $parentescos[$i] );
   $nascimento = mysqli_real_escape_string( $con, $nascimentos[$i] );

   $query .= $i?',':'';   // poe a virgula a partir do 2o item 
   $query .= "('$nome','$parentesco','$nascimento')";
}

Better than this is to do a PREPARE followed by the proper EXECUTE, avoiding a lot of unnecessary operation and using the correct tool, but there already escapes much of what was asked.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.