whether json_decode response is empty or not

Question

I just started working with json for real and trying to learn as best as I can! I want to share this work I made, I feel like this could need some improvements, if not just much of it.

So okay, I use twitch.tv REST_API. And here is my code. Basically I want to run this every minute as a crontab through my web hosting company. I know that you can get the (encoded) json data through this way: "http://api.justin.tv/api/stream/list.json?channel=example,example2,example3"; as well. And it is probably faster? but then I don't know how to set my stream offline in the database.

So I guess what I am asking is just how I can improve this.

$result = mysql_query("SELECT streamname FROM streams") or die(mysql_error());

$ids=array(); 
while($row = mysql_fetch_assoc($result))  
{
    $ids[]=$row["streamname"]; 
}

$mycurl = curl_init();
for($i=0;$i<count($ids);$i++)
{

    curl_setopt ($mycurl, CURLOPT_HEADER, 0);
    curl_setopt ($mycurl, CURLOPT_RETURNTRANSFER, 1); 

    $url = "http://api.justin.tv/api/stream/list.json?channel=$ids[$i]";
    curl_setopt ($mycurl, CURLOPT_URL, $url);

    $web_response =  curl_exec($mycurl);
    $result = json_decode($web_response);

    if(empty($result))
    {
        $sql = "UPDATE streams SET online = '0' WHERE streamname = '" . $ids[$i] . "'";
    }
    else
    {
        $sql = "UPDATE streams SET online = '1' WHERE streamname = '" . $ids[$i] . "'";
    }
    mysql_query($sql) or die(mysql_error());
}

You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Dec 22 '12 at 10:01

score 1 · Accepted Answer · answered Dec 22 '12 at 10:29

Apparently you want to mark channels offline which don't appear in the API result, and the other way, mark channels online, which still appear.

First a note which was already said in the comments. Please no longer use the mysql extension of PHP. This is deprecated and will be removed in future versions of PHP I recommend MySQLi: http://php.net/manual/en/book.mysqli.php

Currently you are fetching data for each channel, which of course slows the process and unnecessarily puts load on the justin.tv servers.

When querying the status, the limit is the maximum size of the GET request which is 8192 bytes on most servers.

Now, instead checking the result against empty you can consider all channels to be offline, then loop through the result and mark the channels in your result online again. Do this in an array or object (can be your database result you fetched for your channel list) and update all channels in one query.

Thank you for the help, now it seems obvious, that I should do this method! But MySQL LI I had no idea of, thanks. I will check it out. :) — 今際のアリス, Dec 24 '12 at 12:42

whether json_decode response is empty or not

1 Answers1