Vikas Kukreja Vikas Kukreja - 2 months ago 7
PHP Question

How to Fetch query result 1 time from each starting alphabate

The question's heading may confuse you so please allow me to explain it here. I'm currently hosting a pin code search website of India. In a mysql query, I'm trying 20 post offices of a state. I want every single query result should start from a unique alphabate, i.e. the first result should show the result of a post office starting from alphabet A, the second result should show the post office name starting from alphabet B and so on till the 20th record as given below :

Anand Vihar

Bangla Sahib

Connaught Place

Delhi Haat

Eshwar Colony

Friends Colony

Gitanajali Enclave

Harish Nagar

Indira Colony

Jangpura

Karol Bagh.... till the 20th record starting with unique alphabet initial.

Below is the current query I'm using :-

$sql="SELECT `states`, `districts`, `pincodes`, `postoffices`, count(`postoffices`) as totcount
FROM `table_pins` WHERE `states`='$state'
group by `postoffices`
LIMIT 20";

$result = mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

Answer

I believe you want to group by first letter. Here is one way of doing it

select substr(postoffices,1,1) as alphabet
from table_pins
group by substr(postoffices,1,1)

you can check similar answers here