Reading an API result and inserting it into a DB

I want to enter into my database the results I get from an external api. The results are in the format: date(comma)IP(comma)interger(space) etc as show in the example below. The last four digits of the IP returned are always (dot)XXX for security.

2010-04-14,208.120.29.XXX,16822 2010-04-14,207.255.139.XXX,4933389 2010-04-14,75.138.118.XXX,837202 2010-04-14,74.103.14.XXX,55335 2010-04-13,207.255.139.XXX,5095034 2010-04-13,98.202.1.XXX,739088 2010-04-13,74.103.14.XXX,220200 2010-04-12,141.165.135.XXX,7574145 2010-04-11,141.165.135.XXX,4858152

Code (markup):

I not really sure how to do this but was thinking something like explod the space and then a foreach list($time,$ip,$amount) or something.

then do something like

INSERT INTO table_name ('date', 'ip' , 'amount') VALUES ('date', 'ip', 'amount');

for each of them.

Any advice on how to do this or go about it, as my PHP knowledge is really basic, would be greatly appreciated.

Thanks in advance.

 

Yes I'd probably explode by the space, then you have an array of you rows. Loop though them in a foreach and explode by comma within the loop to get your field entries. Then call mysql INSERT on each row.

There's probably some fancy regex way to split the data but the above method will work just fine.

 

Thanks for your advice mfscripts.

I've being looking at that and developed the following but it's not not working correctly. Any idea where I've made the mistake?

$url=file("http://api.example.com/?value=datalogs_v1&login=$account&password=$pass");

$temp = explode(' ', $url);
    foreach($temp as $data)
    {
        list($time,$ip,$amount) = explode(',',$data);
		mysql_query("INSERT INTO table_logs (time,ip,amount) VALUES ('$time','$ip','$amount')") or die(mysql_error());
    }
    unset($temp);

PHP:

 

Actually I found my error. Thanks again mfscripts for the advice

What works for me is

$lines = file("http://api.expmple.com?data=valuelogs&login=$login&password=$pass");
foreach ($lines as $line)
{
    foreach (explode(' ', $line) as $data)
    {
        list($time, $ip, $amount) = explode(',', $data);
        $sql = sprintf("INSERT INTO table_logs (time, ip, amount) VALUES('%s', '%s', %d)",
            mysql_real_escape_string($time),
            mysql_real_escape_string($ip),
            $amount);
        mysql_query($sql);
    }
} 

PHP: