Jump to content

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


vincent

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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" ???

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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....

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.