Using PDO and PHP to insert data from form into MySQL database

Question

I'm currently working on taking data from my form "form.php" and sending that to "index.php" where i'm using PDO statements to insert those values into the MySQL database. In MySQL I have it set up where firstname and lastname can not be null because the data is going to be submitted back to website where the First Name + Last Name = Full Name and that full name pops up on the supporters list.

My question is,

If I don't want to set MySQL to accept a NULL value, what should I change in my PHP to accept values from the form and submit that to the database with the value attached to that instance of that specific user? I tried php.net/docs and the other StackOverflow questions didn't fully solve my challenge, any help is greatly appreciated

Form

FORM.PHP

<form action="../index.php" method="post">
      <fieldset class="form-group">
      <label for="firstname">First Name</label>
      <input type="text" name="firstname" class="form-control" id="firstname" placeholder="First Name">
    </fieldset>


    <fieldset class="form-group">
      <label for="lastname">Last Name</label>
      <input type="text" name="lastname" class="form-control" id="lastname" placeholder="Last Name">
    </fieldset>


        <fieldset class="form-group">
          <label for="exampleInputEmail1">Email address</label>
          <input type="text" name="email" class="form-control" id="email" placeholder="Enter email">
          <small class="text-muted">We'll never share your email with anyone else.</small>
      </fieldset>

      <button type="submit" id="form-button" class="btn btn-primary">Submit</button>

    </form>

I am sending that to index.php which is where I am receiving the error!

"Error in connection to databaseSQLSTATE[23000]: Integrity constraint violation: 1048 Column 'firstname' cannot be null"

Here is my index.php code

$form = $_POST;
$id = $form['id'];
$firstname = $form['firstname'];
$lastname = $form['lastname'];
$email = $form['email'];


try {
$db = new PDO("mysql:host=" . DB_HOST. ";dbname=" .DB_NAME .";port=".DB_PORT,DB_USER,DB_PASS);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$add_supporter = $db->prepare("INSERT INTO supporters (id, firstname, lastname, email) 
                             VALUES (:id, :firstname, :lastname, :email)");

$add_supporter->bindParam(":id", $id);
$add_supporter->bindParam(":firstname", $firstname);
$add_supporter->bindParam(":lastname", $lastname);
$add_supporter->bindParam(":email", $email);
$add_supporter->execute();



} catch (Exception $e) {
echo "Error in connection to database" . $e->getMessage() . "</br>";
die();
}

use a conditional !empty() or alter your column to contain a default value. The former is better. — Funk Forty Niner
– Funk Forty Niner, Commented Feb 21, 2016 at 16:54
I'm with @RiggsFolly. Please clarify this: what should I change in my PHP to accept values from the form and submit that to the database with the value attached to that instance of that specific user? — Cedric Ipkiss
– Cedric Ipkiss, Commented Feb 21, 2016 at 16:57
something like if (!isset($_POST['firstname'],$_POST['lastname'],$_POST['email'])) { yourErrorHandlerHere(); } btw: Your form has no input control with id/name="id"; you might want to drop it from the sql query completely and let mysql handle that field automagically. — VolkerK
– VolkerK, Commented Feb 21, 2016 at 16:58

hakre · Accepted Answer · 2016-02-21 17:48:11Z

If you're new to PHP (or programming in general), here is one of the better tips and I can give for this (and similar) problem(s):

You have already formulated the problem statement. That is not yet (pseudo) code but the first important step:

what would that look like if I validated "firstname" and "lastname" were not empty?

This questions actually contains the solution: You would add validation for those values. Right now, you're not validating the input values but you pass them right along into the database query. In your current case the database validates these values then. From your question I read that you're not confident with that level of validation. You perhaps would consider the validation that is already done in your own words as an error message. But technically, the database-server is guarding the data-structure in your database so that values that are defined to not come in won't come in.

So what is the actual tip that I can give to you? In programming when you have the problem statement (like validate the input values before doing the database interaction), you just pretend you've solved the problem already. By that you move the problem to write the whole solution to your problem out of view and just write the main part of your software as if that is working already. That helps you to divide control-flow problems from data-handling problems. For example your program always needs to have validated values to work but what represents a validated value might change over time.

So in your code you have all form related data grouped together in the $form variable in form of an array. This is the code of your example where you map that $form variable to the parameters of your SQL query:

$id = $form['id'];
$firstname = $form['firstname'];
$lastname = $form['lastname'];
$email = $form['email'];

So before doing such database related actions, you could for example determine whether or not that form validates:

$formIsValid = validate_form($form);
if (false === $formIsValid) {
    // do what needs to be done with an invalid form
} elseif(true === $formIsValid) {
    // do what needs to be done with a valid form (database interaction)
} else {
    throw new UnexpectedValueException('Should never come here');
}

So this is how your code could look like if you would do validation of the form data. But still yet, it does not show how the form is being validated. For sure you need to validate each field of the $form so you need to write it down (defined) the form validation. For example some should not be null, I use the isset statement for that:

function validate_form(array $form) 
{
   if (!isset($form['firstname']) {
       return false;
   }

   if (!isset($form['lastname']) {
       return false;
   }

   return true;
}

I hope this gives you the needed pointers to continue with your script.

Community · Accepted Answer · 2017-05-23 12:19:42Z

1

As I said in comments, use a conditional !empty() against your POST arrays.

if( !empty($_POST['var1']) || !empty($_POST['var2'])){

// do something

}

else{

// do something else

}

You can change the ||- OR to an && - AND.

"and submit that to the database with the value attached to that instance of that specific user?"

As for a specific user, use a WHERE clause.

You would need to use a SELECT if you want to query a particular user.

http://dev.mysql.com/doc/refman/5.7/en/select.html

For all Data Manipulation Statements, consult:

http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-data-manipulation.html

To send mail to a particular user:

Pulled from this Q&A Send php mail to user if form submitted and as an example:

$results = $db->prepare("select $user_email from table where ID=$user_ID");

try{

/*YOUR INSERT STATEMENT FROM ABOVE*/

$to=$results;
$subject="Whatever you want your subject to be";
$headers = "From: [email protected]\r\n";
$headers .= "Reply-To: [email protected] \r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";

$message= "WHATEVER MESSAGE";
mail ($to , $subject , $message, $headers);
echo "Your message has been sent";
}
Catch($error){
Do whatever you want here. Add another mail function to let you know that  the post was not accepted, etc.
}

You can also consult this Q&A on Stack:

Select and send mail from my database

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Feb 21, 2016 at 16:59

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

4 Comments

Funk Forty Niner Over a year ago

Halelujiah Smokey! @RiggsFolly everyone has fun on the weekend.

RiggsFolly Over a year ago

Talk about Low hanging Fruit

VolkerK Over a year ago

Like "practicing medicine without a license"? ;-)

Funk Forty Niner Over a year ago

@VolkerK hehe, something like that. Just as long as the scalpel's dull enough, there should be much harm done ;-)

Collectives™ on Stack Overflow

Using PDO and PHP to insert data from form into MySQL database

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related