display data from a certain letter

Question

ok i got this page working well but what would do i have to do to display data from a certain letter?

here is the code i got at present

<html>
    <head>
        <title>MySQLi Read Records</title>

    </head>
<body>

<?php
//include database connection
include 'db_connect.php';

//query all records from the database
$query = "select * from contacts";

//execute the query
$result = $mysqli->query( $query );

//get number of rows returned
$num_results = $result->num_rows;

//this will link us to our add.php to create new record
echo "<div><a href='add.php'>Create New Record</a></div>";

if( $num_results > 0){ //it means there's already a database record

    echo "<table border='1'>";//start table
    //creating our table heading
    echo "<tr>";
        echo "<th>Firstname</th>";
        echo "<th>Lastname</th>";
        echo "<th>Username</th>";
        echo "<th>Action</th>";
    echo "</tr>";

    //loop to show each records
    while( $row = $result->fetch_assoc() ){
            //extract row
            //this will make $row['firstname'] to
            //just $firstname only
            extract($row);

            //creating new table row per record
            echo "<tr>";
                echo "<td>{$name}</td>";
                echo "<td>{$surname}</td>";
                echo "<td>{$mobile}</td>";
                echo "<td>";
                    //just preparing the edit link to edit the record
                    echo "<a href='edit.php?id={$id}'>Edit</a>";
                    echo " / ";
                    //just preparing the delete link to delete the record
                    echo "<a href='#' onclick='delete_user( {$id} );'>Delete</a>";
                echo "</td>";
            echo "</tr>";
    }

    echo "</table>";//end table

}else{
    //if database table is empty
    echo "No records found.";
}

//disconnect from database
$result->free();
$mysqli->close();

?>

</body>
</html>

i am wanting to place multiple entries like the following to dislay under the right letter

<h1>A</h1>
echo "<td>{$name}</td>";
                    echo "<td>{$surname}</td>";
                    echo "<td>{$mobile}</td>";

<h1>b</h1>
echo "<td>{$name}</td>";
                    echo "<td>{$surname}</td>";
                    echo "<td>{$mobile}</td>";


ECT ECT

what i am trying to acchieve is to dispaly all the surnames that begin with a then b

i have found this bit of code on this page http://www.sitepoint.com/forums/showthread.php?303895-Display-Data-Beginning-with-a-Particular-Letter

but how would i make this work for me?

i am novice (extremly) so any help be fantastic i tried to read tutorials i find it better with hands on :)

Updated ***************************

this is working great but now it only lists one line this is my code

<html>
<head>
<title>MySQLi Read Records</title>
</head>
<body>
<?php
//include database connection
include 'db_connect.php';

//query all records from the database
$query = "  SELECT name,
         surname,
         mobile,
         UPPER (LEFT(surname, 1)) AS letter
    FROM contacts 
ORDER BY surname";

//execute the query
$result = $mysqli->query( $query );

//get number of rows returned
$num_results = $result->num_rows;

//this will link us to our add.php to create new record
echo "<div><a href='add.php'>Create New Record</a></div>";

if( $num_results > 0){ //it means there's already a database record

    echo "<table border='1'>";//start table
    //creating our table heading


    //loop to show each records
    while( $row = $result->fetch_assoc() ){
            //extract row
            //this will make $row['firstname'] to
            //just $firstname only
            extract($row);

            //creating new table row per record
            if (!isset($lastLetter) || $lastLetter != $row['letter'])
{
    echo '<h1>', $row['letter'], '</h1>';
    $lastLetter = $row['letter'];
     echo "{$surname}";
}


    }


}else{
    //if database table is empty
    echo "No records found.";
}

//disconnect from database
$result->free();
$mysqli->close();

?>
</body>
</html>

You want to list all letters on the same page or you want to list all letter per page request or form request? — Prix, Aug 18 '13 at 12:11

Prix · Accepted Answer · 2013-08-18T13:17:51.100

Since you are learning, I will give you the idea of how you can archive what you want and the functions you can use.

As you have mentioned you want all records displayed on the same page with their own alphabet letter as the header.

There is a few ways of doing what you want, the most common is to return the data ORDERed BY the field you want on the order you want, in this case ASC.

This will list all your records in alphabetical order.

From there you can use the function LEFT to extract the first letter as another field.

Now here is where you will use some PHP, from the above you already have your records ordered and the first letter for each record.

You can use something like this inside your while:

if (!isset($lastLetter) || $lastLetter != $row['letter'])
{
    echo '<h1>', $row['letter'], '</h1>';
    $lastLetter = $row['letter'];
}

Basically the above will check if $lastLetter has been set/defined which is not for the first record so it will enter the if, print your first header and set $lastLetter to the first letter.

After the first record it will be matching the $lastLetter against the $row['letter'] and once they are not equal, it prints the header again and update the $lastLetter with $row['letter'] which is the current letter.

Your MySQL query would look like this:

  SELECT *,
         LEFT(firstname, 1) AS letter
    FROM contacts 
ORDER BY firstname

However it is always better to define all fields you need instead of catching all the fields in case you have more fields on the table in question:

  SELECT firstname,
         surname,
         mobile,
         LEFT(firstname, 1) AS letter
    FROM contacts 
ORDER BY firstname

NOTE in case your names are not normalize you can use the UPPER function to make the letter uppercase like this:

  SELECT firstname,
         surname,
         mobile,
         UPPER(LEFT(firstname, 1)) AS letter
    FROM contacts 
ORDER BY firstname

See more about the UPPER function here

thanks for your answer please check back in a while i am trying to work with this answer now. i have updated my query to $query = "select * fROM contacts ORDER BY surname ASC"; just trying to update the rest to get it to work. — , Aug 18 '13 at 12:56
@thenashone you will need to use `*, LEFT(surname, 1) AS letter` like mentioned above to grab the letter. — Prix, Aug 18 '13 at 12:58
ok thast grate but is there a way to place all the surnames with s or S under the same heading? or beable to call the letter instead of automaic? — , Aug 18 '13 at 13:29
yes see update at bottom, you can use UPPER to make all letter uppercase. — Prix, Aug 18 '13 at 13:36
thanks ill update my question as its only listing one entry i have made a few with the surname begining with S — , Aug 18 '13 at 13:47
@thenashone put the `echo "{$surname}";` outside the IF the IF is only to print the Letter not the rest. You can put the if after your function extract($row); and keep the rest the same. — Prix, Aug 18 '13 at 14:02

score -1 · Answer 2 · edited May 23 '17 at 10:31

-1

I suggest you do the following steps.

Update your mysql query statement

$query = "select * from contacts order by name_field";

Name_field or whatever is the column name so that you get the result set sorted in dictionary order.

Keep $previous to keep track of what letter you added last time.

Use this function (refer startsWith() and endsWith() functions in PHP) at each row to find whether the name value starts with a different letter from $previous. If there is a change then update $previous and include "" tags with the letter as you desire.

function startsWith($haystack, $needle)
{
    return $needle === "" || strpos($haystack, $needle) === 0;
}

edited May 23 '17 at 10:31

Community

1
1

answered Aug 18 '13 at 12:15

HumbleLearner

103
2
9

please can you simplify this for me as i am new to all this – Aug 18 '13 at 12:20
@YourCommonSense Thats why I included "order by name_field" in the query. What's wrong? – HumbleLearner Aug 18 '13 at 12:21
@thenashone unfortunately, this site is not your personal tutoring service. You are supposed to read some manual first and then ask a certain question. not a step-by step tutorial – Your Common Sense Aug 18 '13 at 12:24
@YourCommonSense What are you suggesting do it in php or mysql? Because your earlier comment was telling the other way. – HumbleLearner Aug 18 '13 at 12:26
@humblelearner yes i do i can display the data just wanted it like i posted above lol. – Aug 18 '13 at 12:27
@your common sense i fully understand what your saying but if i have a basic knowledge in something and i would seek help when in trouble i have read the information in manuals and tutorials but i am new to this so most of it dont make to much sense hence why i said i am better with a hands on approach. – Aug 18 '13 at 12:27
@thenashone don't reason to anyone in stackoverflow – HumbleLearner Aug 18 '13 at 12:30
@YourCommonSense You have bluntly said its wrong. I suppose entitled? – HumbleLearner Aug 18 '13 at 12:31
@YourCommonSense Great. I understood everything that you said. Got it! – HumbleLearner Aug 18 '13 at 12:36
Just re-read the question. May be I got it wrong - it seems it contradicts with the title. My apologies then. If you edit your answer I'll be able to take out my downvote – Your Common Sense Aug 18 '13 at 12:39
REmoving my comments as irrelevant ones. I beg my pardon again. – Your Common Sense Aug 18 '13 at 12:43
@YourCommonSense Downvote if you want to downvote. The answer stays that way. May be someone else can edit. Don't make it like its about you by begging pardons as this is not a site to learn about you. – HumbleLearner Aug 18 '13 at 12:53
Oh sorry. I mean some minor edit, as it will reset the timer and let me vote back. – Your Common Sense Aug 18 '13 at 12:58

score -1 · Answer 3 · answered Aug 18 '13 at 12:20

First of all you should change your SQL query to:

SELECT * FROM contacts ORDER BY name ASC

this will sort all of the contacts from A to Z. Now, to able to determine an initial alphabet of a contact name use substr function...

$initial_letter = strtoupper(substr($name, 0, 1));

display data from a certain letter

3 Answers3