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

Question

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.?

Do you want to show each letter of the alphabet even if there isnt a company that starts with that letter? — azzy81
– azzy81, Commented Jan 2, 2014 at 10:59
Keep track of first letters in an array & than cross check & continue if its already exists in it. — Rikesh
– Rikesh, Commented Jan 2, 2014 at 11:00
@SubstanceD The idea was to show only letters of actually existing names. So if there is no company which names starts with A then there no A letter is shown. — mike777
– mike777, Commented Jan 2, 2014 at 11:13

Arvind Sridharan · Accepted Answer · 2014-01-02 11:00:43Z

4

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

answered Jan 2, 2014 at 11:00

Arvind Sridharan

4,0855 gold badges32 silver badges56 bronze badges

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

2 Comments

mike777 Over a year ago

Thank you. I wanted to do it in PHP but this solves my issue anyway.

Arvind Sridharan Over a year ago

@user3153286 can you mark it correct if it has solved your problem?

azzy81 · Accepted Answer · 2014-01-02 11:04:21Z

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>';
}

Sabin Jose · Accepted Answer · 2014-01-02 11:12:29Z

0

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;

edited Jan 2, 2014 at 11:12

answered Jan 2, 2014 at 11:06

Sabin Jose

6589 silver badges19 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related