mbriney of Mount Vernon Labs
5/30/2016 - 2:55 AM

Takes a column of image URLs in a MySQL database, runs it against the imagga API to find colors, writes a separate index table and then upda

Takes a column of image URLs in a MySQL database, runs it against the imagga API to find colors, writes a separate index table and then updates each record with a comma delimited foreground and background color list.

colors.php
content_copyfile_download
<?php

$servername = "127.0.0.1";
$username = "root";
$password = "";
$dbname = "mv-dev";
$imagga_key = "";
$imagga_secret = "";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT mediaid,image FROM mv_collections_media where foreground_colors IS NULL LIMIT 5";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {

      $foreground = "";
      $background = "";

      $api_credentials = array(
      'key' => $imagga_key,
      'secret' => $imagga_secret
      );

      $ch = curl_init();

      curl_setopt($ch, CURLOPT_URL, 'https://api.imagga.com/v1/colors?url='.urlencode($row["image"]));
      curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
      curl_setopt($ch, CURLOPT_HEADER, FALSE);
      curl_setopt($ch, CURLOPT_USERPWD, $api_credentials['key'].':'.$api_credentials['secret']);

      $response = curl_exec($ch);
      curl_close($ch);

      $json_response = json_decode($response);

      // Detects if the image exists
      if (isset($json_response->unsuccessful[0]->info)){
        echo $json_response->unsuccessful[0]->info."\n";
        // If the image isn't found
        $sql2 = "UPDATE mv_collections_media SET foreground_colors='error' WHERE mediaid='".$row["mediaid"]."'";
        if ($conn->query($sql2) === TRUE) {} else {}

      } else {

        //If the image is found get the colors
        foreach ($json_response->results[0]->info->foreground_colors as $color){
          $foreground = $foreground.$color->closest_palette_color.",";

          // Add the color to the color table
          $sql2 = "INSERT INTO mv_imagga_colors (color,html,parent) VALUES ('".$color->closest_palette_color."','".$color->closest_palette_color_html_code."','".$color->closest_palette_color_parent."')";
          if ($conn->query($sql2) === TRUE) {} else {}

        }
        foreach ($json_response->results[0]->info->background_colors as $color){
          $background = $background.$color->closest_palette_color.",";

          // Add the color to the color table
          $sql2 = "INSERT INTO mv_imagga_colors (color,html,parent) VALUES ('".$color->closest_palette_color."','".$color->closest_palette_color_html_code."','".$color->closest_palette_color_parent."')";
          if ($conn->query($sql2) === TRUE) {} else {}

        }
        echo rtrim($foreground, ',')."\n";
        echo rtrim($background, ',')."\n";

        // Update the media records with the colors found
        $sql2 = "UPDATE mv_collections_media SET foreground_colors='".rtrim($foreground, ',')."' WHERE mediaid='".$row["mediaid"]."'";
        if ($conn->query($sql2) === TRUE) {} else {}
        $sql2 = "UPDATE mv_collections_media SET background_colors='".rtrim($background, ',')."' WHERE mediaid='".$row["mediaid"]."'";
        if ($conn->query($sql2) === TRUE) {} else {}

      }

    }
} else {
    echo "0 results";
}
$conn->close();