mysql＆php - 更新int列，其中一个商已转换为int，给我留下了分子或零

Question

Please see the updates below!

This is what I want to do: Get the last update timestamp (stored as int) of an article, together with the popularity at the time of the last update (also int), calculate the new popularity and the resulting trend (= an estimate for the change of the popularity per time unit since the last update) and update the article.

The problem is, the value that gets written to the DB row is almost always either the numerator of the trend equation (so the calculated popularity) or 0. Static values for the popularity don't change a thing, however static values for last_update or time() result in the right value being inserted. Something else that's strange: Leaving last_update out of the update query also results in the right value for trend to be inserted. It's like the last_update column affects the trend column, I don't understand how.

This is my code, please note I'm using codeigniter so I used activerecord for getting the article. Plus I used intval() for nearly all numeric values because my first guess was a datatype problem:

$this->db->where('id', 1);
$article_query = $this->db->get('articles');
$article = $article_query->row_array();

$article['last_update'] = intval($article['last_update']);
$time = intval(time());
$new_popularity = // Calculate Popularity here (I tried with static values, doesn't affect the result) ;
$time_diff = intval(($time - $article['last_update']));

$trend = intval((($new_popularity - $article['popularity']) / $time_diff)); 

var_dump($trend);

$this->db->query('UPDATE `articles` SET `popularity` = '.$new_popularity.',  `trend` = '.$trend.' WHERE `id` =  1 ');

The var dump gives me an int with the expected value. The query log also shows the expected query, something like

UPDATE `articles` SET `popularity` = 50000, `last_update` = 1374840645, `trend` = 10 WHERE `id` =  1

But 50000 gets inserted for trend. As mentioned before, static values for $time and $article['last_update'] make the problem go away, so does leaving the last_update out of the query. I also tried ceil(), floor(), casting (int) on every single value, nothing works.

All the involved columns are int(11), which should be big enough. I also tried varchar for trend and inserting it as a string - no success. I have been dealing with this problem for 2 days and I'm really desperate for any help!

I'm running MySQL 5.5.32 on Windows 7.

Please make the weirdness go away, thank you!

edit: to clarify further: For testing purposes I set $article['popularity'] to 0, so I could test repeatedly and still get a trend >0. That's why 50000 got inserted and not the actual difference.

update: this is where my code is now. Set last_update to TIMEZONE type with ON UPDATE CURRENT_TIMESTAMP.

$this->db->where('id', 1);
$article_query = $this->db->get('articles');
$article = $article_query->row_array();

date_default_timezone_set('Europe/Berlin');
$article['last_update'] = intval(strtotime($article['last_update']));

$time = intval(time());
$new_popularity = 50000; // Static test value
$time_diff = intval(($time - $article['last_update']));

$trend = intval((($new_popularity - 0) / $time_diff)); // Zero for testing purposes only, so that there will always be a positive trend.

var_dump($trend);

$this->db->_protect_identifiers=false;
$this->db->query('UPDATE articles SET popularity = ?,  trend = ? WHERE id =  ?', Array($new_popularity, $trend, 1));

Query log:

UPDATE articles SET popularity = 50000, trend = 403 WHERE id =  1

Actual values via phpMyAdmin: popularity = 50000, trend = 50000. I'm also running the code on a clean install of apache and mysql now, with php 5.4.15, mysql 5.6.11. Next I'll try without codeigniter alltogether I guess...

UPDATE: My own mistake, I didn't read the log very carefully and didn't notice something like this occuring:

130730 17:25:48    51 Connect   root@localhost on zeenr
           51 Init DB   zeenr
           51 Query SET NAMES utf8
           51 Query SET SESSION sql_mode="STRICT_ALL_TABLES"
           51 Query SELECT *
FROM (`articles`)
WHERE `id` =  1
           51 Query UPDATE articles SET `popularity` = 50000, `trend` = 179 WHERE `id` =  1
           51 Quit  
130730 17:25:49    52 Connect   root@localhost on zeenr
           52 Init DB   zeenr
           52 Query SET NAMES utf8
           52 Query SET SESSION sql_mode="STRICT_ALL_TABLES"
           52 Query SELECT *
FROM (`articles`)
WHERE `id` =  1
           52 Query UPDATE articles SET `popularity` = 50000, `trend` = 50000 WHERE `id` =  1
           52 Quit  

Why is it happening? I have no loop whatsoever in my code.

Answer 1

To really know what query is run you should enable the MySql log:

SET GLOBAL general_log = 'ON';

After running the query there is a logfile in C:\xampp\mysql\data. (in my case)

Also try without protecting identifiers and with query bindings:

$this->db->query(
    'UPDATE articles SET popularity = ?, trend = ? WHERE id = ?',
    array($new_popularity, $trend, 1)
);

EDIT:

Have you tried the active record syntax?

$data = array
(
    'popularity' => $new_popularity,
    'trend' => $trend,
);

$this->db->where('id', 1);
$this->db->update('articles', $data);