Table_sup |
+-------------------+
| supid=>int |
| scompany=>varchar |
| sstate=>varchar |
| scity=>varchar |
| scat=>varchar |
PHP Code
<?php
include "db_connect.php"; // including configuration file
?>
<form name="frmdropdown" method="post" action="sample.php">
<center>
<h2 align="center">Select State</h2>
<strong> Select State : </strong>
<select name="getData">
<option value="">ALL--(<?php echo "" . $totalusa;?>) </option>
<?php
$dd_res=mysqli_query($con,"Select DISTINCT sstate, count(sstate) AS state from sup GROUP BY sup.sstate");
while($r=mysqli_fetch_row($dd_res))
{ echo "<option value='$r[0]'> $r[0]</option>";
}
?>
</select>
<input type="submit" name="Select" value="Select"/>
<br><hr>
Result I get
Select State
-------------
| ALL |
| CA |
| CO |
| FL |
| GA |
What I want
Select State
+-------------------+
| ALL |
| CA (472) |
| CO (7) |
| FL (5) |
| GA (45) |
Like this.each state in dropdown I want total counts of records in each state
You are not fetching second column from the mysqli results. Try as below.
<?php
$dd_res=mysqli_query($con,"Select sstate, count(sstate) AS state from sup GROUP BY sstate");
while($r=mysqli_fetch_row($dd_res))
{ echo "<option value='$r[0]'> $r[0] ( $r[1] )</option>";
}
?>
Because countries can have multiple states and each state can have multiple cities when you join these 1 to many and 1 to many many your state count is inflated. So you need the distinct count of state. The city count is already unique to country and state, thus doesn't need the distinct. where as state is not unique to country city, thus distinct is needed. This of course assumes you want the count of unique states in each country.
SELECT c.name, count(distinct s.name) as statecount, count(ci.name) citycount
FROM countries c,states s,cities ci
WHERE ci.state_id = s.id
and s.country_id = c.id
GROUP BY s.name
Consider the following example: http://rextester.com/ZGYF56786
How to get count of State and city of country using SQL query from database?