form data does not commit to MySQL database

Question

Tried searching the other threads but found nothing that helps:

The code below is part of a form, this passes the data from the form to the DB, the connection has been tested and works.

When the submit button is pressed, nothing happens, no echo for success or failure, and no new record in the database. I can't seem to find what the issue is:

<?php

if(isset($_POST['submit'])){

    require '/connectDB.php';

    try {

        $stmt = $db->prepare("INSERT INTO tbl_matchOfficials 
                              (MO_FN, MO_LN, MO_Gender, MO_DOB, MO_DOD, 
                               Nationality, twitterHandle, Active, TMO)
                       VALUES ('$_POST[MO_FN]', '$_POST[MO_LN]', 
                               '$_POST[MO_Gender]', '$_POST[MO_DOB]', 
                               '$_POST[MO_DOD]', '$_POST[Nationality]', 
                               '$_POST[twitterHandle]', '$_POST[Active]', 
                               '$_POST[TMO]')");

        $stmt->bindParam('MO_FN', $MO_FN);
        $stmt->bindParam('MO_LN', $MO_LN);
        $stmt->bindParam('MO_Gender', $MO_Gender);
        $stmt->bindParam('MO_DOB', $MO_DOB);
        $stmt->bindParam('MO_DOD', $MO_DOD);
        $stmt->bindParam('Nationality', $Nationality);
        $stmt->bindParam('twitterHandle', $twitterHandle);
        $stmt->bindParam('Active', $TMO);
        $stmt->bindParam('TMO', $TMO);
        $stmt->execute();
        echo "New record created successfully";
    }
    catch(PDOException $e)
    {
        echo "Error: " . $e->getMessage();
    }
    $db = null;
}
?>

Appreciate your help.

EDIT:

It's clear from the awesome assistance below that there is something else wrong, here is the whole code for the form. The form displays and holds the data as it should, it just won't insert and echo back (good or bad).

<!DOCTYPE HTML>
<html>
    <head>
        <title>New Match Official</title>
    <style>
        .error {color: #FF0000;}
    </style>
    <!-- Load jQuery from Google's CDN -->
    <!-- Load jQuery UI CSS  -->
    <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" />

    <!-- Load jQuery JS -->
    <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
    <!-- Load jQuery UI Main JS  -->
    <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>

    <!-- Load SCRIPT.JS which will create datepicker for input field  -->
    <script src="script.js"></script>
    <script src="script1.js"></script>

    <link rel="stylesheet" href="runnable.css" />
    </head>
    <h1>
        New Match Official
    </h1>
<body>

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

if(isset($_POST['submit'])){

    require '/connectDB.php';

    try {

        $stmt = $db->prepare("INSERT INTO tbl_matchOfficials 
                              (MO_FN, MO_LN, MO_Gender, MO_DOB, MO_DOD, 
                               Nationality, twitterHandle, Active, TMO)
                       VALUES (:MOFN, :MOLN, 
                               :MOGender, :MODOB, 
                               :MODOD, :Nationality,
                               :twitterHandle, :Active,
                               :TMO)");

        $stmt->bindParam(':MOFN', $_POST['MO_FN']);
        $stmt->bindParam(':MOLN', $_POST['MO_LN']);
        $stmt->bindParam(':MOGender', $_POST['MO_Gender']);
        $stmt->bindParam(':MODOB', $_POST['MO_DOB']);
        $stmt->bindParam(':MODOD', $_POST['MO_DOD']);
        $stmt->bindParam(':Nationality', $_POST['Nationality']);
        $stmt->bindParam(':twitterHandle', $_POST['twitterHandle']);
        $stmt->bindParam(':Active', $_POST['Active']);
        $stmt->bindParam(':TMO', $_POST['TMO']);
        $stmt->execute();
        echo "New record created successfully";
    }
    catch(PDOException $e)
    {
        echo "Error: " . $e->getMessage();
    }
    $db = null;
}
?>

<?php
// define variables and set to empty values
$MO_FN = $MO_LN = $MO_Gender = $MO_DOB = $MO_DOD = $Nationality = $twitterHandle = $Active = $TMO = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
  $MO_FN = test_input($_POST["MO_FN"]);
  $MO_LN = test_input($_POST["MO_LN"]);
  $MO_Gender = test_input($_POST["MO_Gender"]);
  $MO_DOB = test_input($_POST["MO_DOB"]);
  $MO_DOD = test_input($_POST["MO_DOD"]);
  $Nationality = test_input($_POST["Nationality"]);
  $twitterHandle = test_input($_POST["twitterHandle"]);
  $Active = test_input($_POST["Active"]);
  $TMO = test_input($_POST["TMO"]);
}

function test_input($data) {
  $data = trim($data);
  $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}

// define variables and set to empty values
$FNErr = $LNErr = $GenderErr = $NationalityErr = $ActiveErr = $TMOErr = "";
$MO_FN = $MO_LN = $MO_Gender = $Nationality = $Active = $TMO = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
  if (empty($_POST["MO_FN"])) {
    $FNErr = "First Name is required";
  } else {
    $MO_FN = test_input($_POST["MO_FN"]);
 // check if name only contains letters and whitespace
    if (!preg_match("/^[a-zA-Z ]*$/",$MO_FN)) {
      $FNErr = "Only letters and white space allowed"; 
    }
  }

  if (empty($_POST["MO_LN"])) {
    $LNErr = "Last Name is required";
  } else {
    $MO_LN = test_input($_POST["MO_LN"]);
 // check if name only contains letters and whitespace
    if (!preg_match("/^[a-zA-Z ]*$/",$MO_LN)) {
      $LNErr = "Only letters and white space allowed"; 
    }
  }

  if (empty($_POST["MO_Gender"])) {
    $GenderErr = "Gender is required";
  } else {
    $MO_Gender = test_input($_POST["MO_Gender"]);
  }

  if (empty($_POST["Nationality"])) {
    $NationalityErr = "Nationality is Required (i.e. AUS for Australia)";
  } else {
    $Nationality = test_input($_POST["Nationality"]);
  }

  if (empty($_POST["Active"])) {
    $ActiveErr = "Please state if Match Official is still active";
  } else {
    $Active = test_input($_POST["Active"]);
  }

  if (empty($_POST["TMO"])) {
    $TMOErr = "Please state if Match Official performs the role of a TMO";
  } else {
    $TMO = test_input($_POST["TMO"]);
  }
}
?>
<p><span class="error">* required field.</span></p>
<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
<table>
    <tr>
        <td>First Name:</td>
        <td><input type="text" name="MO_FN" value="<?php echo $MO_FN;?>"><span class="error">* <?php echo $FNErr;?></span></td>
    </tr>
    <tr>
        <td>Last Name:</td>
        <td><input type="text" name="MO_LN" value="<?php echo $MO_LN;?>"><span class="error">* <?php echo $LNErr;?></span></td>
    </tr>
    <tr>
        <td>Gender:</td>
        <td><input type="radio" name="MO_Gender" <?php if (isset($MO_Gender) && $MO_Gender=="Male") echo "checked";?> value="Male">Male
            <input type="radio" name="MO_Gender" <?php if (isset($MO_Gender) && $MO_Gender=="Female") echo "checked";?> value="Female">Female
            <span class="error">* <?php echo $GenderErr;?></span></td>
    </tr>
    <tr>
        <td>Date of Birth:</td>
        <td><input type="text" id="datepicker" name="MO_DOB" value="<?php echo $MO_DOB;?>"></td>
    </tr>
    <tr>
        <td>Date of Death:</td>
        <td><input type="text" id="datepicker1" name="MO_DOD" value="<?php echo $MO_DOD;?>"></td>
    </tr>
    <tr>
        <td>Nationality (TLA):</td>
        <td><input type="text" maxlength="3" name="Nationality" value="<?php echo $Nationality;?>"><span class="error">* <?php echo $NationalityErr;?></span></td>
    </tr>
    <tr>
        <td>Twitter Handle:</td>
        <td><input type="text" name="twitterHandle" value="<?php echo $twitterHandle;?>"></td>
    </tr>
    <tr>
        <td>Active Referee:</td>
        <td><input type="radio" name="Active" <?php if (isset($Active) && $Active=="Yes") echo "checked";?> value="Yes">Yes
            <input type="radio" name="Active" <?php if (isset($Active) && $Active=="No") echo "checked";?> value="No">No
            <span class="error">* <?php echo $ActiveErr;?></span></td>
    </tr>
    <tr>
        <td>TMO:</td>
        <td><input type="radio" name="TMO" <?php if (isset($TMO) && $TMO=="Yes") echo "checked";?> value="Yes">Yes
            <input type="radio" name="TMO" <?php if (isset($TMO) && $TMO=="No") echo "checked";?> value="No">No
            <span class="error">* <?php echo $TMOErr;?></span></td>
    </tr>
    <tr>
        <td><br><br><input type="submit"></td>
    </tr>
</table>
</form>

    <?php
echo "<h2>Your Input:</h2>";
echo "<table>";
echo "<tr><td>First Name: </td><td>$MO_FN</td></tr>";
echo "<tr><td>Last Name: </td><td>$MO_LN</td></tr>";
echo "<tr><td>Gender: </td><td>$MO_Gender</td></tr>";
echo "<tr><td>Date of Birth: </td><td>$MO_DOB</td></tr>";
echo "<tr><td>Date of Death: </td><td>$MO_DOD</td></tr>";
echo "<tr><td>Nationality: </td><td>$Nationality</td></tr>";
echo "<tr><td>Twitter Handle: </td><td>$twitterHandle</td></tr>";
echo "<tr><td>Active: </td><td>$Active</td></tr>";
echo "<tr><td>TMO: </td><td>$TMO</td></tr>";
echo "</table>"
?>
</body>
</html>

Add [error reporting](http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php/845025#845025) to the top of your file(s) _while testing_ right after your opening PHP tag for example ` — RiggsFolly, Aug 15 '16 at 23:50
Probably this line `require '/connectDB.php';` is wrong as that says go back to the drive root and look for a file called `connectDB.php` — RiggsFolly, Aug 15 '16 at 23:51
It's because you're preparing your query all wrong. Instead of the variables, you need to use something like placeholders `:MOFN`. Have a look at the documentation http://php.net/manual/en/pdo.prepared-statements.php — icecub, Aug 15 '16 at 23:51
are you sure `$_POST['submit']` is set? have you tried `var_dump`ing `$_POST` to check? — Memor-X, Aug 15 '16 at 23:55
Ok, working through these one by one, appreciate the help! be right back with answers — RBurns, Aug 16 '16 at 00:13
RiggsFolly - No difference with the error reporting. And this code is on a MAMP server, so no danger with the connection file being in the root at present. — RBurns, Aug 16 '16 at 00:14
Memor-X, I had it working previously but would insert to DB on load instead of on submit (because I didn't add that, so I added that). — RBurns, Aug 16 '16 at 00:18
Try my answer below @RBurns. It should solve your problem :) — icecub, Aug 16 '16 at 00:19
Icecub yeah that wouldn't surprise me, I'm no coding whisked, I'm one of these that googles what I need and try to amend it. It works 80% of the time. I'm 40 years old, when I started learning code it was just HTML and JS, they were easy :), learning new tricks nowadays is a bit harder. Once again, I appreciate all the help, Thank you. — RBurns, Aug 16 '16 at 00:19
@RBurns so when it was working you didn't have `if(isset($_POST['submit']))`? then my statement still stands have you tried `var_dump`ing `$_POST` to check that there is a `submit` key in the array — Memor-X, Aug 16 '16 at 00:20
You don't have to excuse yourself. You're trying to solve your problems on your own first and only ask us for help when you can't. That's all we expect from you here on Stack Overflow :) — icecub, Aug 16 '16 at 00:21
I don't know how to do that Memor-X, if you can point I can follow. — RBurns, Aug 16 '16 at 00:30
in regards to your recent edit i don't see anything in your form with `name="submit"` as such my suspicion may be correct and your problem is because `if(isset($_POST['su‌bmit']))` is false so the code inside is not being executed — Memor-X, Aug 16 '16 at 00:31
@RBurns [var_dump](http://php.net/manual/en/function.var-dump.php), very useful for debugging — Memor-X, Aug 16 '16 at 00:32
@Memor-X You are correct. All he needs to do is change `` to `` — icecub, Aug 16 '16 at 00:32
I knew it would be stupid, tunnel vision! THANK YOU SO MUCH!!! — RBurns, Aug 16 '16 at 00:33

score 1 · Answer 1 · answered Aug 16 '16 at 00:07

PDO uses Named Placeholders for the variables inside the query. More information about that can be found here: Prepared statements and stored procedures

That said, try this instead. Unless something else is going on, it should solve your problem:

<?php
error_reporting(E_AL‌L);
ini_set('display_err‌ors', 1);

if(isset($_POST['submit'])){

    require '/connectDB.php';

    try {

        $stmt = $db->prepare("INSERT INTO tbl_matchOfficials 
                              (MO_FN, MO_LN, MO_Gender, MO_DOB, MO_DOD, 
                               Nationality, twitterHandle, Active, TMO)
                       VALUES (:MOFN, :MOLN, 
                               :MOGender, :MODOB, 
                               :MODOD, :Nationality,
                               :twitterHandle, :Active,
                               :TMO)");

        $stmt->bindParam(':MOFN', $_POST['MO_FN']);
        $stmt->bindParam(':MOLN', $_POST['MO_LN']);
        $stmt->bindParam(':MOGender', $_POST['MO_Gender']);
        $stmt->bindParam(':MODOB', $_POST['MO_DOB']);
        $stmt->bindParam(':MODOD', $_POST['MO_DOD']);
        $stmt->bindParam(':Nationality', $_POST['Nationality']);
        $stmt->bindParam(':twitterHandle', $_POST['twitterHandle']);
        $stmt->bindParam(':Active', $_POST['Active']);
        $stmt->bindParam(':TMO', $_POST['TMO']);
        $stmt->execute();
        echo "New record created successfully";
    }
    catch(PDOException $e)
    {
        echo "Error: " . $e->getMessage();
    }
    $db = null;
}
?>

PS: I'm assuming you're using PDO btw. If you're using MySQLi instead, please let me know in a comment here because this will not work with MySQLi. I will edit the answer to work with MySQLi instead if nessesary. — icecub, Aug 16 '16 at 00:15
Yes ice cub, I'm using PDO. That didn't work, so perhaps something else is up. Let me amend the first post to show the whole form code. Sorry in advance for how painful it will be, but it is not on the live web yet, so am not vulnerable to injection at present. Hopefully the code is a little bit protected. — RBurns, Aug 16 '16 at 00:25
@RBurns Please don't add more code to your question than is nessesary to solve the problem. A question (or answer) should never become an entire book. If you can't work it out, I'm always willing to help you out in more detail through Teamviewer. That way I can code on your screen together with you and solve the issues at hand :) — icecub, Aug 16 '16 at 00:28
@RBurns Sure. If you still require help or just want me to teach a few things, feel free to send me an email and we can take it from there. You can find my email on my profile here. Just click my name :) — icecub, Aug 16 '16 at 00:35

score 0 · Accepted Answer · answered Aug 16 '16 at 00:37

0

For those who follow this in future, answer was to add a name="submit" to the submit button code!

After that also came a connection error, but that was because my connectDB.php file is in the root (I am on MAMP) and so needed the initial / removed.

Thanks to everyone for the quick and awesome assistance.

answered Aug 16 '16 at 00:37

RBurns

83
7

I do suggest you send my an email though. This type of problems can be prevented in the future. You just need to add a bit more debugging / safeguards to your code. Like if you wrapped the form inside the an `else` statement, you would've noticed the form simply appeared again after the submit. Which shouldn't happen :) – icecub Aug 16 '16 at 00:43
I have a few more questions, will drop you a line now. Thank you for helping. – RBurns Aug 16 '16 at 00:44
Awesome. I'm looking forward to hear from you :) – icecub Aug 16 '16 at 00:48

form data does not commit to MySQL database

2 Answers2