insert many rows into MySQL with php script

Question

This is in reference to multiple posts (like this one) but I canot figure out how it works. I am trying to insert multiple rows at once with a sql statement. Here is my code with what I found in previous posts:

$mysqli = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_DATABASE);

if (mysqli_connect_error()) {
    die('Connect Error (' . mysqli_connect_errno(). ') ' . mysqli_connect_error());
}

$body = file_get_contents('php://input');
$jsonArray = json_decode($body, true);

// Test
$results = print_r($jsonArray, true);
file_put_contents('filename.txt', print_r($results, true)); // write file to see formatting key/value

$sql = array();
foreach ($jsonArray as $row) {   

    $sql[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';

}
$mysqli->query('INSERT INTO tbl_syncListLite (text, category) VALUES '.implode(',', $sql));

if ($mysqli === TRUE) {
    $response = array('status' => '1');
} else {
    $response = array('status' => '0');
}

echo json_encode($response);

$mysqli->close();

?>

I really do not understand this part:

$sql[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';

$mysqli->query('INSERT INTO tbl_syncListLite (text, category) VALUES '.implode(',', $sql));

...and especially if 'text' and 'category id' refer to specific words or if I have to replace them with my fieldnames. Moreover I do not understand how it can manage multiples fields and if some are integers vale and so one (datetime,...).

If anybody could help me, it would be really appreciated.

this doesn't work with `mysqli_` => `mysql_real_escape_string` and you should have checked for the real error why it failed you http://php.net/manual/en/mysqli.error.php — Funk Forty Niner, Feb 16 '16 at 23:12
possible duplicate of [Can I mix MySQL APIs in PHP?](http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php) — Funk Forty Niner, Feb 16 '16 at 23:12

score 0 · Answer 1 · answered Feb 16 '16 at 23:10

0

The text and category value need to be replaced with what you want to insert into your table along with the new entry. It can handle so many entries because it takes several values (the imploded array, divided with commas), which are defined at the start of the Insert Into function.

answered Feb 16 '16 at 23:10

Bitte Wenden

100
10

But I also try with my values: $sql[] = '("'.$row['lastname']).'", "'.$row['firstname'].'")'; $mysqli->query('INSERT INTO tbl_syncListLite (firstname, lastname) VALUES '.implode(',', $sql)); but it does not work either... An idea? Thanks for your help – Trichophyton Feb 16 '16 at 23:38
@Trichophyton Is your table called tbl_syncListLite? – Bitte Wenden Feb 17 '16 at 05:31
Yes this the table name. It works fine with the $sql = "INSERT INTO tbl_syncListLite (firstname, lastname,...) VALUES ('$firstname', $'lastname',...)" and then $mysqli->query($sql). But not when I replace with this new fonction... I cannot figure out why... – Trichophyton Feb 17 '16 at 08:43

insert many rows into MySQL with php script

1 Answers1