使用mysqli从数组插入MySQL [英] Insert into MySQL from array with mysqli

问题描述

我想将一些数据从已解析的JSON插入到表中，但是当我这样做时，它不起作用，它返回0行，我想念的是什么?我是这个"mysqli"的新手.我有25000多行要插入到表中.

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);
}

推荐答案

似乎您应该在数据值周围设置单引号.还添加 mysqli_error 检查您的

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));
}

或更妙的是，使用 mysqli_stmt_bind_param 像这样.允许MySQLi处理整个查询数据的结构，而不必担心单引号的放置.另外，还为 mysqli_connect_error a href ="http://www.php.net/manual/zh/function.mysqli-connect.php" rel ="nofollow noreferrer"> mysqli_connect 行:

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));
}

请注意，我在 ，因为我不清楚您的$value['data_id']是数字还是字符串. mysqli_stmt_bind_param($query, 'is',…表示第一个值是整数(i)，下一个值是字符串(s).随时进行调整以最适合您的实际数据类型.

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.

这篇关于使用mysqli从数组插入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！