JSON to SQLite (small project)

Developers Oportunity
#1

I have been a programmer for 20+ years but I’m new to SC and PHP.

I need to download this JSON data into a SQLite table. About 5 fields.
https://api.coinmarketcap.com/v2/ticker/?start=1&limit=100

At this point I’d rather just pay someone to write this routine.
Maybe build a long term relationship for other little projects.
If interested, send me a PM and I’ll send you detailed specs.
Will make payments via paypal.

#2

Hi townsend,

just to give you a direction:

Generate a table (either in database builder or in Scriptcase via (I’m using the fields in your JSON…).

// Generate table (drop if exists)
$sql_generate = "DROP TABLE IF EXISTS `table_1`;
CREATE TABLE `table_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `symbol` varchar(200) NOT NULL,
  `website_slug` varchar(200) NOT NULL,
  `rank` int(11) NOT NULL,
  `circulating_supply` int(11) NOT NULL,
  `total_supply` int(11) NOT NULL,
  `max_supply` int(11) NOT NULL,
  `last_updated` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
";
sc_exec_sql($sql_generate);

Then put your JSON-string into a string variable ($json) and convert it into an array.
Loop over the array, generating an sql INSERT statement which you execute via sc_exec_sql() macro:

$json = '
{
    "data": {
        "1": {
            "id": 1, 
            "name": "Bitcoin", 
            "symbol": "BTC", 
            "website_slug": "bitcoin", 
            "rank": 1, 
            "circulating_supply": 17431412.0, 
            "total_supply": 17431412.0, 
            "max_supply": 21000000.0, 
            "quotes": {
                "USD": {
                    "price": 3826.16164626, 
                    "volume_24h": 6938611961.83235, 
                    "market_cap": 66695400035.0, 
                    "percent_change_1h": -0.55, 
                    "percent_change_24h": 7.89, 
                    "percent_change_7d": 9.51
                }
            }, 
            "last_updated": 1545243024
        }, 
...
    "metadata": {
        "timestamp": 1545242181, 
        "num_cryptocurrencies": 2072, 
        "error": null
    }
}';
$array = json_decode($json, true);

foreach($array['data'] as $arr_1)
    {
        $sql = "INSERT INTO table_1 SET ";
        foreach($arr_1 AS $fieldname=>$value)
            {

                if (!is_array($value))
                    {
                        $sql .= "`" . $fieldname . "` = '" . $value ."',";
                    }
            }
        $sql = substr($sql,0,-1);
        sc_exec_sql($sql);
    }

Hint: I did completely skip the detail table (quotes) in this sample.

If you need any more help or want me to do the complete thing, please drop me a PM.

Have a great day!

Best

Gunter
Scriptcase.Coach