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

Is your XML declared as XML??

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

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..

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

great

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/>";
}

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);

    }
Member Avatar for 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);

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 ","

Member Avatar for diafol

damn:

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

should be

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

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 :(

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());


?>
Member Avatar for 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.

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 :( ?

I did it :P
just removed ")" from

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

and now all records in db , thanks for help.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.