设置cron作业以根据其投票值更新文章信息 [英] Set a cron job to update article information depending on its vote values

问题描述

我想设置一个每15分钟执行的cron作业。在深入描述这些查询之前，我将解释我的数据库是如何工作的。

I want to set a cron job that will execute every 15 minutes. Before going into deep description of these query I'll explain how my database works.

我有两个表故事 > votes 。有关文章（例如标题，类别等）的所有信息都存储在故事表格中。与文章相关的所有投票都存储在投票表格中，这并不奇怪。在投票表中，有一个名为 item_name 的字段，与story表中的 id 对应。最后，在故事表中，我有一个名为显示的字段，默认设置为0.我想将其更改为不同的值，

I have 2 tables 'stories' and 'votes'. All information about article like title, category etc. is stored in the 'stories' table. It's not a big surprise that all the votes related to article are stored in a 'votes' table. In the votes table there is a field named 'item_name' which corespondents to the 'id' of article that is in 'stories' table. Finally, in 'stories' table I have a field named 'showing' which is by default set to 0. And I want to change it to different values depending on how many votes article has, but more about it latter.

这里是我的数据库的结构：

Here is a structure of my database:

故事表

投票表

现在，当我清除数据库结构时，我得到我的问题。我需要一个查询，看看所有文章，显示= 0，总结与特定文章通信的投票。如果投票总数= 10，则表示= 1，如果投票总数= -10，则表示= 2。我不知道这样的查询会怎么样，我真的不：/
任何人都可以建议一个解决方案吗？

Ok now when I cleared database structure lets get down to my question. I need a query that would look at all articles where showing=0 and sum up votes that corespondent to a particular article. If sum of votes = 10 than set showing=1 if sum of votes = -10 than set showing=2. I don't know how such query would look, I really don't :/ So could anyone suggest a solution please?

我问过类似的问题，有些人建议触发器或更新数据库每次投票。但我宁愿检查它每15分钟与cron工作。 ）））

Addition: I asked similar question before and some people suggested trigger's or updating database each time a vote is cast. But I would rather check it every 15 min with cron job. )))

EDIT：只是为了让您更了解其运作方式）））

Just to give you more understanding of how it works )))

例如，来自stories表格的此行：

For example this row from 'stories' table:

id | 12

st_auth | 作者姓名

st_date | 故事日期

st_title | 故事标题

st_category | 故事类别

st_body | 故事主体

显示| 1表示已批准，2表示未批准

id| 12
st_auth | author name
st_date | story date
st_title| story title
st_category| story category
st_body| story body
showing| 0 for pending, 1 for approved and 2 for unapproved

公司从。vote表中回复此表：

Co responds to this one from ;votes' table:

id | 83

item_name | 12（文章ID）

vote_value | 1 for upvote - 1 for downvote
etc ...

id| 83
item_name| 12 (id of article)
vote_value| 1 for upvote -1 for downvote etc...

推荐答案

select sum(vt.vote_value), vt.item_name
from Votes vt
join stories st on st.id = vt.item_name
and st.showing = 0
group by vt.item_name

这应该给你所有的文章显示0和总投票价值。

This should give you all the articles with showing 0 and total vote value. After that you can process them row by row and update voting if needed.

添加：
我不是一个PHP程序员，我主要做PERL，但我猜测它必须是这样的：

Added: I am not a PHP programmer, I mostly do PERL, but I guess it has to be something like this:

<?php 
    $query = "select sum(vt.vote_value) as vote_value, vt.item_name from Votes vt join stories st on st.id = vt.item_name and st.showing = 0 group by vt.item_name";
    $result = mysql_query($query); 
    while($row = mysql_fetch_array($result))
    {
        if($row['vote_value'] > 9) {
            $showing = 1;
        }
        else if($row['vote_value'] < -9) {
            $showing = 2;
        }
        else {
            $showing = 0;
        }
        $query2 = "UPDATE `stories` SET `showing` = $showing WHERE `id` = '".$row['item_name']."'";
        mysql_query($query2); 
    }
?>

这篇关于设置cron作业以根据其投票值更新文章信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！