I just finished a script to import data from an XML file to MySQL, I have more than 280,000+ row, but after the import is done I only see 91,000 rows. After verifying if every query was running without any problem, I found the following one :
INSERT INTO `hotels` (`hotelId`, `hotelFileName`, `hotelName`, `rating`, `cityId`, `cityFileName`, `cityName`, `stateId`, `stateFileName`, `stateName`, `countryCode`, `countryFileName`, `countryName`, `imageId`, `Address`, `minRate`, `currencyCode`, `Latitude`, `Longitude`, `NumberOfReviews`, `ConsumerRating`, `PropertyType`, `ChainID`, `Facilities`) VALUES (1568054, 'Der_Obere_Wirt_zum_Queri', '\"Der Obere Wirt\" zum Queri', 3, 34633, 'Andechs', 'Andechs', 212, 'Bavaria', 'Bavaria', 'DE', 'Germany', 'Germany', 51498149, 'Georg Queri Ring 9', 92.1476, 'EUR', '48.009423000000', '11.214504000000', 16, 8.5, 0, 0, '1|3|5|8|22|27|45|49|53|56|64|66|67|139|202|209|213|256|')
Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in /home/hotels/public_html/insert.php on line 57
INSERT INTO `hotels` (`hotelId`, `hotelFileName`, `hotelName`, `rating`, `cityId`, `cityFileName`, `cityName`, `stateId`, `stateFileName`, `stateName`, `countryCode`, `countryFileName`, `countryName`, `imageId`, `Address`, `minRate`, `currencyCode`, `Latitude`, `Longitude`, `NumberOfReviews`, `ConsumerRating`, `PropertyType`, `ChainID`, `Facilities`) VALUES (1658359, 'Seclusions_of_Yallingup', '\"Seclusions\" of Yallingup', 4, 72257, 'Yallingup', 'Yallingup', 172, 'Western_Australia', 'Western Australia', 'AU', 'Australia', 'Australia', 53234107, '58 Zamia Grove', 238.7800, 'AUD', '-33.691192000000', '115.061938999999', 0, 0, 3, 0, '3|6|13|14|21|22|28|39|40|41|51|53|54|56|57|58|65|66|141|191|202|204|209|210|211|292|')
Warning: mysql_free_result() expects parameter 1 to be resource, boolean given in /home/hotels/public_html/insert.php on line 57
INSERT INTO `hotels` (`hotelId`, `hotelFileName`, `hotelName`, `rating`, `cityId`, `cityFileName`, `cityName`, `stateId`, `stateFileName`, `stateName`, `countryCode`, `countryFileName`, `countryName`, `imageId`, `Address`, `minRate`, `currencyCode`, `Latitude`, `Longitude`, `NumberOfReviews`, `ConsumerRating`, `PropertyType`, `ChainID`, `Facilities`) VALUES (1491947, '1_Melrose_Blvd', '#1 Melrose Blvd', 5, 964, 'Johannesburg', 'Johannesburg', NULL, '', '', 'ZA', 'South_Africa', 'South Africa', 46777171, '1 Melrose Boulevard Melrose Arch', , 'ZAR', '-26.135656000000', '28.067751000000', 0, 0, 9, 0, '6|7|9|11|12|15|17|18|21|32|34|39|41|42|50|51|56|58|60|140|173|202|293|296|')
Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'ZAR', '-26.135656000000', '28.067751000000', 0, 0, 9, 0, '6|7|9|11|12|15|17|18' at line 1
This is my query code :
if($obj->stateFileName != "")
{
$query = "INSERT INTO `hotels` (`hotelId`, `hotelFileName`, `hotelName`, `rating`, `cityId`, `cityFileName`, `cityName`, `stateId`, `stateFileName`, `stateName`, `countryCode`, `countryFileName`, `countryName`, `imageId`, `Address`, `minRate`, `currencyCode`, `Latitude`, `Longitude`, `NumberOfReviews`, `ConsumerRating`, `PropertyType`, `ChainID`, `Facilities`) VALUES ({$obj->hotelId}, '".mysql_real_escape_string($obj->hotelFileName)."', '".mysql_real_escape_string($obj->hotelName)."', {$obj->rating}, {$obj->cityId}, '".mysql_real_escape_string($obj->cityFileName)."', '".mysql_real_escape_string($obj->cityName)."', {$obj->stateId}, '".mysql_real_escape_string($obj->stateFileName)."', '".mysql_real_escape_string($obj->stateName)."', '{$obj->countryCode}', '".mysql_real_escape_string($obj->countryFileName)."', '".mysql_real_escape_string($obj->countryName)."', {$obj->imageId}, '".mysql_real_escape_string($obj->Address)."', {$obj->minRate}, '{$obj->currencyCode}', '{$obj->Latitude}', '{$obj->Longitude}', {$obj->NumberOfReviews}, {$obj->ConsumerRating}, {$obj->PropertyType}, {$obj->ChainID}, '{$obj->Facilities}')";
$result = mysql_query($query);
}
else
{
$query = "INSERT INTO `hotels` (`hotelId`, `hotelFileName`, `hotelName`, `rating`, `cityId`, `cityFileName`, `cityName`, `stateId`, `stateFileName`, `stateName`, `countryCode`, `countryFileName`, `countryName`, `imageId`, `Address`, `minRate`, `currencyCode`, `Latitude`, `Longitude`, `NumberOfReviews`, `ConsumerRating`, `PropertyType`, `ChainID`, `Facilities`) VALUES ({$obj->hotelId}, '".mysql_real_escape_string($obj->hotelFileName)."', '".mysql_real_escape_string($obj->hotelName)."', {$obj->rating}, {$obj->cityId}, '".mysql_real_escape_string($obj->cityFileName)."', '".mysql_real_escape_string($obj->cityName)."', NULL, '', '', '{$obj->countryCode}', '".mysql_real_escape_string($obj->countryFileName)."', '".mysql_real_escape_string($obj->countryName)."', {$obj->imageId}, '".mysql_real_escape_string($obj->Address)."', {$obj->minRate}, '{$obj->currencyCode}', '{$obj->Latitude}', '{$obj->Longitude}', {$obj->NumberOfReviews}, {$obj->ConsumerRating}, {$obj->PropertyType}, {$obj->ChainID}, '{$obj->Facilities}')";
$result = mysql_query($query);
}
For the You have an error in your SQL syntax error, you can just look at the actually query where it tells you to spot the problem. Searching for ZAR I found this:
..., '1 Melrose Boulevard Melrose Arch', , 'ZAR', '-26.135656000000', ...
As you can see, , , is not valid syntax, it is missing an element. Looking at your code it seems like it is missing {$obj->minRate}. This field is empty for some records (or at least for the one record you posted). To fix this, you can try replacing that with:
is_null($obj->minRate) ? 'NULL' : $obj->minRate
For the mysql_free_result warning, you haven't shown the code where that method is called, so it's hard to say what that's about.
The insert syntax is invalid, it contains two commas with no value: '1 Melrose Boulevard Melrose Arch', , 'ZAR',
Try changing it to: '1 Melrose Boulevard Melrose Arch', NULL, 'ZAR',
Also, this probably means that whatever converted the XML to SQL insert statements has a bug. But you haven't provided us any of that code so it wouldn't be possible to help you fix it here.