mysql & php - update int column with a quotient already converted to an int leaves me with numerator or zero
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));
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 [email protected] 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 [email protected] 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.