skip to Main Content

I would like to display businesses from within our database as an alphabetical list, sorted by first letter. Currently, Not all letters are represented. As a visual example, the user page would look like this:

Display business by name –
| A | C | F | G | H | L | …
(wheras each letter above would display the businesses that start with that letter.)

However, I’m having difficulty with displaying the mysql query result of the ‘first letters’ on the php page.
To generate the result as a query, I have the following line:

$result4 = mysqli_query($conn,"SELECT DISTINCT LEFT('bname', 1) FROM 'bisEntry');

(This came from Returning the DISTINCT first character of a field (MySQL))

Then, to display the "first letter" of each business on the page, I have tried the following:

while ($row = mysqli_fetch_array($result4, MYSQLI_ASSOC)) {
        echo "| ". $row['bname'] ." ";
        }

With this first example, the array is correctly ‘counting’ for the different first letters and displaying this:

Display business by name –
| | | | | | | …

However, the actual letters aren’t showing. I’m hung up on how to convert the results from the array, to the actual letters that will be displayed.

AND I tried…

while($row = mysqli_fetch_array($result4))
        {
            foreach ($row['category'] as $name) {
            echo "| ". $name;
            }

However, this returned the error
foreach() argument must be of type array|object which leads me to beleive I am not actally creating the array correctly.

I’m sure the solution is in fromt of my nose, but I am just not able to untangle it. Thanks for the assistance.

2

Answers


  1. That’s because you do not have column bname selected directly, so column name becomes LEFT('bname', 1) (e.g. [["LEFT('bname', 1)" => 'A'], ["LEFT('bname', 1)" => 'C']]). Add alias as column name:

    $result4 = mysqli_query($conn, "SELECT DISTINCT LEFT('bname', 1) AS bname FROM bisEntry");
    

    You can always debug what is returned in your variables using xDebug or simply var_dump($row);

    Login or Signup to reply.
  2. When you need to fetch only one column from query result since (PHP 8 >= 8.1.0) you can use mysqli_fetch_column in next way:

    <?php
    $conn = &$mysqli;
    $result4 = mysqli_query($conn,"SELECT DISTINCT LEFT(`bname`, 1) FROM `bisEntry`");
    
    while ($letter = mysqli_fetch_column($result4, 0)) {
        echo "| ". $letter ." ";
    }
    

    https://phpize.online/s/Xn

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search