SQL INSERT not working.

ronc0011 · November 1, 2015

OK obviously I’m missing something. The good news is I’m not getting any errors Woo-Hoo!!!

For background this is really just a continuation from an earlier issue / question...

http://forums.phpfreaks.com/topic/298933-problem-with-prepare-statement/

I can’t figure out where it’s failing or the cause of it.

Maybe someone here can spot it. Here’s what I’ve got so far…

MyForm.html…


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


   <form method="post" action="process.php" >
    
         First Name: <input type="text" id="firstname" name="firstname" /><br />
          Last Name: <input type="text" id="lastname" name="lastname" /><br />
              email: <input type="text" id="email" name="email" /><br />
                     <input type="submit" value="submit" />
   </form>

    <br />
    <br />
    <br />
    <br />
    <br />
    <br />

    <div style =" border:1px solid black; margin-bottom: 30px; padding:20px 20px 10px 30px;background-color:#d6f3ff;">
            <a href='index.php'>HOME</a> <br />
            <a href='myform.html'>My Form</a> <br />
            <a href='process.php'>PROCESS</a> <br />  
            <a href='conn.php'>CONN.PHP</a> <br />
</div>


</body>
</html>

BTW I have put nav links in all these pages just to simplify my life a little.

& next is the process.php file which is where the form sends the form data

NOTE the html block is just to format and display the nav links….


<?php
include 'conn.php';


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

$fname = $_POST['firstname'];
$lname = $_POST['lastname'];
$email = $_POST['email'];

$stmt = $db->prepare("INSERT INTO guest (fname, lname, email) VALUES (:fname, :lname, :email)");
        $stmt->bindValue(':fname', '$fname');
        $stmt->bindValue(':lname', '$lname');
        $stmt->bindValue(':email', '$email');
}



$stmt = $db ->query('SELECT * FROM guest');
$results = $stmt->fetchAll ();

foreach ($results as $row) {

    $firstname = htmlentities($row ['fname']);
    $lastname = htmlentities($row ['lname']);
    $email = htmlentities($row ['email']);

    echo $firstname . " " . $lastname . " " . $email . "<br />";


 }


?>

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

    <div style =" border:1px solid black; margin-bottom: 30px; padding:20px 20px 10px 30px;">
            <a href='index.php'>HOME</a> <br />
            <a href='myform.html'>My Form</a> <br />
            <a href='process.php'>PROCESS</a> <br />  
            <a href='conn.php'>CONN.PHP</a> <br />
</div>



</body>
</html>


<?php


//  Leftover debris from earlier efforts / attempts.

//echo "Hello," . $firstname . " " . $lastname . " your email is " . $email;



 //           echo  "<br />" . "<br />" . "<br />";




?>

Oh & BTW the conn.php file looks like this…


<?php


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

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

}


        echo "<a href='index.php'>HOME</a>" . "<br />";
        echo "<a href='myform.html'>My Form</a>" . "<br />";
        echo "<a href='process.php'>PROCESS</a>" . "<br />";
        echo "<a href='conn.php'>CONN.PHP</a>" . "<br />";




?>

Another NOTE: I put nav links on all the pages just for the sake of making life a little easier as I jink around through these files trying to sort this out.

The pages load and appear to work OK except the INSERT statement doesn’t seem to be doing anything at all. But nothing is throwing any errors. I’m getting output from the “fetchAll” / “foreach block. But the records it is returning are existing records that I entered when I built the database

Just to check this out I setup this alternate configuration. I changed the “Action in the myform.html file to point to a file called showMe.php on which I just copied the first part of process.php. i.e….

The showMe.php file...


<?php
include 'conn.php';


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

    $fname = $_POST['firstname'];
    $lname = $_POST['lastname'];
    $email = $_POST['email'];


    // I add this just to check that the asignment lines are working.

    echo "Hello" . $fname . " " . $lname . " " . "your email address is" . $email;

}


?>

And guess what it all works except for the INSERT clause / statement.

So what am I missing here?

Barand · November 1, 2015

The INSERT appears to do nothing because that is exactly what it is doing.

You have to execute the query.

mac_gyver · November 1, 2015

The INSERT appears to do nothing because that is exactly what it is doing.

You have to execute the query.

which you were dong in both sets of code in your previous thread.

not trying to give you a hard time (yet), but you must actually learn what each statement you are using does, so that you will know how it contributes to the goal you are trying to achieve, and where it belongs in your code to do it's job. then, when the goal isn't being met, you will have a good idea where to start looking to find what's causing the problem.

these are the three different statements you were using in your previous code -

$stmt = $db ->prepare (); // prepare an sql statement, that has place-holders in it for inputs

$stmt ->bindValue(); // bind actual values to the place-holders in the sql statement

$stmt ->execute (); // execute the query

so, the flow is prepare, bind inputs, run the query. prepare, bind, execute. prepare, bind, execute... (okay, so now i am giving you a hard time, because you apparently didn't look at your code and try to figure out why it isn't working or what's different about it from the code you wrote before.)

Edited November 1, 2015 by mac_gyver

ronc0011 · November 1, 2015

OK, so I added the execute line & now it’s throwing an error …

“Invalid parameter number: parameter was not defined”

This is supposed to be on line 15, the “execute” line

The adjusted code. Process.php


<?php
include 'conn.php';


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

$fname = $_POST['firstname'];
$lname = $_POST['lastname'];
$email = $_POST['email'];

$stmt = $db->prepare("INSERT INTO guest (fname, lname, email) VALUES (:$fname, :$lname, :$email)");
        $stmt->bindValue(':fname', '$fname');
        $stmt->bindValue(':lname', '$lname');
        $stmt->bindValue(':email', '$email');
        $stmt->execute();
}



$stmt = $db ->query('SELECT * FROM guest');
$results = $stmt->fetchAll ();

foreach ($results as $row) {

    $firstname = htmlentities($row ['fname']);
    $lastname = htmlentities($row ['lname']);
    $email = htmlentities($row ['email']);

    echo $firstname . " " . $lastname . " " . $email . "<br />";


 }


?>

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

    <div style =" border:1px solid black; margin-bottom: 30px; padding:20px 20px 10px 30px;">
            <a href='index.php'>HOME</a> <br />
            <a href='myform.html'>My Form</a> <br />
            <a href='process.php'>PROCESS</a> <br />  
            <a href='conn.php'>CONN.PHP</a> <br />
</div>



</body>
</html>


<?php

//echo "Hello," . $firstname . " " . $lastname . " your email is " . $email;

//            echo  "<br />" . "<br />" . "<br />";

//            echo "<a href='index.php'>HOME</a>" . "<br />";
//            echo "<a href='myform.html'>My Form</a>" . "<br />";
//            echo "<a href='process.php'>PROCESS</a>" . "<br />";
 //           echo "<a href='conn.php'>CONN.PHP</a>" . "<br />";

 //           echo  "<br />" . "<br />" . "<br />";




?>

So here’s something weird; the first time I ran this it output to the page …

$fname $lname $email

It added it to the list of other entries in the table “guest”.

I’ve tried two different values in the VALUES argument. When I was first typing this in, the IDE kept wanting to auto complete the values as VALUES ($fname, $lname, $email). That didn’t seem right to me so I changed em back to VALUES (fname, lname, email) which is how the example I’m following has them. I’m thinking that when I tried it with the execute statement I was usining VALUES ($fname, $lname, $email). Thus the $fname $lname $email. But honestly I couldn’t say for sure. It starts getting almost impossible to keep track of these variables / place holders etc.

In any event the firsttime I ran it it produced something even if it was something wrong. I then switched the VALUES and after that everything throws errors i.e.

“Invalid parameter number: parameter was not defined' in C:\Apache\htdocs\forms\process.php on line 15”

In any event they are both throwing the same error. I should probably clarify some things. The database “forms” has 1 table named “guest” with 4 columns “id”, fname, lname, email.

The form has three input tags named “firstname”, “lastname”, & “email”.

The php is defining 3 variables $fname, $lname, and $email.

So now I’m back to throwing errors in the INSERT block or is it the “prepare” clause? Now this time there is no age field so no “int” issue.

benanamen · November 1, 2015

Regarding your changing names around, firstname=dbcolumn fnname, lastname=dbcolumn lname, Just dont! It is a bad practice. if your DB column is fname then name your form field fname.

Remember KISS - Keep It Simple Stupid

And abandoning the 'age' issue, you should have understood what you were STILL doing wrong even after the correct answer was given to you instead of just walking away from it. You would have learned something.

Edited November 1, 2015 by benanamen

ronc0011 · November 1, 2015

benanamen,

Well that is the point. I get lost in all this rewriting the code over and over between all the errors and everything else. Today is another example. Just like previous days fatigue is beginning to take hold and the never ending errors are really wearing me down. I’ve been at this going on 4 days and it keeps crashing around the same place I just rebuilt the example from a tutorial I was following and it works. It doesn’t output anything to the page but the new records do show up in the database. What this example doesn’t show is how to retrieve the values from an html form So what I’ve been attempting to do is combine the examples of processing form data with a prepared INSERT statement. I can get the prepared INSERT statement to work just fine…

Current example. I just rebuilt this from the example I’m working from and it WORKS

[code}

<?php

include 'conn.php';

echo "Hello World!";

$stmt = $db->prepare("INSERT INTO guest(fname, lname, email) VALUES (:fname, :lname, :email)");

$stmt->bindValue(':fname', 'manny');

$stmt->bindValue(':lname', 'danny');

$stmt->bindValue(':email', 'md@dann.com');

$stmt->execute();

?>

[/code]

However the previous page same code persist in throwing this error.

"Invalid parameter number: parameter was not defined in C:\Apache\htdocs\insert3\insert.php on line 9"

BTW Line 9 is the execute line.

Here is the previous page I was working with and when I couldn’t get it to stop throwing errors I cut it back to just the code from the tutorial example and it continues to throw the errors. It’s not a cash issue, I renamed it and it still errors So why is one page throwing errors and the other one isn’t?

<?php
include 'conn.php';

$stmt = $db->prepare("INSERT INTO guest(fname, lname, email)VALUES(:fname, :lname, :email)");

$stmt->bindValue(':firstname', 'johnny');
$stmt->bindValue(':lastname', 'bob');
$stmt->bindValue(':email', 'jb@coit.me');
$stmt->execute();





?>

ronc0011 · November 1, 2015

It's really starting to look like I'm in for a repeat of the same scenario I experienced sooo many times before. Tomorrow I'll have to go to work and I'll be exhausted when I get home and will never even think of this again until just maybe if nothing else cones up just maybe next weekend. Would'nt hold my breath. Just as likely to be another 6 months before I get to revisit this again.

I do appreaciate you guys trying to help though.

I think if I actually had a working example of code that does this complete from html form to the php that processes it and outputs the results to the page I could probably track down where I'm going astray

Barand · November 1, 2015

$stmt->bindValue(':fname', '$fname');

In that statement you are setting the :fname parameter to the literal string $-f-n-a-m-e and not the contents of the variable $fname. That is because of the single quotes around the variable. Variables inside double-quotes are expanded to their values but those inside single-quotes are not.

Lose the quotes around the variables. Should be

$stmt->bindValue(':fname', $fname);

benanamen · November 1, 2015

Your problem is EXACTLY what I just told you about changing names around!

Your values are :fname, :lname but you are binding :firstname :lastname

You have just proven my point!

$stmt = $db->prepare("INSERT INTO guest(fname, lname, email)VALUES(:fname, :lname, :email)");

$stmt->bindValue(':firstname', 'johnny');
$stmt->bindValue(':lastname', 'bob');
$stmt->bindValue(':email', 'jb@coit.me');
$stmt->execute();

@Barand, you didnt notice this (His last example. He keeps switching stuff around. Hard to keep up.). :happy-04:

Edited November 1, 2015 by benanamen

Barand · November 1, 2015

... if your DB column is fname then name your form field fname.

Remember KISS - Keep It Simple Stupid

In other words, never miss out on an opportunity to expose your database structure to the world at large

benanamen · November 1, 2015

Its not that hard for a hacker to know that your first name field is either fname, FirstName, firstName, or first_name.

But do share. What would you name your first name form input and what would you name the db column for the same? :confused:

Purposely naming one different than the other is really just a form of security by obscurity and we both know that although it is better than nothing, it is a worthless attempt at security.

Edited November 1, 2015 by benanamen

ronc0011 · November 1, 2015

Your problem is from EXACTLY what I just told you about changing names around!

Your values are :fname, :lname but you are binding :firstname :lastname

You have just proven my point!

$stmt = $db->prepare("INSERT INTO guest(fname, lname, email)VALUES(:fname, :lname, :email)");

$stmt->bindValue(':firstname', 'johnny');

$stmt->bindValue(':lastname', 'bob');

$stmt->bindValue(':email', 'jb@coit.me');

$stmt->execute();

You were right I didn't see it at first. Like I said fatigue starts getting me. What I'm really trying to do is bind the values from my htmlform to the fname, lname, email placeholders, but when I started getting all these errors it derailed m. Got me totally off track of what I was trying to get to work.

ronc0011 · November 3, 2015

OK I'm back to working on this seemingly impossible tast. Here's what i'm trying to do. I have a prepared INSERT statement and I want to use values that I get from an html form. I can get the INSERT statement to work if it is the only thing on the page with no output back to the page but as soon as I start trying to incorporate the form retrieval elements or the output elements it all crashes...

This works but it outputs nothing to the screen so I have to open PHPMyAdmin to check the results. but it is there ...

<?php

include 'conn.php';

$stmt = $db->prepare("INSERT INTO guest(fname, lname, email)VALUES(:fname, :lname, :email)");

$stmt->bindValue(':fname', 'alice');
$stmt->bindValue(':lname', 'may');
$stmt->bindValue(':email', 'a@may.me');
$stmt->execute();

?>

So OK when I try to feed this statement data from my form and then output it to the screen Nothing I try works which should be evident from the myriad post I've already made here on this subject.

here's my latest attempt.

<?php
include 'conn.php';


/*

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

    $fname = $_POST['fname'];
    $lname = $_POST['lname'];
    $email = $_POST['email'];
}

*/


$stmt = $db->prepare("INSERT INTO guest(fname, lname, email)VALUES(:fname, :lname, :email)");

$stmt->bindValue(':fname', '$_POST['fname']');
$stmt->bindValue(':lname', '$_POST['lname']');
$stmt->bindValue(':email', '$_POST['email']');
$stmt->execute();


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

    $fname = $_POST['fname'];
    $lname = $_POST['lname'];
    $email = $_POST['email'];


    // I add this just to check that the asignment lines are working.

    echo "Hello" . $fname . " " . $lname . " " . "your email address is" . $email;

}


?>

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


   <form method="post" action="insert.php" >
    
         First Name: <input type="text" id="fname" name="fname" /><br />
          Last Name: <input type="text" id="lname" name="lname" /><br />
              email: <input type="text" id="email" name="email" /><br />
                     <input type="submit" value="submit" />
   </form>

  




</body>
</html>

NOTE: I have tried several variations on the values in the Bindvalues lines everything just keeps throwing errors.

OK so I gotta go to work. Probably wont get back to this until tomorrow morning.

Thank you to everyone who has been trying to help me with this.

Edited November 3, 2015 by ronc0011

Barand · November 3, 2015

In that statement you are setting the :fname parameter to the literal string $-f-n-a-m-e and not the contents of the variable $fname. That is because of the single quotes around the variable. Variables inside double-quotes are expanded to their values but those inside single-quotes are not.

Lose the quotes around the variables. Should be
$stmt->bindValue(':fname', $fname);

You are still repeating the same mistake. Read and learn.

Also you should check that the values were posted before using. You almost had this check but commented out the code. That check should have enclosed the query too otherwise you check if they exist but then go ahead and use them anyway.

ronc0011 · November 3, 2015

One last attempt before I go to work. I did some more tinkering with the code and here's what I have now...


<?php
include 'conn.php';


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

    $fname = $_POST['fname'];
    $lname = $_POST['lname'];
    $email = $_POST['email'];


    // I add this just to check that the asignment lines are working.

    echo "Hello" . $fname . " " . $lname . " " . "your email address is" . $email;

}


$stmt = $db->prepare("INSERT INTO guest(fname, lname, email)VALUES(:fname, :lname, :email)");

$stmt->bindValue(':fname', '$_POST["fname"]');
$stmt->bindValue(':lname', '$_POST["lname"]');
$stmt->bindValue(':email', '$_POST["email"]');
$stmt->execute();



/*

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

    $fname = $_POST['fname'];
    $lname = $_POST['lname'];
    $email = $_POST['email'];


    // I add this just to check that the asignment lines are working.

    echo "Hello" . $fname . " " . $lname . " " . "your email address is" . $email;

}

*/
?>

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


   <form method="post" action="insert.php" >
    
         First Name: <input type="text" id="fname" name="fname" /><br />
          Last Name: <input type="text" id="lname" name="lname" /><br />
              email: <input type="text" id="email" name="email" /><br />
                     <input type="submit" value="submit" />
   </form>

  




</body>
</html>

OK so this isn't throwing errors.

But what it is sending to the database is "$_POST["fname"], $_POST["lname"], $_POST["email"] "

It seems no matter what I try to use in the bindValues lines i.e. $fname etc. or $POST_[fname]... no matter what it doesn't like it, period.

Also the echo statement isn't doing anything.

Barand · November 3, 2015

But what it is sending to the database is "$_POST["fname"], $_POST["lname"], $_POST["email"] "

Yes, it will.

For the THIRD time (and last), do not put those variable inside single quotes.

$stmt->bindValue(':fname', $_POST["fname"]);

If you totally disregard the advice given, why bother posting at all and waste our time.

ronc0011 · November 3, 2015

HA!!!!

that did it I'm afraid the whole naming thing is really twisting me in knots. It's something I'm really going to have to work on a lot.

I really gotta thank you a lot for your help & patience. I know I'm gonna be working a lot with this, ie. update, delete, select, etc. hopefully I'm on the right track now.

ronc0011 · November 3, 2015

OK just for clarity sake here is what just worked...

<?php
include 'conn.php';


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

    $fname = $_POST['fname'];
    $lname = $_POST['lname'];
    $email = $_POST['email'];

    
    $stmt = $db->prepare("INSERT INTO guest(fname, lname, email)VALUES(:fname, :lname, :email)");

    $stmt->bindValue(':fname', $fname);
    $stmt->bindValue(':lname', $lname);
    $stmt->bindValue(':email', $email);
    $stmt->execute();





    // I add this just to check that the asignment lines are working.

    echo "Hello" . $fname . " " . $lname . " " . "your email address is" . $email;

}


$stmt = $db->prepare("INSERT INTO guest(fname, lname, email)VALUES(:fname, :lname, :email)");

$stmt->bindValue(':fname', '$_POST["fname"]');
$stmt->bindValue(':lname', '$_POST["lname"]');
$stmt->bindValue(':email', '$_POST["email"]');
$stmt->execute();



/*

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

    $fname = $_POST['fname'];
    $lname = $_POST['lname'];
    $email = $_POST['email'];


    // I add this just to check that the asignment lines are working.

    echo "Hello" . $fname . " " . $lname . " " . "your email address is" . $email;

}

*/
?>

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


   <form method="post" action="insert.php" >
    
         First Name: <input type="text" id="fname" name="fname" /><br />
          Last Name: <input type="text" id="lname" name="lname" /><br />
              email: <input type="text" id="email" name="email" /><br />
                     <input type="submit" value="submit" />
   </form>

  




</body>
</html>

I moved the INSERT statement inside the "if' statement and using the PHP variables " $fname, $lname, $email " this time without any quotes. I'm thinking that those two things together were what was giving me grief. I know you gave me references in the PHP documentation, I'll have to re-read back through this thread and track it down. Honestly the PHP documentation I find to be difficult to follow, thus my preference for the YouTube tutorials. I do in fact go to the PHP documentation periodically put it usually isn't my first choice 'Go To" resource.

Well, I really do gotta go to work now. And thank you again for your help & patience.

Edited November 3, 2015 by ronc0011

Barand · November 3, 2015

Why are you preparing, binding then executing the query twice? That will write each record twice to the table.

ronc0011 · November 4, 2015

Why are you preparing, binding then executing the query twice? That will write each record twice to the table.

Actually the second one is just a leftover from me cutting and pasting. I copied the second one and pasted it into the space above the INSERT line and then I just forgot to comment out the first occurrence of it, the one at the bottom. It's just me shuffling stuff around and attempting to post the examples to this forum. Anyway your advice worked and pointed the way for me so now I can tweak to my hearts content and find and prove all my mistakes while knowing what's correct and works.

shan · November 4, 2015

why dont you try this @ronc0011

first include name to your submit button:

<input type="submit" name="login-button" value="login" />

then try this code:

if(isset($_POST['login-button'])){
...insert logic
}

mac_gyver · November 4, 2015

@shan, that has nothing to do with any problem in this thread. the OP is already testing a form field that will always exist if the form has been submitted.

shan · November 4, 2015

k @macgyver

Sign In

SQL INSERT not working.

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

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