using curl to consume web service

[OsaMasw]

am not good enough with curl functions but I understand some :P
I have this webservice http://www.psnapi.com.ar/ps3/api/psn.asmx?op=getGames
that required to enter a variable "sPSNID" to get list of games
the XML output will be like this:

<ArrayOfGame>
<Game>
<Id>NPWR00132_00</Id>
<IdGameEurope/>
<Title>GTA IV</Title>
<Image>http://trophy01.np.community.playstation.net/trophy/np/NPWR00132_00_FB41DD6DD0A782A55D7A8497108B84EB19EDA56C/EF4A80E7CECAFBA06A2F60DA0CC3CAC53B77C9D0.PNG</Image>
<Progress>3</Progress>
<TrophiesCount>
<Earned>3</Earned>
<Total>3</Total>
<Platinum>0</Platinum>
<Gold>0</Gold>
<Silver>0</Silver>
<Bronze>3</Bronze>
</TrophiesCount>
<Trophies/>
<OrderPlayed>1</OrderPlayed>
<Updated>false</Updated>
<LastUpdated>2012-12-09T13:24:24</LastUpdated>
<Released>0001-01-01T00:00:00</Released>
<TotalPoints>1275</TotalPoints>
<TotalTrophies>66</TotalTrophies>
<UserPoints>45</UserPoints>
<Stars>3.975</Stars>
<Reviews>20</Reviews>
<Platform>ps3</Platform>
</Game> ...... and so on until  </ArrayOfGame>

am using this class

<?php
class GetGamespsn{
    public $psnid;
    public $xmlobj;

    public function __construct($psnid='') {
        if($psnid) {
            $this->psnid = $psnid;
            $this->load();
        }
    }

    public function load() {
        if($this->psnid) {
            $url = "http://www.psnapi.com.ar/ps3/api/psn.asmx/getGames?sPSNID={$this->psnid}";

            $ch = curl_init();
            curl_setopt($ch, CURLOPT_URL, $url);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

            $result = curl_exec($ch);

            curl_close($ch);

            $this->xmlobj = simplexml_load_string($result);
        }
    }

    public function __get($name) {
        return $this->xmlobj->Game->$name;
    }
}


$psnid = new GetGamespsn("hawkiq");

print $psnid->Title; // Just for testing to get title
?>

but allways I got this error

Warning: simplexml_load_string() [function.simplexml-load-string]: Entity: line 1: parser error : Start tag expected, '<' not found in C:\AppServ\www\lab\getgames.php on line 25

Warning: simplexml_load_string() [function.simplexml-load-string]: System.NullReferenceException: Object reference not set to an instance of an obj in C:\AppServ\www\lab\getgames.php on line 25

Warning: simplexml_load_string() [function.simplexml-load-string]: ^ in C:\AppServ\www\lab\getgames.php on line 25

[GliderPilot]

Is your XML declared as XML??

<?xml version="1.0" encoding="ISO-8859-1"?>

[veedeoo]

Hi,

When you run this script, is your computer connected to the Internet? If so, then we need to test your script outside the class. I am not sure if that class is even functional. Lets make a simple function that will load external xml file via cURL.

here we go...

        function loadThisXmlFile($url){

        $ch = curl_init();
        curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/5.0 (Windows; U; MSIE 7.0; Windows NT 6.0; en-US)');  
        curl_setopt($ch,CURLOPT_CONNECTTIMEOUT,10);
        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
        curl_setopt($ch, CURLOPT_AUTOREFERER, true);
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        ## store data as an output
        $output = curl_exec($ch);
        curl_close($ch); 
        ## load stored data 
        $xml = @simplexml_load_string($output);
        ## return output as an xml file
        return $xml; 

        }

to use the above function for testing do it like this..

    ## change the url to your own xml target
    print_r( loadThisXmlFile('http://feeds.bbci.co.uk/news/england/london/rss.xml'));

If all is well, then there should be some data showing on the page.

Let me know what you are getting after running the simple function I have provided above, so that we can move on to the next step..

[OsaMasw]

Thats great, I don't know whats wrong in my code but this works great
here is the output I get

I need to extract all information from this xml is that fine ?

$player = loadThisXmlFile('http://www.psnapi.com.ar/ps3/api/psn.asmx/getGames?sPSNID=hawkiq');
foreach ($player->Game as $gameinfo) {
echo 'Title: '.$gameinfo->Title.'; image: '. $gameinfo->Image . "<br/>";
}

[OsaMasw]

when am displaying the record its works fine but when try to insert it into db only the first record entered

    foreach ($player->Game as $gameinfo) {
    $gameuid = $gameinfo->Id;
    $gtitle = $gameinfo->Title;
    $userid = "hawkiq";
    $gimg = $gameinfo->Image;
    $gprogress = $gameinfo->Progress;
    $gearned= $gameinfo->Title;
    $gplatinum = $gameinfo->TrophiesCount->Platinum;
    $ggold = $gameinfo->TrophiesCount->Gold;
    $gsilver = $gameinfo->TrophiesCount->Silver;
    $gbronze = $gameinfo->TrophiesCount->Bronze;
    $glastup = $gameinfo->LastUpdated;
    $gtotaltrophies = $gameinfo->TotalTrophies;

    $sql ="INSERT INTO `games` (
    `gameid` ,
    `gameuid` ,
    `gtitle` ,
    `userid` ,
    `gimg` ,
    `gprogress` ,
    `gearned` ,
    `gplatinum` ,
    `ggold` ,
    `gsilver` ,
    `gbronze` ,
    `glastup` ,
    `gtotaltrophies`
    )
    VALUES (
    '', '$gameuid', '$gtitle', '$userid', '$gimg', '$gprogress', '$gearned', '$gplatinum', '$ggold', '$gsilver', '$gbronze',  '$glastup', '$gtotaltrophies'
    )";

    $query = mysql_query($sql);

    }

[diafol]

I'd suggest storing the values bit in an array, which you can then explode to form multiple values clause. So you just run the one sql query at the end.

foreach ($player->Game as $gameinfo) {
    //clean info if it's from input / untrustworthy   
    $clean = array_map("mysql_real_escape_string",array($gameinfo->Id,$gameinfo->Title,"hawkiq",$gameinfo->Image,$gameinfo->Progress,$gameinfo->Title,$gameinfo->TrophiesCount->Platinum,$gameinfo->TrophiesCount->Gold,$gameinfo->TrophiesCount->Silver,$gameinfo->TrophiesCount->Bronze,$gameinfo->LastUpdated,$gameinfo->TotalTrophies));
    //place each record into a storage array
    $keep[] = "('','" . implode("','", $clean) . "')";
}


//Your SQL (outside the foreach loop)
//The explode just parses the records to the VALUES clause of the query
$sql ="INSERT INTO `games` (
`gameid` ,
`gameuid` ,
`gtitle` ,
`userid` ,
`gimg` ,
`gprogress` ,
`gearned` ,
`gplatinum` ,
`ggold` ,
`gsilver` ,
`gbronze` ,
`glastup` ,
`gtotaltrophies`
)
VALUES (" .
    explode(",",$keep)
. ")";

//test
//echo $sql;

$query = mysql_query($sql);

[OsaMasw]

I've tried the code you've give me, I followed every step from begining to end everything works fine
but when it comes to $sql its not work
the out put of $sql is

INSERT INTO `games` ( `gameid` , `gameuid` , `gtitle` , `userid` , `gimg` , `gprogress` , `gearned` , `gplatinum` , `ggold` , `gsilver` , `gbronze` , `glastup` , `gtotaltrophies` ) VALUES (Array)

correct me if I wrong, BUT is must there a values in VALUES() ?
I printed out the $keep[], and it give an array with information seperated by ","

[diafol]

damn:

VALUES (" .
    explode(",",$keep)
. ")";

should be

VALUES (" .
    implode(",",$keep)
. ")";

[OsaMasw]

OOOOH i hate when I keep getting errors
I tried to show the sql and it was correct and in mysql_query there is an error

Invalid query: Column count doesn't match value count at row 1

I've tried to remove the gameid since its auto increment and remove it from $keep array
still the same error :(

[OsaMasw]

This is the whole script to check

SQL

CREATE TABLE `games` (
  `gameid` int(11) NOT NULL auto_increment,
  `gameuid` varchar(255) NOT NULL,
  `gtitle` varchar(255) NOT NULL,
  `userid` varchar(255) NOT NULL,
  `gimg` varchar(500) NOT NULL,
  `gprogress` int(11) NOT NULL,
  `gearned` int(11) NOT NULL,
  `gplatinum` int(11) NOT NULL,
  `ggold` int(11) NOT NULL,
  `gsilver` int(11) NOT NULL,
  `gbronze` int(11) NOT NULL,
  `glastup` varchar(255) NOT NULL,
  `gtotaltrophies` int(11) NOT NULL,
  PRIMARY KEY  (`gameid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

php

<?php

include("../config.php");
        function loadThisXmlFile($url){

        $ch = curl_init();
        curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/5.0 (Windows; U; MSIE 7.0; Windows NT 6.0; en-US)');  
        curl_setopt($ch,CURLOPT_CONNECTTIMEOUT,10);
        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
        curl_setopt($ch, CURLOPT_AUTOREFERER, true);
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        ## store data as an output
        $output = curl_exec($ch);
        curl_close($ch); 
        ## load stored data 
        $xml = @simplexml_load_string($output);
        ## return output as an xml file
        return $xml; 

        }


            ## change the url to your own xml target
    $player = loadThisXmlFile('http://www.psnapi.com.ar/ps3/api/psn.asmx/getGames?sPSNID=hawkiq');

foreach ($player->Game as $gameinfo) {
    //clean info if it's from input / untrustworthy   
    $clean = array_map("mysql_real_escape_string",array($gameinfo->Id,$gameinfo->Title,"hawkiq",$gameinfo->Image,$gameinfo->Progress,$gameinfo->Title,$gameinfo->TrophiesCount->Platinum,$gameinfo->TrophiesCount->Gold,$gameinfo->TrophiesCount->Silver,$gameinfo->TrophiesCount->Bronze,$gameinfo->LastUpdated,$gameinfo->TotalTrophies));
    //place each record into a storage array
    $keep[] = "('" . implode("','", $clean) . "')";
}

//Your SQL (outside the foreach loop)
//The explode just parses the records to the VALUES clause of the query
$sql ="INSERT INTO `games` (
`gameuid` ,
`gtitle` ,
`userid` ,
`gimg` ,
`gprogress` ,
`gearned` ,
`gplatinum` ,
`ggold` ,
`gsilver` ,
`gbronze` ,
`glastup` ,
`gtotaltrophies`
)
VALUES (" .
    implode(",",$keep)
. ")";

//test
//echo $sql;
//exit();
$query = mysql_query($sql)or die('Invalid query: ' . mysql_error());


?>

[diafol]

As I don't have your data, I can't check the code. If you uncomment the echo and copy/paste the screen output, perhaps we can see what's going on. It syas you have different number of fields. Check that.

[OsaMasw]

this is the results by echo the sql

INSERT INTO `games` ( `gameuid` , `gtitle` , `userid` , `gimg` , `gprogress` , `gearned` , `gplatinum` , `ggold` , `gsilver` , `gbronze` , `glastup` , `gtotaltrophies` ) VALUES (('NPWR01452_00','Castlevania: Lords of Shadowâ„¢','hawkiq','http://trophy01.np.community.playstation.net/trophy/np/NPWR01452_00_C46312687437D6BFE29E7ABB550925B799196978/C4B5343236C81E1F0D51ED7FBDAEF8B79DEFC7F3.PNG','1','Castlevania: Lords of Shadowâ„¢','0','0','0','1','2012-12-14T06:24:17','54'),('NPWR00132_00','GTA IV','hawkiq','http://trophy01.np.community.playstation.net/trophy/np/NPWR00132_00_FB41DD6DD0A782A55D7A8497108B84EB19EDA56C/EF4A80E7CECAFBA06A2F60DA0CC3CAC53B77C9D0.PNG','4','GTA IV','0','0','0','4','2012-12-14T06:24:03','66'),

this is the first two records
I've tried to remove ")" from
$keep[] = "'" . implode("','", $clean) . "'";

but still the same, is there any old way to save records one by one :( ?

[OsaMasw]

I did it :P
just removed ")" from

VALUES " .
    implode(",",$keep)
. "";

and now all records in db , thanks for help.