php使用mysqli从数组插入mysql

Question

hi 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. thx

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

---

it works now, it inserted all the object data, the code that i made thx to replies is something like this:

$mysqli = mysqli_connect('localhost', 'root', '', '') or die(mysqli_connect_error());
if (!$mysqli) {
    die('Could not connect: ' . mysqli_error());
}

$allData = $dataSource->getAllData();

foreach ($allData as $key => $value) {
    $query = mysqli_prepare($mysqli, "INSERT INTO `table`(`data_id`, `name`) VALUES (?, ?)");
    mysqli_stmt_bind_param($query, 'is', $value['data_id'], $value['name']);
    mysqli_stmt_execute($query);
    mysqli_stmt_close($query);
}

hope everything is ok here, i'm new in this mysqli and i need a lot of practice with programming

Answer 1

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

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

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.