mysql 数组 插入,使用mysqli从数组插入MySQL

I want to insert some data from a parsed JSON to a table, but when I do it, it doesn't work, it returns 0 rows, what am I missing? I'm new yet with this "mysqli". I have more than 25000 rows to insert to the table.

$mysqli = mysqli_connect('localhost', 'root', '', '');

$allData = $dataSource->getAllData();

foreach ($allData as $key => $value) {

$query = 'INSERT INTO `table`(`data_id`, `name`) VALUES (' . $value['data_id'] . ', ' . $value['name'] . ')';

$result = mysqli_query($mysqli, $query);

}

解决方案

Seems like you should set single quotes around your data values. Also adding a mysqli_error check for your mysqli_query line so you can actually see what is happening:

$allData = $dataSource->getAllData();

foreach ($allData as $key => $value) {

$query = "INSERT INTO `table`(`data_id`, `name`) VALUES ('" . $value['data_id'] . "', '" . $value['name'] . "')";

$result = mysqli_query($mysqli, $query) or die(mysqli_error($mysqli));

}

Or better yet, use mysqli_stmt_bind_param like this. Allow MySQLi to deal with the whole query data structuring instead of having to worry about single quote placement. Also, added a check for mysqli_connect_error on your mysqli_connect line:

// Connecting, selecting database

$mysqli = mysqli_connect('localhost', 'root', '', '') or die(mysqli_connect_error());

$allData = $dataSource->getAllData();

foreach ($allData as $key => $value) {

// Set the query.

$query = "INSERT INTO `table`(`data_id`, `name`) VALUES (?, ?)";

// Bind the params.

// mysqli_stmt_bind_param($query, 'ss', $value['data_id'], $value['name']);

mysqli_stmt_bind_param($query, 'is', $value['data_id'], $value['name']);

// Run the query.

$result = mysqli_query($mysqli, $query) or die(mysqli_error($mysqli));

}

Note that I have a commented line for the mysqli_stmt_bind_param since it’s not clear to me if your $value['data_id'] is a number or a string. The mysqli_stmt_bind_param($query, 'is',… means the first value is an integer (i) and the next value is a string (s). Feel free to adjust to best fit your actual data types.