如何使用PHP和MySQL按字母顺序和数字方式获取数据？

Question

How can I get data alphabetically letter-wise and numeric-wise using PHP and MySQL?

I want to display my data like this:

**0-9      A             B              C           D               E        **
   1       Apple         Beautiful      Cat         DAD             Ear 
   2       Ant           Ball           Cart        Doll            Eat

Please help me approach this using PHP and MySQL.

<ul>
    <?php include "config.php";
        $sql4 = mysql_query("select * from categories");
        $count = mysql_num_rows($sql4);
        while($rows = mysql_fetch_array($sql4)){
     ?>

<li><?php echo $rows['category_name'] ?></li>
<?php } ?>

</ul>

Answer 1

You could create a 2D array: Let the $result be the mysql_result of your query:

$data = array();
while($row = mysql_fetch_assoc($result)) {
  $letter = strtolower(substr($row["category_name"],0,1));
  if(is_numeric($letter)) $letter = "0-9";
  if(!isset($data[$letter])) $data[$letter] = array();
  $data[$letter][] = $row["category_name"];
}

and then iterate it to produce the table you want:

foreach($data as $column=>$values) {
  // $column contains the first letter (or 0-9)
  foreach($values as $value) {
    // $value contains category_name
  }
}

I hope you can finish it from this point, since this site is not supposed to solve all the problem for you.