Using YouTube Data API v3 to get channel data and store it in a database

Once the script that would get data from a certain YouTube channel worked (more details here) there were a few modifiations done in order to make it function in a way that would meet the requirement of automated influencers reach updates (details from the IRM project here).

The upgraded php script would make a connection to the database of influencers, get the data required for the API request (YouTube channel id) and update the values of subscribers and lifetime views for the whole database.

Here is how it was done:

dbconnect.php file that holds the required information for the connection.

<?php

//database connection required information
	$hostname="host_address";
	$port="host_port";
	$username="phpMyAdmin username";
	$password="phpMyAdmin password";
	$dbname="Database name";
	$mytable="Table name";

The php script that updates each database entry’s subscribers and lifetime views values.


<?php

include("dbconnect.php");
	
	// YouTube API Key
	$api_key = "registered_api_key_here";

	//create connection 
	$conn = mysqli_connect($hostname, $username, $password, $dbname, $port);
	// check connection
	if (mysqli_connect_errno())
	{
	echo "Failed to connect to MySQL: " . mysqli_connect_error();
	}
	
	//select my database
	mysqli_select_db($conn, $dbname);
	
    // select query for the entry id and the column that holds the channel_id
	$query = "SELECT id, extra_column_6 FROM $mytable";

	  if ($channel_id_result = mysqli_query($conn, $query)) {
	   // array that holds the result set from the query
	  	$resultSet = array();
       
        while ($row = mysqli_fetch_assoc($channel_id_result)) {
			
			$resultSet[] = $row;
        }
	// loop that goes through the array
        foreach ($resultSet as $result){
        		
				//declaring $id as the result from the id column from the table
        		$id = $result['id'];
				//declaring $chann_id as the result from the extra_column_6 from the table
        		$chann_id = $result['extra_column_6'];
				

				//Making the requests using the data stored in the database
        		$subscribers = file_get_contents('https://www.googleapis.com/youtube/v3/channels?part=statistics&id='.$chann_id.'&key='.$api_key);
				$views = file_get_contents('https://www.googleapis.com/youtube/v3/channels?part=statistics&id='.$chann_id.'&key='.$api_key);

				// Decoding json response
				$response = json_decode($subscribers, true );
				$response2 = json_decode($views, true );

				// subscribers and lifetime views count placed in a variable
				$subscribersCount = intval($response['items'][0]['statistics']['subscriberCount']);
				$viewsCount = intval($response2['items'][0]['statistics']['viewCount']);

				// update query for subscribers and lifetime views count
				$update = "UPDATE $mytable SET extra_column_9 = $subscribersCount,
				 		   extra_column_10 = $viewsCount
						   WHERE id = $id";	
						   
				// checking if the updated was successfull		   
				if (mysqli_multi_query($conn, $update)) {
    				echo "."; 
				} else {
    				echo "Error: " . $update . "<br>" . mysqli_error($conn);
				}
        	}
      }
	  // Redirecting the user to the page when the table in the database is updated
	  echo "</br></br>Redirecting..";
	  echo "<script>window.location = 'http://examplepage.com'</script>"; 
    	
?>

Useful Sources:
YouTube Data API v3
JSON
PHP and MySQL
Filesystem Functions
JSON Functions
Variable handling Functions

Share this Post

Leave a Reply

Your email address will not be published. Required fields are marked *