Integrate YouTube API Data V3 In PHP and MySQL

PHP
#1

Good day everyone

I was trying to integrate YouTube API Data V3 In PHP and MySQL when i encountered the
following error below.

Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1292
Incorrect datetime value: ‘2022-05-25T15:39:37Z’ for column ‘published_at’ at row 1 in
C:\laragon\www\phpyoutube\index.php:50 Stack trace: #0 C:\laragon\www\phpyoutube\index.php
(50): PDOStatement->execute() #1 {main} thrown in C:\laragon\www\phpyoutube\index.php on
line 50

See my code below

<?php
$key = "**REDACTED**";
$base_url = "https://www.googleapis.com/youtube/v3/";
$channelid = "UCBVDN0GxaRsmtXbsn11kmeQ";
$maxResult = 10;

$API_URL = $base_url . "search?order=date&part=snippet&channelid=" . $channelid .
    "&maxResults=" . $maxResult . "&key=" . $key;

/*You can make it according to date or without date as below
$API_URL = $base_url . "search?order=date&part=snippet&channelid=" . $channelid .
"&maxResult=" . $maxResult . "&key=" . $key;

Without date
$API_URL = $base_url . "search?part=snippet&channelid=" . $channelid .
"&maxResult=" . $maxResult . "&key=" . $key;
 */
$videos = json_decode(file_get_contents($API_URL));
include "Dbconnect.php";
$db = new Dbconnect();
$conn = $db->connect();
//echo "<pre>";
//print_r($videos);
foreach ($videos->items as $video) {

    $sql = "INSERT INTO videos (id, video_type, video_id, title, thumbnail_url, published_at)
VALUES (NULL, 1, :vid, :title, :turl, :pdate)";

    $stmt = $conn->prepare($sql);
    //$stmt->bindParam(":vtype", 1);
    $stmt->bindParam(":vid", $video->id->videoId);
    $stmt->bindParam(":title", $video->snippet->title);
    $stmt->bindParam(":turl", $video->snippet->thumbnails->high->url);
    $stmt->bindParam(":pdate", $video->snippet->publishedAt);
    $stmt->execute();
}
?>

Kindly help

#2

Can you please show what the structure of your table is by posting the output of SHOW CREATE TABLE videos?

#3

Thank you for your reply below is my table structure

CREATE TABLE videos (
id int(5) NOT NULL,
video_type tinyint(2) NOT NULL,
video_id varchar(20) NOT NULL,
title varchar(250) NOT NULL,
thumbnail_url varchar(100) NOT NULL,
published_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#4

The value from the API is an ISO 8601 format. The T indicates the time part follows and the Z indicates UTC timezone offset. You would need to change the T to a space and remove the Z to produce a MySql datetime format.

Edit: You should prepare the query once, before the start of any looping, use implicate binding, by simply supplying an array of values to the execute() call, then just call the execute([…]) method inside of the loop.

Edit2: You should also leave the id and its null value out of the query.