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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
<!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> |
Here’s what makes the back end tick:
dbupdate.php (where the real action is)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
<?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(); ?> |
Login.php (Database credentials that I have to swap to fake values so nobody turns my site into a zombie minion)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<?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!)
1 2 3 4 5 6 7 8 |
<?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); ?> |