Coinmarket API

I built a thing which stores and displays digital currency market data

Check out the live feed at https://designerofstuff.com/coinmk

Grab the repo and mess around with it here: https://github.com/haa-gg/Coin-Market-API-Example

More specifically, the goal is to create a script which talks to the Coin Market API (https://api.coinmarketcap.com/v1/ticker/), gets the relevant data for all 1000-something digital currencies out in the wild and puts the current values into a SQL database that I can refrence later. The live feed was more of a bi-product that I got attached to.

The heavy lifting of this project was to set up a database, write a SQL command to input the relevant data from the API into the database, then set the command to run once a day by means of a cron job.

Here’s the code you need to make the live feed section work, all neat n’ tidy

<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
  <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>Coin Data</title>
  <meta name="author" content="name">
  <meta name="description" content="description here">
  <meta name="keywords" content="keywords,here">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/foundation/6.4.3/css/foundation.css" />
<link rel="stylesheet" href="coin-style.css">
  </head>
  <body>

  <div class="grid-container">

  <h1>Big list of digital currencies</h1>

  </div>



  <?php
//Set variable equal to the contents of this remote url (in your face, cross origin policy!) 
$geocodingAPI = file_get_contents('https://api.coinmarketcap.com/v1/ticker/?limit=2000');

//Throw the JSON data into a more PHP friendly array
$geocodingAPI = json_decode($geocodingAPI, true);

//Troubleshooting junk
//echo $geocodingAPI[1]['id'];
//echo count($geocodingAPI);
//echo '<pre>'; print_r($geocodingAPI); echo '</pre>';


   ?>

   <?php for( $i = 0 ; $i < count($geocodingAPI); $i++) { ?>
  
  <div class="grid-container">
   
    <table border="1">
        <tr>
            <td>ID:</td>
            <td id="id"><?php echo $geocodingAPI[$i]['id']; ?></td>
        </tr>
        <tr>
            <td>Name:</td>
            <td id="name"><?php echo $geocodingAPI[$i]['name']; ?></td>
        </tr>
        <tr>
            <td>Symbol:</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['symbol']; ?></td>
        </tr>
         <tr>
            <td>Rank:</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['rank']; ?></td>
        </tr>
         <tr>
            <td>Price (USD):</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['price_usd']; ?></td>
        </tr>
         <tr>
            <td>Price (BTC):</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['price_btc']; ?></td>
        </tr>
         <tr>
            <td>24 Hour Volume USD:</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['24h_volume_usd']; ?></td>
        </tr>
         <tr>
            <td>Market Cap USD:</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['market_cap_usd']; ?></td>
        </tr>
         <tr>
            <td>Available Supply:</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['available_supply']; ?></td>
        </tr>
         <tr>
            <td>Total Supply:</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['total_supply']; ?></td>
        </tr>
         <tr>
            <td>Percentage Change 1h:</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['percent_change_1h']; ?></td>
        </tr>
         <tr>
            <td>Percentage Change 24h:</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['percent_change_24h']; ?></td>
        </tr>
         <tr>
            <td>Percentage Change 7d:</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['percent_change_7d']; ?></td>
        </tr>
         <tr>
            <td>Last Updated:</td>
            <td id="symbol"><?php echo $geocodingAPI[$i]['last_updated']; ?></td>
        </tr>
        
    </table>

    </div>



  <?php } ?>

  </body>
</html>

100

101

102

103

104

105

106

107

108

109

110

<!DOCTYPE html>

<html>

<head>

</head>

<body>

<h1>Big list of digital currencies</h1>

</div>

<?php

//Set variable equal to the contents of this remote url (in your face, cross origin policy!)

$geocodingAPI = file_get_contents('https://api.coinmarketcap.com/v1/ticker/?limit=2000');

//Throw the JSON data into a more PHP friendly array

$geocodingAPI = json_decode($geocodingAPI, true);

//Troubleshooting junk

//echo $geocodingAPI[1]['id'];

//echo count($geocodingAPI);

//echo '<pre>'; print_r($geocodingAPI); echo '</pre>';

<?php for( $i = 0 ; $i < count($geocodingAPI); $i++) { ?>

<tr>

</tr>

<tr>

</tr>

<tr>

<td>Symbol:</td>

</tr>

<tr>

</tr>

<tr>

<td>Price (USD):</td>

</tr>

<tr>

<td>Price (BTC):</td>

</tr>

<tr>

<td>24 Hour Volume USD:</td>

</tr>

<tr>

<td>Market Cap USD:</td>

</tr>

<tr>

<td>Available Supply:</td>

</tr>

<tr>

<td>Total Supply:</td>

</tr>

<tr>

<td>Percentage Change 1h:</td>

</tr>

<tr>

<td>Percentage Change 24h:</td>

</tr>

<tr>

<td>Percentage Change 7d:</td>

</tr>

<tr>

<td>Last Updated:</td>

</tr>

</table>

</div>

<?php } ?>

</body>

</html>

Here’s what makes the back end tick:

dbupdate.php (where the real action is)

<?php 

//Get database credentials
include 'login.php';

//Get raw API (JSON) data and swap it to array
include 'apicall.php';

// sql to create table
//The $name stuff is to generate the new table name in our database
$name = date ('d-m-Y');
$name = "cd-".$name;
//Defining the properties of our tablew
$sql = "CREATE TABLE `".$name."` (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
id_name VARCHAR(30) NOT NULL,
name VARCHAR(30) NOT NULL,
symbol VARCHAR(10) NOT NULL,
rank INT(30) NOT NULL,
price_usd INT(30),
price_btc INT(30),
vol_usd INT(30),
mkt_cap INT(30),
avaliable_supply INT(30),
total_supply INT(30),
pcnt_1h DECIMAL(10,3),
pcnt_24h DECIMAL(10,3),
pcnt_7d DECIMAL(10,3),
update_time INT(30),
reg_date TIMESTAMP
)";

//Only make the new rows if the program does not have any issue creating the table (had an issue where it would add the same data over and over again if you refreshed the page)
if ($conn->query($sql) === TRUE) {
  echo "Table ".$name." created successfully, break out the champagne!";

  //This loop checks how many entries are in the API array then tells the loop to run that many times so it inserts every entry from the array into our fresh table
for( $i = 0 ; $i < count($geocodingAPI); $i++) {

  $sql = "INSERT INTO `".$name."` (id_name, name, symbol, rank, price_usd, price_btc, vol_usd, mkt_cap, avaliable_supply, total_supply, pcnt_1h, pcnt_24h, pcnt_7d, update_time)
  VALUES ('".$geocodingAPI[$i]['id']."',
  '".$geocodingAPI[$i]['name']."',
  '".$geocodingAPI[$i]['symbol']."',
  '".$geocodingAPI[$i]['rank']."',
  '".$geocodingAPI[$i]['price_usd']."',
  '".$geocodingAPI[$i]['price_btc']."',
  '".$geocodingAPI[$i]['24h_volume_usd']."',
  '".$geocodingAPI[$i]['market_cap_usd']."',
  '".$geocodingAPI[$i]['available_supply']."',
  '".$geocodingAPI[$i]['total_supply']."',
  '".$geocodingAPI[$i]['percent_change_1h']."',
  '".$geocodingAPI[$i]['percent_change_24h']."',
  '".$geocodingAPI[$i]['percent_change_7d']."',
  '".$geocodingAPI[$i]['last_updated']."' )";


if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}
}


} else {
  echo "Error creating table: " . $conn->error;
}



$conn->close();

?>

<?php

//Get database credentials

include 'login.php';

//Get raw API (JSON) data and swap it to array

include 'apicall.php';

// sql to create table

//The $name stuff is to generate the new table name in our database

$name = date ('d-m-Y');

$name = "cd-".$name;

//Defining the properties of our tablew

$sql = "CREATE TABLE `".$name."` (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

id_name VARCHAR(30) NOT NULL,

name VARCHAR(30) NOT NULL,

symbol VARCHAR(10) NOT NULL,

rank INT(30) NOT NULL,

price_usd INT(30),

price_btc INT(30),

vol_usd INT(30),

mkt_cap INT(30),

avaliable_supply INT(30),

total_supply INT(30),

pcnt_1h DECIMAL(10,3),

pcnt_24h DECIMAL(10,3),

pcnt_7d DECIMAL(10,3),

update_time INT(30),

reg_date TIMESTAMP

)";

//Only make the new rows if the program does not have any issue creating the table (had an issue where it would add the same data over and over again if you refreshed the page)

if ($conn->query($sql) === TRUE) {

echo "Table ".$name." created successfully, break out the champagne!";

//This loop checks how many entries are in the API array then tells the loop to run that many times so it inserts every entry from the array into our fresh table

for( $i = 0 ; $i < count($geocodingAPI); $i++) {

$sql = "INSERT INTO `".$name."` (id_name, name, symbol, rank, price_usd, price_btc, vol_usd, mkt_cap, avaliable_supply, total_supply, pcnt_1h, pcnt_24h, pcnt_7d, update_time)

VALUES ('".$geocodingAPI[$i]['id']."',

'".$geocodingAPI[$i]['name']."',

'".$geocodingAPI[$i]['symbol']."',

'".$geocodingAPI[$i]['rank']."',

'".$geocodingAPI[$i]['price_usd']."',

'".$geocodingAPI[$i]['price_btc']."',

'".$geocodingAPI[$i]['24h_volume_usd']."',

'".$geocodingAPI[$i]['market_cap_usd']."',

'".$geocodingAPI[$i]['available_supply']."',

'".$geocodingAPI[$i]['total_supply']."',

'".$geocodingAPI[$i]['percent_change_1h']."',

'".$geocodingAPI[$i]['percent_change_24h']."',

'".$geocodingAPI[$i]['percent_change_7d']."',

'".$geocodingAPI[$i]['last_updated']."' )";

if ($conn->query($sql) === TRUE) {

echo "New record created successfully";

} else {

echo "Error: " . $sql . "<br>" . $conn->error;

}

} else {

echo "Error creating table: " . $conn->error;

}

$conn->close();

 <?php 
 
//Obviously fake credentials
$servername = "localhost";
$username = "CatWithAJetpack";
$password = "TheVoiceOfMorganFreeman";
//This is the actual db name, didn't see any harm in posting that
$dbname = "coinmk";

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


?>

<?php

//Obviously fake credentials

$servername = "localhost";

$username = "CatWithAJetpack";

$password = "TheVoiceOfMorganFreeman";

//This is the actual db name, didn't see any harm in posting that

$dbname = "coinmk";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

Apicall.php (Super tiny file to get data from the friendliest API I’ve ever met, no need for credentials to get data!)

 <?php
//Set variable equal to the contents of this remote url (in your face, cross origin policy!) 
$geocodingAPI = file_get_contents('https://api.coinmarketcap.com/v1/ticker/?limit=2000');

//Throw the JSON data into a more PHP friendly array
$geocodingAPI = json_decode($geocodingAPI, true);

   ?>

<?php

//Set variable equal to the contents of this remote url (in your face, cross origin policy!)

$geocodingAPI = file_get_contents('https://api.coinmarketcap.com/v1/ticker/?limit=2000');

//Throw the JSON data into a more PHP friendly array

$geocodingAPI = json_decode($geocodingAPI, true);