CODEWITHSUNDEEP
phpmysqlsql
Suffii
Suffii

Reputation: 5784

Issue On Updating a Temporary Table Using SQL Select Statement

Can you please take a look at this code and let me know why I am not able to UPDATE a temporary table on $query3

$query = "CREATE TEMPORARY TABLE IF NOT EXISTS `charts_ecolo_yes` (


           SET `econo_sum_projects` = (SELECT COUNT(`project`) FROM `ecolo-cu-yes`  WHERE c_1000=1 ),
       SET `econo_sum_powerline` = (SELECT SUM(`powerline_length`) FROM `ecolo-cu-yes`  WHERE c_1000=1 ),
       SET `econo_sum_roads` = (SELECT SUM(`road_length`) FROM `ecolo-cu-yes`  WHERE c_1000=1 ),
       SET `econo_sum_cost` = (SELECT SUM(`cost_per_year`) FROM `ecolo-cu-yes`  WHERE c_1000=1 ),
       SET `econo_sum_penstlock` = (SELECT SUM(`penstlock` FROM `ecolo-cu-yes`  WHERE c_1000=1 )
        ";
$con->query($query3);
$query4 = "SELECT *  FROM `charts_ecolo_yes`" ;
$results = $con->query($query4);
if ($results) {
    $row = $results->fetch_array(MYSQLI_NUM);
    $row = array_map('floatval', $row); // Convert strings to numbers
    echo json_encode($row);
}

Here is the sample result page, which you can see even after running the $con->query($query3); I am still getting default values(100) at last 5th columns.

Thanks

Upvotes: 0

Views: 36

Answers (1)

Rahul
Rahul

Reputation: 77866

Your UPDATE query syntax is wrong, you don't need SET for all column. It should be

$query3= " UPDATE `charts_ecolo_yes`
SET `econo_sum_projects` = some_value,
`econo_sum_powerline` = some_other_value,

Upvotes: 1

Related Questions