I have been working on a cronjob that runs a few checks across different tables in my database.
The issue is that although it works, I feel like I am doing something horribly wrong in terms of the standards set out by others and also in the future for any adjustments I make.
The code below DOES work perfectly, but a foreach
in a foreach
seems horrible. Is there any way to tidy up what I am doing, or would it be acceptable code by a good standard?
// CRON JOB - Add notification if a build has been updated
require("../../commonlive.php");
require '../../functions/database-functions.php';
// select all users, and run a foreach
$query = "SELECT * FROM users";
$query_params = array();
try {
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
}
catch(PDOException $ex)
{
die("Failed to run query: " . $ex->getMessage());
}
$rows = $stmt->fetchAll();
// for each user, check its user ID against the build tracking and then see when the build was updated last
foreach($rows as $row){
$id = $row['id'];
//echo "user id: $id, ";
// check the build_tracking table for a row that matches the user id
$query = " SELECT * FROM build_tracking WHERE user_id = :user_id";
$query_params = array(':user_id' => $id);
try {
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
$rows = $stmt->fetchAll();
foreach ($rows as $row) {
$build_id = $row['build_id'];
$user_id = $row['user_id'];
$view_time = $row['view_time'];
//echo "build id track: $build_id";
// check the build_tracking table for a row that matches the user id and build id.
$query = " SELECT * FROM blogs WHERE id = :build_id AND frontpage = 1";
$query_params = array(':build_id' => $build_id);
try {
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
$rows = $stmt->fetchAll();
foreach ($rows as $row) {
$build_id = $row['id'];
$buildOwnerID = $row['userid'];
$lastupdated = $row['lastupdated'];
$stmt = $db->prepare("SELECT * FROM followers WHERE userid = $user_id AND blogid = $build_id");
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$countfollow = $stmt->rowCount();
if ($view_time < $lastupdated && $countfollow > 0) {
$userTo = $id;
$userFrom = $buildOwnerID;
$type = 'buildUpdate';
$date = time();
$postID = 1;
$buildID = $build_id;
$commentID = 1;
// see if a notif already exists with the user id and build id
$query = "SELECT * FROM notifications WHERE user_to = :userTo AND buildID = :buildID AND type = :type AND status != 3";
$query_params = array(':userTo' => $userTo, ':buildID' => $buildID, ':type' => $type);
try {
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
$checkForExistOfNotif = $stmt->rowCount();
}
catch(PDOException $ex)
{
die("Failed to run query: " . $ex->getMessage());
}
// if no add a notification, if YES
if (!$checkForExistOfNotif) {
//echo "adding, $build_id";
addNotification($userTo, $userFrom, $type, $date, $postID, $buildID, $commentID);
}
}
}
}
catch(PDOException $ex) { die();
}
}
}
catch(PDOException $ex) { die();
}
}
foreach($rows as $row):
is pretty unusual when you're not exiting out of the PHP block to insert HTML. \$\endgroup\$