Date: 03/11/07 (PHP Community)    Keywords: php, mysql, database, sql

Hi, folks. I've been pulling my hair out over this one. I have a class that's supposed to serve as a base class for a variety of others; it's a sort of PHP version of Rails' ActiveRecord. Object properties map to columns in an associated database table. One of the columns is hide, a column that holds either 0 or 1. If a given row has a 1 in its hide column, the framework I'm building should omit the item from display.

So, I have an is_hidden() function that returns the object's status (as a Boolean), and the method that's giving me fits is toggle_hidden(), which takes an optional argument: basically, you can "toggle the hidden-state on", toggle it off, or "just toggle it", meaning flip it from whichever state it's in to the other one.

So that method looks like this:

function toggle_hidden($state = 'toggle') {
     global $DBH;
     if (is_string($state)) {
          $state = strtolower($state);
     }
     # Add some syntactic sugar to make life sweet...
     if ($state == 'off' || $state == 'false' || $state == 'no' || $state == false) {
          $state = 0;
     } elseif ($state != 'toggle') {
          $state = 1;
     } else {
               $state = (int) $this->is_hidden();
               $state = 1 - $state;
     }
     $sql = "UPDATE $this->_db_table SET hide = '$state' WHERE id = '$this->id'";
     $dbq = mysql_query($sql);
     if (! $dbq) {
          $this->_errormsg = "Unable to set 'hide' state of $this->_objname #this->id to $state; SQL error: " . mysql_error();
          return false;
     }
     return true;
}

($DBH is a global database handle that's already been set up; the object properties $id, $_db_table, $_objname and $_errormsg all do exactly what it seems like they should. They're not the problem.)

If I call toggle_hidden(1) or toggle_hidden(0), I can force an object's state to hidden or visible, no problem. But if I call the method with no argument, it claims it's doing the right thing... but nothing changes.

Even worse, I wrote up this version with loads of print statements for debugging:

function toggle_hidden($state = 'toggle') {
     global $DBH;
     print "INITIAL CALL: toggle_hidden($state)
\n";
     if (is_string($state)) {
          $state = strtolower($state);
     }
     if ($state == 'off' || $state == 'false' || $state == 'no' || $state == false) {
          $state = 0;
          print "I'm forcing \$hide to $state
\n";
     } elseif ($state != 'toggle') {
          $state = 1;
          print "forcing \$hide to $state
\n";
     } else {
               $state = (int) $this->is_hidden();
               print "state was originally $state; ";
               $state = 1 - $state;
               print "converting to $state
\n";
     }
     $sql = "UPDATE $this->_db_table SET hide = '$state' WHERE id = '$this->id'";
     print "SQL Query: $sql
\n";
     $dbq = mysql_query($sql);
     if (! $dbq) {
          $this->_errormsg = "Unable to set 'hide' state of $this->_objname #this->id to $state; SQL error: " . mysql_error();
          return false;
     }
     print "affected " . mysql_affected_rows() . " rows
\n";
     $sql = "SELECT id, hide FROM $this->_db_table";
     # Tell me what *really* happened...
     print "
\n$sql:
\n";
     $dbq = mysql_query($sql);
     while (list($id, $hide) = mysql_fetch_row($dbq)) {
          print "     $id [hide] = $hide
\n";
     }
     print "
\nreturning true
\n";
     return true;
}

When I run it, it not only claims it's doing what it should; it then runs the "tell me what *really* happened" section and claims that the database now has the new values.

Except that it doesn't. So that final "SELECT id, hide FROM $this->_db_table" call is effectively returning invalid data?

Here's sample output from a run of this method.

Loaded object Practitioner id #2; hide is '1'
INITIAL CALL: toggle_hidden(toggle)
state was originally 1; converting to 0
SQL Query: UPDATE practitioners SET hide = '0' WHERE id = '2'
affected 1 rows

SELECT id, hide FROM practitioners:
     1 [hide] = 1
     2 [hide] = 0

returning true

Despite what the last few lines of output claim, the database has not changed; I can query the database with any other tool (including other pages from this very same framework!) and see that both rows still have 1 in their hide column.

Does anyone here have any clue what's going on, and how I can get my database queries to stop lying to me? (It seems almost like I need to commit the change, but I'm not using transactions...)

Source: http://community.livejournal.com/php/551083.html