来自同一行的SUM值，其中多列等于相同的值

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.

任何建议或想法都非常感谢。

1 个解决方案

#1

for($i=1;$i<=4;$i++)
{
$str_1='animal_type'.$i;
$str_2='animal_size'.$i;
$qry="SELECT CONCAT(COUNT(*),'x') AS N,CONCAT($str_1,' ',$str_2) AS M FROM BOOKINGS WHERE bid = '$bid' GROUP BY CONCAT($str_1,' ',$str_2)";
$res=mysqli_query($qry);
while($result=mysqli_fetch_object($res))
{
echo $result->N.' '.$result->M;
}
}

check this..