CODEWITHSUNDEEP
mysql
adinell
adinell

Reputation: 69

MySQL filtering query

i am looking to write a query that filters the output according to the number of occurences of in a column. e.g (a room number 1 occurs 5 times in the column room number. if the number of occurences is between 0-5 the room number 5 should be among the output, if not it shouldn't be an output)

this is my query:

$query = 'SELECT room_no, COUNT(room_no) as count FROM banabas_sr GROUP BY room_no HAVING count BETWEEN 0 AND 6';

but it doesn't output anything even when all the room's in the table occur only once.

when i click this button:

<input type="submit" name="check" value="CHECK AVALIBILITY" class="mdl-button mdl-js-button mdl-button--raised mdl-button--colored" id="magic" />

it is supposed to pass this info:

<div class="labels-input room">
                   <!--Hostel-->
                   <?php
                   if ($row['gender'] == 'Male') {
                     $option1 = 'Banabas';
                     $option2 = 'Banabas Annex';
                   }
                   else {
                     $option1 = 'Faith';
                     $option2 = 'Faith Annex';
                   }?>
                   <div class="labas"><div class="form-group">
                     <div class="input-group input-group-sm">
                       <select class="form-control" name="hostel" required>
                         <option></option>
                         <option><?php echo $option1; ?></option>
                         <option><?php echo $option2; ?></option>
                       </select>
                     </div>
                   </div></div>
                   <!--Hostel-->

                   <!--Room Type-->
                   <div class="labas"><div class="form-group">
                     <div class="input-group input-group-sm">
                       <select class="form-control" name="r_type" required>
                         <option></option>
                         <option>Executive</option>
                         <option>Standard</option>
                       </select>
                     </div>
                   </div></div>
                   <!--Room Type-->

to this php script:

<?php
  if (isset($_POST['check'])) {
    $hostel = $_POST['hostel'];
    $type = $_POST['r_type'];
    if ($type == 'Standard') {
      $_POST['r_type'] = 'SR';
    }
    else {
      $_POST['r_type'] = 'ER';
    }
    if ($hostel == 'Banabas' && $type == 'SR') {
      $query = 'SELECT room_no, COUNT(room_no) as count FROM banabas_sr GROUP BY room_no HAVING count(room_no) BETWEEN 0 AND 6';
      $ex = mysql_query($query);
      while ($row = mysql_fetch_array($ex)) {
        echo $msg = "<option value='" . $row['room_no'] . "'>" . $row['room_no'] . "</option>";
      }
    }
  }
 ?>

to populate this div:

<div class="labas"><div class="form-group">
                     <div class="input-group input-group-sm">
                       <select class="form-control" name="av_rooms">
                         <?php echo $msg; ?>
                       </select>
                     </div>
                   </div>

Upvotes: 0

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

COUNT() is always going to be at least one for this query (when room_no is not NULL). You need another list of room numbers find the unmatched rooms. Let me assume rooms table:

SELECT r.room_no, COUNT(room_no) as num
FROM rooms r LEFT JOIN
     banabas_sr b
     ON r.room_no = b.room_no
GROUP BY r.room_no
HAVING num BETWEEN 0 AND 6;

I don't like using count as the name of a column because it is also the name of a function.

Upvotes: 1

sagi
sagi

Reputation: 40471

Try this:

$query = 'SELECT room_no, COUNT(room_no) as count FROM banabas_sr GROUP BY room_no HAVING count(room_no) BETWEEN 0 AND 6';

The having clause after the group by reffers to aggregate functions on the columns , you can use max,min,avg and ETC but you have to calculate it inside the having clasue, not use the column from the select part.

Upvotes: 1

Related Questions