user827391012 user827391012 - 2 months ago 6
MySQL Question

Separating the value of the rows in the table

I am planning to put a query into my variable but my problem is I need to group a different set of values to separate them in my PHP output I am wondering if this is possible, even help with the SQL query would be a big help. See my screenshot:
enter image description here

If you look at the bottom of the table

section
column you will see that there is a
GI21
and
GI122
. Now when the query I am using right now is this.

SELECT `scstock`.*, `schead`.* FROM `scstock` LEFT JOIN `schead` ON schead.TrNo = scstock.TrNo WHERE (`schead`.`curriculumcode`='BSIT 2011') AND (`schead`.`styear`='4') AND (`schead`.`terms`='1ST') AND (`schead`.`isBlock`=1)


or in Yii

->select(['scstock.*', 'schead.*'])
->leftJoin('schead', 'schead.TrNo = scstock.TrNo')
->where(['schead.curriculumcode' => $curriculumcode,
'schead.styear' => $year,
'schead.terms' => $term,
'schead.isBlock' => 1
])
->asArray()
->all();


That will output all the rows the has
isBlock = 1
but what I need to do is to separate
GI21
and
GI122
, maybe put them in an array like right now but maybe there is a better implementation but right now after putting them in an array I return them to be put in the view in a table like this.

<table class="table table-bordered" id="studentTable">
<th>Subject</th>
<th>Schedule</th>
<th>Section</th>
<th>Action</th>
<th>Slots</th>
<th>Status</th>
<?php foreach($getBlock as $values): ?>

<tr>
<td><?= $values['subjectcode']; ?></td>
<td><?= $values['schedday'] . ' ' . $values['schedtime'] ?></td>
<td><?= $values['section'] ?></td>
<td><?= '....' ?></td>
<td><?= $values['slots'] ?></td>
<td><?= '....' ?></td>
</tr>

<?php endforeach; ?>
</table>


Now to separate them in the view maybe I'll just put the
foreach
in the
table
tag to separate the sections?How do I group a different set of values to separate them in my PHP output? Any kind of help would be appreciated.

Answer

You could use two separated query one for not (G121 and GI122) eg: getBlock1

->select(['scstock.*', 'schead.*'])
    ->leftJoin('schead', 'schead.TrNo = scstock.TrNo')
    ->where(['schead.curriculumcode' => $curriculumcode,
              'schead.styear' => $year,
              'schead.terms' => $term,
              'schead.isBlock' => 1,
              'not in','schead.section',['G121', 'GI122'],
              ])
    ->asArray()
    ->all();

and second with in (G121 and GI122) eg: getBlock2

where(['not in','attribute',$array]);



->select(['scstock.*', 'schead.*'])
    ->leftJoin('schead', 'schead.TrNo = scstock.TrNo')
    ->where(['schead.curriculumcode' => $curriculumcode,
              'schead.styear' => $year,
              'schead.terms' => $term,
              'schead.isBlock' => 1,
              'in','schead.section',['G121', 'GI122'],
              ])
    ->asArray()
    ->all();

the you can related in a first foreach to $getBlock1 and in a second foreach at $getBlock2

Comments