有缺陷的插入算法[关闭]

Question

I'm in the process of implementing a system that keeps track of all instances when a product goes in and out of stock. I have a column in the table that keeps track of whether or not a given record is open or closed (open meaning it's out of stock and hasn't been restocked and closed meaning it came back into stock at some point). Up to this point, I haven't had that many issues with SQL queries when it came to making sure they worked. However, I've been running the script used for this table population and nothing is being inserted into the table.

The functions I use for this specific table's updates are as follows.

//Records item as being back in stock
function itemInStock($item){
    $db_controller = new DBHandler();
    $selectQuery = "SELECT * FROM out_of_stock_record
                    WHERE ListID = '$item->ID'
                    AND Status = 'OPEN'";
    $rs = $db_controller->select($selectQuery);

    /*If this record exists, then the only thing we need to do is update the BackInStockTS and Status columns.  It came back in stock on this date at this time, therefore it's closed.*/
    if($rs){
        $currentDate = date("Y-m-d H:i:s");
        $updateQuery = "UPDATE out_of_stock_record 
                        SET BackInStockTS = '$currentDate', Status = 'CLOSED' 
                        WHERE ID = '$item->ListID' AND Status = 'OPEN'";
        $db_controller->update($updateQuery);
    }
}

//Records item as being out of stock
function itemOOStock($item){
    $db_controller = new DBHandler();
    $selectQuery = "SELECT ID FROM out_of_stock_record
                    WHERE ID = '$item->ID'
                    AND Status = 'OPEN'";
    $rs = $db_controller->select($selectQuery);

    /*We only need to make sure the record DOESN'T exist, because if that's true, then all we need to do is insert this.  If it already exists, there's nothing to do.
      The only other check occurs later on that sees if the item is back in stock, then updates the existing open record in order to close it and record the date it
      came back in stock.*/
    if(!$rs){
        $currentDate = date("Y-m-d H:i:s");
        $start = "INSERT INTO out_of_stock_record (ID, OutOfStockTS, BackInStockTS, Status) VALUES (";
        $end = sprintf("'%s', '%s', NULL, '%s')", $item->ID, $currentDate, "OPEN");
        $start .= $end;
        $db_controller->insert($start);
    }
}

I don't know what the issue is. I use the exact same database handler for insertion on a differently-named table in another part of this script that I have zero issues with, and I'm not sure it has something to do with the table's structure. It consists of varchar columns for both ID and status, datetime columns for the OOS and IS dates, and I included another column for recording the last update for this table, but that just changes automatically when insertion occurs.

I haven't had any issues with this script before aside from some that I unintentionally created and later corrected. The table is supposed to update every time I visit the URL for this page. Any suggestions on what may be causing this? If it helps, the database this script uses and the site that it's hosted on are on the same server.

Answer 1

I was using the wrong check for the if statements. They should have looked like these.

if(mysql_num_rows($rs) == 0){
    //Insert record
}

if(mysql_num_rows($rs) != 0){
    //Update record
}