Jump to content
ronc0011

Problem with prepare statement

Recommended Posts

 

 

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.

Share this post


Link to post
Share on other sites

Post an sql dump of your DB.

 

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

Edited by benanamen

Share this post


Link to post
Share on other sites

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 by ginerjm

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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 by benanamen

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

@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 by benanamen

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by benanamen

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by mac_gyver

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

@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 by benanamen

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by benanamen

Share this post


Link to post
Share on other sites

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/

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

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.