Hello, thanks for the great script..
I am trying to insert a remote xml contents into a mysql database. Problem is I keep getting a "Parse error: syntax error, unexpected T_VARIABLE.."
Please help me identify the error in my script:
function myRecordHandler($city)
{
$sql =
"INSERT INTO getcities (city_id,countrycode,languagecode,name,nr_hotels)
VALUES (
'"$city["city_id"]"', "$city["countrycode"]"', "$city["languagecode"]"', "$city["name"]"', "$city["nr_hotels"]"'
)
";
mysql_query($sql);
}
$url = "http://user:pass@server.com/xml/bookings.getCities";
MagicParser_parse($url,"myRecordHandler", "XML|getCities/result/");Appreciate it
Okay, we've got rid of the errors but the table is not getting any kind of data. just blank. any ideas ?
Hi,
There's quite a few conditions for successfully inserting data, so it could be any number of things that aren't quite right.
If you look through the following thread, you will see how to add some debug code to your script that should help work out what the problem is...
http://www.magicparser.com/node/429
If you're still not sure where the problem lies after the ideas in that thread, if you could post more of your code it will help to work out what's wrong...
Cheers,
David.
Hello David, we appreciate your help. making progress here..
The script is now insering records into the mysql table. only problem is, it is just insering the first 59 records. (we have thousands of records in the xml file)
and this is the output of the debug code you mentioned in the other thread: (notice that it says 1000 records but only inserts 59 records into the table, records should be more than 1000 anyway)
Processed 1000 records.
Last SQL statement was: INSERT INTO getcities (city_id,countrycode,languagecode,name,nr_hotels) VALUES ( '-2750126', 'dk', 'zh', 'Skagen', '1' ) thanks for your time
Hi,
I would try inserting that SQL query directly into MySQL, for example with phpMyAdmin. That should reveal any error messages that are the result of the first 59 records being imported.
Before doing that, you might get a clue by printing the last mysql error message at the end of the script:
<?php
print mysql_error();
?>
It is likely to be a duplicate key problem I would have thought - this is normally the case where only a certain number of records are imported.
Hope this helps,
Cheers,
David.
Indeed,
erroroutput:
Duplicate entry '-2750126' for key 1
How can we solve this ?
thanks
Hi,
Are you deleting all records before the parse? You will need to have code like this in your script before inserting new records:
$sql = "DELETE FROM getcities";
mysql_query($sql);That should help make progress - provided that there aren't duplicate city ID's in the feed...!
Cheers,
David.
Yes, we are deleting before the parse. And yes we do have duplicate city_id's that that we would like to insert into the table. each city_id would have 4 corresponding translations and therefore it is not exactly a unique id. we would like to treat it as any other field. How do we tell the script to treat is like a normal field ? I thought I'd paste the script here in order to give you more information:
require("key.php");
require("MagicParser.php");
$connection = mysql_connect("server.com:3306",$username, $password) or die(mysql_error());
$db = mysql_select_db("vastor_xml",$connection) or die ("Couldn't select database.");
//1- EMPTY TABLE
$sql = "DELETE FROM getcities";
mysql_query($sql);
//2- INSERT NEW DATA
$counter = 0;
function myRecordHandler($city)
{
global $counter;
global $sql;
$counter++;
$sql =
"INSERT INTO getcities (city_id,countrycode,languagecode,name,nr_hotels)
VALUES
(
'".$city["CITY_ID"]."',
'".$city["COUNTRYCODE"]."',
'".$city["LANGUAGECODE"]."',
'".$city["NAME"]."',
'".$city["NR_HOTELS"]."'
)
";
mysql_query($sql);
}
$url = "http://user:password@server.com/xml/bookings.getCities";
MagicParser_parse($url,"myRecordHandler", "XML|getCities/result/");
print "<p>Processed ".$counter." records.</p>";
print "<p>Last SQL statement was: ".$sql."</p>";
print mysql_error();Hi,
It's not the code that is causing it to be required to be unique, it is how the table is set-up in the database.
What you need to do is remove the unique (primary) key setting from city_id, and add another field (just call it "id") as the database's id field. You should make this field "auto-increment", which you can define when creating a table in something like phpMyAdmin...
Then, every row will have it's own unique ID in your database, and it won't matter if city_id's are duplicated.
Cheers,
David.
The funny part is, our city_id in the mysql table is a varchar(255) and has no ID attributes assigned to it whatsover. I do not know why its being handled as a unique ID. could it be something in the source XML feed ?
Hi,
I would suggest dropping the table, and re-building it from scratch, with an id field defined as the primary key as the first field. This will prevent any other field from being selected by the database as the primary key.
Cheers,
David.
Top man, all good here, no errors. except that we're only getting 1000 records.
Am guessing our XML provider is setting this limit for the development period.
Hi,
To construct your SQL statement, you need to use the dot operator "." to concatenate the strings. Also, the fields names will be in UPPER CASE from Magic Parser. Try this:
function myRecordHandler($city){
$sql =
"INSERT INTO getcities (city_id,countrycode,languagecode,name,nr_hotels)
VALUES
(
'".$city["CITY_ID"]."',
'".$city["COUNTRYCODE"]."',
'".$city["LANGUAGECODE"]."',
'".$city["NAME"]."',
'".$city["NR_HOTELS"]."'
)
";
mysql_query($sql);
}
$url = "http://user:pass@server.com/xml/bookings.getCities";
MagicParser_parse($url,"myRecordHandler", "XML|getCities/result/");
Cheers,
David.