Need help for mysqli query

Backend Development
1

Hello everyone!

I need some help on how I should write my query to get the result that I want. First of all I’ll show the result I try to achieve

desire result
desire result743×529 9.09 KB

Here is my first try

<p class="column-title"id="doing" >Todo</p>
<?php
    include 'dbconnect.php';

    $sql = "SELECT * FROM job_register
    WHERE
    job_assign ='{$_SESSION['username']}' AND  job_status = '' AND  job_cancel = ''
    OR
    job_assign ='{$_SESSION['username']}' AND  job_status = 'Incomplete'  AND job_cancel = ''
    OR
    job_assign ='{$_SESSION['username']}' AND  job_status = 'Ready'  AND job_cancel = ''
    ORDER BY jobregisterlastmodify_at
    DESC LIMIT 50";
    
    $queryRecords = mysqli_query($conn, $sql);
    if ($queryRecords) {
        while ($row = mysqli_fetch_array($queryRecords)) {
?>
						<button class="collapsible"><li><?php echo $row['customer_name']?>  [<?php echo $row['customer_grade']?>] </li></button>
							<div class="content">
									<div class="card" id="notYetStatus" data-id="<?php echo $row['jobregister_id'];?>"  data-toggle="modal" data-target="#myModal" >
									<button type="button" class="btn btn-light text-left font-weight-bold font-color-black"> <!-- Modal-->
									<ul class="b" id="draged">
										<strong text-align="center"><?php echo $row['job_priority']?></strong>
										<li><?php echo $row['job_order_number']?></li>
										<li><?php echo $row['customer_name']?>  [<?php echo $row['customer_grade']?>] </li>
										<li><?php echo $row['job_description']?></li>
										<li><?php echo $row['machine_name']?></li>
										<li><?php echo $row['machine_type']?></li>
										<li><?php echo $row['serialnumber']?></li>
										<strong text-align="center" style="color:red"><?php echo $row['reason']?></strong>
									</ul>
									<div class="status"  id="incompleteStatus">
										<?php echo $row['job_status']?>
									</div>
									</div>
							</div>
					<?php } } ?>

The result I get was something like this:

  • Company A
    -Job A-1
  • Company A
    -Job A-2
  • Company B
    -Job B-1

So I try another approach by adding group by and for each like this

                    <?php
						include 'dbconnect.php';
						$sql = "SELECT * FROM job_register
						WHERE
						job_assign ='{$_SESSION['username']}' AND  job_status = '' AND job_cancel = ''
						OR
						job_assign ='{$_SESSION['username']}' AND  job_status = 'Incomplete' AND job_cancel = ''
						OR
						job_assign ='{$_SESSION['username']}' AND  job_status = 'Ready' AND job_cancel = ''
						GROUP BY customer_name
						ORDER BY jobregisterlastmodify_at
						DESC LIMIT 50";
						$queryRecords = mysqli_query($conn, $sql);
						if ($queryRecords) {
							while ($row = mysqli_fetch_array($queryRecords)) {
					?>
						<button class="collapsible"><li><?php echo $row['customer_name']?>  [<?php echo $row['customer_grade']?>] </li></button>
						<?php foreach ($queryRecords as $res) :?>
							<div class="content">
									<div class="card" id="notYetStatus" data-id="<?php echo $row['jobregister_id'];?>"  data-toggle="modal" data-target="#myModal" >
									<button type="button" class="btn btn-light text-left font-weight-bold font-color-black"> <!-- Modal-->
									<ul class="b" id="draged">
										<strong text-align="center"><?php echo $row['job_priority']?></strong>
										<li><?php echo $row['job_order_number']?></li>
										<li><?php echo $row['customer_name']?>  [<?php echo $row['customer_grade']?>] </li>
										<li><?php echo $row['job_description']?></li>
										<li><?php echo $row['machine_name']?></li>
										<li><?php echo $row['machine_type']?></li>
										<li><?php echo $row['serialnumber']?></li>
										<strong text-align="center" style="color:red"><?php echo $row['reason']?></strong>
									</ul>
									<div class="status"  id="incompleteStatus">
										<?php echo $row['job_status']?>
									</div>
									</div>
							</div>
						<?php endforeach; ?>
					<?php } } ?>

And this is how I get instead

  • Company A
    -Job A-1

Company B, Job for company B and Job 2 for company A is missing

Can someone help me how can I improve my query to get the result that I want?
Sorry that I can show only 1 photo as I’m a new user here.