用while循环或其他方法消除多余的代码

Question

I have a select drop down list of items on my website and I want the total of each category in the list to have a number beside it that shows the total number of items in that category. I want my directory list of items to update when the user selects a category from my drop down list.

I am having trouble coding it. I have a table with the SiteTypes and SiteTypeID's called sitetypes. And another table called sites with all the urls in the database which is what I want to display to my users. There are different categories of urls, my select menu should display each category of urls when selected. I am having trouble figuring out how to display

$sql = "SELECT COUNT(u.url) AS total, u.SiteTypeID, p.SiteType FROM sites AS u LEFT JOIN sitetypes AS p USING (SiteTypeID) WHERE SiteTypeID=3";
$sq2 = mysqli_query($dbc, $sql);

$sq3 = "SELECT COUNT(u.url) AS total, u.SiteTypeID, p.SiteType FROM sites AS u LEFT JOIN sitetypes AS p USING (SiteTypeID) WHERE SiteTypeID=4";
$sq4 = mysqli_query($dbc, $sq3);

$sq5 = "SELECT COUNT(u.url) as total, u.SiteTypeID, p.SiteType FROM sites AS u LEFT JOIN sitetypes AS p USING (SiteTypeID) WHERE SiteTypeID=5";
$sq6 = mysqli_query($dbc, $sq5);


echo $row['url'] $row['SiteType']; 
echo $row2['url'] $row2['SiteType'];
echo $row3['url'] $row3['SiteType'];

I want to display the total number of urls in the database per category. Any ideas would be appreciated. I tried using while loops but I can't get it to work.

Answer 1

I am a bit confused about exactly what you are trying to get (could you post some sample data and sample output?). However why not merge the 3 queries together?

<?php

$sql = "SELECT COUNT(u.url) AS total, u.SiteTypeID, p.SiteType 
        FROM sites AS u 
        LEFT JOIN sitetypes AS p USING (SiteTypeID) 
        WHERE SiteTypeID IN (3, 4, 5)
        GROUP BY u.SiteTypeID, p.SiteType";

if ($sql2 = mysqli_query($dbc, $sql))
{
    while ($row = mysqli_fetch_assoc($sql2)) 
    {
        echo $row["total"].." - ".$row["SiteTypeID"].." - ".$row["SiteType"]."<br >";
    }
}

?>