"Pointer Sort" and mySQL

[Invisionary]

I come from a background of a year-worth of college-level C/C++ training when it comes to prior programming, and I'm just starting to use PHP/mySQL.

 

A "pointer sort" (Yes I've searched around everywhere and I'm not able to find anything by that name) that I remember reading about is where you have some array (we'll call it $order[]) that originally consists of the values 1 through $size and when the sort is done contains the sorted order of those rows. I'm probably just calling it by a less-common name, which would explain why I'm not able to find help elsewhere.  :-\

 

For example, a pointer array $order = {1,2,3,4,5} with an array of information $info = {'e','z','m','a','c'} becomes $order = {4,5,1,3,2} for purposes of displaying the contents of $info in sorted order without changing the actual contents of $info.

 

First off, does this go under a more common name that I should be searching for?

 

Second, we get to my specific problem:

 

Assume some mySQL table $result of with three columns (fields):

• "id" : the ID# of the member (int)
  
• "status" : whether the member is 'Active' or 'Inactive' (enum)
  
• "name" : the name of the member (string)
  

 

The ID#'s start with 1, and do not contain all values from 1 to $rows = mysql_numrows($result). (meaning the highest ID# is greater than $rows) Additionally, some of the ID# are 'Inactive', and should not be processed in the display step.

 

What I want to do is display the contents of $result in alphabetical order by name. I obviously don't want to change the contents of my table, as doing so would probably cause problems in other code already in place if I did. Therefore, I need to use a "pointer sort" done at page load that when completed the array $order contains the ID#'s of $result in the appropriate order. The sort structure I had in mind was the selection sort. I've converted the C++ equivalent into PHP below:

    // instantiate and populate the array
$array = array( 1, 2, 3 /*, 4, 5, ... */, $n);
    // instantiate an int size for the size of the array
$size = $n; // doesn't matter what it is, $size is just a pre-determined value that happens to be the size of $array

$startScan;
$minIndex;
$minValue;

for ( $startScan = 0; $startScan < ( $size - 1 ); $startScan++ )
{
    $minIndex = $startScan;
    $minValue = $array[$startScan];

    for ( $index = $startScan + 1; $index < $size; $index++ )
    {
        if ( $array[$index] < $minValue )
        {
            $minValue = $array[$index];
            $minIndex = $index;
        }
    }
    $array[$minIndex] = $array[$startScan];
    $array[$startScan] = $minValue;
}

 

There's two changes that need to be done with this. First, the code would need to compare strings, not ints. It would probably need to do so with strcmp(). Second, it would need to be adjusted to be a "pointer sort", where the contents of array $order are like I described above. Third, it would need to work with the $result table I mentioned. Fourth, it would need to compensate for the restriction I wrote about where there are "gaps" in the ID# column if necessary. (the restriction about only displaying 'Active' ID#'s is of minimal importance until the display step)

 

My biggest problem here is understanding how to rewrite that code so that it accomplishes these goals. Sitting down and writing the actual code is of secondary importance.

 

Can someone please help me with this? I'm on IMs pretty much at any time day or night and respond to emails pretty quickly.

 

[trq]

Ive read most of your post and it appears to me that your problems would be best fixed with a better sql query. If you get your query correct, your data will come out of the database in the order you want. Besides, that fact there is no such thing as a pointer in php there is no need to get php to jump through hoops trying to sort your records.

 

An example may be.

 

SELECT * FROM tbl ORDER BY name;

 

Can we see your query? maybe we can improve on it further.

 

PS: id's (assumed to be auto incrementing primary keys) should not be relied upon for sorting or anything other than relating records to other tables.

[Invisionary]

The query is:

$query="SELECT * FROM raw";

 

Are you telling me that if I have some code...

	// count rows
$rows = mysql_numrows($result);
// row traversal loop
$i = 0;
for ( $i = 0; $i < $rows; $i++ )
{
/* row traversal code here */
}

...that I don't have to do any extraneous PHP coding at all? I can just traverse the display code and $i will refer to $name instead of $id? Or are you saying there's an inherent id value in each row in a mySQL table?

[trq]

I can just traverse the display code and $i will refer to $name instead of $id? Or are you saying there's an inherent id value in each row in a mySQL table?

 

Sorry, you've lost me a little. To get php/mysql to display your data ordered by name, you just need the right query.

 

<?php

  $query="SELECT * FROM raw ORDER BY name";
  if ($result = mysql_query($query)) {
    if (mysql_num_rows($result)) {
      while ($row = mysql_fetch_assoc($result)) {
        echo $row['name']."\n";
      }
    }
  }

?>

 

PS: while loops are much better suited to loooping database results.

[Wildbug]

SQL is your friend. 

 

SELECT * FROM raw WHERE status='Active' ORDER BY name

 

No additional PHP processing required to get that information.