Announcement

Collapse
No announcement yet.

JSON to SQLite (small project)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • JSON to SQLite (small project)

    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/tic...rt=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...).

    PHP Code:
    // 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:

    PHP Code:
    $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($jsontrue);

    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

    Gunter Eibl
    email@GunterEibl.de

    asdw.de - Scriptcase & Open Source projects (German/English)
    scriptcase.coach - professional coaching for Scriptcase users (German)
    GunterEibl.de - About me (German)

    Comment

    Working...
    X