Returning Data From from a Sub-query With fetchcolumn()

zdarma 0
Viewed 24

I am trying to simply my code. I know this works and will return a value for $waiver_cash:

    $waiver_query = $pdo->prepare("SELECT cap_number + waiver_number - :salary - 
                        (SELECT sum(current_salary)
                            FROM salaries
                            WHERE gm = :gm AND franchise IS NULL AND waiver_bid IS NULL) - 
                        (SELECT COALESCE(sum(waiver_bid), 0) FROM salaries
                            WHERE gm = :gm) AS waiver_cash
                      FROM base_numbers;");
$waiver_query->execute(['salary' => $salary, 'gm' => $gm]);
foreach ($waiver_query as $row) {
    $waiver_cash = $row['waiver_cash'];
}

However, what I want to do, is this:

    $waiver_query = $pdo->prepare("SELECT cap_number + waiver_number - :salary_retained - 
                                      (SELECT sum(current_salary)
                                       FROM salaries
                                       WHERE gm = :gm AND franchise IS NULL AND waiver_bid IS NULL) - 
                                         (SELECT COALESCE(sum(waiver_bid), 0) 
                                          FROM salaries
                                          WHERE gm = :gm) AS waiver_cash
                               FROM base_numbers;");
$waiver_query->execute(['salary_retained' => $salary_retained, 'gm' => $gm]);
$waiver_cash = $waiver_query->fetchColumn();

When I do it with "fetchColumn()" nothing gets returned. Other than changing the $pdo->prepare to $pdo->query and putting the variables in the SELECT statements, is what I want to do, possible?

pdo prepared-statement php
1 Answers

I found the issue. I have another query which fetches the data for $salary_retained.

    $salary_retained_query = $pdo->prepare("SELECT COALESCE(sum(current_salary), 0) FROM salaries WHERE salary_retained= :gm;");
$salary_retained_query->execute(['gm' => $gm]);
$salary_retained = $salary_retained_query->fetchColumn();

Some of the GMs didn't have any criteria which matched and so the value returned was "null". Then in the query which I was having issues, the sum was returning a null, so in my webpage, it would return blank. By changing my code from sum(current_salary to COALESCE(sum(current_salary), 0), the issue was resolved.

When testing in PGAdmin, those GMs who I knew din't have any value in the "salary_retained" column, I would put a 0 for them in the calculations, so my query worked. Thanks to islemdev for making me rethink how the data I was using was being returned.

zdarma0
Related