PDO query not inserting data into MySQL database, not showing any errors

Question

I'm setting up a form to submit data into a MySQL database using PDO (which I am not that familiar with).

This is the code to input the data from a simple input form with Name, Birthday and two hardware options to choose from:

<?php
session_start();

if (isset ($_POST['next_1']))
{
    $name = $_POST['Name'];
    $geburtsdatum = $_POST['Geburtsdatum'];
    $hardware = $_POST['hardware'];
    $true = "1";
}
else
{
    header('Location: pdo_test.php', true, 302);
}
?>

<!DOCTYPE html>
<html lang="de">
<meta charset="UTF-8"> 
<head>
<title>PDO-Test</title>

<link rel="stylesheet" type="text/css" href="style/style.css" />

</head>

<body>
<img id="logo" src="bilder/some_logo.png" alt="some Logo">
<h1>PDO Test Seite</h1><br>

<?php
//Showing Errors
error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<br>";

//Verbindung zur Datenbank
include 'includes/connect.php'; 

echo "<br>";
echo "<b>Name: </b>" . $name . "<br><b>Geburtsdatum: </b>" . $geburtsdatum . "<br>" ;

    foreach ($hardware as $key => $ho) 
    {
        switch($ho)
        {
            case "Phone";
                echo "Phone: " . $ho . "<br>";
                break;
            case "Headset";
                echo "Headset: " . $ho . "<br>";
                break;
        }
    }

try
{   
    $conn->beginTransaction();

    $stmt = $conn->prepare("INSERT INTO testdb(name, geburtsdatum, phone, headset) VALUES(:name, :geburtsdatum, :phone, :headset)");
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':geburtsdatum', $geburtsdatum);
    foreach ($hardware as $key => $ho)
    {
        switch($ho)
        {
            case "Phone";
                $stmt->bindParam(':phone', $true);
                break;
            case "Headset";
                $stmt->bindParam(':headset', $true);
                break;
        }
    }

    $stmt->execute();

    $conn->commit();
    echo "<br>New records created successfully";
}
catch(PDOException $e)
{
    $conn->rollBack();
    error_log("Database rollback; inserting data failed: ".$e->getMessage());
}

?> 

</body>
</html>

I'm using the echo part to double-check the input from the form (can be ignored for this problem, everything working fine there).

Looking around in different posts similar to my problem, I only found 'solutions' how to enable error reporting.

In my connect.php (include 'includes/connect.php';) I have already enabled error reporting as following: $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

and in the main script I added

error_reporting(E_ALL);
ini_set('display_errors', 1);

For some reasons, the only output i receive on my website is the echo stuff and the 'Connected successfully' line from my connection script. I don't receive any errors, but at the same time my data is not getting inserted into my database.

Any ideas how to get the error messages to show up are appreciated as well as any solutions that help me with getting the data into my database.

Does your error-logs tell you anything? When you get an exception, the query is rolled back and the message logged. — Qirel, Jun 21 '19 at 09:10
For testing-purposes, you can add `die("Error: ".$e->getMessage());` within your catch-block (but it will be in your error logs too if you just check those). — Qirel, Jun 21 '19 at 09:11
where can i access the error logs? my website is on a server from the company i am working at where i only have partial access, unfortunately — Alex Schwartz, Jun 21 '19 at 09:14
so, i got the error to show, by swapping out ```error_log``` with ```echo``` in the catch-block. It says 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens ' — Alex Schwartz, Jun 21 '19 at 09:19

score 0 · Accepted Answer · 2019-06-24T11:20:18.427

0

First thing I can see is that you select 4 columns but only give 3 values, maybe even 2 since I can't see $true being declared anywhere. Try this:

case "Phone";
      $stmt->bindParam(':phone', $true);
      $stmt->bindParam(':headset', null);
      break;
case "Headset";
      $stmt->bindParam(':headset', $true);
      $stmt->bindParam(':phone', null);
      break;

edited Jun 24 '19 at 11:20

answered Jun 21 '19 at 09:20

$true is declared in line 9 as '1' and the problem with your suggestion is, that one can select both Phone and Headset (it's a checkbox input, not a radio input. missed to clarify that, my bad). So your suggestion would set Headset to 1 and Phone to null, even if both were checked, since Headset comes second. Or am I mistaken here? – Alex Schwartz Jun 21 '19 at 09:25
Then set two variables, `$headset = null` and `$phone = null`, and in your loop, overwrite those variables if selected. Then bind them with the variable before the loop, as you do with the other values. – Qirel Jun 21 '19 at 09:27

PDO query not inserting data into MySQL database, not showing any errors

1 Answers1