PHP - Order a list of database elements

Question

I am displaying a list of items based on a database. The order of the items is by ProductID.

When I add an item to the database, it is automatically assigned a ProductID.

However, I would like to be able to either

a) sort the list alphabetically by name, without changing the ProductID in the database or
b) add another column to the database called, for example: Rank, and have this list display the list by Rank, rather than by ProductID.

Here is my code. Any help in re-writing this section of the code to accomplish either a) or b) would be very helpful!

PS: I'm a little familiar with PHP and databases, but I am by no means an experienced coder.

$category = array_search(strtolower('upright'), $CFG["Category"]);
$product2 = new ProductData();
$where2 = sprintf(" WHERE CategoryID=%d ORDER BY ProductID DESC", $category);
$rows2 = $product2->GetRows($where2);

$count2 = count($rows2);
$line_count2 = 4;
$total_lines2 = ceil($count2/$line_count2);

// Photo
$photo5 = new PhotoData();

$thumb_array5 = array();
$count3 = count($rows2);
for ( $i = 0; $i < $count3; $i++ )
{
    $ret = $photo5->Query($rows2[$i]["ProductID"]);
    if ( $ret != 0 )
    {
        continue;
    }

    $thumb_array5[$photo5->Get("ProductID")] = $photo5->Get("Photo1");
}

So to clarify you want to change the order they are displayed in the database according to what criteria? And this is purely for display in the database or you're displaying this list on a page? If its the former, that's a matter of setting a primary key on the column in the Database, if it's the latter it's a matter of changing your SQL query. — dfoxx
– dfoxx, Commented Jul 24, 2014 at 2:21

Edper · Accepted Answer · 2014-07-30 23:49:01Z

1

Your SELECT query is not shown which I assume is coming from your ProductData() class. If you could change the source of your ProductData() that includes ranking, you could use a variable for ranking, like:

 SELECT  
      @Ranking := @Ranking + 1 AS Rank,
      productID,
      product_name
 FROM   yourTable t, (SELECT @Ranking := 0) r
 ORDER BY  product_name;

Otherwise you could do it in your Php instead:

First change the ORRDER BY to your product name instead of product id:

$category = array_search(strtolower('upright'), $CFG["Category"]);
$product2 = new ProductData();
$where2 = sprintf(" WHERE CategoryID=%d ORDER BY Product_Name", $category);

Then in your php use a variable to do the ranking:

 $count3 = count($rows2);
 echo "<table><tr><th>Ranking</th><th>Product Name</th><th>Product Name</th></tr>";
 for ( $rank = 0; $rank < $count3; $rank++ )
 {
   echo "<tr>";
   echo "<td>$rank+1</td><td>".$rows2[$rank]["Product_name"]."</td><td><img src='".$photo5->Get("Photo1")."'></td>"
   echo "</tr>";
 }
 echo "</table>";

edited Jul 30, 2014 at 23:49

answered Jul 24, 2014 at 2:31

Edper

9,3621 gold badge32 silver badges49 bronze badges

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

3 Comments

Pianosman Over a year ago

I believe this second option, the PHP option, does what it already is doing. That code still sorts by the most recently added item, rather than by the "rank" characteristic. How do I pull from the database by the non-primary column?

Edper Over a year ago

What is the criteria of your ranking? Is it ordered by product name or something else?

Edper Over a year ago

@Pianosman:see my update above and if not correct answer my question above regarding criteria.

Collectives™ on Stack Overflow

PHP - Order a list of database elements

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related