Help sorting and alphabetizing results..

moreebolaforyou

basically, what i'm trying to do is return a list of results, but split them up alphabetically, with a "header" letter seperating each letter of the alphabet

i want the results to end up looking something like this:

A
adam
andy
B
bill
bob
C
carl

etc...

the only problem i am having is trying to figure out how to efficiantly add these header letters. i know there has to be a better way than either making 26 queries to the database, or making 26 if statements, but obviously i cannot think of a way.

as you can probably see, i'm rather new to php, so any ideas or help would be greatly appreciated.

neon

Here's a litle PHP level trick.... hope you see the point in it.

$result = array();
$arr = array("adam","bill","clay","james","sarah");
foreach($arr as $data) { // replace with you own database loop
        $first_char = strtoupper(substr($data,0,1)); //get the first char ans make it uppecase
        if(!array_key_exists($first_char,$result)) //no array for that char.... let's make one
                $result[$first_char] = array();
        array_push($result[$first_char],$data); // insert the data
}

moreebolaforyou

that somewhat made sense to me, but how does that lead to me outputting the result?

dalecosp

foreach ($result as $key=>$value) {
        echo $key."<br>";
        foreach ($value as $v) {
                echo ucfirst($v)."<br>";
        }
}

Try adding something like this... 🙂

moreebolaforyou

alright, like i said, i'm rather new to php, so i have some questions.

in the first snippet of code you have this:
$arr = array("adam","bill","clay","james","sarah");

i know you're putting the data into the array, but the data i need is in a database. i.e. i don't know what the data is before the database loop, which you tell me to put in after this.

i'll just leave that one for now, but i'm sure i will have other questions. this is turning out to be something difficult for me, when i thought it would be rather easy. perhaps because i havn't used arrays that much? i'm not sure.

dalecosp

OK, I'd recommend some manual reading and STFW'ing, but...

$query="select * from names";
$result=mysql_query($query);
$arr=mysql_fetch_array($result);

Quid pro quo ... you didn't say which database, so I used the mysql functions...also, you need to connect to the db first, of course...

neon

Ok, you must have somekind of database query, so use that...
Code after query could be something like this, this can be done in database level but let's use the code sniplets from previous posts.

$db_result = mysql_query("SELECT name FROM sometable"); //your database query
$result = array();
while($row = mysql_fetch_array($db_result)) {
        $first_char = strtoupper(substr($row['name'],0,1)); // replace name with correct field name 
        if(!array_key_exists($first_char,$result))  

                $result[$first_char] = array();
        array_push($result[$first_char],$data); 
}
foreach ($result as $key=>$value) {
        echo "<b>$key</b><br>";
        foreach ($value as $v) {
                echo ucfirst($v)."<br>";
        }
}

If you wish I can also provide an additional way to do "the job".

moreebolaforyou

let me try this, thanks a lot guys, it makes a lot more sense. i'm kind of confused by how you're showing to display the results, but i'll toy around with it and see what happens.

moreebolaforyou

i know i'm probably doing something totally wrong here, as i really don't understand the section of the code that's supposed to output the data. but all it is outputting right now is a bunch of 's

here is my code:

$query = mysql_query("SELECT * FROM WiskateVideoReviews WHERE videoReviewStatus='1' ORDER BY videoCompany");
$result=array();

while ($row = mysql_fetch_array($query))
{

 $first_char = strtoupper(substr($row[$videoCompany],0,1));

if(!array_key_exists($first_char,$result))

$result[$first_char] = array();
array_push($result[$first_char],$data);

}
foreach ($result as $key=>$value)
{
echo "$key ";
foreach ($value as $v)
{
echo ucfirst($v)." ";
}
}

i think the copy and paste screwed up the formatting...

neon

$query = mysql_query("SELECT * FROM WiskateVideoReviews WHERE videoReviewStatus='1' ORDER BY videoCompany"); 
$result=array(); 

while ($row = mysql_fetch_array($query)) 
{ 
$first_char = strtoupper(substr($row['WiskateVideoReviews'],0,1));// <-- HERE! not $videocompany, but WiskateVideoReviews
if(!array_key_exists($first_char,$result)) 
$result[$first_char] = array(); 
array_push($result[$first_char],$data); 
} 
foreach ($result as $key=>$value) 
{ 
echo "<b>$key</b><br>"; 
foreach ($value as $v) 
{ 
echo ucfirst($v)."<br>"; 
} 
}

hope it works now

jowestla

meta-code-snippet!

function sort_results()
{

   $LastFirstCharacter='';
   do_your database_connect()

   $query_results=$your_db_channel->execute(select .... order by name);

[ .. your exception handling ...]

while(! $query_results->EOF)
{
    if($LastFirstCharacter!=substr($query_results->fields('name'),0,1))
    {
        $szReturn.=capitalize(substr($query_results->fields('name'),0,1)).'<br>';
        $LastFirstCharacter=substr($query_results->fields('name'),0,1);
    }
     $szReturn.=$query_results->fields('name').'<br>' ;
     $query_results->MoveNext()
}
return($szReturn);
}

moreebolaforyou

i'm beginning to really think that this one was way over my head. i'm still just getting 's.

if anything, i can just go back to displaying it without the sections of the alphabet seperated, no worries.

neon

Ok, the problem is that I don't know your database table structure...
Which colum holds the name you want to print out?
In my last post you can see the yellow comment replace the part inside ' ' with the colum name (don't remove ''-marks) and the try again.

moreebolaforyou

neon, you were right. the first snippet of code i posted, i was close, i just accidently was calling a variable instead of the column name.

with that last suggestion, it sort of fixed things. now it is displaying the header letters (A, B, C) with a corresponding number of 's that should be there compared to the data that's in the database.

it should be displaying the contents of the "videoCompany" row. any ideas?

jowestla, if i cannot get this to work, i will try your suggestion next.

neon

Ah sorry, my mistake.
Format following row:
array_push($result[$first_char],$data);
to something like this:
array_push($result[$first_char],$row['THE_COLUM_NAME_HERE');

and that should do it

moreebolaforyou

wow, thanks a lot, it worked perfect. now i just need to figure out how to get it to display all of the other information i need. i assume i'll need to stick it into the array to do this...i'll read up on that now and if i can't figure out i know who to talk to, ha.

dalecosp

...i'll read up on that now and if i can't figure out i know who to talk to, ha.

No thanks, we have enough Ebola for right now....

🙂

Bijan

Well, let's go back to the beginning! What was wrong if we used 26 queries? Is what you wrote really faster than mysql queries, even if the number is 26?

neon

The fast alternative solution is this:

$sql = "SELECT colum FROM table ORDER BY colum";
$result = mysql_query($sql);
$last_item = '';
while($row = mysql_fetch_row($result)) {
         $first_char = strtoupper(substr($row[0],0,1));
          if($last_item != $first_char) { //ok we have a new char
                 echo "<b>$first_char</b><br>\n";
                 $last_item = $first_char;
          }
           echo $row[0]."<br>\n";
}

There we sort names in database level and then just do the printing stuff at php level.
This solution doesn't give that much freedom for future use, because in the other solution you can pop anything out of the result array.

[edit] thanks dalecosp, I'm just tired. It's midnight around here...

dalecosp

while($row = mysql_retch_row($result)) {

Almost puked when I saw that ...

🙂

j/k, we all makes mistook oncet in a whale...