I am trying to get an output of the amount of animal types for each "booking" record in my database, the database structure is similar to this.
bid, name, business, animal_type1, animal_size1, animal_type2, animal_size2, animal_type3, animal_size3, animal_type4, animal_size4
An example record would be
1, John Smith, John Smith's Pets, Dog, Small, Dog, Medium, Cat, Small, Dog, Giant, Dog, Large
I am trying to get an output like this.
2x Small Dogs
1x Medium Dogs
1x Small Cat
1x Giant Dog
This is the code I have been playing with based on tutorials but not having much luck, and this method would require running a separate query for each type and size.
<?php
$sql = "SELECT animal_type1,animal_type2,animal_type3,animal_type4,animal_type5 AS total
FROM bookings WHERE bid = '$bid' AND `animal_type1 = 'Dog' AND booking_status = 'Unbooked'";
$result = mysqli_query($GLOBALS["mysqli_ston"], $sql);
$rows = mysqli_fetch_array($result); ?>
<?php echo $rows['COUNT(animal_type1) + COUNT(animal_type2) + COUNT(animal_type3) + COUNT(animal_type4) + COUNT(animal_type5)'] ?>
<?php echo $rows['total']; ?>
Any suggestions or ideas are much appreciated.