Jump to content
sraidr69

Insert Into statement

Recommended Posts

I am getting an Item Post Failed error and not sure why.  Obviously the record is not inserted into the DB.  Im fairly new to pdo and cannot see the error.  Anyone see the issue?

$stmt = $conn->prepare("Insert Into clients (fname, lname, username, password, addr, city, st, zip, phone1, phone2, email)
                                Values (:fname, :lname, :username, :password, :addr, :city, :st, :zip, :phone1, :phone2, :email)");
        $rec = $stmt->execute(array (
        								':fname'=>$_POST["fname"],
                              ':lname'=>$_POST["lname"], 
                              ':username'=>$_POST["uname"], 
                              ':password'=>$hash, 
                              ':addr'=>$_POST["addr"], 
                              ':city'=>$_POST["city"], 
                              ':st'=>$_POST["st"], 
                              ':zip'=>$_POST["zip"], 
                              ':phone1'=>$_POST["p11"] . $_POST["p12"] . $_POST["p13"], 
                              ':phone2'=>$_POST["p21"] . $_POST["p22"] . $_POST["p23"], 
                              ':email'=>$_POST["email"]));
           

Share this post


Link to post
Share on other sites

:psychic:

 

Have you considered that maybe the problem is somewhere else?

Share this post


Link to post
Share on other sites

Not familiar with that specific error message.  Can you elaborate?  Or at least show us the code that generates it?

Share this post


Link to post
Share on other sites

Wait, is it required to have the colon in the key name for the array passed to execute()? Because I'm not doing that and it's working for me with SQLite. Maybe it depends on the driver?

 

I have a demo in like 20 minutes, so I'm not changing anything right now.

Share this post


Link to post
Share on other sites

There really isnt anymore code to it, just display the error and set the $password and $hash.  I have tried with and without the ":" and it does not work either way.  Here is the entire code for the insert into statement...

 

if($user=="n" && $email=="n") {        
        $password = $_POST["pword"];
        $hash = password_hash($password, PASSWORD_DEFAULT);

        $stmt = $conn->prepare("Insert Into clients (fname, lname, username, password, addr, city, st, zip, phone1, phone2, email)
                                Values (:fname, :lname, :username, :password, :addr, :city, :st, :zip, :phone1, :phone2, :email)");
        $rec = $stmt->execute(array (
                              'fname'=>$_POST["fname"],
                              'lname'=>$_POST["lname"], 
                              'username'=>$_POST["uname"], 
                              'password'=>$hash, 
                              'addr'=>$_POST["addr"], 
                              'city'=>$_POST["city"], 
                              'st'=>$_POST["st"], 
                              'zip'=>$_POST["zip"], 
                              'phone1'=>$_POST["p11"] . $_POST["p12"] . $_POST["p13"], 
                              'phone2'=>$_POST["p21"] . $_POST["p22"] . $_POST["p23"], 
                              'email'=>$_POST["email"]));
                         
        if($rec) {
            echo "Item post successful.";
        }else{
            echo "Item post failed.";
        }

    }

 
The code provides this error "Item post failed."
Edited by sraidr69

Share this post


Link to post
Share on other sites

Do you have error checking turned on?

Do a check of your Prepare call b4 doing the execute too

Share this post


Link to post
Share on other sites

Yes error checking is on.  Something interesting happened when I removed all the colons from both the values statement and array string and the insert was kind of successful.  It insert a new line into the database but all the data was null.  So I checked the $_POST[''] values and they have data.

Share this post


Link to post
Share on other sites

The colons are *not* optional in the values statement. They are necessary so SQL can recognise them as placeholders (otherwise they are assumed to be column names)

Share this post


Link to post
Share on other sites

I got the code above to execute but it did not insert into the table still.  I tried changing it around to the code below with the same result.  Could there be something with the mysql that is not allowing it to insert?  I am able to read data from the db.

$p1=$_POST["p11"] . $_POST["p12"] . $_POST["p13"];
        $p2=$_POST["p21"] . $_POST["p22"] . $_POST["p23"];        
        $str ="Insert Into clients (fname, lname, username, password, addr, city, st, zip, phone1, phone2, email)
                                Values (:fname, :lname, :username, :password, :addr, :city, :st, :zip, :phone1, :phone2, :email)";
                
        $fname=$_POST["fname"];
        $lname=$_POST["lname"];
        $uname=$_POST["uname"];
        $myhash=$hash;
        $addr=$fname=$_POST["addr"];
        $city=$_POST["city"];
        $st=$_POST["st"];
        $zip=$_POST["zip"];
        $p11=$p1;
        $p12=$p2;
        $email=$_POST["email"];
        $stmt = $conn->prepare($str); 
        //$stmt->bindParam(':fname', $fname);
        //$stmt->bindParam(':lname', $lname);
        //$stmt->bindParam(':username', $uname);
        //$stmt->bindParam(':password', $myhash);
        //$stmt->bindParam(':addr', $addr);
        //$stmt->bindParam(':city', $city);
        //$stmt->bindParam(':st', $st);
        //$stmt->bindParam(':zip', $zip);
        //$stmt->bindParam(':phone1', $p11);
        //$stmt->bindParam(':phone2', $p12);
        //$stmt->bindParam(':email', $_POST["email"]);
	     
	     $stmt->execute(array('fname'=>$fname, 'lname'=>$lname, 'username'=> $uname, 'password'=>$myhash, 'addr'=>$addr, 'city'=>$city, 'st'=>$st, 'zip'=>$zip, 'phone1'=>$p11, 'phone2'=>$p12, 'email'=>$_POST["email"]));
        echo $str;
Edited by sraidr69

Share this post


Link to post
Share on other sites

First simply turning on error reporting (without exemptions) should had help you out or pointing into you into the right direction. 

 

Second I would minimize at first what you are trying to insert into database. 

 

Here's an example of of a tutorial that I starting on php pdo

function createLogin(array $data, $pdo) {
    /* Secure the Password by hashing the user's password. */
    $data['password'] = password_hash($data['password'], PASSWORD_BCRYPT, array("cost" => 15));
    try {

        /* Set the query variable */
        $query = 'INSERT INTO myUsers (name, password, email, security, confirmation, date_added) VALUES (:name, :password, :email, :security, :confirmation, NOW())';

        /* Prepare the query */
        $stmt = $pdo->prepare($query);

        /* Execute the query with the stored prepared values */
        $result = $stmt->execute([
            ':name' => $data['name'],
            ':password' => $data['password'],
            ':email' => $data['email'],
            ':security' => $data['security'],
            ':confirmation' => $data['confirmation']
        ]); // End of execution:
        return TRUE;
    } catch (PDOException $error) {
        // Check to see if name is already exists:
        $errorCode = $error->errorInfo[1];
        if ($errorCode == MYSQL_ERROR_DUPLICATE_ENTRY) {
            error_log("Duplicate Name was Enter", 1, "jrpepp@pepster.com");
        } else {
            throw $error;
        }
    }
} 

thirdly I would take a look at your database table structure, for example here's the structure of the above in MySQL:

    $sql = "CREATE TABLE IF NOT EXISTS myUsers ("
            . "ID int(11) AUTO_INCREMENT PRIMARY KEY,"
            . "name varchar(60) NOT NULL,"
            . "password varchar(255) NOT NULL,"
            . "email varchar(60) NOT NULL,"
            . "security varchar(25) NOT NULL,"
            . "confirmation varchar(255) NOT NULL,"
            . "date_added datetime NOT NULL DEFAULT '0000-00-00 00:00:00')"; 

the above is part of a script that I wrote for an install script, but you can get the structure using phpMyAdmin. Looking over the structure should give you an idea where you forgot to cross a t or dot an i. 

 

HTH John

 

Share this post


Link to post
Share on other sites

1. Error Reporting is on.  There is no error.

2. I minimized to uname, password and email with the same result.

3. These 3 fields are varchar(45)

        
        $str ="Insert Into clients (username, password, email)
                            Values (:username, :password, :email)";
                
        $uname=$_POST["uname"];
        $password = $_POST["pword"];
        $hash = password_hash($password, PASSWORD_DEFAULT);
        $email=$_POST["email"];
        
        $stmt = $conn->prepare($str); 	     
        $stmt->execute(array('username'=> $uname, 'password'=>$hash, 'email'=>$_POST["email"]));

Share this post


Link to post
Share on other sites

First and foremost, you are using PDO, correct? I don't see anywhere in this thread that you've specifically said that - of course it's early and I may have missed it.

 

Secondly, do me a favor and do this:

print("<p>".password_hash('testing', PASSWORD_DEFAULT)."</p>");

Count the number of letters in the output - it's more than 45. Technically, I don't think that's what would be causing the insert to fail (I'm pretty sure it would just truncate the inserted data, but even then every password would be invalidated).

 

And of course, as requinix said,

 

Instead of guessing why it failed, actually find out.

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.