gw2 v2 api tradepost ranking

#! /usr/bin/php -q
<?php
function is_url_exist($url){
    $ch = curl_init();
    curl_setopt ($ch, CURLOPT_URL, $url);
    curl_exec($ch);

    $code = curl_getinfo($ch,CURLINFO_HTTP_CODE);
        if($code==200 or $code==302){
            $status=0;
        }else{
            $status=1;
        }

    curl_close($ch);
    return $status;

}
$amount_added_to_db = 0;

# Get the item numbers in an array
$url1 = "http://api.guildwars2.com/v2/commerce/listings";
$response1 = file_get_contents($url1);
$data1 = json_decode($response1, true);

# Split up the array in chunks for further processing (processing the whole array at once will trigger an out of memery error)
$chunksize = round(sizeof($data1)/10);  #divided by 10 equals around 2215
$offset = 0; # change offset by x * chunkset everytime this script runs again (0<x<10) to fill the database completely
$length = $chunksize;
$smallchunk = array_slice($data1,$offset, $length);

# Get item names
for ($x=0;$x<$length;$x++){
        $itemurl1 = "http://api.guildwars2.com/v2/items/{$smallchunk[$x]}"; #if i use https i get echo of http contents in cli interface :/
        if (is_url_exist($itemurl1)==0){
            $response_array = file_get_contents($itemurl1);
            $temporary_array = json_decode($response_array, true);
            $associative_array[$x] = array('name' => $temporary_array['name'],'id' => $smallchunk[$x]);
        } else {
            $associative_array[$x] = array('name' => 'invalidurl','id' => $smallchunk[$x]);
        }
}

try {
        # Make a connection to the database (dont forget to chmod u+w+x,g+w+x file and folder on the server)
        $db = new PDO('sqlite:/var/www/gw2db/gw2.sqlite');
        $db ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        # Create the database (if already made, IF NOT EXISTS will prevent error)
        $db->exec("CREATE TABLE IF NOT EXISTS alltheitems (Id INTEGER PRIMARY KEY, itemname TEXT, itemnumber INTEGER)");

        # Prepare the query ONCE
        $stmt = $db->prepare('INSERT INTO alltheitems (itemname, itemnumber) VALUES(:name, :number)');
        $stmt->bindParam(':name', $itemname, SQLITE3_TEXT);
        $stmt->bindParam(':number', $itemnumber, SQLITE3_INTEGER);

        # Insert values and execute statements
        for ($x=0;$x<sizeof($associative_array);$x++){
            $itemname = $associative_array[$x]['name'];
            $itemnumber = $associative_array[$x]['id'];

            # Check if the item is already in the database or invalid URL return
                $duplicatecheck = $db->query("SELECT * FROM alltheitems WHERE itemnumber LIKE '$itemnumber'");
                $alreadyinDB=0;
                foreach($duplicatecheck as $row)
                    {
                      $alreadyinDB=1;
                    }

                $invalid = strcmp($itemname,"invalidurl");

                if (($alreadyinDB==0) && ($invalid!==0)) {
                $stmt->execute();
                $amount_added_to_db = $amount_added_to_db + 1;
                }
        }

        # Retrieve the just formed database
        $fetchdb = $db->query("SELECT itemnumber, itemname FROM alltheitems");
        # Make array from fetchdb object
        $data_array = $fetchdb->fetchAll();
        # We just want to fetch the TP info of the latest added items, negative offset means it will start slicing from the end of array
        if ($amount_added_to_db > 0){
                $data_array_latest_added = array_slice($data_array,-$amount_added_to_db);

                for ($x=0; $x<sizeof($data_array_latest_added);$x++) {
                    $itemurl= "http://api.guildwars2.com/v2/commerce/listings/{$data_array_latest_added[$x]['itemnumber']}";
                    $itemname = $data_array_latest_added[$x]['itemname'];

                    if (is_url_exist($itemurl)==0){
                        $response_array = file_get_contents($itemurl);
                        $temporary_array = json_decode($response_array, true);
                        $joint_array{$x}= array('name'=>$itemname, 'id'=>$temporary_array['id'],'buys'=>$temporary_array['buys'],'sells'=>$temporary_array['sells']);
                    } else {
                        $foo_array = array (0,0);
                        $joint_array{$x}= array('name'=>$itemname, 'id'=>'99999','buys'=>$foo_array,'sells'=>$foo_array);
                    }

                }

                if (empty($joint_array) === FALSE) {

                    for ($y=0; $y<sizeof($joint_array);$y++) {

                        $totalbuys = 0;
                        $totalsells = 0;
                        $totalbuyprice = 0;
                        $totalsellprice = 0;

                        for ($x=0;$x<sizeof($joint_array[$y]['buys']);$x++) {
                            $totalbuys = $joint_array[$y]['buys'][$x]['listings'] * $joint_array[$y]['buys'][$x]['quantity'] + $totalbuys;
                            $totalbuyprice = $joint_array[$y]['buys'][$x]['listings'] * $joint_array[$y]['buys'][$x]['quantity'] * $joint_array[$y]['buys'][$x]['unit_price'];
                            $countB = $x;
                            if ($totalbuys >= 1000){
                                break;
                            }
                        }

                        for ($x=0;$x<sizeof($joint_array[$y]['sells']);$x++) {
                            $totalsells = $joint_array[$y]['sells'][$x]['listings'] * $joint_array[$y]['sells'][$x]['quantity'] + $totalsells;
                            $totalsellprice = $joint_array[$y]['sells'][$x]['listings'] * $joint_array[$y]['sells'][$x]['quantity'] * $joint_array[$y]['sells'][$x]['unit_price'];
                            $countS = $x;
                            if ($totalsells >= 1000){
                                break;
                            }
                    }

                    if (($totalsells >= 1000) && ($totalbuys >= 1000)){
                        $totalbuyprice = $totalbuyprice - ($totalbuys - 1000)* $joint_array[$y]['buys'][$countB]['unit_price'];
                        $totalsellprice = $totalsellprice - ($totalsells - 1000)* $joint_array[$y]['sells'][$countS]['unit_price'];

                        $ratio = $totalbuyprice / ($totalsellprice + $totalbuyprice);
                        $ratio_small = number_format((float)$ratio, 2, '.', '');
                    } else {
                        $ratio_small = 0;
                    }

                    $db->exec("CREATE TABLE IF NOT EXISTS rating (Id INTEGER PRIMARY KEY, itemnumber INTEGER, itemname TEXT, itemratio REAL)");

                        # Prepare the query ONCE
                        $stmt = $db->prepare('INSERT INTO rating (itemnumber, itemname, itemratio) VALUES(:number, :name, :ratio)');
                        $stmt->bindParam(':number', $itemnumber, SQLITE3_INTEGER);
                        $stmt->bindParam(':name', $itemname, SQLITE3_TEXT);
                        $stmt->bindParam(':ratio', $itemratio, SQLITE3_FLOAT);

                        # Insert values and execute statements
                        $idnumber = $joint_array[$y]['id'];
                        $alreadyinDBcheck = $db->query("SELECT * FROM rating WHERE itemnumber LIKE '$idnumber'");
                            $found=0;
                            foreach($alreadyinDBcheck as $row)
                                {
                                    $found=1;
                                }
                            if ($found==0) {
                                $itemratio = $ratio_small;
                                $itemnumber = $joint_array[$y]['id'];
                                $itemname = $joint_array[$y]['name'];
                                $stmt->execute();
                            } else {
                                $db->exec("UPDATE rating SET itemratio='$ratio_small' WHERE itemnumber LIKE '$joint_array[$y][id]'");
                                #echo ' already in DB <br>';
                                }



                }
            }
        }

        # add creation timestamp
        date_default_timezone_set("Europe/Amsterdam");
        $time = "This page has been updated at " . date("d-m-Y h:i:sa"). "<br><br>";
        $db->exec("CREATE TABLE IF NOT EXISTS creationtime (Id INTEGER PRIMARY KEY, timestring TEXT)");
            $stmt = $db->prepare('INSERT INTO creationtime (timestring) VALUES(:time)');
            $stmt->bindParam(':time', $timestring, SQLITE3_TEXT);
            $timestring = $time;
            $stmt->execute();

        # Clear the table
        #$db-> exec ('DELETE FROM rating');
        #$db-> exec ('DELETE FROM alltheitems');

        # close the database connection
        $db = NULL;




    }

    catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}



?>