JSON Archives | phpGrid - PHP Datagrid

Clone CoinMarketCap.com Cryptocurrency Price Table with WebSockets

Wed, 26 Jan 2022 18:50:27 +0000

Coinmarketcap.com is without a doubt the most popular cryptocurrency price tracking website. I’ve often wondered if its elegant bitcoin prices table could be reproduced with phpGrid. That is exactly what we are about to do today!

The table includes crypto pricing statistics as well as sparkline chart based on real-time data from the backend server. It’s a proof of concept to show how far phpGrid can be pushed before I have to resort to other tools and frameworks.

Setup

Acquire the full version of phpGrid. The free Lite version is not compatible with this tutorial.

Follow these steps to install:

Extract download file,
Upload the phpGrid folder to web server,
Complete the installation by configuring conf.php file.

For demo purpose, conf.php, set all database values to blank since our project doesn’t require a database.

1
2
3
4
5
6
7

// set all to blank
define(‘PHPGRID_DB_HOSTNAME’, ‘’);
define(‘PHPGRID_DB_USERNAME’, ‘’);
define(‘PHPGRID_DB_PASSWORD’, ‘’);
define(‘PHPGRID_DB_NAME’, ‘’);
define(‘PHPGRID_DB_TYPE’, ‘’);
define(‘PHPGRID_DB_CHARSET’,’’);

Sample JSON

The first step is to obtain a crypto data feed. Though it may be automatically retrieved from places like Binance or FTX.US, I decided to start with a local JSON data source for simplicity’s sake.

JSON sample

We’ll use this top 10 crypto sample to populate our CoinMarketCap clone.

Index.php

Let’s create index.php with the following:

1
2
3
4
5
6
7

require_once("phpGrid/conf.php");

$url = "data/coinmarket_top10.json";
$data = file_get_contents($url);
$json_output = json_decode($data, true);
$dg = new C_DataGrid($json_output, "id", "CoinMarket");
$dg -> display();

$json_output is the sample JSON data source save in data folder. It is passed to C_DataGrid as the first parameter. It should display a very rudimentary grid similar to the following.

JSON as data source is covered in this tutorial.

I’ll confess, this is still a long way from what I’d like to accomplish. Let’s make some changes to the user interface.

Update User Interface

First by adding descriptive column title,

1
2
3
4
5
6

$dg->set_col_title(‘Percentage24Hstrong>’, ‘24h %’);
$dg->set_col_title(‘Percentage7Dstrong>’, ‘7d %’);
$dg->set_col_title(‘MarketCapstrong>’, ‘Market Cap’);
$dg->set_col_title(‘Volume24Hstrong>’, ‘Volume(24h)’);
$dg->set_col_title(‘CirculatingSupplystrong>’, ‘Circulating Supply’);
$dg->set_col_title(‘Last7Daysstrong>’, ‘Last 7 Days’);

Hide non-display columns,

1 2	$dg->set_col_hidden("Coin”); $dg->set_col_hidden("MaxSupply”);

Fix text alignment,

1
2
3
4
5
6

$dg->set_col_align(‘Price’, ‘right’);
$dg->set_col_align(‘Percentage24H’, ‘right’);
$dg->set_col_align(‘Percentage7D’, ‘right’);
$dg->set_col_align(‘MarketCap’, ‘right’);
$dg->set_col_align(‘Volume24H’, ‘right’);
$dg->set_col_align(‘CirculatingSupply’, ‘right’);

Update money format

1
2
3
4

$dg->set_col_currency('Price', '$');
$dg->set_col_currency('MarketCap', '$');
$dg->set_col_currency('Volume24H', '$');
$dg->set_col_currency('CirculatingSupply', '');

Add row color and hover effect

1	$dg -> set_row_color(‘white’, ‘white’, ‘white’);

Fix column sort

1
2
3
4

$dg->set_col_property("Price”, array("sorttype”=>"currency"));
$dg->set_col_property("MarketCap”, array("sorttype”=>"currency"));
$dg->set_col_property("Volume24H”, array("sorttype”=>"currency"));
$dg->set_col_property("CirculatingSupply”, array("sorttype”=>"integer"));

Add responsive width to screen size

1	$dg-> enable_autowidth(true);

Finally some misc. style tweaks

1
2
3

$dg->set_caption(false);
$dg->set_col_property(‘Coin’, array(‘classes’=>‘coin’));
$dg->set_col_property(‘Name’, array(‘classes’=>‘name’));

Already look better! But it’s still not quite what I was looking for.

Adding Cascading Style Sheets (app.css)

I use a Chrome extension “What Font” to find out the font it used on CoinMarketCap. It is a free font from Google Fonts called “Inter”. Here are the embed links:

1
2
3

Then I made a few adjustments with some additional custom styles so I could “perfectly” match the look (almost)

It looked better but still with a few issues:

Missing the coin icons and tick symbols
The prices are static and do not updates automatically like CMC does.

Let’s fix those.

Icons and Tickers

CoinMarketCap has the format: Icon Name Ticker as illustrated below.

I found a free library of all the major crypto icons on http://cryptoicons.co/ and downloaded them to “images/icons” folder.

Next, we need to use phpGrid custom formatter.

phpGrid Custom formatter

This is section is the most crucial. It’s what really makes our cloned table “shine”. Custom formatter in phpGrid is a super powerful and versatile function for manipulating, modifying, or even adding additional information in a single column in grid. You can learn more about it on custom formatter online documentation.

I’m adding custom formatter to the followings:

Name (with icon & ticker)
Price
24h change (%)
7day change (%)
Volume with dollar amount
Circulating supply with % bar

PHP

1
2
3
4
5

$dg->set_col_property('Name', array('formatter'=>'###nameFormatter###')); // must have ###
$dg->set_col_property('Volume24H', array('formatter'=>'###volumeFormatter###')); // must have ###
$dg->set_col_property('CirculatingSupply', array('formatter'=>'###circulatingSupplyFormatter###')); // must have ###
$dg->set_col_property('Percentage24H', array('formatter'=>'###percentageChangeFormatter###')); // must have ###
$dg->set_col_property('Percentage7D', array('formatter'=>'###percentageChangeFormatter###')); // must have ###

Javascript (app.js)

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

It looks pretty good!

Real-Time Data via Binance WebSocket

Last but not least, the data must be updated in real-time, similar to CoinMarketCap or CoinGecko, without requiring the entire page to be refreshed. For this experiment, I want the price to tick in the same way as CMC does.

WebSocket is supported by all major web browsers, with the exception of Opera Mini, according to caniuse.com. I was concerned that it would be a huge undertaking. As a result, I pushed this to the bottom of my to-do list. Fortunately, this was not the case. Binance’s websocket trade stream API made it simple.

1
2
3
4
5
6
7
8
9
10
11

// real time data via Websocket
$(document).ready(function(){
let coins = ['BTC', 'ETH', 'BNB', 'USDT', 'SOL', 'ADA', 'XRP', 'DOT', 'USDC', 'DOGE'];
coins.forEach(function(coin){
var wss = new WebSocket(`wss://stream.binance.com:9443/ws/${coin.toLowerCase()}usdt@trade`);
wss.onmessage = function (event) {
var messageObject = JSON.parse(event.data)
$("#CoinMarket").jqGrid("setCell", coin, "Price" ,(messageObject.p));
}
})
})

From its API, there are tons of great data can be consumed. For our purpose, we will only need messageObject.p, which returns ‘Order Price’.

Bonus: Adding Sparkline Chart of “last 7 days”

On the coin market cap, todays’ cryptocurrency price table also features a lovely “mini” line chart for column “last 7 days”. The name for the type of chart is “sparkline”, and it is typically drawn without axes or coordinates. I wonder if it’s possible to include that.

It turns out jQuery already has a Sparkline plugin. It takes a simple array of numbers. For example:

1	"Last7Days”: "[64125.80, 64398.60, 64134.50, 64806.70, 64932.60, 66904.40, 67527.90]”

call to the sparkline() function to display the sparkline

1	$('.sparklines').sparkline('html', { enableTagOptions: true, fillColor:false, lineWidth:2, width:'100%', height:'50px' });

remember include Sparkline plugin before

1	<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-sparklines/2.1.2/jquery.sparkline.min.js" type="text/javascript">script>

The Final Outcome of CoinMarketCap.com Clone

Not too shabby!

Demo | Download source code on GitHub

Summary

Our little CMC cryptocurrency price chart looks darn good. Considering how little code it took. It uses custom formatter for most of the ‘fancy’ stuffs such as tickers and icons. WebSocket is definitely something very exciting to show live data stream.

Potential Enhancements

This is a fun experiment to really push the envelop with phpGrid beyond common use cases such as inventory management, CRM, timesheet applications.

Some potential useful enhancements:

Interactive cryptocurrency market cap chart
Pick and choose cryptocurrencies for display
Filters
Search
Database to store price history

Demo | Download source code on GitHub

The post Clone CoinMarketCap.com Cryptocurrency Price Table with WebSockets appeared first on phpGrid - PHP Datagrid.

JSON Data Source

Sun, 15 Aug 2021 01:54:10 +0000

It’s also possible to use JSON string as a data source with one extra step: use json_decode and set the second parameter to true to return the decoded JSON string to an associative array.

Once you have the array, it can be passed to the phpGrid constructor as if it’s a local array data source. This is useful when your data is real-time or loaded from a remote source such as stock quote and RSS etc.

1
2
3
4
5
6

$url = "http://myurl.com/json_string";
$json = file_get_contents($url);
$json_output = json_decode($json, true);

$dg = new C_DataGrid($json_output['items'], "id", "items");
$dg -> display();

Load Stored Procedure with OUT parameter

Tip: sp() function is your friend

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

use phpCtrl\C_DataGrid;
use phpCtrl\C_DataBase;
require_once("/file/path/to/conf.php");

$db = new C_DataBase(PHPGRID_DB_HOSTNAME, PHPGRID_DB_USERNAME, PHPGRID_DB_PASSWORD, PHPGRID_DB_NAME, PHPGRID_DB_TYPE,PHPGRID_DB_CHARSET);

$results = $db->sp('CALL GetOrderCountByStatus(?, @total)', 'Shipped');
$data = $db->db_query("SELECT @total");

// create PHP array from results returned by stored proc
$data1 = [];
$count = 0;
while($row = $db->fetch_array_assoc($data)) {
$data_row = array();
for($i = 0; $i < $db->num_fields($data); $i++) {
$col_name = $db->field_name($data, $i);
$data1[$count][$col_name] = $row[$col_name];
}
$count++;
}

$dg = new C_DataGrid($data1, "id", "data1");
$dg -> display();

Create Array from Database Table

Learn how to create array from database from Complex Query tutorial.

Save local array back to database

It’s possible to save local array data back into a relational DB. The technique uses a local array as indirect mean to edit complex database query with JOINS, UNION etc. See KB: Save local array back to database via Ajax

Demo

The post JSON Data Source appeared first on phpGrid - PHP Datagrid.

JSON in Plain English

Thu, 22 Jul 2021 04:27:13 +0000

JSON is an open standard, lightweight data-interchange format. It stands JavaScript Object Notation.

JSON is extremely common and useful in the modern webs for read and write on a website. Before JSON, there is XML, which has much overhead and much more difficult to parse than JSON, which is not only easy for humans to read and write, but also easy for computer to parse.

JSON was derived from JavaScript, hence the name JavaScript Object Notation. However, it is a language-independent data format. JSON always have file extension .json. phpGrid also uses JSON for retrieving and updating datagrids without refreshing the entire page.

A simplest JSON contains a key and value:

1
2
3

{
"color": "red"
}

A JSON key should always have double quotes around it whereas values can have a few of the basic types: string, number, float, boolean, and null. For instance:

1
2
3
4
5
6
7

{
"String": "Hello",
"Number": 1,
"Float": 0.21,
"Boolean": false,
"Empty": null
}

The types are explanatory. Non-string values don’t require double quotes. It’s important to use comma after each value.

JSON also can have array as types. Array is made with brackets [ and ] with values in between separated with comma’s. For example:

1
2
3

{
"colors":[ "red", "blue", "yellow" ]
}

JSON can also have objects . They are defined with curly brackets { and }. Objects as values in JSON must follow the same rules as JSON. In other words, you could have a JSON object inside another JSON object. This is called nesting.

JSON object example:

1
2
3
4
5
6
7

{
"student":
{
"name":"John",
"age":18
}
}

Even nested JSON objects:

1
2
3
4
5
6
7
8
9
10
11
12

{
"student":
{
"name": "John",
"age": 18,
"grades":
{
"math": 90,
"english": 87
}
}
}

A complete syntax sample with all the data types discussed:

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

{
"firstName": "John",
"lastName": "Smith",
"isAlive": true,
"age": 27,
"address":
{
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021-3100"
},
"phoneNumbers":
[
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "office",
"number": "646 555-4567"
}
],
"children":[],
"spouse": null
}

Summary

A JSON file is just a text file that stores data in files ended with .json extension. JSON data must conform the following data format:

Data is in name/value pairs
Data is separated by commas
Curly braces hold objects
Square brackets hold arrays

To learn more about JSON, I encourage you to check out the official JSON website!

Image Source: json.org

The post JSON in Plain English appeared first on phpGrid - PHP Datagrid.

Local Array Data Source *

Wed, 01 Apr 2015 14:59:00 +0000

* This feature is available with a commercial license.

phpGrid now supports local array data source (version 5.5+). No database is required for local data. So it’s NOT necessary to define PHPGRID_DB_* variables in conf.php when using local array. Simply pass a PHP array as the first parameter to the phpGrid constructor. Everything else is virtually the same.

In the below example, the first segment creates a local PHP array, namely $data1, which will be used as the data source for phpGrid. The second segment demonstrates passing the $data1 to the phpGrid constructor and call its methods. All existing phpGrid methods can be used the same way as a database-driven grids*.

For local array, it’s highly recommended to use all lower case for array key (e.g., bar1, bar2…) and use “id” as the primary key.

Make sure to check out the live example!

Local Array (PHP)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

$name = array('Bonado', 'Sponge', 'Decker', 'Snob', 'Kocoboo');
for ($i = 0; $i < 200; $i++)
{
$data1[$i]['id'] = $i+1;
$data1[$i]['foo'] = md5(rand(0, 10000));
$data1[$i]['bar1'] = 'bar'.($i+1);
$data1[$i]['bar2'] = 'bar'.($i+1);
$data1[$i]['cost'] = rand(0, 100);
$data1[$i]['name'] = $name[rand(0, 4)];
$data1[$i]['quantity'] = rand(0, 100);
$data1[$i]['discontinued'] = rand(0, 1);
$data1[$i]['email'] = 'grid_'. rand(0, 100) .'@example.com';
$data1[$i]['notes'] = '';
}

phpGrid Code ($data1 is the local array created above. Use “id” as the primary key name.)

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

// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");

$dg = new C_DataGrid($data1, "id", "data1");
$dg -> set_col_title("id", "ID") -> set_col_width('id', 20);
$dg -> set_col_title("foo", "Foo");
$dg -> set_col_title("bar", "Bar");
$dg -> set_col_title('discontinued', 'disc.') -> set_col_width('discontinued', 35);
$dg -> set_col_align('cost', 'right') -> set_col_currency('cost', '$');
$dg -> set_col_width('bar1', 40);
$dg -> set_col_width('quantity', 220);
$dg -> set_row_color('lightblue', 'yellow', 'lightgray');
$dg -> enable_search(true);
$dg -> enable_edit('FORM', 'CRUD');
$dg -> enable_export('EXCEL');
$dg -> enable_resize(true);
$dg -> set_col_format('email', 'email');
$dg -> set_col_dynalink('name', 'http://example.com', array("id", "name"));
$dg -> set_caption('Array Data Test');
$dg -> set_col_hidden('bar2');
$dg -> set_col_property('notes', array('edittype'=>'textarea','editoptions'=>array('cols'=>40,'rows'=>10))) -> set_col_wysiwyg('notes');
$dg -> set_dimension(900, 400);
//$dg -> set_multiselect(true);

$dg -> set_conditional_value('discontinued', '==1', array("TCellStyle"=>"tstyle"));
$dg -> set_conditional_format("cost","CELL",array("condition"=>"lt","value"=>"20.00","css"=> array("color"=>"black","background-color"=>"yellow")));

$dg -> set_theme($theme_name);

// set data sort type. With data array, we don't have data type information as we usually do with database fields.
$dg -> set_col_property("quantity", array("sorttype"=>"integer")); // display different time format
$dg -> set_col_property("cost", array("sorttype"=>"currency")); // display different time format

$dg -> set_databar('quantity', 'blue');

$dg -> display();

Screenshot

* Note that master-detail, subgrid, export, and file uploads are not yet available when using local array data source.

See Live Example!

JSON Data Source

It’s also possible to use JSON string as a data source with one extra step: use json_decode and set the second parameter to true to return the decoded JSON string to an associative array.

1
2
3
4
5
6

$url = "http://myurl.com/json_string";
$json = file_get_contents($url);
$json_output = json_decode($json, true);

$dg = new C_DataGrid($json_output['items'], "id", "items");
$dg -> display();

Create Array from Database Table

Learn how to create array from database from Complex Query tutorial.

Save local array back to datbase

Demo

The post Local Array Data Source * appeared first on phpGrid - PHP Datagrid.