How to add variables into a mysql table from php

Question

Okay so my objective is to have people able to select there schedule. her is the code so far

<?php

//if form has been submitted process it
if(isset($_POST['submit'])){

    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "jesuitschedule";

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $sql = 'INSERT INTO schedule (Saturdaymorning, Saturdayafternoon, Sundaymorning, Sundayafternoon, weekday) VALUES (:Saturdaymorning, :Saturdayafternoon, :Sundaymorning, :Sundayafternoon, :weekday)';
        // use exec() because no results are returned
        $conn->exec($sql);
        echo "New record created successfully";
        }
    catch(PDOException $e)
        {
        echo $sql . "<br>" . $e->getMessage();
        }

    $conn = null;
}

//define page title
$title = 'schedule';

//include header template
require('layout/header.php');
?><!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>schedule</title>
    <link href="//netdna.bootstrapcdn.com/bootstrap/3.1.0/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="style/main.css">
</head>
<body>

<div class="container">

    <div class="row">

        <div class="col-xs-12 col-sm-8 col-md-6 col-sm-offset-2 col-md-offset-3">
            <form role="form" method="post" action="" autocomplete="off">
                <h2>Please Select your schedule</h2>
                <hr>


                <div class="form-group">
                    <input type="checkbox" name="Saturdamorning" id="Satmor" class="form-control input-lg" placeholder="User Name" value="yes" tabindex="1">Saturday Morning <br>
                </div>
                <div class="form-group">
                    <input type="checkbox" name="Saturdayafternoon" id="Sataft" class="form-control input-lg" placeholder="S" value="yes" tabindex="2">Saturday Afternoon <br>
                </div>
                <div class="form-group">
                    <input type="checkbox" name="Sundaymorning" id="Sunmor" class="form-control input-lg" placeholder="S" value="yes" tabindex="3">Sunday afternoon <br>
                </div>
                <div class="form-group">
                    <input type="checkbox" name="Sundayafternoon" id="Sataft" class="form-control input-lg" placeholder="S" value="yes" tabindex="4">Sunday Morning <br>
                </div>
                <div class="form-group">
                    <input type="checkbox" name="weekday" id="email" class="form-control input-lg" placeholder="S" value="yes" tabindex="5">weekday <br>
                </div>


                <div class="row">
                    <div class="col-xs-6 col-md-6"><input type="submit" name="submit" value="Register" class="btn btn-primary btn-block btn-lg" tabindex="6"></div>
                </div>
            </form>
        </div>
    </div>

</div>

</body>
</html>

When I run my code all it works no errors but I when I check the table all I get is blank rows. The code adds a new sets of rows just doesn't add the data to them. I am trying to add either Yes, or to keep it blank if they do not select it. Any help would be great thanks.

**A:** You didn't do anything with the binds in your values. RTM http://php.net/pdo.prepared-statements and didn't use your POST arrays and possible typo in `Saturdamorning` — Funk Forty Niner, Jan 16 '16 at 23:45
Now, someone's bound to put in an answer, so just wait for it. It won't be me. Not below anyway; I've answered it here in comments. You now know what to go for. — Funk Forty Niner, Jan 16 '16 at 23:47

score 1 · Accepted Answer · answered Jan 17 '16 at 00:00

As pointed out by Fred -ii-, you've not bound anything to your statement. Here's you code using a prepared statement. I've also commented the code as well to explain my position

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

    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "jesuitschedule";

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        // You have a sql statement, but attempting to insert non-existant values. So you'll either
        // wind up with an error, those values given in the statement inserted into the table,
        // or just empty values.
        //$sql = 'INSERT INTO schedule (Saturdaymorning, Saturdayafternoon, Sundaymorning, Sundayafternoon, weekday) VALUES (:Saturdaymorning, :Saturdayafternoon, :Sundaymorning, :Sundayafternoon, :weekday)';

        // Create a prepared statement, let's you easily bind parameters
        $stmt = $con->prepare(
            'INSERT INTO schedule (
                Saturdaymorning, Saturdayafternoon, Sundaymorning, Sundayafternoon, weekday
                ) VALUES (
                :Saturdaymorning, :Saturdayafternoon, :Sundaymorning, :Sundayafternoon, :weekday
                )';
        );

        // use exec() because no results are returned
        //$conn->exec($sql); // You're executing a statement with no bound parameters

        // You can use bindParam, but I find this method a tad easier
        // Take the stmt created above, and bind the values to the parameters given
        // in the statement, BUT, also execute. :)
        $stmt->execute(array(
            ':Saturdaymorning' => 'value',
            ':Saturdayafternoon' => 'value',
            ':Sundaymorning' => 'value',
            ':Sundayafternoon' => 'value',
            ':weekday' => 'value'
        ));
        echo "New record created successfully";
        }
    catch(PDOException $e)
        {
        echo $sql . "<br>" . $e->getMessage();
        }

    $conn = null;
}

If you'd like more info on this, take a look at the PDO page from the PHP site, which is where I pulled your fix: PHP: PDO - Manual

okay thank you but it just imports "value" into the table. How would I get it to import if they selected a options. so if they only selected Saturdaymorning in the table saturdaymornings colum would have a "yes" in it — MathMXC, Jan 17 '16 at 00:41
You have to replace value with the value you want entered into the table — Pazuzu156, Jan 17 '16 at 00:46

How to add variables into a mysql table from php

1 Answers1