PHP更新基于其他表的表数据

I am trying to create a cron job that will select the sum of points from a transaction table.

我正在尝试创建一个cron作业,它将从事务表中选择点的总和。

Based on the Sum of the points and the employee id I must update the total point table. I want to make sure that I am using the best method and that this will work.

根据积分和员工ID,我必须更新总积分表。我想确保我使用最好的方法,这将有效。

 <?php

    $conn = mysql_connect("localhost", "mysql_user", "mysql_password");

    if (!$conn) {
        echo "Unable to connect to DB: " . mysql_error();
        exit;
    }

    if (!mysql_select_db("mydbname")) {
        echo "Unable to select mydbname: " . mysql_error();
        exit;
    }

    $sql = "SELECT ID, SUM(POINTS) as Points, FROM Transactions WHERE Status = 1 Group By ID";

    $result = mysql_query($sql);

    if (!$result) {
        echo "Could not successfully run query ($sql) from DB: " . mysql_error();
        exit;
    }

    if (mysql_num_rows($result) == 0) {
        echo "No rows found, nothing to print so am exiting";
        exit;
    }


    while ($row = mysql_fetch_assoc($result)) {  

    mysql_query("UPDATE Totals SET Points=" + $row["Points"] + "WHERE ID=" +  $row["id"]);

    }


    mysql_free_result($result);

    ?>

2 个解决方案

#1

You can still join tables (and subqueries) on UPDATE statements. Try this one,

您仍然可以在UPDATE语句上连接表(和子查询)。试试这个,

UPDATE Totals a 
        INNER JOIN
        (
            SELECT ID, SUM(POINTS) as Points, 
            FROM Transactions 
            WHERE Status = 1 
            Group By ID
        ) b
        ON a.ID = b.ID
SET a.Points = b.Points

Hope this helps.

希望这可以帮助。

example of using PDO Extension (Code Snippet).

使用PDO扩展(代码片段)的示例。

<?php

$query = "UPDATE Totals a 
            INNER JOIN
            (
                SELECT ID, SUM(POINTS) as Points, 
                FROM Transactions 
                WHERE Status = ? 
                Group By ID
            ) b
            ON a.ID = b.ID
    SET a.Points = b.Points";

$iStatus = 1;
$stmt = $dbh->prepare($query);
$stmt->bindParam(1, $iStatus);

$stmt->execute();

?>

PDO Manual
PDO PreparedStatement

PDO手册PDO PreparedStatement

#2

Why can't you just test the script out out before you run it via a cron job? I can't spot anything that's wrong with the syntax; but then again, I only gave it a quick glance and I don't know what your table structure is like.

为什么不能在通过cron作业运行之前测试脚本?我无法发现语法上的任何错误;但话又说回来,我只是快速浏览一下,我不知道你的桌子结构是什么样的。

If you're looking for the BEST way to do things, then you should looking into using mysqli or PDO instead of the mysql functions. That way, you can make use of prepared statements, which won't be as taxing on your DBMS if you're planning on running multiple queries inside a loop. Prepared statements don't require you to make separate round trips to the server, whereas the old mysql functions do.

如果您正在寻找最好的方法,那么您应该考虑使用mysqli或PDO而不是mysql函数。这样,您可以使用预准备语句,如果您计划在循环内运行多个查询,则不会对DBMS产生负担。准备好的语句不需要你单独往返服务器,而旧的mysql函数可以。

#1