PHP script to update an 'open' or 'close' status in a database has some bugs I cant figure out

Question

Hoping for some help! :)

Background of problem:
I have an SQL database of unique items with three columns: [index] [id] [status]
[index] = incremented #
[id] = unique string id name
[status] = either a '0' which represents an open work station or a '1' which represents an occupied workstation

My PHP script takes an array of ids I want to change the status of from a webpage with the POST/AJAX method
it then assigns each value in this array to a local array of string values
then it uses the $array_unique function to remove any duplicate values from the input array
so far so good no problems...

//import array of all points to change - there may be duplicates at this stage
$dataI = json_decode(stripslashes($_POST['dataI']));

  foreach($dataI as $e){
 $updatePOST[$b] = $e;
 $b++;
  }

//remove duplicate values from array
$unique = array_unique($updatePOST);
echo ("The following positions have been changed:");

    //this prints out a list of each unique item to change
    foreach($unique as $f){
        $uniqueModify[$c] = $f;
        echo "[";
        echo $uniqueModify[$c];
        echo "], ";
        $c++;
        }

the script then connects to the database and assigns each row and its column values to local arrays

//cycle through rows in the database table.
$query = "SELECT * FROM $usertable";
$result = mysql_query($query);

// for each row in the database assign index value, id (unique name of each changeable item), and status (1/0) of each item to arrays
if ($result) {
while($row = mysql_fetch_array($result)) {
        $index[$i] = $row["index"];
        $id[$i] = $row["id"];
        $status[$i] = $row["status"];
        //echo $status[$i];
        $i++;
        $j++; //stores and increments array length value
    }
}

still no issues.
this is where I get to my problem. if the database's state begins with all statuses set to '0' then I am able to change the id's status to '1' no problem. Inversely, if the database's state begins with all '1' then I am able to change any ID's status to '0' no problem, and examining the database reflects accurate changes being made to the specific unique IDs from the $uniqueModify array

what happens next is that if I want to change an ID's status back from '0' to '1' or from '1' to '0' after an initial status change for the ID I get all sorts of mixed results. Sometimes it will change one or two of the values in the POSTed input array, sometimes it will change none. there does not appear to be any rhyme or reason as to why it changes some and not others that I can figure out. The echoes I have set up still indicate that the appropriate part of the code has been reached, yet the change is not reflected in the database. This only happens after the first state change has occurred for each ID, running hte PHP again with a new set of IDs will still change them once

//cycle through array result of database and if the id matches the unique input item change the status from 1 to 0 or from 0 to 1
for ($l=0;$l<$j;$l++) {
if ($id[$l] = $uniqueModify[$l])
{
    $idUPDATE = $id[$l]; //stores id to a non-array value - not sure if necessary
    if ($status[$l] == 0)
        {
            echo " Close"; //close the location, ie change 0 (unoccupied) to 1 (occupied)
            $update = "UPDATE $usertable SET status='1' WHERE id='$idUPDATE'";
        }
    if ($status[$l] == 1)
        {
            echo " Open"; //close the location, ie change 0 (unoccupied) to 1 (occupied)
            $update = "UPDATE $usertable SET status='0' WHERE id='$idUPDATE'";
        }
    //check that code has made changes to the table
    $result = mysql_query($update);
    if($result){
        echo "- result successfully changed ";
    }
}
}

mysql_close($link);

I just find it very odd that the code reaches the correct point, and will work to change an initial state in both 1/0 directions but begins to fail after that point. it does not appear to be an issue with the unique ID names, because it will change all unique names on the first run. I thought maybe it had to do with cached data but I am using Chrome's developer mode to test with cache disabled and still get the same results.

thanks for taking a look!

Answer 1

So I'm going to go ahead and suggest you make a major change to your code: don't use mysql_* functions as they are deprecated.

Here's a solution that replaces a lot of your code AND uses PDO library (instead of mysql_). You'll have to replace database connection info, of course. It really does replace a lot so, if I took out some functionality that I shouldn't let me know and I'll slip it back in.

<?php
// array_unique takes an array as a parameter and returns what that array would be with no duplicates... description is really in the function name.
$dataI = array_unique(json_decode(stripslashes($_POST['dataI'])));
// configuration // change variable values as appropriate
$dbhost = "localhost";
$dbname = "yourDbName";
$user = "yourUserName";
$pass = "youPassword";

try {
    // open connection to databae
    $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $user, $pass);

    // prepare query // read up on this. there's good stuff out there
    // a CASE statement is like an if else if statement
    $stmt = $conn->prepare("UPDATE $usertable SET status = (CASE WHEN status='1' THEN '0' WHEN status='0' THEN '1' END) WHERE id=:id");
    $stmt->bindParam(':id', $idUPDATE); // note how $query has :id at the end. Using ->prepare() and ->bindParam in this way makes building queries (IMO) easier
    // now, since we prepared the statement and bound the parameter to $idUPDATE, all we need to do to update multiple rows is loop like so:
    foreach($dataI as $idUPDATE) {
        $result = $stmt->execute();
    }
    // always perform clean-up
    $stmt->closeCursor();
    $conn = null;
} catch (PDOException $e) { // always perform error checking on PDO
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>

Now, here's another version of the above code but with some SELECT statements before and after the updates happen so you can see the changes.

<?php
$dataI = array_unique(json_decode(stripslashes($_POST['dataI'])));
// configuration
$dbhost = "localhost";
$dbname = "yourDbName";
$user = "yourUserName";
$pass = "youPassword";

try {
    $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $user, $pass);
    echo "<pre>"; // makes it easier to see the var_dumps
    ///// left this SELECT purely for testing //////
    // creates an array of the same length as $dataI and fills it with '?'
    // lets us bind an unknown # of values. Honestly, we could do this with the UPDATE
    // and avoid the foreach loop and yet keep the protection from SQL injection
    $inQuery = implode(',', array_fill(0, count($dataI), '?'));
    $selectBefore = $conn->prepare("SELECT myIndex, id, status FROM $usertable WHERE id IN (" . $inQuery . ")");
    $selectBefore->execute($dataI);
    var_dump($selectBefore->fetchAll(PDO::FETCH_ASSOC));
    $selectBefore->closeCursor();
    ///// left this SELECT purely for testing //////

    $stmt = $conn->prepare("UPDATE $usertable SET status = (CASE WHEN status='1' THEN '0' WHEN status='0' THEN '1' END) WHERE id=:id");
    $stmt->bindParam(':id', $idUPDATE);

    foreach($dataI as $idUPDATE) {
        $result = $stmt->execute();
        ///// more code just to see what results look like /////
        var_dump($result,$stmt->rowCount());
        ///// more code just to see what results look like /////
    }
    $stmt->closeCursor();

    ///// left this SELECT purely for testing //////
    $selectAfter = $conn->prepare("SELECT myIndex, id, status FROM $usertable WHERE id IN (" . $inQuery . ")");
    $selectAfter->execute($dataI);
    var_dump($selectAfter->fetchAll(PDO::FETCH_ASSOC));
    $selectAfter->closeCursor();
    ///// left this SELECT purely for testing //////
    echo "</pre>done";
    // don't forget to cleanup by closing the connection when you're done with it
    $conn = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}
?>

You might want to test this by using the second script and visiting the AJAX page directly in your browser. After testing, be sure to remove any unnecessary print/echo/var_dump statements.

Links for reference:

PDO tutorial at tutsplus

PHP documentation for PDO