Modified PDO & lastInsertId()

Question

It's just a new form of PDO that I don't fully understand. I know problems similar to this one have been solved on this site. But I’ve been attracted to this new (new to me) class-based system of PDO. It's sleek & concise. I figured everything out, all dynamic user-data INSERTS into the database just fine. However, I can’t figure out how to include lastInsertId() using this particular style. The user doesn’t input the post id & I can’t use GET request, which is how I normally get the post id.

What I have below, after many failed attempts, is the best I can do. Obviously it doesn’t work. Any corrections to my code would be much appreciated.

Here is the database class:

functions.php

class DB{

    private static function connect(){
        $pdo = new PDO('mysql:host=localhost;dbname=poetionpics;charset=utf8', 'root', '');
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $pdo;
    }
    public static function query($query, $params = array()){
        $stmt = self::connect()->prepare($query);
        $stmt->execute($params);

        if(explode(' ', $query)[0] == 'SELECT'){
        $data = $stmt->fetchALL();
        return $data;
        }
    }
    public function lastInsertId(){

        $pdo = new PDO('mysql:host=localhost;dbname=poetionpics;charset=utf8', 'root', '');
    $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    return $pdo->lastInsertId();
        }


}

Here is my INSERT code:

action.php

for($count = 0; $count < count($_POST['hidden_post_title']); $count++){

$post_title = (isset($_POST['hidden_post_title'][$count])) ? strip_tags($_POST['hidden_post_title'][$count]) : NULL;
$post_desc = (isset($_POST['hidden_post_desc'][$count])) ? strip_tags($_POST['hidden_post_desc'][$count]) : NULL;
$newvidurl = (isset($_POST['hidden_vid_url'][$count])) ? strip_tags($_POST['hidden_vid_url'][$count]) : NULL;
$url_1 = (isset($_POST['url1_hidden_id'][$count])) ? strip_tags($_POST['url1_hidden_id'][$count]) : NULL;
$url_2 = (isset($_POST['url2_hidden_id'][$count])) ? strip_tags($_POST['url2_hidden_id'][$count]) : NULL;


DB::query('INSERT INTO cloudbook_posts VALUES (\'\', :post_title,  :post_desc)',
array(':post_title'=>$post_title, ':post_desc'=>$post_desc));

//This is possibly problematic code or wrong location....

$postid = DB::lastInsertId('SELECT id FROM cloudbook_posts WHERE  id=:id',
array(':id'=>$_POST['id']))[0]['id'];

//End problematic code

DB::query('INSERT INTO vid_info VALUES (\'\', :newvidurl, :postid)',
array(':newvidurl'=>$newvidurl, ':postid'=>$postid));

DB::query('INSERT INTO url_1 VALUES (\'\', :url_1, :postid)',
array(':url_1'=>$url_1, ':postid'=>$post_id));
}

echo str_replace(array('hidden_post_title', 'hidden_post_desc',    'url1_hidden_id', 'url2_hidden_id', '{', '}', '"', ',', ':'), '',
htmlspecialchars(json_encode($result), ENT_NOQUOTES));

?>

What I’m getting in the database is this:

vid_info table
id  |     newvidurl    | postid
69  |   some user data | 0

What I want is:

vid_info table
id |       newvidurl    | postid
69 |   some user data   | $postid (see action.php for variable value)

Answer 1

This is indeed an interesting case.

The lastInsertId() issue is a direct consequence of the improper design (and partially a cargo cult copy-pasted code).

This "new form of PDO" is a just a display of common mistakes (so common that I even wrote an article about it). And one of such problems is:

You have to understand that each PDO instance creates a distinct connection to DB server. Thus, you should never ever opening and closing a new connection in each function. Because it will considerably slow down your PHP and won't let you utilize some DB features that can be used only within the same connection - i.e. transactions or getting the insert id.

So now you can tell that the problem is coming from the fact that a new connection is created in the lastInsertId() method. And the solution is to always keep the same connection.

There are two ways to fix the issue: an easier for the moment one which will make things harder in the future, or one which is a bit harder to implement but that make your code less coupled and easier to maintain. Both explained in my other article on making such "sleek & concise class-based system of PDO" (but based on a lot of experience and questions seen here on Stack Overflow).

The most proper solution would be to get of that static stuff as it makes hard to maintain code. So the best solution would be to create a regular class and then create a single instance that have to be passed around all your code. In this case all calls to $this->dbh will make sense and point to the same PDO instance. In this case you will have to change the DB:query() notation to $db->query().

However, the code in my article uses the different approach of extending PDO. Your approach is better, so let's rewrite your code to a regular class

class DB{

    public function __construct()
    {
        $host = '127.0.0.1';
        $db   = 'poetionpics';
        $user = 'root';
        $pass = '';
        $charset = 'utf8mb4';

        $options = [
            \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
            \PDO::ATTR_EMULATE_PREPARES   => false,
        ];
        $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
        try {
             $pdo = new \PDO($dsn, $user, $pass, $options);
        } catch (\PDOException $e) {
             throw new \PDOException($e->getMessage(), (int)$e->getCode());
        }
    }
    public function query($query, $params = array())
    {
        $stmt = $this->pdo->prepare($query);
        $stmt->execute($params);
        return $stmt;
    }
    public function lastInsertId()
    {
        return $this->pdo->lastInsertId();
    }
}

But remember, as you are not extending PDO, you must replicate all PDO methods in your class.

In case at the time the idea to give up such a sleek approach is unbearable to you, then you could use the static method, but one which keeps a single PDO instance for you. There are again two ways both explained down the article linked above.

After having a proper DB class we can rewrite your queries

$db = new DB();

$sql = 'INSERT INTO cloudbook_posts VALUES (null, :post_title,  :post_desc)';
$db->query($sql, ['post_title'=>$post_title, 'post_desc'=>$post_desc]);

$postid = $db->lastInsertId();

$sql = 'INSERT INTO vid_info VALUES (null, :newvidurl, :postid)';
$db->query($sql, ['newvidurl'=>$newvidurl, 'postid'=>$postid]);