Problem with prepare statement

ronc0011 · October 30, 2015

I’m trying to post data to a MySQL DB table. In fact I’m working on learning how to interact with a DB from a web form. I had it almost working a couple of times but when I start trying to tweak it, it all goes awry. But the consistent issue that keeps popping up is an issue with “ Incorrect integer value: ':age' for column 'age' at row 1 “

This table "people" in the DB "peoples" has 3 columns; “id”, “name”, “age”. I was able to get it to work as long as I stuck with a numbered array. But when I try to use an associative array I start getting the error. The only thing I can think of is the id field in the DB. That maybe that’s throwing off my row count. Though obviously I don’t want to display the id field data in my webpage.

So here’s the basic code I’m trying to make work.


<?php

   include 'connForm.php';
?>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>PDO with Forms</title>
</head>

<body>


    <form method="post" action="connform.php">

          Name: <input type="text" id="name" name="name" /> <br />
          Age: <input type="text" id="age" name="age" /> <br />
               <input type="submit" value="add" /> <br />

    </form>

    <br />
    <br />



</body>
</html>

<?php
    

   



echo htmlentities($row['name']). " is " . htmlentities($row['age']). " years old "  . "<br>";



?>

conform.php contains the following code…

<?php

try {
    $db = new PDO('mysql:host=127.0.0.1;dbname=peoples', 'root', 'r00t');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}
catch (PDOException $e)
{
    echo $e->getMessage();
    echo '<br>';
    echo 'You may have a problem';

}

$stmt = $db->query('SELECT * FROM people');
$result = $stmt->fetchAll ();

foreach($result as $row) {
          $name = htmlentities($row ['name']);
          $age = htmlentities($row ['age']);


}


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

        $name = $_POST ['name'];
        $age = $_POST ['age'];

        $stmt = $db ->prepare ("INSERT INTO people (name, age) VALUES (':name', ':age') ");

        
        $stmt ->bindValue(':name', $_POST ['name']);
        $stmt ->bindValue(':age', $_POST ['age']);
        $stmt ->execute ();


}


?>

One of the biggest problems with trying to learn anything from the internet is

that everybody has their own way of doing everything and each one wants to show off their precieved expertise. This means that trying to learn anything means

trying to distill all these dispirit methods into the simplest cut & dried method I can manage. oddly of all the tutorials out there on YouTube dealing with PHP and PDO there are almost none dealing with PDO and web forms. I say "none" I mean, like 2 maybe 3 though I don't think I've found #3.

benanamen · October 30, 2015

Post an sql dump of your DB.

Why are you creating variables on your insert and then not using them?

Edited October 30, 2015 by benanamen

ginerjm · October 30, 2015

Don't wrap the query parms in the single quotes. Also when you use bindvalue (which I don't) the default type will be a string which you don't want for the age value.

This works much better for me:

$stmt->execute(array('name'=>$_POST['name'],'age'=>$_POST['age']));

Edited October 30, 2015 by ginerjm

mac_gyver · October 30, 2015

One of the biggest problems with trying to learn anything from the internet is that everybody has their own way of doing everything

this is why you should use the documentation to learn the basics. if you read the relevant sections of the php.net documentation for the PDO class, you will find the meaning of and see examples showing how to use each method.

while slightly off topic, a person's age is not a fixed number, unless they are dead. you should be storing a person's date of birth, and calculating their age.

benanamen · October 30, 2015

The problem you are having is because you are running MySQL in strict mode. To verify, run this query: SELECT @@sql_mode

https://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sql-mode-strict

@mac_gyver is right, age should be calculated from birth date. Age is always changing, you don't want to store a hard number.

Edited October 30, 2015 by benanamen

mac_gyver · October 30, 2015

@benanamen, the problem isn't strict mode. it's because you don't put single-quotes around the place-holders in the sql syntax.

benanamen · October 30, 2015

@benanamen, the problem isn't strict mode. it's because you don't put single-quotes around the place-holders in the sql syntax.

Yes, but allow me to rephrase,

The reason the OP is seeing that error is because he is running in strict mode. If strict mode was disabled Mysql would not throw an exception and add :name and a zero to the DB.

So the problem itself is not caused by strict mode, it just throws an exception instead of entering what it can into the DB.

I am just not always up for giving detailed info, but I could have phrased it better. Thanks for keeping my blade sharp.

Edited October 30, 2015 by benanamen

ginerjm · October 30, 2015

I still think it is because he bound the age as a string rather than an int. And the quotes on the query parms that I previously mentioned.

We'll just have to wait and see what the OP responds with.

benanamen · October 30, 2015

I still think it is because he bound the age as a string rather than an int. And the quotes on the query parms that I previously mentioned.

We'll just have to wait and see what the OP responds with.

No, ginerjm, it is exactly as @macgyver said.

Remove the red quotes and there is no problem

$stmt = $db ->prepare ("INSERT INTO people (name, age) VALUES (':name', ':age') ");

I thought it was a good opportunity to bring up strict mode. In my experience, most people don't even know it exists. It has also jumped around from version to version as to the default setting. Check out this read from the Mysql Devs about it.

http://mysqlserverteam.com/improvements-to-strict-mode-in-mysql/

* I think I will add this to my list of why I use positional placeholders (?,?) instead of named parameters. You just cant mess up a question mark. (Yeah, I know, you can have too many or not enough..)

Edited October 30, 2015 by benanamen

ronc0011 · October 30, 2015

First & foremost I want to thank everyone for your replies but unfortunately that boat has sailed. I’ve abandoned that line of attack and moved on. I’m thinking maybe my first step should be; How to load form data into php variables here’s my current attempt (failed)…

First part of my page…

<?php

include 'conn.php';


?>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body>
      
      <form method="post" action="insert2.php">

                First Name;<input type="text" id="firstname" name="firstname" /> <br />
                Last Name<input type="text" id="lastname" name="lastname" /><br />
                Post Code<input type="text" id="postcode" name="postcode" /><br />
                <input type="submit" value="Add" /><br />
      
      </form>

    <br />
    <br />


</body>
</html>

Last part of page in PHP tags…


<?php

$firstname = $_POST ['firstname'];
$lastname = $_POST ['lastname'];
$postcode = $_POST ['postcode'];

$stmt = $db ->prepare ("INSERT INTO names (firstname, lastname, postcode) VALUES (:firstname, :lastname, :postcode) ");

$stmt ->bindValue(':firstname', '$firstname');
$stmt ->bindValue(':lastname', '$lastname');
$stmt ->bindValue(':postcode', '$postcode');
$stmt ->execute ();

$stmt = $db->query("SELECT * FROM NAMES");

$results = $stmt ->fetchAll(PDO::FETCH_ASSOC);


foreach ($results as $row){
    
    $firstname = htmlentities($row['firstname']);
    $lastname = htmlentities($row['lastname']);
    $postcode = htmlentities($row['postcode']);

    echo $firstname . ' ' . $lastname . ' ' . $postcode . '<br>';
    
}
?>

& BTW the conn.php page looks like this…

<?php

try {
    $db = new PDO('mysql:host=127.0.0.1;dbname=testdb', 'root', 'r00t');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}
catch (PDOException $e)
{
    echo $e->getMessage();
    echo '<br>';
    echo 'You may have a problem';

}


?>

No doubt there are any number of problems with this page but apparently the first is with the assignment of form data to PHP variables because it is throwing this error “Undefined index: firstname” for all 3 variables i.e.


$firstname = $_POST ['firstname'];
$lastname = $_POST ['lastname'];
$postcode = $_POST ['postcode'];

Lines 33, 34, 35.

Also, new DB this one has 4 fields “id”, “firstname”, “lastname”, “postcode”

And BTW the fetchAll and foreach loop appear to work just fine because the page does output the 15 or so recordsets after it displays the errors.

mac_gyver · October 30, 2015

the undefined index errors are because your form processing code is running unconditionally, before there is any form data to process.

your form processing code should be at the top of your file, before the <!DOCTYPE html> tag (so that any validation errors can be displayed when you redisplay the form) AND the form processing code needs to be inside of a conditional statement that's testing that a form has been submitted, so that the form processing code only runs when there is form data.

edit: see the following post for a suggested code layout that will help make your code fool proof - http://forums.phpfreaks.com/topic/297824-database-issues-and-working/?do=findComment&comment=1519095

Edited October 30, 2015 by mac_gyver

ginerjm · October 30, 2015

benanamen - That is Exactly what I said all along. Remove the quotes around the query parms. What did you think I meant??

benanamen · October 30, 2015

@ginerjm,

I think I got confused with this:

"I still think it is because he bound the age as a string rather than an int"

I was thinking you were saying it didn't work because he didn't do this:

$stmt->bindValue(':age', $_POST['age'], PDO::PARAM_INT);

Must have misunderstood you.

Edited October 30, 2015 by benanamen

ginerjm · October 30, 2015

It definitely had to do with the quotes but the bind could have made for an additional problem. But - we'll never know....

benanamen · October 30, 2015

But - we'll never know.

@ginerjm,

I actually set up a test DB. It was just the quotes. If you mean the bind like PDO::PARAM_INT it didn't make any difference at all.

If I used named placeholders I would have caught the quotes right off but I like my question marks.

Edited October 30, 2015 by benanamen

ronc0011 · November 1, 2015

Just an update. this project was becoming such a cluttered mess I scrapped it. and BTW the last attempt after removing the quotes was still producing s string of errors. So anyway, like I said I abandoned this attempt and started a new run at it new project htdocs/forms/myform.html etc.

The new thread is starting of better somewhat. the new thread is at...

http://forums.phpfreaks.com/topic/298980-sql-insert-not-working/

Sign In

Problem with prepare statement

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information