CODEWITHSUNDEEP
phpmysql
Victor
Victor

Reputation: 621

SELECT and than UPDATE updates 2 rows instead of one

This is driving me crazy for few hours:

I have a list of manufacturers and they have to be updated from time to time. I have to select the oldest updated and then write the current time as the last update time.

This is my script (part of a function inside a class):

    public function get_manufacturer () { 

            $sql = '
            SELECT manufacturer_id FROM
            manufacturer
            ORDER BY last_update LIMIT 1
            ';

            $query = $this->query($sql);
            $manufacturer = $query->row;

            //echo $manufacturer['manufacturer_id'];

            $sql = 'UPDATE manufacturer
            SET last_update = '.time().'
            WHERE manufacturer_id ='. $manufacturer['manufacturer_id'];
            $query = $this->query($sql);

            return $manufacturer;

     }

The problem is that the update query UPDATES TWO ROWS, instead of one, even if I put LIMIT 1. It updates the row with the correct manufacturer_id and also the next one.

ANOTHER ODD THING IS THAT EVERYTHING WORKS NORMALLY IF I PUT AN ECHO BETWEEN THE TWO QUERIES.

I've noticed also that everything seems to work normally if I don't use ORDER BY. (Unfortunately I need ORDER BY in my query).

I was thinking that I have to clear some cache or something like this between the two queries. Please advice!

UPDATE

This is the query function, but I've tried just with mysql_query() instead of this function and the result is the same.

 public function query($sql) {
    $resource = mysql_query($sql, $this->connection);

    if ($resource) {
        if (is_resource($resource)) {
            $i = 0;

            $data = array();

            while ($result = mysql_fetch_assoc($resource)) {
                $data[$i] = $result;

                $i++;
            }

            mysql_free_result($resource);

            $query = new stdClass();
            $query->row = isset($data[0]) ? $data[0] : array();
            $query->rows = $data;
            $query->num_rows = $i;

            unset($data);

            return $query;  
        } else {
            return TRUE;
        }
    } else {
        exit('Error: ' . mysql_error($this->connection) . '<br />Error No: ' .    mysql_errno($this->connection) . '<br />' . $sql);
            }
}

UPDATE 2

I have put the same question again but with a simplified (and complete) code that you can test on your own server. See here: Why UPDATE used after SELECT chages 2 rows instead of one?

Thank you for your help!

Upvotes: 0

Views: 125

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

Either manufacturer_id is not UNIQUE or you are calling the function twice.

Upvotes: 1

endyourif
endyourif

Reputation: 2202

Try selecting the last_update field in the first query and add it as an AND clause in the update statement to ensure some more uniqueness.

Upvotes: 0

Related Questions