I have some code here which I have a feeling can be made much more efficient. I have been building a library catalogue database, and I am looking for a way to store multiple columns (with multiple entries) extracted from an SQL database into one PHP array. This would mean only one foreach loop would then be required in the 'view' code to print the results out and store them in a table. Any of you guys have useful ideas? :)
My code in its current state is below:
MODEL CODE:
$matches["loandates"] = array();
$matches["loanduedates"] = array();
$matches["itemnames"] = array();
$matches["itemauthors"] = array();
try { // if we do find an item, then we run this query against the database to get a list of all the current loans.
$results = $db->prepare("
SELECT il.loan_date, il.loan_duedate, i.item_name, i.item_author
FROM itemloan il
INNER JOIN libraryitem li
ON il.libraryItem_id = li.libraryItem_id
INNER JOIN item i
ON li.item_id = i.item_id
WHERE il.user_id = ?
ORDER BY il.loan_date asc
");
$results->bindParam(1,$userid); // puts user id value where the placeholder is. First value is character of placeholder, second value is variable we want to bind to placeholder).
$results->execute();
} catch (Exception $e) {
echo "Data could not be retrieved from the database.";
exit;
}
while ($row = $results->fetch(PDO::FETCH_ASSOC)) { // we loop through the loans one at a time, and add them to our matches variable
$matches["loandates"][] = $row["loan_date"];
$matches["loanduedates"][] = $row["loan_duedate"];
$matches["itemnames"][] = $row["item_name"];
$matches["itemauthors"][] = $row["item_author"];
}
return $matches;
}
VIEW CODE:
<table id="name">
<?php
foreach($user["itemnames"] as $itemname) {
echo "<tr><td id='radiocell'><input type='radio' name='libraryitemid' id='radio'></td><td class='user'>" . $itemname . "</td></tr>";
}
?>
</table>
<table id="author">
<?php
foreach($user["itemauthors"] as $itemauthor) {
echo "<tr><td class='user'>" . $itemauthor . "</td></tr>";
}
?>
</table>
<table id="date">
<?php
foreach($user["loandates"] as $loandate) {
$time = strtotime($loandate);
$myFormatForView = date("l d F Y", $time);
echo "<tr><td class='user'>" . $myFormatForView . "</td></tr>";
}
?>
</table>
<table id="duedate">
<?php
foreach($user["loanduedates"] as $loanduedate) {
$time = strtotime($loanduedate);
$myFormatForView = date("l d F Y", $time);
echo "<tr><td class='user'>" . $myFormatForView . "</td></tr>";
}
?>
</table>
I ultimately want just one table with one foreach loop rather than four tables with four foreach loops. If you get my meaning.
I would be very thankful if anyone could help! Thanks heaps in advance!!
Regards,
Robert, London UK
