PDO检查结果是否存在并呈现字母菜单

Question

I want to check if a query brings an result. For now on I make a rowcount for my query. If the rowcount is > then 0 I make again a new query fetching all results for the query. As I make an alphabethic query loop (i.e. WHERE name LIKE "a%" ) in the range from a-z it would result in 52 queries to complete the task, that's a bit overscored imo. How wolud you face this kind of task ? With the result I want to render a menu with alphabetic ordered links.

A
----
Apple Link
Annanas Link

B
----
Banana Link

etc.

Perhaps there is another way to do that in one query and exlopde in some way the result alphabetic as I need to render for each Letter a new introducing menupoint. I hope you have some usefull pointers to tuneup my performance

Answer 1

Can't you just select everything at once and sort by your column?

SELECT * FROM your_table ORDER BY column_name ASC

Then you can loop through if there are rows and compare the first letter to determine what letter you are on:

$stmt->execute();
if ($stmt->rowCount()) {
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $value = $row['column_name'];
        $first_letter = $value[0];
    }       
}

Once you have the first letter, you can do whatever you want with the value

To extend on this answer, you can use something like this to echo out the values with a header:

// initialize this variable
$current_letter = '';

// if you get results
if ($stmt->rowCount()) {
    // loop through each row
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        // when you loop, get the value
        $value = $row['column_name'];
        // if the value does not have the current letter you are on:
        if ($current_letter != $value[0]) {
            // echo a header for the new letter
            echo "<h2>" . $value[0] . "</h2>";
            // set the new letter to the current letter
            $current_letter = $value[0];
            // echo the actual value
            echo $value . "<br />";
        } else {
            // the value falls under our current letter, echo it
            echo $value . "<br />";
        }
    }
}