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 个解决方案

#1

更多相关文章

随机推荐