0

I'm working on a small custom CMS for a browser-based game I'm writing mostly as a learning project and am having trouble writing information to my database. I generally know how this works and have it functioning elsewhere on the project, but here specifically it is giving me some trouble. I know that the POSTs and the $data array in general are working properly because I can echo the information from them just fine. But when I try to write to the database using PDO, nothing seems to make it through.

Here is what I have. I also know that my constants are correct, because I had some information in the database, and it was dropped properly according to my resetDatabase function.

To be clear, it is the processUser function that isn't getting the desired information to the database:

    function resetDatabase() {

        $conn = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD);
        $sql = 'DROP TABLE IF EXISTS user; 
                     CREATE TABLE IF NOT EXISTS user (
                     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                     email VARCHAR(90) UNIQUE,
                     username VARCHAR(20),
                     firstname VARCHAR(40),
                     lastname VARCHAR(40),
                     birthday DATE,
                     password CHAR(128),
                     registerdate TIMESTAMP
                     )';
        $st = $conn->prepare($sql);
        $st->execute();
        $conn = null;

    }

    resetDatabase();


    $data = array(
            'firstName'    => htmlspecialchars($_POST['firstName']),
            'lastName'     => htmlspecialchars($_POST['lastName']),
            'username'     => htmlspecialchars($_POST['username']),
            'email'        => $_POST['email'],
            'birthday'     => strtotime($_POST['birthday']),
            'registerDate' => strtotime('now'),
            'password'     => hash('sha512', $_POST['password'])
            );

    function processUser($data = array()) {

        $conn = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD);
        $sql = 'INSERT INTO user (email, username, firstname, lastname, birthday,
                registerdate, password) VALUES (:email, :username, :firstName,
                :lastName, :birthday, :registerDate, :password)';
        $st = $conn->prepare($sql);
        $st->bindValue(':email', $data['email'], PDO::PARAM_STR);
        $st->bindValue(':username', $data['username'], PDO::PARAM_STR);
        $st->bindValue(':firstName', $data['firstName'], PDO::PARAM_STR);
        $st->bindValue(':lastName', $data['lastName'], PDO::PARAM_STR);
        $st->bindValue(':birthday', $data['birthday'], PDO::PARAM_INT);
        $st->bindValue(':registerDate', $data['registerDate'], PDO::PARAM_INT);
        $st->bindValue(':password', $data['password'], PDO::PARAM_STR);
        $st->execute();
        $conn = null;
    }


    processUser($data);

Anyone know where my code is tripping up? Thank you for the help.

rrr45
  • 47
  • 5
  • Is that a plain PHP file? I mean no POO or something else? – ReynierPM Sep 28 '14 at 01:15
  • This is a plain php file (that is not the complete file shown, though). – rrr45 Sep 28 '14 at 01:16
  • Where `DB_DNS`, `DB_USERNAME` and `DB_PASSWORD` comes from? Why you set `$con = null` all the time? Why you not write a simple class? – ReynierPM Sep 28 '14 at 01:17
  • Is error reporting turned on? `error_reporting(-1); ini_set('display_errors', 'On');` – wavemode Sep 28 '14 at 01:17
  • The DB constants are set in a config file and are working properly for the first function. Error reporting is also turned on in the same config file with `ini_set('display_errors', true);`, but I'm just getting a blank page (I do also see test echos when I tell the function to also echo a $data item to see if everything else seems to be set right) – rrr45 Sep 28 '14 at 01:20
  • You should use something like `try { $st->execute(); } catch(PDOException $ex) { var_dump($ex->getMessage()); }` to catch errors – ReynierPM Sep 28 '14 at 01:20
  • Hmm, still not showing anything wrong. – rrr45 Sep 28 '14 at 01:30
  • 1
    After `$st->execute();` in your function, try `echo $st->errorInfo();` to show any errors from the last query. The exception will be thrown if you fail to connect, but not if there's an sql error once you're connected. – Jeremiah Winsley Sep 28 '14 at 01:33
  • Thanks Jeremiah, that narrowed it down, but I can't figure out quite what the exact problem is. Something with the birthday value. When I var_dump that errorInfo, I get this message: array(3) { [0]=> string(5) "22007" [1]=> int(1292) [2]=> string(64) "Incorrect date value: '936853200' for column 'birthday' at row 1" } – rrr45 Sep 28 '14 at 01:42
  • That means your date is incorrectly formatted to be stored in a mysql `DATE` field. Take a look at http://stackoverflow.com/a/2215359/3794472 – Jeremiah Winsley Sep 28 '14 at 01:44
  • Bah, I see now. What a silly mistake. Thanks for your help. – rrr45 Sep 28 '14 at 01:49

0 Answers0