How to check if all mysql results are equal-CodePudding

I'm facing a difficulty at the moment - I'm working on a real-estate website where the seller has some developments which have individual units within each development (a house with 4 apartments, for example).

The development has a status column in the database which specifies if it is Available, Reserved or Sold whereas each unit also has the same columns in their table.

The problem I'm trying to tackle is that developments still appear as Available even if all units are marked as Sold - of course the sellers can change this on their own but many times don't.

I want to make a script (which eventually will be used as a cron job) to check all development units statuses and change the development's status to Reserved if all units are set to Reserved, for example.

I wrote the following code to echo all properties and their respective units using a units foreach inside a developments foreach

<?php
$get_developments_sql = "SELECT property_id, property_name, status FROM properties WHERE is_newdevelopment = 1 AND is_active = 1";
$get_developments = mysqli_query($dbconnect, $get_developments_sql);

foreach($get_developments as $development):
    $get_units_sql = "SELECT id, property_id, property_name, status FROM property_units WHERE property_id =".$development['property_id'];
    $get_units = mysqli_query($dbconnect, $get_units_sql);
    ?>
    <span>Development ID: <?=$development['property_id'];?></span><br>
    <span>Development Status: <?=$development['status'];?></span><br>
    <span>Development Name: <?=$development['property_name'];?></span><br><br>
    <!-- Each Unit -->
    <?php
    foreach($get_units as $unit):
        ?>
        <span>Unit Name: <?=$unit['property_name'];?> -> <?=$unit['status'];?></span><br>
        <?php
    endforeach;
    ?>
    <hr>
    <?php
endforeach;
?>

The thing is I'd need to check if all unit statuses from a single development are the same because in case they were, I'd want to change the status of the development to the one which all units have.

So in this case I'd like to be able to see through PHP if all units have the status have the same value by using the $unit['status'] value or something.

Does anyone have an insight on how I could do this? I just need to know what method I can use to verify if all units results have the same value in the status column.

It doesn't need to be in PHP if there is a better way somehow through a query or something.

CodePudding user response：

You can use a SQL query such as this to identify properties whose all children have exactly one status:

select properties.property_id, max(property_units.status) as max_status
from properties
join property_units on properties.property_id = property_units.property_id
group by properties.property_id
having count(distinct property_units.status) = 1

Once you have established that the above query produces the expected result, convert it to an update-with-join query:

update properties
join (
    select property_id, max(status) as max_status
    from property_units
    group by property_id
    having count(distinct status) = 1
) as pu_status on properties.property_id = pu_status.property_id
set properties.status = pu_status.max_status

DB<>Fiddle

CodePudding user response：

Big thanks to @Salman A for his helpful take on how the query should look like,

I managed to use that to later on apply the status of a development's units to the development's status itself,

Thank everyone for your valuable takes,

    <?php

    // This query allows to check the results where developments have all units with the same status
    $get_developments_sql = "SELECT properties.property_id, properties.status, max(property_units.status) as the_status
                            FROM properties
                            JOIN property_units on properties.property_id = property_units.property_id
                            GROUP BY properties.property_id
                            HAVING count(distinct property_units.status) = 1";
    $get_developments = mysqli_query($dbconnect, $get_developments_sql);

foreach($get_developments as $development):?>
    
        <?php

            // Only for Visual Representation - Query to fetch all units from the development we're iterating
            $get_units_sql = "SELECT id, property_id, property_name, status FROM property_units WHERE property_id =".$development['property_id'];
            $get_units = mysqli_query($dbconnect, $get_units_sql);
            
            // Update the developments we're currently receiving from the first query and update their status according to the consensus status of their units
            // Using prepared statements
            $update_development_status_sql = "UPDATE properties SET status = ? WHERE property_id = ?";
            $stmt = $dbconnect->prepare($update_development_status_sql);

            // Update the property status with the unanimous status from its units through the property_id they're linked to (the development itself)
            $stmt->bind_param('si', $development['the_status'], $development['property_id']);
            $stmt->execute();
        
        ?>
        
        <!-- Visual representation so I can see in a list the developments and their respective units and the statuses of each so it's easier to see the change in the developments statuses -->
        <span>Development ID: <?=$development['property_id'];?></span><br>
        <span>Development Status: <strong><?=$development['status'];?></strong></span><br>
        <span>Status of All Units: <strong><?=$development['the_status'];?></strong></span><br><br>
        
        <!-- Each Unit -->
        <?php foreach($get_units as $unit):?>
            <span>Unit Name: <?=$unit['property_name'];?> -> <?=$unit['status'];?></span><br>
        <?php endforeach;?>
        <hr>
        
    <?php endforeach;?>

Cheers