Auto increment ID not being correclty assigned in SQL database by PHP

Question

I am confused. Something isn't working in my new CMS I am designing. I am following this tutorial but am changing some things (including using SQLite not MySQL) and adding more functionality. http://www.elated.com/articles/cms-in-an-afternoon-php-mysql/

Anyway, nearly everything works now but it just will not save new posts. The edit post functionality does work though.

Here is the base form (the same form that works fine to edit a post):

<h3>Create a blog post</h3>
<form method="POST" action="index.php?action=<?php echo $posts['formAction']; ?>">
     <input type="hidden" id="id" name="id" value="<?php echo $posts['post'] -> id; ?>" />
     <p>Title: <input id="title" name="title" type="text" placeholder="Enter the post title" required autofocus maxlength="255" value="<?php echo $posts['post'] -> title; ?>"/></p>
     <p>Category: <input id="category" name="category" type="text" placeholder="Enter the post category" required maxlength="255" value="<?php echo $posts['post'] -> category; ?>"/></p>
     <p>Tags: <input id="tags" name="tags" type="text" placeholder="Enter some tags for the post" required value="<?php echo $posts['post'] -> tags; ?>"/></p>
     <p>Summary:</p><textarea id="summary" name="summary" cols="100" rows="5" placeholder="Enter a short summary of the post's content" required><?php echo $posts['post'] -> summary; ?></textarea>
     <p>Content:</p><textarea id="body" name="body" cols="100" rows="30" placeholder="Enter the main content of the post" required><?php echo $posts['post'] -> body; ?></textarea>
     <p>Publication date: </p><input type="date" id="pubDate" name="pubDate" placeholder="DD-MM-YYYY" required maxlength="10" value="<?php echo $posts['post'] -> pubDate ? date('Y-m-d', $posts['post'] -> pubDate) : ''; ?>" />
     <br />
     <input id="saveChanges" name="saveChanges" type="submit" value="Create Post"/>
</form>

This ends up posting to the following function in index.php:

function newPost()
{
    $posts = array();
    $posts['formAction'] = "newPost";

    if (isset($_POST['saveChanges']))
    {
        $post = new Post;
        $post -> storePostValues($_POST);
        $post -> insertPost();
        header("Location: index.php?status=changesSaved");
    }
    elseif (isset($_POST['cancel']))
    {
        header('Location: index.php');
    }
    else
    {
        $posts['post'] = new Post;
        require('editPost.php');
    }
}

And finally the relevant code in the Post() class:

public function __construct($data = array()) {
    if (isset($data['id'])) $this -> id = (int) $data['id'];
    if (isset($data['title'])) $this -> title = $data['title'];
    if (isset($data['pubDate'])) $this -> pubDate = (int) $data['pubDate'];
    if (isset($data['reviseDate'])) $this -> reviseDate = (int) $data['reviseDate'];
    if (isset($data['category'])) $this -> category = $data['category'];
    if (isset($data['tags'])) $this -> tags = $data['tags'];
    if (isset($data['summary'])) $this -> summary = $data['summary'];
    if (isset($data['body'])) $this -> body = $data['body'];
    if (isset($data['views'])) $this -> views = (int) $data['views'];
    if (isset($data['likes'])) $this -> likes = (int) $data['likes'];
    if (isset($data['dislikes'])) $this -> dislikes = (int) $data['dislikes'];
}

        //Sets object properties from form POST values
public function storePostValues($params) {
    $this -> __construct($params);

    if (isset($params['pubDate'])) {
        $pubDate = explode('-', $params['pubDate']);
        if (count($pubDate) == 3) {
            list ($d, $m, $y) = $pubDate;
            $this -> pubDate = mktime(0, 0, 0, $m, $d, $y);
        }
    }
    if (isset($params['reviseDate'])) {
        $reviseDate = explode('-', $params['reviseDate']);
        if (count($reviseDate) == 3) {
            list ($d, $m, $y) = $reviseDate;
            $this -> reviseDate = mktime(0, 0, 0, $m, $d, $y);
        }
    }
}

public function insertPost() {
    if (!is_null($this -> id)) trigger_error("Post::insertPost(): Post ID already assigned.", E_USER_ERROR);
    $dblocat = "sqlite:" . $_SERVER['DOCUMENT_ROOT'] . "/cgi-bin/blog.db";
    $dbcon = new PDO($dblocat);
    $dbcon->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $sqlstr = "INSERT INTO posts(title, pubDate, reviseDate, category, tags, summary, body, views, likes, dislikes) VALUES(:title, FROM_UNIXTIME(:pubDate), FROM_UNIXTIME(:reviseDate), :category, :tags, :summary, :body, :views, :likes, :dislikes)";
    $preparedstr = $dbcon -> prepare($sqlstr);
    $preparedstr -> bindValue(":title", $this -> title, PDO::PARAM_STR);
    $preparedstr -> bindValue(":pubDate", $this -> pubDate, PDO::PARAM_INT);
    $preparedstr -> bindValue(":reviseDate", $this -> reviseDate, PDO::PARAM_INT);
    $preparedstr -> bindValue(":category", $this -> category, PDO::PARAM_STR);
    $preparedstr -> bindValue(":tags", $this -> tags, PDO::PARAM_STR);
    $preparedstr -> bindValue(":summary", $this -> summary, PDO::PARAM_STR);
    $preparedstr -> bindValue(":body", $this -> body, PDO::PARAM_STR);
    $preparedstr -> bindValue(":views", 0, PDO::PARAM_INT);
    $preparedstr -> bindValue(":likes", 0, PDO::PARAM_INT);
    $preparedstr -> bindValue(":dislikes", 0, PDO::PARAM_INT);
    $preparedstr -> execute();
    $this -> id = $dbcon -> lastInsertId();
    $dbcon = null;
}

So what happens? Well, every time I save a post that error in the insertPost() function is triggered saying that the post ID already exists... but it is set to auto increment in the database so I'm not quite sure what is going on... I'm relatively new to PHP and have spent hours now trying to see why this isn't working.

Any advice would be appreciated and I can send more code if required. Again, I am basing it on this tutorial but I have modified it and now use SQLite. http://www.elated.com/articles/cms-in-an-afternoon-php-mysql/

Thanks in advance, Ilmiont

Answer 1

You just have to remove this line to make it work (in your function __construct())

if (isset($data['id'])) $this -> id = (int) $data['id'];

The id is set to "auto increment", so it means you should not provide any id (database will choose one for you).

Edit:

Actually you should keep this line right here. As you noticed, removing it will prevent you to get the post id.

Here is my opinion: When you create a new post, you should have a few lines like that :

// User has posted the article edit form: save the new article
$post = new Post;
$post->storeFormValues( $_POST );
$post->insert();

right ? Now what about adding this unset statement, right before "insert" :

// User has posted the article edit form: save the new article
$post = new Post;
$post->storeFormValues( $_POST );
unset($post->id);
$post->insert();

(you may need to adapt variable name acording to your code)