CODEWITHSUNDEEP
phpmysql
mike777
mike777

Reputation: 3

PHP or MySQL - how to display only one table row out of many with the same value

I wrote this simple code which outputs first letters of values (in this case names of companies) stored in every each table row: 

include_once('db.php'); //connection with database

$result = mysql_query("SELECT distinct Name FROM companies ORDER BY Name");

while ($row = mysql_fetch_array($result)) {
$letter = substr($row[0], 0, 1);     
echo '<p><a href="#">' . $letter . '</a>/p>';
}

The companies table contains names of companies and the $letter variable contains first letter of those names.

Apparently in the companies table there are many names starting with the same letter.

Now, how to display only one letter "A", one letter "B", one letter "C" etc. out of many letters "A", "B", "C" etc.?

Upvotes: 0

Views: 1089

Answers (3)

Sabin Jose
Sabin Jose

Reputation: 658

Use this SQL statement 

SELECT DISTINCT SUBSTR(Name,1,1) FROM companies ORDER BY Name;

This will return distinct values from first letter of Names;

Upvotes: 0

azzy81
azzy81

Reputation: 2269

To do this in PHP you can create a blank array and push the first letter of each company onto the array if it doesnt already exist like this: 

include_once('db.php'); //connection with database

$result = mysql_query("SELECT distinct Name FROM companies ORDER BY Name");

$letters = array();

while ($row = mysql_fetch_array($result)) {
    if(!in_array(substr($row[0], 0, 1), $letters)){
        array_push($letters, substr($row[0], 0, 1));
    }    
} 

foreach($letters as $l){
    echo '<p><a href="#">' . $l . '</a></p>';
}

If you want to show all letters of the alphabet even if a company doesnt start with that letter you can use the PHP range function like this:

foreach (range('a', 'z') as $letter) {
    echo '<p><a href="#">' . $letter . '</a></p>';
}

Upvotes: 0

Arvind Sridharan
Arvind Sridharan

Reputation: 4045

Why don't you put that in your query itself?

Modify the query to 

SELECT distinct SUBSTRING(Name,1,1) FROM company ORDER BY SUBSTRING(Name,1,1)

Here's the sql fiddle

Upvotes: 4

Related Questions