Inserting into mysql database with php

Question

I imagine this is a simple issue, I simply cannot find out where or why. (hope this isn't a duplicate)..

My intent is to grab the info from an input and from a textarea and insert it into my database into the proper table that already exists "journals". However after hitting submit and without receiving any errors there is nothing added to the database... thoughts?

here is my "view" (post.php):

<fieldset>
    <form method="post" action="push.php">
        <input type="text" name="datetitle" /><br />
        <textarea name="journalcontent"></textarea><br />
        <input type="submit" />
    </form>
    <?php echo $datetitle ?>
    <p><?php $output ?></p>
</fieldset>

here is my "index" (push.php) with obvious parts omitted:

<?php

$dsn = '*';
$username = '*';
$password = '*';

include "model.php";

try {
    $db = new PDO($dsn, $username, $password);
} catch (PDOException $exc) {

    echo 'connection failed';
    exit;
}

echo 'goodzo';

$datetitle = $_POST['datetitle'];
$journalcontent = $_POST['journalcontent'];

if (!empty($datetitle)) {
    $output = add_entry($datetitle, $journalcontent);
} else {
    $output = "empty";
}

include "post.php";

?>

and lastly my model.php:

<?php
function add_entry($datetitle, $journalcontent) {
    global $db;
    $query = 'INSERT INTO journals
                (entry_date, contents)
              VALUES
                 ($datetitle, $journalcontent)';
    try {
        $statement = $db->prepare($query);
        $statement->execute();
        $statement->closeCursor();
    } catch (PDOException $e) {
        $error_message = $e->getMessage();
        display_db_error($error_message);
    }
}
?>

Doesnt the model.php include have to come after the PDO connection? Otherwise $db refers to nothing.... — Kylie, Jul 06 '13 at 23:26
@KyleK no, as it's only referenced when the function is called. *php* — 19greg96, Jul 06 '13 at 23:27
[You should really fix that nasty SQL injection hole in your application.](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) — PeeHaa, Jul 06 '13 at 23:33

James Holderness · Accepted Answer · 2013-07-06T23:49:28.280

5

When you use a single quote, it doesn't expand the variables in the string. Also, your parameters need to be in quotes if they're not integers. So the query assignment should look like this:

$query = "INSERT INTO journals
            (entry_date, contents)
          VALUES
             ('$datetitle', '$journalcontent')";

That said, you should really be using bind parameters to pass the values to the query. Something like this:

$query = 'INSERT INTO journals
            (entry_date, contents)
          VALUES
             (?, ?)';

$statement = $db->prepare($query);
$statement->bindParam(0, $datetitle, PDO::PARAM_STR);
$statement->bindParam(1, $journalcontent, PDO::PARAM_STR);
$statement->execute();

edited Jul 06 '13 at 23:49

answered Jul 06 '13 at 23:25

James Holderness

22,721
2
40
52

I've made the changes yet still not seeing any data being inserted into the database. Would that suggest a problem with my table? (I am successfully connecting to the DB for sure) – captainrad Jul 06 '13 at 23:34
The table I am inserting to does have an additional column being the primary key which is an auto-incrementing 'id'. Could this be an issue? – captainrad Jul 06 '13 at 23:44
1

You shouldn't have to insert anything into an auto incrementing column. Also, if you're are still using your original code (my first example with the updated quotes), I've noticed another problem with that - the parameters need to be quoted too. – James Holderness Jul 06 '13 at 23:48

score 0 · Answer 2 · answered Jul 06 '13 at 23:28

You should turn PDO's error reporting on first of all; I would use

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Unless $datetitle and $journalcontent are both integers, the query will fail due to an SQL syntax error and a lack of string quoting. You should parameterize the query to avoid this problem as well as possible injection.

$query = <<<SQL
    INSERT INTO journals
        (entry_date, contents)
    VALUES
        (?, ?)
SQL;
$statement = $db->prepare($query);
$statement->execute(array($datetitle, $journalcontent));

Inserting into mysql database with php

2 Answers2