MySQL Query doesn"t update rows

I've got a weird problem here. I've been trying to find out whats wrong for several hours now. I've got a function that should update a table in my database and set the "date_vente" (date_sold for english) to NOW(). The query is mighty simple but it keeps reporting 1 matched row, 0 changed.

Problem is, the data should be changed. If i run the query manually, it works fine, if i run it from the php app, it fails...




//Marque le panier comme vendu

$sqlstring = "UPDATE ".$prefix_tables_panier."paniers SET date_vente = NOW() WHERE no_panier = ".$data_panier["no_panier"];

$result = mysql_query($sqlstring, $conn_panier);

echo $sqlstring.'<br>';

echo mysql_affected_rows($conn_panier); echo '<br>';

echo mysql_error($conn_panier); echo '<br>';

echo mysql_errno($conn_panier); echo '<br>';

var_dump($conn_panier); echo '<br>';

var_dump(mysql_error($conn_panier)); echo '<br>';

var_dump(mysql_info($conn_panier)); echo '<br>';

var_dump(mysql_stat($conn_panier)); echo '<br>';

And the output is




UPDATE panier_paniers SET date_vente = NOW() WHERE no_panier = 840

0



0

resource(5) of type (mysql link)

string(0) ""

string(40) "Rows matched: 1 Changed: 0 Warnings: 0"

string(145) "Uptime: 1640423 Threads: 5 Questions: 39673341 Slow queries: 0 Opens: 1132 Flush tables: 1 Open tables: 235 Queries per second avg: 24.185" 7

The problem is it should write a value of NOW to the table containing the baskets but it is not. And no, it's not because of a wrong connection, the same connection id (var_dumped it) is used in the function just before, calling it and they both have the same id, the connection is still open and there is a value of "0000-00-00 00:00:00" in the column i'm trying to update...

Can anyone give me some more hints to look at?

Thanks

UPDATE




UPDATE panier_paniers SET date_vente = NOW() WHERE no_panier = 840

0



0

resource(5) of type (mysql link)

string(0) ""

string(40) "Rows matched: 1 Changed: 0 Warnings: 0"

string(145) "Uptime: 1641927 Threads: 5 Questions: 39681590 Slow queries: 0 Opens: 1132 Flush tables: 1 Open tables: 235 Queries per second avg: 24.168"



UPDATE panier_paniers SET date_vente = "2011-12-28 12:00:17" WHERE no_panier = 840

1



0

resource(5) of type (mysql link)

string(0) ""

string(40) "Rows matched: 1 Changed: 1 Warnings: 0"

string(145) "Uptime: 1641927 Threads: 5 Questions: 39681591 Slow queries: 0 Opens: 1132 Flush tables: 1 Open tables: 235 Queries per second avg: 24.168"

go

This is getting weird, VERY VERY WEIRD!

UPDATE #2




CREATE TABLE IF NOT EXISTS `panier_paniers` (

  `no_panier` bigint(20) unsigned NOT NULL auto_increment,

  `client_prenom` varchar(100) NOT NULL default '',

  `client_nom` varchar(100) NOT NULL default '',

  `client_entreprise` varchar(150) default NULL,

  `client_adresse` varchar(150) NOT NULL default '',

  `client_ville` varchar(150) NOT NULL default '',

  `client_province_etat` char(2) default NULL,

  `client_pays` char(2) NOT NULL default '',

  `client_code_postal_zip` varchar(15) NOT NULL default '',

  `client_telephone` varchar(20) NOT NULL default '',

  `client_type_telephone` enum('domicile','travail','cellulaire') NOT NULL default 'domicile',

  `client_telecopieur` varchar(20) default NULL,

  `client_courriel` varchar(150) NOT NULL default '',

  `client_mailinglist_from` datetime default NULL,

  `client_langue` char(2) NOT NULL default '',

  `client_no_client` bigint(20) unsigned default NULL,

  `expedition_mode` varchar(20) NOT NULL default '',

  `expedition_no_livraison` bigint(20) unsigned default NULL,

  `expedition_produit` varchar(100) default NULL,

  `expedition_produit_no_compte` varchar(100) default NULL,

  `expedition_frais_livraison` decimal(10,2) NOT NULL default '0.00',

  `expedition_frais_manut` decimal(10,2) NOT NULL default '0.00',

  `expedition_assurance` char(1) NOT NULL default 'X',

  `expedition_signature` char(1) NOT NULL default 'X',

  `expedition_conf_livraison` char(1) NOT NULL default 'X',

  `expedition_emballage` text,

  `expedition_no_suivi` varchar(100) default NULL,

  `expedition_prenom` varchar(100) default NULL,

  `expedition_nom` varchar(100) default NULL,

  `expedition_entreprise` varchar(150) default NULL,

  `expedition_adresse` varchar(150) default NULL,

  `expedition_ville` varchar(150) default NULL,

  `expedition_province_etat` char(2) default NULL,

  `expedition_pays` char(2) default NULL,

  `expedition_code_postal_zip` varchar(15) default NULL,

  `expedition_telephone` varchar(20) default NULL,

  `expedition_type_telephone` enum('domicile','travail','cellulaire') NOT NULL default 'domicile',

  `expedition_exporte_ups_connect` char(1) NOT NULL default '',

  `expedition_mis_a_jour_ups_connect` char(1) NOT NULL default '',

  `paiement_mode` varchar(20) NOT NULL default '',

  `paiement_no_paiement` bigint(20) unsigned default NULL,

  `paiement_numero_taxe_fed` bigint(20) unsigned NOT NULL default '0',

  `paiement_numero_taxe_prov` bigint(20) unsigned NOT NULL default '0',

  `paiement_numeros_taxes_speciales` varchar(255) NOT NULL,

  `paiement_numero_po` varchar(20) default NULL,

  `paiement_numero_bon` varchar(20) default NULL,

  `paiement_cc_pa_trnid` varchar(10) default NULL,

  `paiement_cc_pa_montant` decimal(10,2) NOT NULL default '0.00',

  `paiement_cc_capture` text,

  `paiement_desjardins_TxID` varchar(25) default NULL,

  `paiement_desjardins_date_envoi` datetime NOT NULL default '0000-00-00 00:00:00',

  `date_creation` datetime NOT NULL default '0000-00-00 00:00:00',

  `ip_creation` varchar(15) NOT NULL default '',

  `host_creation` varchar(255) default NULL,

  `date_vente` datetime NOT NULL default '0000-00-00 00:00:00',

  `date_annulee` datetime NOT NULL default '0000-00-00 00:00:00',

  `date_completee` datetime NOT NULL default '0000-00-00 00:00:00',

  `date_inventaire` datetime NOT NULL default '0000-00-00 00:00:00',

  `date_acceptation` datetime NOT NULL default '0000-00-00 00:00:00',

  `note` text,

  `note_client` text,

  `no_utilisateur` bigint(20) unsigned default NULL,

  PRIMARY KEY  (`no_panier`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=841 ;

UPDATE #3

Ok, look at this spooky thing:




//Marque le panier comme vendu

$sqlstring = 'UPDATE panier_paniers SET date_vente = NOW() WHERE no_panier = '.$data_panier["no_panier"];

$result = mysql_query($sqlstring, $conn_panier);

echo $sqlstring.'<br>';

echo mysql_affected_rows($conn_panier); echo '<br>';

echo mysql_error($conn_panier); echo '<br>';

echo mysql_errno($conn_panier); echo '<br>';

var_dump($conn_panier); echo '<br>';

var_dump(mysql_error($conn_panier)); echo '<br>';

var_dump(mysql_info($conn_panier)); echo '<br>';

var_dump(mysql_stat($conn_panier)); echo '<br>';



$sqlstring='UPDATE panier_paniers SET date_vente  = "2011-12-28 10:09:12" WHERE no_panier = '.$data_panier["no_panier"];

mysql_query($sqlstring, $conn_panier);

echo $sqlstring.'<br>';

echo mysql_affected_rows($conn_panier); echo '<br>';

echo mysql_error($conn_panier); echo '<br>';

echo mysql_errno($conn_panier); echo '<br>';

var_dump($conn_panier); echo '<br>';

var_dump(mysql_error($conn_panier)); echo '<br>';

var_dump(mysql_info($conn_panier)); echo '<br>';

var_dump(mysql_stat($conn_panier)); echo '<br>';

As you can see, both should be valid, the first one will not update anything, the second does update the row. Switch around and test again:




$sqlstring='UPDATE panier_paniers SET date_vente  = "2011-12-28 10:09:12" WHERE no_panier = '.$data_panier["no_panier"];

mysql_query($sqlstring, $conn_panier);

echo $sqlstring.'<br>';

echo mysql_affected_rows($conn_panier); echo '<br>';

echo mysql_error($conn_panier); echo '<br>';

echo mysql_errno($conn_panier); echo '<br>';

var_dump($conn_panier); echo '<br>';

var_dump(mysql_error($conn_panier)); echo '<br>';

var_dump(mysql_info($conn_panier)); echo '<br>';

var_dump(mysql_stat($conn_panier)); echo '<br>';



//Marque le panier comme vendu

$sqlstring = 'UPDATE panier_paniers SET date_vente = NOW() WHERE no_panier = '.$data_panier["no_panier"];

$result = mysql_query($sqlstring, $conn_panier);

echo $sqlstring.'<br>';

echo mysql_affected_rows($conn_panier); echo '<br>';

echo mysql_error($conn_panier); echo '<br>';

echo mysql_errno($conn_panier); echo '<br>';

var_dump($conn_panier); echo '<br>';

var_dump(mysql_error($conn_panier)); echo '<br>';

var_dump(mysql_info($conn_panier)); echo '<br>';

var_dump(mysql_stat($conn_panier)); echo '<br>';

RESULT:




UPDATE panier_paniers SET date_vente = "2011-12-28 10:09:12" WHERE no_panier = 840

1



0

resource(5) of type (mysql link)

string(0) ""

string(40) "Rows matched: 1 Changed: 1 Warnings: 0"

string(145) "Uptime: 1643141 Threads: 5 Questions: 39713580 Slow queries: 0 Opens: 1286 Flush tables: 1 Open tables: 219 Queries per second avg: 24.169"

UPDATE panier_paniers SET date_vente = NOW() WHERE no_panier = 840

1



0

resource(5) of type (mysql link)

string(0) ""

string(40) "Rows matched: 1 Changed: 1 Warnings: 0"

string(145) "Uptime: 1643141 Threads: 5 Questions: 39713581 Slow queries: 0 Opens: 1286 Flush tables: 1 Open tables: 219 Queries per second avg: 24.169"

Now both updates work... I tried a verify, repair and optimize to see if it wasn't a corruption issue, nothing changed...

Source: Tips4all, CCNA FINAL EXAM

Comments

UserMay 10, 2012 at 3:04 AM
So the problem was after all an issue with logic and bad coding practices. The problem came from the previous programmers who liked to have no structure and place code in weird places. I found the error outside of the scope of what we where looking for.

I might say, if you get such an error, look for flaws in your code where a same query could be executed right before the current one that SEEMS to fail. That will save you HOURS of trouble looking for a solution that doesn't pertain to the problem at hand.

CCNA, CCNP, MCSA, CCNA Final Exam, All Answer Test Module With 100/100

Search This Blog

MySQL Query doesn"t update rows

Labels

Comments

Post a Comment

Popular posts from this blog

Create Subdomains on the fly with .htaccess (PHP)

Wildcards in a hosts file

Senior Java EE interview questions [closed]