With the help of r937 and Drummin :tup: I am using the following sequence to forward a new message from a gebruiker_id (user_id) along with all other messages from that gebruiker_id to any available moderator/admin:
$b = "SELECT gebruiker_id
, bericht
, verzonden
FROM berichten
WHERE gebruiker_id =
( SELECT gebruiker_id
FROM berichten
WHERE verzonden =
( SELECT MIN(verzonden)
FROM berichten
WHERE isNieuw = 1
) )ORDER BY verzonden DESC";
$berichten = $pdo->prepare($b);
$berichten->execute();
while($row = $berichten->fetch()) {
echo "<p>{$row['bericht']}</p>";
}
That works great. At some point though I need to update the berichten table to set the isNieuw value to 0, so that no other available modarator will get the same messages (results). What would be the best place to run the update since the results from the actual query are based on an isNieuw value = 1.
$b = "SELECT gebruiker_id
, bericht
, verzonden
FROM berichten
WHERE gebruiker_id =
( SELECT gebruiker_id
FROM berichten
WHERE verzonden =
( SELECT MIN(verzonden)
FROM berichten
WHERE isNieuw = 1
))
ORDER BY verzonden DESC";
$berichten = $pdo->prepare($b);
$berichten->execute();
while($row = $berichten->fetch()) {
$status = 0;
$gebruiker = $row['gebruiker_id'];
$u = "UPDATE berichten
SET isNieuw = ?
WHERE gebruiker_id = ?";
$update = $pdo->prepare($u);
$update->execute(array($status,$gebruiker));
echo "<p>{$row['bericht']}</p>";
}
What I now actually need is an if statement arround the while($row = $berichten->fetch()) {} block something like:
if (empty($berichten)){
echo "Er zijn momenteel geen nieuwe berichten.";// There are no new messages
}else{
while($row = $berichten->fetch()) {
$status = 0;
$gebruiker = $row['gebruiker_id'];
$u = "UPDATE berichten
SET isNieuw = ?
WHERE gebruiker_id = ?";
$update = $pdo->prepare($u);
$update->execute(array($status,$gebruiker));
echo "<p>{$row['bericht']}</p>";
}
}
The if (empty($berichten)){ is obvious not working I tried:
$results = $berichten->fetch(PDO::FETCH_ASSOC);
if (empty($results)){
echo "Er zijn op dit moment geen nieuwe berichten";
}else{
$berichten = $pdo->prepare($b);
$berichten->execute();
while($row = $berichten->fetch()) {
$status = 0;
$profiel = $row['profiel_id'];
$u = "UPDATE berichten
SET isNieuw = ?
WHERE profiel_id = ?";
$update = $pdo->prepare($u);
$update->execute(array($status,$profiel));
echo "<p>{$row['bericht']}</p>";
}
But for some reason when using it within the if else statement all messages with isNieuw = 1 status are set to 0 at the same time, while when using it without the if statement I first get the oldest new message and after refreshing the page the Second oldest and so on. What should I adjust to make this work?
Just wanted to add that this might sound like overkill, but no worries, will take 10 mins to set all that up, and it is much more reliable than trying to hack together some code. Looks like you have a tricky little query going on there.
You can also try this, see if it works better for you:
replace
if (empty($berichten))
with
if ( $berichten->rowCount() == 0 )
Using empty to check for an empty or null result will not work with PDO.
Actually, using rowCount() isn’t 100% reliable either, but in your instance, it should work fine…as long as the rest of the logic is correct.
Even if the above does fix things, I still strongly suggest going with the foreign key / trigger setup for this.
I believe you’d want to grab the bericht_id so you have a specific record id and isNieuw for that record, so you can then check isNieuw for a value of 1 and update ONLY THAT RECORD based on that record id or bericht_id. I haven’t had a problem using ->rowCount() on a select statement, though I’ve read that some do. You need to set it to a variable then use it in your IF statement. Anyway this is what I came up with.
$b = "SELECT bericht_id
, isNieuw
, gebruiker_id
, bericht
, verzonden
FROM berichten
WHERE gebruiker_id =
( SELECT gebruiker_id
FROM berichten
WHERE verzonden =
( SELECT MIN(verzonden)
FROM berichten
WHERE isNieuw = 1
))
ORDER BY verzonden DESC";
$berichten = $pdo->prepare($b);
$berichten->execute();
$nieuwe_berichten = $berichten->rowCount();
if (empty($nieuwe_berichten)){
echo "Er zijn momenteel geen nieuwe berichten.";// There are no new messages
}else{
while($row = $berichten->fetch()) {
$status = 0;
if($row['isNieuw'] == 1){
$bericht_id = $row['bericht_id'];
$u = "UPDATE berichten
SET isNieuw = ?
WHERE bericht_id = ?";
$update = $pdo->prepare($u);
$update->execute(array($status,$bericht_id));
}
echo "<p>{$row['bericht']}</p>";
}
}
Hi arout77 and Drummin. Thank you both for your input. I just found out that updating the isNieuw status at this point is giving me several problems so I prefer to update that value after the moderator/admin has replied to the message. So sorry for the work. Another sollution would be the admin_id in the table berichten ((admin_id smallint(4) NOT NULL DEFAULT ‘0’) So I was thinking of doing an update query just before the select query in which I update the admin_id with $_SESSION[‘admin_id’] something like:
$u = "UPDATE berichten
SET admin_id = ?
WHERE (
SELECT * FROM berichten
WHERE verzonden = MIN(verzonden)
AND isNieuw = 1))";
$update = $pdo->prepare($u);
$update->execute(array($admin));
and then use the admin_id in the select query
WHERE isNieuw = 1
AND admin_id = $_SESSION['admin_id'];
But that isn’t working. Probably because of the way I have the update query constructed but I am not sure?
Might be a little late now, but given the complexity of the existing query you have to perform such a simple action(s), I’d say that it may be wise to re-evaluate the DB schema. This is a trivial task that you are trying to do, let the software work with you, not against you!
Hi arout77. You’re probably right. I made things to complex and couldn’t get my head arround things that normally are quite easy. Yesterday I stayed away from the computer for most of the day and in the evening I saw things way clearer. Thank you for all your input.
Thanks a lot.