CODEWITHSUNDEEP
phpmysqlsqlcron
HELP_ME
HELP_ME

Reputation: 2729

PHP update table data based on other table

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

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. 

 <?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);

    ?>

Upvotes: 2

Views: 1190

Answers (2)

John Woo
John Woo

Reputation: 263703

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

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).

<?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

Upvotes: 3

user399666
user399666

Reputation: 19879

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.

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.

Upvotes: 0

Related Questions