php – An answer only if all records have the same value-ThrowExceptions

Exception or error:

I have a simple table:

id | from | tu | status_os1 | status_os2

status_os1 and status_os2 can be only paid or unpaid.

I want to show a message "You have all dues paid".

So my question is – what should I write in the SQL part to give an answer only if all records have values "paid"?

I was trying do it in below way but it also failed:

$rezultat = $polacz->query("SELECT case when COUNT(status_os1) = 0 then 1 end FROM skladki WHERE status_os1='unpaid'");
$wynik = $rezultat->num_rows;
if($wynik>0)
{
    echo "You have all dues paid";
}       
How to solve:

I would approach this from the opposite side: you give the message if none of the rows has either status_o1 or status_o2 as ‘unpaid’:

$rezultat = $polacz->query("SELECT COUNT(*) AS cnt FROM skladki WHERE status_os1='unpaid' or status_os2='unpaid'");
$row = $rezultat ->fetch_assoc();
$wynik = $row["cnt"];
if($wynik==0)
{
    echo "You have all dues paid";
}  

Answer:

SELECT COUNT(*) FROM skladki WHERE status_os1 = “paid” and status_os2 = “paid”

Answer:

You can probably use CASE expression in COUNT() like

SELECT 
COUNT(case when status_os1 = 'paid' and status_os2 = 'paid' then 1 else 0 end) 
FROM skladki;

Answer:

You need a subquery that filters out all records where, for that person or client, there is no record in the table with a value of ‘Unpaid’.

Select * from table t
where not exists 
   (Select * from table
    Where id = t.Id
       and 'unpaid' in 
          (status_os1, status_os2))

Answer:

Try it from reverse side:

SELECT COUNT(*) FROM skladki WHERE status_os1 = "unpaid" or status_os2 = "unpaid"

If there are at least 1 record – some record has unpaid status 🙂

Answer:

Use a case expression to conditionally count all unpaid items. If 0 returned, return all dues paid message.

select case when count(case when status_os1 = 'unpaid' or status_os2 = 'unpaid' then 1 end) = 0 then 'You have all dues paid'
           else 'You have unpaid dues'
        end
from skladki 

Leave a Reply

Your email address will not be published. Required fields are marked *