A union would work, if doing a single query is really needed, but two is simpler.
Astonecipher, show us a sample of how to do that. I would like to use it in another site, well, maybeâŚ
I attempted a union in a couple tests, but, could not get it to show the multiple rows along with the correct
grand total. It did show the grand total in a last col but, it was not correct. I would like to see an example.
(My week point is unions for sure!)
Willymec, that error is due to the code you used. You used .= which expects to append to a variable.
So, $output .= âsomethingâ needs to have $output already existing. Add $output=""; at the beginning somewhere without the period so you start with a valid variable $output but with empty contents. Then, when the code hits the append-to-$output, it will work correctly. Hope that makes sense!
I can, but I need the query being used, with the columns. @willymec, you ALWAYS want to specify columns in a query, the only exception to this is testing or when you know what you will get back due to a sub query. So, this is okay,
SELECT * FROM (
SELECT column1, column2, column3 FROM table_abc WHERE id = 5
)
Because we know that we will get back column1, 2, and 3, even if we change the structure of the table.
SELECT column1, column2, column3 FROM table_abc WHERE id = 5
This does pull the three cols and displays them with all the rows.
BUT,
SELECT *, SUM(sum_total) AS grand_total FROM (
SELECT column1, column2, column3, SUM(column1+column2+column3) AS sum_total FROM table_abc WHERE id = 5
)
only shows one row which includes the three cols, sum_total and grand_total, but, not all of the rows!
ErnieAlex, When I add $output=""; It removes the error, but also removes the Final Total query results. I am missing something here.
Yes! You need to put that line way up before your queries.
In other words, you must initialize the variable starting out before you fill it with anything.
NOT instead of the line you have now, but, in addition to and before you run any queriesâŚ
OR just remove the period. Normally, when you are creating HTML code from PHP commands, you would start with nothing like $output = ââ; and, then add parts of the HTML code as needed using the .= instead of the =âŚ
In your code, you only show us creating it twice. So, the first place should not have the period and the others after that one should include it. Do you understand that?
Example:
$output = âxyzâ; Variable now is zyz
$output = âabcâ; Variable now is abc
$output .= âxyzâ; Variable now is abcxyz (note it is APPENDED to the previous valuesâŚ)
Hope that explains it better!
1 Like
That was a comment on doing select * in general, not for the union question. I still need to column names from that, because you need to account for the columns returned.
Ok no more errorâs and I cleaned up some of the code. Change sum to week, and final to month.
<?php
// filter_weekly_activity_bydate.php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
{
require 'db_connection2.php';
$stmt = $pdo->prepare("
DELETE t1 FROM weekly_activity_count t1
INNER JOIN
weekly_activity_count t2
WHERE
t1.id < t2.id AND t1.date = t2.date");
$stmt->execute();
}
$stmt = $pdo->prepare(" SELECT *, SUM(w.week_total) AS month_total
FROM
( SELECT *, ifnull(left_count, 0) + ifnull(center_count, 0) + ifnull(right_count, 0) + ifnull(allother_count, 0) + ifnull(youthbalcony_count, 0) + ifnull(play_praise_nursery_count, 0) + ifnull(childrens_worship_count, 0) AS week_total
FROM weekly_activity_count
WHERE
month(date) = ?
AND
year(date) = ?
GROUP BY date desc ) w
");
$stmt->execute([$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
//-----------------------------Table------------------------------------//
$week_total = 0;
$output = "";
$output .= '
<table> ';
foreach($result as $row)
$output .= ' <th>Month Total:</th>
<td>'. $row["month_total"] .'</td> ';
$output .= '</table>';
$stmt = $pdo->prepare(" SELECT *, ifnull(left_count, 0) + ifnull(center_count, 0) + ifnull(right_count, 0) + ifnull(allother_count, 0) + ifnull(youthbalcony_count, 0) + ifnull(play_praise_nursery_count, 0) + ifnull(childrens_worship_count, 0) AS week_total
FROM weekly_activity_count
WHERE
month(date) = ?
AND
year(date) = ?
GROUP BY date desc
");
$stmt->execute([$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
//-----------------------------Table------------------------------------//
$week_total = 0;
$output .= '
<table class="table table-bordered">
<tr>
<th width="10%"><span>Left Section</span></th>
<th width="10%"><span>Center Section</span></th>
<th width="10%"><span>Right Section</span></th>
<th width="10%"><span>All Other Section</span></th>
<th width="10%"><span>Youth Balcony Section</span></th>
<th width="10%"><span>Play Praise Nursery Area</span></th>
<th width="10%"><span>Childrens Worship Area</span></th>
<th width="10%"><span>WeekTotal</span></th>
<th width="10%"><span>Date</span></th>
</tr>';
foreach($result as $row)
{
$week_total += $row["week_total"];
$output .= '
<tr>
<td>'. $row["left_count"] .'</td>
<td>'. $row["center_count"] .'</td>
<td>'. $row["right_count"] .'</td>
<td>'. $row["allother_count"] .'</td>
<td>'. $row["youthbalcony_count"] .'</td>
<td>'. $row["play_praise_nursery_count"] .'</td>
<td>'. $row["childrens_worship_count"] .'</td>
<td>'. $row["week_total"] .'</td>
<td>'. $row["date"] .'</td>
</tr>
';
}
$output .= '</table>';
echo $output;
$pdo=null;
// By this way you can close connection in PDO.
?>
Thank you all for all your time and expert help. I have learned even more. Thank you for being patient also.
Now astonecipher Iâm not sure if you are asking me for something?
I donât have any data to play with, but this should give you every thing in a single return.
SELECT
left_count,
center_count,
right_count,
allother_count,
youthbalcony_count,
play_praise_nursery_count,
childrens_worship_count,
0
FROM
weekly_activity_count
UNION
SELECT
0,
0,
0,
0,
0,
0,
0,
(ifnull(left_count, 0) +
ifnull(center_count, 0) +
ifnull(right_count, 0) +
ifnull(allother_count, 0) +
ifnull(youthbalcony_count, 0) +
ifnull(play_praise_nursery_count, 0) +
ifnull(childrens_worship_count, 0) AS week_total
FROM
weekly_activity_count
WHERE
month(date) = ?
AND
year(date) = ?
Getting error
Error
``
SELECT
left_count,
center_count,
right_count,
allother_count,
youthbalcony_count,
play_praise_nursery_count,
childrens_worship_count,
0
FROM
weekly_activity_count
UNION
SELECT
0,
0,
0,
0,
0,
0,
0,
(ifnull(left_count, 0) +
ifnull(center_count, 0) +
ifnull(right_count, 0) +
ifnull(allother_count, 0) +
ifnull(youthbalcony_count, 0) +
ifnull(play_praise_nursery_count, 0) +
ifnull(childrens_worship_count, 0) AS week_total
FROM
weekly_activity_count
WHERE
month(date) = 1
AND
year(date) = 2019 LIMIT 0, 25
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS week_total FROM weekly_activity_count WHERE month(date) = 1 AND y' at line 27
Line 27 >> ifnull(childrens_worship_count, 0) AS week_total
SELECT
left_count,
center_count,
right_count,
allother_count,
youthbalcony_count,
play_praise_nursery_count,
childrens_worship_count,
0
FROM
weekly_activity_count
UNION
SELECT
0,
0,
0,
0,
0,
0,
0,
(
ifnull(left_count, 0) +
ifnull(center_count, 0) +
ifnull(right_count, 0) +
ifnull(allother_count, 0) +
ifnull(youthbalcony_count, 0) +
ifnull(play_praise_nursery_count, 0) +
ifnull(childrens_worship_count, 0)
) AS week_total
FROM
weekly_activity_count
WHERE
month(date) = 1
AND
year(date) = 2019 LIMIT 0, 25
I missed a closing parenthesis
