PDO Insert operation using PHP and MYSQL not working

Question

This is my first time using pdo and I seem to be having a issue writing to my database using a insert statement. I have a html form to "create" an account that goes that pushes the data to my php page. I get the response that the data is being spit out by using an echo, but nothing changes in my db. Thanks for your time and help. I'm sure this is a easy fix or a stupid mistake.

customer.html below

<html>
<head>
<link href="css/formlayout.css" rel="stylesheet" type="text/css"/>
<title></title>
<script type="text/javascript">
function validate()
{
    var Email = document.getElementById("txtEmail");
    var Phone = document.getElementById("txtPhone");
    var Password1 = document.getElementById("txtPassword");
    var Password2 = document.getElementById("txtPwdVerify");
    var State = document.getElementById("lstState");

    if (State.selectedIndex == "0")
    {
        alert("Please Select a State!");

    }

    var Zip = document.getElementById("txtZip");
    if(Email.value.indexOf("@") == -1)
    {
        alert("Please enter a valid email address");
        Email.select();
        return false;

    }

    var PhoneEdit = /^\d\d\d-\d\d\d-\d\d\d\d$/;
    if(!PhoneEdit.test(Phone.value))
    {
        alert("Please enter a phone number in the form of ###-###-####");
        Phone.select();
        Phone.focus();
        return false;
    }

    if(Password1.value != Password2.value )
    {
        alert("Passwords do not match!");
        Password1.select();
        Password1.focus();
        return false;
    }

    if(isNaN(Zip.value))
    {
        alert("Please enter a valid zip code");
        Zip.select();
        Zip.focus();
        return false;
    }
    return true;
}
</script>
</head> 
<body>
    <div id="Container">

<h1>Customer Account</h1>
    <div id="Logo">

    </div>
    <div id="OrderForm">
    <form name="PDS" id="PDS" action="http://iurl/folder/insert/response.php" method="get">
        <div id="FormFields">
    <fieldset>
    <legend>Customer</legend>   
        <label for="txtFirstName">First Name: </label>
        <input type="text" name="txtFirstName" id="txtFirstName" required autofocus/>
        <label for="txtLastName">Last Name: </label>
        <input type="text" name="txtLastName" id="txtLastName" required />
        <label for="txtEmail">Email: </label>
        <input type="email" name="txtEmail" id="txtEmail" required />
        <label for="txtPhone">Phone: </label>
        <input type="text" name="txtPhone" id="txtPhone" placeholder="(###)###-####" 
        required pattern="[0-9]{3}-[0-9]{3}-[0-9]{4}"/>


    </fieldset>

        <fieldset>
    <legend>Address</legend>    
        <label for="txtAddress">Address: </label>
        <input type="text" name="txtAddress" id="txtAddress" Required/>
        <label for="txtCity">City: </label>
        <input type="text" name="txtCity" id="txtCity" Required/>
        <label for="lstState">State: </label>
        <select id="lstState" name="lstState">
<option value="none" selected>Please select a state</option>
<option value="AK">AK</option>
 <option value="AL">AL</option>
 <option value="AR">AR</option>
 <option value="AZ">AZ</option>
 <option value="CA">CA</option>
 <option value="CO">CO</option>
 <option value="CT">CT</option>
 <option value="DC">DC</option>
 <option value="DE">DE</option>
 <option value="FL">FL</option>
 <option value="GA">GA</option>
 <option value="HI">HI</option>
 <option value="IA">IA</option>
 <option value="ID">ID</option>
 <option value="IL">IL</option>
 <option value="IN">IN</option>
 <option value="KS">KS</option>
 <option value="KY">KY</option>
 <option value="LA">LA</option>
 <option value="MA">MA</option>
 <option value="MD">MD</option>
 <option value="ME">ME</option>
 <option value="MI">MI</option>
 <option value="MN">MN</option>
 <option value="MO">MO</option>
 <option value="MS">MS</option>
 <option value="MT">MT</option>
 <option value="NC">NC</option>
 <option value="ND">ND</option>
 <option value="NE">NE</option>
 <option value="NH">NH</option>
 <option value="NJ">NJ</option>
 <option value="NM">NM</option>
 <option value="NV">NV</option>
 <option value="NY">NY</option>          
 <option value="OH">OH</option>
 <option value="OK">OK</option>
 <option value="OR">OR</option>
 <option value="PA">PA</option>
 <option value="RI">RI</option>
 <option value="SC">SC</option>
 <option value="SD">SD</option>
 <option value="TN">TN</option>
 <option value="TX">TX</option>
 <option value="UT">UT</option>
 <option value="VT">VT</option>
 <option value="VA">VA</option>
 <option value="WA">WA</option>
 <option value="WV">WV</option>
 <option value="WI">WI</option>
 <option value="WY">WY</option>
        </select>
        <label for="txtZip">Zip Code: </label>
        <input type="text" name="txtZip" id="txtZip" maxlength="5" required/>

    </fieldset>

    <fieldset>
    <legend>Password</legend>   
        <label for="txtPassword">Password: </label>
        <input type="password" name="txtPassword" id="txtPassword" Required/>
        <label for="txtPwdVerify">Verify Password: </label>
        <input type="password" name="txtPwdVerify" id="txtPwdVerify"/>

    </fieldset>


    </div>
    <div id="FormButtons"></div>
    <input type="submit" id="btnSubmit" value="Submit" onclick="validate()">
    <input type="reset" id="btnReset"/>
    </form>
    </div>
    </div>
</body>
</div>

Here is my response.php

<html>  
<head>
<body>
<h1>Thank You!</h1>


<?php

$fname = $_GET['txtFirstName'];
$lname = $_GET['txtLastName'];
$email = $_GET['txtEmail'];
$phone = $_GET['txtPhone'];
$address = $_GET['txtAddress'];
$city = $_GET['txtCity'];
$state = $_GET['lstState'];
$zip = $_GET['txtZip'];

    $dsn = 'mysql:host=hostname;dbname=dbname';
    $username = 'username';
    $password = 'password';

    $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);

        try{
    $db = new PDO($dsn,$username,$password);
    $Insert = "Insert into customerinfo(FirstName, LastName, Address, City, State, Zip, Phone, EMail, Password) 
    Value (:FirstName, :LastName, :Address, :City, :State, :Zip, :Phone, :EMail)";
    $SQL = $db->prepare($Insert);
    $SQL->bindValue(':FirstName',$fname);
    $SQL->bindValue(':LastName',$lname);
    $SQL->bindValue(':Address',$address);
    $SQL->bindValue(':City',$city);
    $SQL->bindValue(':State',$state);
    $SQL->bindValue(':Zip',$zip);
    $SQL->bindValue(':Phone',$phone);
    $SQL->bindValue(':EMail',$email);
    $SQL->execute();

        $SQL->closeCursor();


 }
 catch(PDOEXCEPTION $e){
    $error_message = $e->getMessage();
    echo("<p>Database error: $error_message</p>");
    exit();
 }




?>

<?php

echo("<h2>First Name: $fname </h2>");
echo("<h2>Last Name: $lname </h2>");
echo("<h2>Address: $address </h2>");
echo("<h2>City: $city </h2>");
echo("<h2>State: $state </h2>");
echo("<h2>Zip Code: $zip </h2>");
echo("<h2>Phone Number: $phone </h2>");
echo("<h2>E-Mail: $email </h2>");

$db = null;



?>

You should be getting an error, no? Not enough values to columns being passed in. You aren't sending the password... — chris85
– chris85, Commented Nov 29, 2015 at 18:10
No error. I'll try to add the rest of the fields to see what happens. — Lance Hietpas
– Lance Hietpas, Commented Nov 29, 2015 at 18:16
@chris85 You hit it right on the head my man. I added the password and now all is good. Thanks! — Lance Hietpas
– Lance Hietpas, Commented Nov 29, 2015 at 18:21
Great, please accept answer below. You also should look into why that error reporting is not working. — chris85
– chris85, Commented Nov 29, 2015 at 18:23
@chris85 It's happened to me before, where MySQL errors weren't showing because I was using JS, and found it to be the strangest thing. Could be the same thing here. I'd have to test their code to be 100% certain but after a day like day, I've no more energy ;-) — Funk Forty Niner
– Funk Forty Niner, Commented Nov 29, 2015 at 22:06

chris85 · Accepted Answer · 2015-11-29 18:17:45Z

Your code had 9 columns defined but only 8 values being written. Give this a try also note you shouldn't store passwords in plain text.

<html>  
<head>
<body>
<h1>Thank You!</h1>


<?php

$fname = $_GET['txtFirstName'];
$lname = $_GET['txtLastName'];
$email = $_GET['txtEmail'];
$phone = $_GET['txtPhone'];
$address = $_GET['txtAddress'];
$city = $_GET['txtCity'];
$state = $_GET['lstState'];
$zip = $_GET['txtZip'];
$user_password = $_GET['txtPassword'];
    $dsn = 'mysql:host=hostname;dbname=dbname';
    $username = 'username';
    $password = 'password';

    $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);

        try{
    $db = new PDO($dsn,$username,$password);
    $Insert = "Insert into customerinfo(FirstName, LastName, Address, City, State, Zip, Phone, EMail, Password) 
    Value (:FirstName, :LastName, :Address, :City, :State, :Zip, :Phone, :EMail, :Password)";
    $SQL = $db->prepare($Insert);
    $SQL->bindValue(':FirstName',$fname);
    $SQL->bindValue(':LastName',$lname);
    $SQL->bindValue(':Address',$address);
    $SQL->bindValue(':City',$city);
    $SQL->bindValue(':State',$state);
    $SQL->bindValue(':Zip',$zip);
    $SQL->bindValue(':Phone',$phone);
    $SQL->bindValue(':EMail',$email);
    $SQL->bindValue(':Password', $user_password);
    $SQL->execute();
    $SQL->closeCursor();


 }
 catch(PDOEXCEPTION $e){
    $error_message = $e->getMessage();
    echo("<p>Database error: $error_message</p>");
    exit();
 }




?>

<?php

echo("<h2>First Name: $fname </h2>");
echo("<h2>Last Name: $lname </h2>");
echo("<h2>Address: $address </h2>");
echo("<h2>City: $city </h2>");
echo("<h2>State: $state </h2>");
echo("<h2>Zip Code: $zip </h2>");
echo("<h2>Phone Number: $phone </h2>");
echo("<h2>E-Mail: $email </h2>");

$db = null;



?>

You also should probably transmit this form via POST rather than GET.

Additionally this isn't checking that the verified password matches the initial password..

Collectives™ on Stack Overflow

PDO Insert operation using PHP and MYSQL not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related