Ok I get it I’m lost! Just was trying to add one more report, by month and year.
The report by year works fine. It’s when I repeated everything to make the year report, I then add by month also. This way I can pull records by all months of the year (year) report. And also by month and year (month) and (year). I have learned a lot lately and have nearly finished this project, except the month year report. I will study more, and look more closely at your suggestions. I have cleaned up the code and made sure all letter case matches.
Thank you so much for giving of your very valuable time.
Strangely this code below works, I’m sure something is a miss because of your expert comment " Also, stop doing this - $result = $query; nonsense.". Which I would trust over my experience, but this code below works. What will be a problem if I continue with this way?
<?php
{
include 'db_connection2.php';
$query = " SELECT
g.name as `group`,
COUNT(ar.present) as attended
FROM
attendance_record ar
INNER JOIN
_person p
ON ar.personid = p.id
INNER JOIN
_person_group g
ON ar.groupid = g.id
-- WHERE
AND
month(date) = ? AND year(date) = ?
AND
ar.present = 1
GROUP BY g.name
ORDER BY ar.date, g.name ASC ";
$stmt = $pdo->prepare($query);
$stmt->execute([$_POST["month"],$_POST["year"]] );
$result = $query;
//-----------------------------Table------------------------------------//
$output .= '
<table class="table table-bordered">
<div align="center"><font size=4>
Total present (by Class)-------PDO------ Not Working Code</font>
</div>
<tr>
<th style="text-align:center;" width=".001%"><font size=2><span>Class</span></th>
<th style="text-align:center;" width=".001%"><font size=2><span>Attended</span></th>
</tr>
';
foreach($stmt as $row)
{
$output .= '
<tr>
<td style="text-align:center;">' . $row['group'] . '</td>
<td style="text-align:center;">' . $row['attended'] . '</td>
</tr>
';
}
$output .= '</table>'; }
$pdo=null;
// By this way you can close connection in PDO.
?>
I have no idea how that is working, and I sincerely doubt that it is. $stmt, which you are looping over, is a pdo object, not a result set.
Ok, well it did indeed work, idk how? Still I have changed the code around. Works perfect!
<?php
{
include 'db_connection2.php';
$stmt = $pdo->prepare( " SELECT
g.name as `group`,
COUNT(ar.present) as attended
FROM
attendance_record ar
INNER JOIN
_person p
ON ar.personid = p.id
INNER JOIN
_person_group g
ON ar.groupid = g.id
-- WHERE
AND
YEAR(date) = ?
AND
ar.present = 1
GROUP BY g.name
ORDER BY g.name ASC
");
$stmt->execute([$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
//-----------------------------Table------------------------------------//
$output .= '
<table class="table table-bordered">
<div align="center"><font size=4>
Total present (by Class)</font>
</div>
<tr>
<th style="text-align:center;" width=".001%"><font size=2><span>Class</span></th>
<th style="text-align:center;" width=".001%"><font size=2><span>Attendance</span></th>
</tr>
';
foreach($result as $row)
{
$output .= '
<tr>
<td style="text-align:center;">' . $row['group'] . '</td>
<td style="text-align:center;">' . $row['attended'] . '</td>
</tr>
';
}
$output .= '</table>'; }
$pdo=null;
// By this way you can close connection in PDO.
?>
That is different than,
This.
A PDOStatement object is Traversable and can be looped over with a foreach(){} loop. However, for the reasons already stated in this thread, is the equivalent of painting yourself into a corner, and should be avoided.
I have now add another client-side report by class, year and month. But can’t get the code right to pull records.
Code I have used and works, But that leaves open allow a hacker to inject sql below.
<?php
{
require 'db_connection2.php';
$stmt = $pdo->prepare( " SELECT *
FROM
_person, attendance_record, _person_group, age_bracket
WHERE
_person.id = attendance_record.personid
AND
_person_group.id = attendance_record.groupid
AND
age_bracket.id = _person.age_bracketid
AND
attendance_record.present = 1
AND
_person_group.id = '".$_POST["class"]."'
AND
month(date) = ?
AND
year(date) = ?
ORDER BY attendance_record.date, age_bracket.id ASC
");
$stmt->execute([$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
//-----------------------------Table------------------------------------//
$output .= '
<table class="table table-bordered">
<tr>
<th width="10%"><font size=2><span>FirstName</span></th>
<th width="15%"><font size=2><span>LastName</span></th>
<th width="10%"><font size=2><span>Age Class</span></th>
<th width="25%"><font size=2><span>Class</span></th>
<th width="10%"><font size=2><span>Status</span></th>
<th width="15%"><font size=2><span>Date</span></th>
</tr>
';
foreach($result as $row)
{
$output .= '
<tr>
<td><font size=2>'. $row["first_name"] .'</td>
<td><font size=2>'. $row["last_name"] .'</td>
<td><font size=2>'. $row["label"] .'</td>
<td><font size=2>'. $row["name"] .'</td>
<td><font size=2>'. $row["status"] .'</td>
<td><font size=2>'. $row["date"] .'</td>
</tr>
';
}
$output .= '</table>';
}
echo $output;
$pdo=null;
// By this way you can close connection in PDO.
?>
Below is the code I have tried but wont pull records.
//filter_Attendance_class.php
<?php
{
require 'db_connection2.php';
$stmt = $pdo->prepare( " SELECT *
FROM
_person, attendance_record, _person_group, age_bracket
WHERE
_person.id = attendance_record.personid
AND
_person_group.id = attendance_record.groupid
AND
age_bracket.id = _person.age_bracketid
AND
attendance_record.present = 1
AND
class(_person_group.id) = ?
AND
month(date) = ?
AND
year(date) = ?
ORDER BY attendance_record.date, age_bracket.id ASC
");
$stmt->execute([$_POST["month"],$_POST["year"],$_POST["class"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
//-----------------------------Table------------------------------------//
$output .= '
<table class="table table-bordered">
<tr>
<th width="10%"><font size=2><span>FirstName</span></th>
<th width="15%"><font size=2><span>LastName</span></th>
<th width="10%"><font size=2><span>Age Class</span></th>
<th width="25%"><font size=2><span>Class</span></th>
<th width="10%"><font size=2><span>Status</span></th>
<th width="15%"><font size=2><span>Date</span></th>
</tr>
';
foreach($result as $row)
{
$output .= '
<tr>
<td><font size=2>'. $row["first_name"] .'</td>
<td><font size=2>'. $row["last_name"] .'</td>
<td><font size=2>'. $row["label"] .'</td>
<td><font size=2>'. $row["name"] .'</td>
<td><font size=2>'. $row["status"] .'</td>
<td><font size=2>'. $row["date"] .'</td>
</tr>
';
}
$output .= '</table>';
}
echo $output;
$pdo=null;
// By this way you can close connection in PDO.
?>
I’m guessing it’s this line, is not correctly written.
class(_person_group.id) = ?
What do you see different in those two queries?
1 Like
Also, the order of the ? place-holders in the sql query statement must match the order of the the values in the ->execute([…]) method call.
2 Likes
Ok I thought I tried that and it didn’t work, so I must have had something backwards. Thank you both for all your time and help. Just when I thought I’m getting it, I’m lost again. But now it is working great, thank you again.
<?php
{
require 'db_connection2.php';
$stmt = $pdo->prepare( " SELECT *
FROM
_person, attendance_record, _person_group, age_bracket
WHERE
_person.id = attendance_record.personid
AND
_person_group.id = attendance_record.groupid
AND
age_bracket.id = _person.age_bracketid
AND
attendance_record.present = 1
AND
_person_group.id = ?
AND
month(date) = ?
AND
year(date) = ?
ORDER BY attendance_record.date, age_bracket.id ASC
");
$stmt->execute([$_POST["class"],$_POST["month"],$_POST["year"]]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
//-----------------------------Table------------------------------------//
$output .= '
<table class="table table-bordered">
<tr>
<th width="10%"><font size=2><span>FirstName</span></th>
<th width="15%"><font size=2><span>LastName</span></th>
<th width="10%"><font size=2><span>Age Class</span></th>
<th width="25%"><font size=2><span>Class</span></th>
<th width="10%"><font size=2><span>Status</span></th>
<th width="15%"><font size=2><span>Date</span></th>
</tr>
';
foreach($result as $row)
{
$output .= '
<tr>
<td><font size=2>'. $row["first_name"] .'</td>
<td><font size=2>'. $row["last_name"] .'</td>
<td><font size=2>'. $row["label"] .'</td>
<td><font size=2>'. $row["name"] .'</td>
<td><font size=2>'. $row["status"] .'</td>
<td><font size=2>'. $row["date"] .'</td>
</tr>
';
}
$output .= '</table>';
}
echo $output;
$pdo=null;
// By this way you can close connection in PDO.
?>Named parameters don’t need to follow an order because there is a reference. Unnamed parameters need to follow the order they are referenced. Anything more than 5 placeholders, I start using named values.
1 Like