CODEWITHSUNDEEP
phphtmlmysql
DemCodeLines
DemCodeLines

Reputation: 1920

MySQL query inserts "0" for all column values

I have an HTML form with 5 textboxes. Upon the click of the submit button, the form is submitted to a page called "admin.php"

admin.php

<?php
include($_SERVER['DOCUMENT_ROOT'] . '/inc/init.php');
$reference_key = $_POST['reference_key'];
$success = false;
switch ($reference_key)
{
    case 0: break;
    case 2: {
        $date = $_POST['date'];
        $time = $_POST['time'];
        $gym = $_POST['gym'];
        $home = $_POST['home'];
        $away = $_POST['away'];
        $success = "";
        try {
            newGame($db7, $date, $time, $gym, $home, $away);
            $success="index.php";
        }
        catch (Exception $e)
        {
            $success="/404";
        }
        //header("Location: " . $success);
    } break;
    default: break;
}

function newGame($db, $date, $time, $gym, $home, $away)
{
    $db->addGameToSchedule($date, $time, $gym, $home, $away);
}
?>

When the form is submitted, a reference key is passed. The value of the reference key is "2" and as you can see in the code above for admin.php, a specific set of code is executed when the key is 2. As pointed out in the code, function newGame is executed, which in turn calls the function addGameToSchedule.

addGameToSchedule function:

public function addGameToSchedule($date, $time, $gym, $home, $away) {
    try {
        print "Date: " . $date . "\nTime: " . $time . "\nGym: " . $gym . "\nHome: " . $home . "\nAway: " . $away;
        $sth = $this -> db -> prepare("INSERT INTO schedule (date, time, gym, home, away) VALUES (':date', ':time', ':gym', ':home', ':away')");
        $sth -> execute(array(':date' => $date, ':time' => $time, ':gym' => $gym, ':home' => $home, ':away' => $away));
    } catch (Exception $e) {
        header('Location: /404');
    }
}

The print statement at the top of the function is to check if the values are being passed correctly. When the code is executed, the print statement correctly prints the information I entered. However, when I check the database for the new row of values, I only see "0" for the 5 columns that I am inserting in.

What am I doing wrong? Please let me know if I need to add more information.

Upvotes: 0

Views: 246

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562330

Don't put parameter placeholders inside quotes. Inside quotes, any literal string containing no digits is converted to 0. Just like SELECT 1 + 'abc' returns 1+0 or 1.

Do this instead:

$sth = $this -> db -> prepare("INSERT INTO schedule (date, time, gym, home, away)
    VALUES (:date, :time, :gym, :home, :away)");

Re comment from @redolent:

Try this:

mysql> SELECT ':date' AS value;
+-------+
| value |
+-------+
| :date |
+-------+

The quoted string is a literal, not a parameter placeholder. In that example, it is just a string literal.

If we force it to be interpreted as a number:

mysql> SELECT 0 + ':date' AS value;
+-------+
| value |
+-------+
|     0 |
+-------+
1 row in set, 1 warning (0.04 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: ':date'

If we force it to be interpreted as a date:

mysql> SELECT DATE(':date') AS value;
+-------+
| value |
+-------+
| NULL  |
+-------+
1 row in set, 1 warning (0.35 sec)

Warning (Code 1292): Incorrect datetime value: ':date'

Upvotes: 2

Related Questions