I am combining two tables using UNION ALL and then Randomized date using ORDER BY RAND()
First table:
OPDNo. | Dep | Ward | Sex | Name | BedNo 1 abc abcmale 1 xx 5 abc abcmale 1 yy 9 abc abcmale 1 zz
Second table:
OPDNo. | Dep | Ward | Sex | Name | BedNo 8 abc abcfemale 0 aa 6 abc abcfemale 0 bb 7 abc abcfemale 0 cc
My result after combining:
SELECT * from `firsttbl` UNION ALL SELECT * from `secondtble` ORDER BY RAND()
IPDno. | OPDNo. | Dep | Ward | Sex | Name | BedNo 1 8 abc abcfemale 0 aa 2 6 abc abcfemale 0 bb 3 1 abc abcmale 1 xx 4 7 abc abcfemale 0 cc 5 5 abc abcmale 1 yy 6 9 abc abcmale 1 zz
Now my problem in which I need your help:
I have one more table bed
Ward | Bed No | Status abcmale 1 0 abcmale 2 0 abcmale 3 1 abcmale 4 0 abcmale 5 0 abcfemale 6 0 abcfemale 7 0 abcfemale 8 1 abcfemale 9 1 abcfemale 10 0 abcfemale 11 0
In my combined table I need to fill BedNo. from my bed table in which I can put only bed no with status 0 of particular ward.
My current code:
<table class="table table-striped table-bordered">
<thead>
<tr>
<th>IPD No.</th>
<th>OPD No.</th>
<th>Dep.</th>
<th>Ward</th>
<th>Bed</th>
<th>Sex</th>
</tr>
</thead>
<tbody>
<?php
$qipd="SELECT MAX(IPDNo) from ipd";
$ripd = $conn->query($qipd);
$ipdn=mysqli_fetch_array($ripd,MYSQLI_NUM);
$ipdn= $ipdn[0]+1;
$i=1;
while($opd = $result->fetch_assoc()){
?>
<form action="saveBulkipd.php" method="post">
<?php
echo '<tr>';
echo '<td><input type="text" readonly value="'.$ipdn.'" name="'.$i.'ipdno"/></td>';
$ipdn++;
echo '<td><input type="text" readonly value="'.$opd['OPDNo'].'" name="'.$i.'opdno"/></td>';
echo '<td><input type="text" readonly value="'.$opd['PDep'].'" name="'.$i.'pdep"/></td>';
echo '<td><input type="text" readonly value="'.$opd['PWard'].'" name="'.$i.'pward"/></td>';
?>
<!--Bed No. Logic-->
<td><input type="text" readonly value="'<?php
$j=0;
$bedq="SELECT `bed` from bed where ward='$opd[PWard]' and status=0";
$rbed = $conn->query($bedq);
$bedno=array();
while($bedn=$rbed->fetch_assoc()) {
$bedno[]=$bedn['bed'];
}
echo $bedno[$j];
$j++;
?>'" name=""/></td>
<!--Bed No. Logic End-->
<?php
echo '<td><input type="text" readonly value="'.$opd['PSex'].'" name="'.$i.'psex"/></td>';
echo '</tr>';
$i++;
}
?>
<tr>
<td><input type="submit" name="bulksubmit" value="Save IPD Date" class="btn btn-default"/></td>
</tr>
</form>
</tbody>
</table>
output I am getting
I am getting same bed no for each ward
Note: For simplicity I have removed many columns my issue is with bed no allotment only.
My desired Result set:
IPDno. | OPDNo. | Dep | Ward | Sex | Name | BedNo 1 8 abc abcfemale 0 aa 6 2 6 abc abcfemale 0 bb 7 3 1 abc abcmale 1 xx 1 4 7 abc abcfemale 0 cc 10 5 5 abc abcmale 1 yy 2 6 9 abc abcmale 1 zz 4
As shown above table Bed no has been assigned using bed table in which status is 0 and of particular ward column.