Uncaught PDOException: SQLSTATE[21S01]: Insert value list does not match column list [duplicate]

Question

So i've tried for the past hours to make sense of this Exception but it keeps saying the insert value list doesn't match column list. This is the code for my php:

function insert_data($humidity, $temperature, $sensor_id, $log_time) {
    $conn = db_connect(); //connects to database

    $sql = "INSERT INTO data(humidity, temperature, log_time, sensor_id)\n"
    . "VALUES ($humidity, $temperature, '$log_time', '$sensor_id');";

    $statement = $conn -> prepare($sql);
    $ok = $statement -> execute();

    $conn = null;

    return $ok;
}

The function is called here:

<?php 
include("lib_db.php");
include("header.php");

if(isset($_POST["data_send"]) && filter_var($_POST["data_send"], FILTER_VALIDATE_BOOLEAN)) {

$sensor_id = $_POST["sensor_id"] ?? '';
$humidity = $_POST["humidity"] ?? '';
$temperature = $_POST["temperature"] ?? '';
$log_time = $_POST["log_time"] ?? '';

$res = insert_data($humidity, $temperature, $sensor_id, $log_time);

if(!$res) throw new ErrorException;
}

$res = select_data();
?>
<div class="container-xxl bg-danger hero-header py-5">
    <div class="container-xxl py-5">
        <div class="container-xxl py-5 px-lg-5 wow fadeInUp">
         <div class="table-responsive">
         <table class="table table-hover table-dark">
             <thead>
                 <tr>
                     <th>#ID</th>
                     <th>Humidity</th>
                     <th>Temperature</th>
                     <th>Log time</th>
                     <th>#Sensor ID</th>
                 </tr>
             </thead>
             <tbody>
             <?php if(isset($res)) { 
                     foreach($res as $row) {
                     ?>
                     <tr>
                         <td><?= $row["id"]?></td>
                         <td><?= $row["humidity"]?></td>
                         <td><?= $row["temperature"]?></td>
                         <td><?= $row["log_time"]?></td>
                         <td><?= $row["sensor_id"]?></td>
                     </tr>
                     <?php } } ?>
             </tbody>
         </table>
    </div>
  </div>
  </div>
</div>
<?php
include("footer.php");
?>

The data retrieved from post is sent from an external C# application that i tested and is functional.

My "data" table structure is data(id, humidity, temperature, log_time, sensor_id) id is an A-I key.

when i try to run insert it gives me this error:

<b>Fatal error</b>:  Uncaught PDOException: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1 in C:\UniServerZ\www\Agriculture IOT\digital-agency-html-template\lib_db.php:115
Stack trace:
#0 C:\UniServerZ\www\Agriculture IOT\digital-agency-html-template\lib_db.php(115): PDOStatement-&gt;execute()
#1 C:\UniServerZ\www\Agriculture IOT\digital-agency-html-template\sensor_api.php(12): insert_data('48,755359214191', '20,443338532373...', '278e8f10-c667-4...', '2023-12-02 20:0...')
#2 {main}
  thrown in <b>C:\UniServerZ\www\Agriculture IOT\digital-agency-html-template\lib_db.php</b> on line <b>115</b><br />

I tried to check for punctuation errors, apostrophe errors, variable misplacements and errors in table structure.

i ran the query on PhPmyAdmin and it works fine.

Answer 1

I see some of your data values in your call stack:

insert_data('48,755359214191', '20,443338532373...', '278e8f10-c667-4...', '2023-12-02 20:0...')

So your INSERT statement is eventually formatted as:

INSERT INTO data(humidity, temperature, log_time, sensor_id)
VALUES (48,755359214191, 20,443338532373, '2023-12-02 20:0...', '278e8f10-c667-4...');
          ^            ^   ^            ^                     ^

I have put ^ where the commas occur. Two of your values contain commas. In SQL syntax, commas separate arguments. The commas between your intended values and the commas inside the values are identical, and MySQL can't tell them apart. So this makes MySQL think you intended 6 items in the VALUES clause, not 4.

You can solve this problem and prevent similar problems by using SQL query parameters instead of interpolating PHP variables into your SQL query string.

$sql = "INSERT INTO data(humidity, temperature, log_time, sensor_id)\n"
. "VALUES (?, ?, ?, ?)";

$statement = $conn->prepare($sql);
$statement->execute([$humidity, $temperature, $log_time, $sensor_id]);

This is the best way to remove all ambiguity about what counts as a value and what counts as SQL syntax — use query parameters to keep the values separate from the query until after the query has been prepared. That way the content of the variables cannot accidentally influence the syntax of the query.

Sometimes if you look at the actual result of formatting your SQL query, instead of only looking at the PHP code and assuming what the result is, problems like this one are easier to see.

I wrote about this in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming, in the chapter "See No Evil."