[SOLVED] "UPDATE mytable SET test=true" working, then "...SET test=false" not working :(

[vincent]

Hi,

 

I've got an anoying problem: I'm trying to update a boolean in a table to true, then (later in my website) to false. The first update is working, the second is not.

 

If I try to comment the first update, then the second one starts to work! Am I missing something?

This is my code:

$resultatEtatOuvert = mysql_query ("UPDATE articles SET  ouvert=true WHERE id=4", $db);
if ($resultatEtatOuvert===FALSE)
{die("error");}

$sqlFermeture = "UPDATE articles SET  ouvert=false WHERE id=4";
$resultatEtatFerme = mysql_query($sqlFermeture, $db);
if ($resultatEtatFerme === FALSE)
{die("error");}
else
{
echo $sqlFermeture;
}

I don't get any of the "die" messages and I get "UPDATE articles SET  ouvert=false WHERE id=4" on the screen. But my "ouvert" value is still set to true. If I try to copy paste the message I get in mySQL query browser, it works! Could it be some kind of permissions problem?

[Wildbug]

What type is your column "ouvert?"  (Confirmed BOOLEAN?)

How are you determining whether or not the update is working?

Which is the above code, first or second update?

 

I see nothing wrong with the above code at this point.

[vincent]

My column "ouvert" is of type "TINYINT(1)"; I tried to update using 0 and 1 instead of true and false but i get the same result (first update working, second is not).

 

I check directly in the database on the server with mysql query browser to check if the update is working. I see the change from 0 (initial value) to 1, but not from 1 to 0.

 

from the beginning to  "die("error")" it's the first update on one of my pages.

from "$sqlFermeture" to the end it's the second update on another page.

 

I even tried updating other columns in the second update and they do change, it's just the "ouvert" column that won't change back to 0.

[Illusion]

Did you use NOT NULL constraint on ouvert column?

Post your table structure.

[vincent]

No I did not use the not null constraint on the ouvert column. here is my table structure:

DROP TABLE IF EXISTS `apiweblocales`.`articles`;
CREATE TABLE  `apiweblocales`.`articles` (
  `id` int(11) NOT NULL auto_increment,
  `idUtilisateur` int(11) default NULL,
  `titre` varchar(200) default NULL,
  `surtitre` varchar(200) default NULL,
  `texte` text,
  `priorite` int(11) default NULL,
  `idEdition` int(11) default NULL,
  `semaine` varchar(50) default NULL,
  `idVille` int(11) default NULL,
  `idPresentation` int(11) default NULL,
  `photo1` varchar(50) default NULL,
  `photo2` varchar(50) default NULL,
  `photo3` varchar(50) default NULL,
  `photo4` varchar(50) default NULL,
  `libelle1` varchar(50) default NULL,
  `libelle2` varchar(50) default NULL,
  `libelle3` varchar(50) default NULL,
  `libelle4` varchar(50) default NULL,
  `dateDernierEnregistrement` datetime default NULL,
  `etat` tinyint(4) default NULL,
  `signature` varchar(50) default NULL,
  `nbrCharTexte` int(11) default NULL,
  `ouvert` tinyint(1) default NULL,
  PRIMARY KEY  USING BTREE (`id`),
  KEY `fkUtilisateur` (`idUtilisateur`),
  KEY `fkVille` (`idVille`),
  KEY `fkPresentation` (`idPresentation`),
  KEY `fkEdition` (`idEdition`),
  CONSTRAINT `fkEdition` FOREIGN KEY (`idEdition`) REFERENCES `editions` (`id`),
  CONSTRAINT `fkPresentation` FOREIGN KEY (`idPresentation`) REFERENCES `presentations` (`id`),
  CONSTRAINT `fkUtilisateur` FOREIGN KEY (`idUtilisateur`) REFERENCES `utilisateurs` (`id`),
  CONSTRAINT `fkVille` FOREIGN KEY (`idVille`) REFERENCES `villes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

In my second update, updating any other column from this table works, but not "ouvert" ???

[Wildbug]

This certainly is a puzzler.  (I must not have seen your edit when I first posted re: is that first or second update.)

Are you connecting with the same code/user/pass in both cases?

 

Can you try checking for mysql errors after the query?  Something like the following:

$resultatEtatFerme = mysql_query($sqlFermeture, $db);
if (mysql_errno()) printf("SQL error: %s<br>\nOn query: %s<br>\n",mysql_error(),$sqlFermeture);

[vincent]

I tried checking for mysql errors after the query, but didn't get any errors.

 

In both cases, I'm calling the same connection methods (with constants for HOST,LOGIN,PASSWORD in it).

 

There must be a mysql restriction that prevents me from changing this value with php again after I just changed it, but how can my mysql_query not return something === to false then?

 

The strangest thing is that I can modify any other column inside the second update and it's just the 'ouvert' column that is not updating.

[vincent]

I just found out something, it's not the "ouvert" column that is having a problem: any column I modify in my first update query cannot be modified in the second update query!

 

Is there a problem doing two update query one after the other in php? (making a new connection to the db in between). I already tried doing a commit between the two, but no success....

[Wildbug]

Is it that you can't modify any column previously modified in the first query, or that you cannot modify any column at all in the second query?  I.e., if you update one column, then try to update that column and another column, do neither columns get changed, or only the one you didn't update in the first query?

 

Try printing mysql_info() after the UPDATE(s).

 

What PHP/MySQL versions are you using?

[vincent]

I found the problem! 

Thanks a lot for your help  Wildbug, details incoming as soon as I understand the details

[Wildbug]

Cool.  I'll be interested to see what it was.

[vincent]

It was quite stupid actually. I'm a beginner in PHP and the website I'm working on was done colaborativelly so I did not had the whole picture of how this was working.

 

There was a page were the first update is made (in the <body>). At the end of this pages there is a validation button that simply reloads this page.

 

At the top of this page there is an include to another php file where, after a condition is met (triggered by the validation button), the second update is made, and then there is the line:

header('Location: anotherpage.php');

 

I was told that this line would redirect the first page. Actually, what it must have done was calling the first update again. What I was doing was update1->update2->update1.

 

This is a beginner's mistake I guess but you helped me dig deeper in the code and understand the big picture. Thanks again Wildbug

 

edit: forgot to mention how I resolved the problem: I put an exit() right after the "header(...)" line. Not sure it's the cleaner way to do it but at least it's working as intended.

 

[Wildbug]

So it seemed like you were going in circles... because you really were going in circles!    Glad to hear you're sorted.  Good luck on the rest.