views:

43

answers:

1

I have a strange problem, I'm sending an SQL query through PHP:

INSERT INTO `lib_plex` (`id`, `active`, `lastUpdated`, `entry_date`, `entry_ip`, `address`, `city`, `state_iso`, `zip_code`, `plex_type`, `price`, `has_garage`, `has_indoor_parking`, `has_outdoor_parking`, `has_pool`, `has_fireplace`, `average_nb_room`, `construction_year`, `building_material`) 

VALUES ('','1','2010-10-27 13:22:59','2010-10-27 13:22:59','2130706433','COMMERCE ST.','85825','OK','73521','commercial','595000','0','0','0','0','0','11','','Aluminum Siding')

And it throws me this error: Invalid query: Column count doesn't match value count at row 1. Although, when I paste and run the same exact query in PhpMyAdmin, it works perfectly, so it got me quite confused...

I counted the number of columns and the the number of values, and they match (19). I tried to remove the 'id' field, since it's auto-incremented, but it didn't change anything. What am I doing wrong? And why does it work in PhpMyAdmin?

Thanks for any help!

EDIT:

here's the php code:

$values = array('', 1, $lastUpdated, $entry_date, $entry_ip, $streetName, $cityId, $listing['stateorprovince'], $listing['postalcode'], $listing['type'], $listing['listprice'], $has_garage, $has_indoor_parking, $has_outdoor_parking, $has_pool, $has_fireplace, $average_nb_room, $listing['yearbuilt'], $listing['exteriortype']); 

$q = "INSERT INTO `lib_plex` (`id`, `active`, `lastUpdated`, `entry_date`, `entry_ip`, `address`, `city`, `state_iso`, `zip_code`, `plex_type`, `price`, `has_garage`, `has_indoor_parking`, `has_outdoor_parking`, `has_pool`, `has_fireplace`, `average_nb_room`, `construction_year`, `building_material`) 
VALUES ('".htmlentities(implode("','",$values),ENT_QUOTES)."')";

$this->execMysqlQuery($q);

and the method that is being called:

private function execMysqlQuery($q, $returnResults = false, $returnInsertId = false){
$c = mysql_connect(DB_SERVER,DB_LOGIN,DB_PASSWORD);
mysql_select_db(DB_NAME, $c);

$result = mysql_query($q);
if (!$result) {
    die('Invalid query: ' . mysql_error(). "<br/>=>".$q);
}

if ($returnInsertId)
    return mysql_insert_id();

mysql_close($c);

if ($returnResults)
    return $result;

return true;
}

And the error:

Invalid query: Column count doesn't match value count at row 1
=>INSERT INTO `lib_plex` (`id`, `active`, `lastUpdated`, `entry_date`, `entry_ip`, `address`, `city`, `state_iso`, `zip_code`, `plex_type`, `price`, `has_garage`, `has_indoor_parking`, `has_outdoor_parking`, `has_pool`, `has_fireplace`, `average_nb_room`, `construction_year`, `building_material`) VALUES ('','1','2010-10-27 13:47:35','2010-10-27 13:47:35','2130706433','COMMERCE ST.','85825','OK','73521','commercial','595000','0','0','0','0','0','11','','Aluminum Siding')
+1  A: 

If you print $q, I'm willing to bet it'll look like this:

INSERT INTO `lib_plex` (`id`, `active`, `lastUpdated`, `entry_date`, `entry_ip`, `address`, `city`, `state_iso`, `zip_code`, `plex_type`, `price`, `has_garage`, `has_indoor_parking`, `has_outdoor_parking`, `has_pool`, `has_fireplace`, `average_nb_room`, `construction_year`, `building_material`) 
VALUES ('&#39;,&#39;1&#39;,&#39;2010-10-27 13:22:59&#39;,&#39;2010-10-27 13:22:59&#39;,&#39;2130706433&#39;,&#39;COMMERCE ST.&#39;,&#39;85825&#39;,&#39;OK&#39;,&#39;73521&#39;,&#39;commercial&#39;,&#39;595000&#39;,&#39;0&#39;,&#39;0&#39;,&#39;0&#39;,&#39;0&#39;,&#39;0&#39;,&#39;11&#39;,&#39;&#39;,&#39;Aluminum Siding');

(I don't have PHP at work; this is a guess)

In other words, htmlentities is turning your quotes into HTML Entities. Specifically, turning ' to &#39;

Don't use htmlentities on things that aren't being sent to the web browser. Use your database driver's escaping method (mysql_real_escape_string) on each individual value being sent in.

Edit: Better yet, use prepared statements and data binding with MySQLi or PDO, which will automatically escape the data as you bind it.

R. Bemrose
Oh my god, thanks for making me feel like an idiot :] that was it!
meowmix
+1 Nice one. ––
webbiedave
I would use MySQLi or PDO, but I'm working for a client and I'm not sure if I'm permitted to use PDO or MySQLi on their server. I'm forced to work in a custom php framework (not mine) that uses its own query methods, which are usually effective enough. It's just that in this case, I have to insert a big amount of entries and those methods seem to cause some memory leak when used massively... So I have to resort to basic mysqlagain, thank you so much!
meowmix