Fetching districts of a state using php mysql

Question

I'm currently working on a Pin Code search directory. I want to display districts count and district names of selected state as given in the following example. If there are 4 districts in Delhi state, the data should be rendered as:

<p>There are 4 districts in Delhi</p>
<ul>
<li>District 1</li>
<li>District 2</li>
<li>District 3</li>
<li>District 4</li>
</ul>

Please note that there are various rows of each district but I would like to use rowcount here to count all the rows of each district as 1, e.g. if there are 50 rows containing all 4 different districts, the total number should be 4.

The URL of this page is something like domain.com/pincode/state.php?state=StateName

Here is my code:

<?php   
$state = $_GET['state'];
$con = mysqli_connect('localhost','dbuser','pass','dbname');
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}    
$sql = "SELECT stateName, districtName FROM pincodes WHERE stateName='$state'";
$result = $con->query($sql);
$rowcount = mysqli_num_rows($result);           
if ($result->num_rows > 0) {    
    while($row = $result->fetch_assoc()) {                                              
        echo "" . $row["districtName"]. "";
        echo " | | ";
    }
} else {
    echo "<p>No results Found</p>";
}
mysqli_close($con);
?>

<h1><?php echo $state; ?> Pincode</h1>
<p>There are a total of <?php echo $rowcount; ?> districts in <?php echo $state; ?>.</p>

Please let me know how can I do this.

Thanks

Vikas

for example, if there are total of 8 districts in a state, I want to render districts of a state in the following way: State Name (as heading) There are a total of 8 districts in "State" district 1 district 2 district 3 district 4 district 5 district 6 district 7 district 8 — Vikas Kukreja
– Vikas Kukreja, Commented Sep 17, 2015 at 13:27
Make a loop on the result from the DB and render the data in an html table. Do you need the actual code? — zion ben yacov
– zion ben yacov, Commented Sep 17, 2015 at 13:30
actually I'm not that good in php coding. Could you provide me the code? All I'm coding this website using Google's help :) — Vikas Kukreja
– Vikas Kukreja, Commented Sep 17, 2015 at 13:32
possible duplicate of loop through database and show in table — CodeGodie
– CodeGodie, Commented Sep 17, 2015 at 13:35

Roman Marusyk · Accepted Answer · 2015-09-17 13:54:47Z

0

You may use following query:

SELECT `stateName`,count(`districtName`) as totcount 
FROM `pincodes` WHERE `statename`='$state'
group by `statename`

edited Sep 17, 2015 at 13:54

Roman Marusyk

24.8k27 gold badges83 silver badges127 bronze badges

answered Sep 17, 2015 at 13:39

Phpdev 2014

226 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vikas Kukreja Over a year ago

It's still not working. As a total of row count, I'm getting only 1 district while there are around 9 district of the selected state. Also, district names are not rendering on the page.

Vikas Kukreja Over a year ago

Made some changes to make it working as $sql="SELECT stateName, districtName, count(districtName) as totcount FROM pincodes` WHERE stateName='$state' group by districtName";` It's working like a charm now !! :)

Collectives™ on Stack Overflow

Fetching districts of a state using php mysql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related