list - A to Z in php

[magicfun]

Hello, im not sure of the proper terminology of what I want to do, but if you could hear me out that would be great.

 

The end result I want a listing from my database like this:

 

A

....title

....title

 

B

....title

....title

 

C

....title

....title

 

like that. I am not sure exactly what i need in my select statement to accomplish this.

 

Its an alphabetical listing that has the letter with matching titles to that letter on one page.

 

Any help would be appreciated. Thanks!

[Prismatic]

SELECT * FROM table WHERE .... ORDER BY column ASC

 

[plodos]

im using this

 

select * from person where condition is true ORDER BY name ASC

 

andy

john

yasn

 

[marcus]

you can try something like this

 

<?php
$r = range('a','z');

$sql = "SELECT * FROM `titles` ORDER BY title ASC";
$res = mysql_query($sql) or die(mysql_error());

$i = 0;
while($row = mysql_fetch_assoc($res)){
echo "<b>".$r[$i]."</b><br>\n";
$title_first_letter = substr(strtolower($row['title']),0,1);
if($title_first_letter == $r[$i]){
	echo $title . "<br>\n";
}

echo "<br>\n";
$i++;
}
?>

[magicfun]

// MySQL call for biographies table

		$query = "SELECT * FROM $dbBiographyTable WHERE approval = 1  ORDER BY title ASC";
		$result = mysql_query( $query ) or die ( "Error on db query biographies.php" );
		mysql_close( $session );
		$num_rows = mysql_num_rows( $result );   

		if(mysql_num_rows($result) == 0){ 
		   echo("Nothing to Display!"); 
		} 

		$r = range('a','z');

		$i=0;
		while($row = mysql_fetch_array($result)){

			echo "<b>".$r[$i]."</b><br>\n";
			$title = $row['title'];
			$title_first_letter = substr(strtolower($row['title']),0,1);
			if($title_first_letter == $r[$i]){
				echo $title . "<br>\n";
			}

			echo "<br>\n";
			$i++;
		}

 

That code only outputs the first two titles for some reason like this:

 

a

Abraham

 

b

Billy

 

c

 

d

 

e

 

f

 

ect...

 

what am i doing wrong?

[magicfun]

::bump:: anyone?

[gizmola]

Yeah it's because the way you have coded, you only print out a title from the db if it happens to have the same first letter as the letter in the range that matches the value of $i, which you increment every time a row is read from the database.  The two loops (one to produce the letter headings, one to fetch rows from the result set, have nothing to do with each other, and it's just happenstance that the first two rows have names that start with A and B respectively, so you get a match.  There is no way to make that structure work out.  You could fix this in a number of different ways, but what I'd suggest is this:

 

$namesarray = array();
foreach(range('a', 'z') as $value) {
    $namesarray[$value] = array();
}


$names = array('alan', 'bill', 'bob', 'dan', 'david', 'fred', 'henry', 'niamh', 'tim', 'tracy', 'zoe');
foreach($names as $value) {
    $namesarray[$value[0]][] = $value;
}


foreach($namesarray as $letter => $names) {
    echo "$letter \n";
    foreach ($names as $name) {
        echo "$name \n";
    }
}

 

The output of my test script is this:

 

[david@penny ~]$ php rmerge.php

a

alan

b

bill

bob

c

d

dan

david

e

f

fred

g

h

henry

i

j

k

l

m

n

niamh

o

p

q

r

s

t

tim

tracy

u

v

w

x

y

z

zoe

 

 

 

So hopefully you get the basic idea -- fill in an array during the fetch loop.  When you exit it, you're ready to output the list.  Add the html you need, as my command line test couldn't use html.  So basically $names was just used as a substitute for your result set.

[magicfun]

using that method I would have to manually add each name to the array? The names are in my database. How would i extract the names from the database and group them without manually adding each name?

 

I'm getting closer here...

[magicfun]

anyone?

[gizmola]

No, hehe.  All you need to do is take the code where I make the $names array.  Instead of a static definition of the names, you replace that code with your mysql select and fetch loop, only inside each fetch you simply get the name and add it to the names array.  Something close to this should work:

 

$namesarray = array();
foreach(range('a', 'z') as $value) {
    $namesarray[$value] = array();
}

$names = array();

$query = "SELECT * FROM $dbBiographyTable WHERE approval = 1  ORDER BY title ASC";
$result = mysql_query( $query ) or die ( "Error on db query biographies.php" );
mysql_close( $session );
$num_rows = mysql_num_rows( $result );   
         
if(mysql_num_rows($result) == 0) {
   echo("Nothing to Display!");
} else {
    while($row = mysql_fetch_array($result)) {
        $names[] = $row['title'];
    }
}


foreach($names as $value) {
    $namesarray[$value[0]][] = $value;
}


foreach($namesarray as $letter => $names) {
    echo "$letter \n";
    foreach ($names as $name) {
        echo "$name \n";
    }
}

[magicfun]

Ok, that code works when i take out this otherwise the loop runs twice

 

$namesarray = array();

foreach(range('a', 'z') as $value) {

    $namesarray[$value] = array();

}

 

$names = array();

[magicfun]

How would i get the 'id' to work with that loop?

 

I have an id in the database and i need to get the value of it along to go to a details page where it is the specific biography. I am not sure how to pass it along without being an array.

 

here is what I have:

 

$query = "SELECT * FROM $dbBiographyTable WHERE approval = 1  ORDER BY title ASC";
		$result = mysql_query( $query ) or die ( "Error on db query biographies.php" );
		mysql_close( $session );
		$num_rows = mysql_num_rows( $result );   
			    
		if(mysql_num_rows($result) == 0) {
		   echo("Nothing to Display!");
		} else {
		    while($row = mysql_fetch_array($result)) {
		    	   $id = $row['id'];
			   $title[] = $row['title'];
			   
		    }
		}


		foreach($title as $value) {
		    $namesarray[$value[0]][] = $value;
		}


		foreach($namesarray as $letter => $title) {
		    echo "<br/>" . "$letter" . "<br/>";
		    foreach ($title as $name) {
		    	
			  echo("<div><a href='biographydetail.php?id=$id' class=''>$name</a></div>");
			   //echo "$name". "<br/>";
		    }
		}

[\code]

[gizmola]

You need to read up on PHP Arrays and learn the basic syntax.  I've illustrated a variety of techniques in this thread, but it's not going to help you code if you don't understand what I'm doing.  This code should be pretty close to what you asked for, although it might have syntax issues.

 

$query = "SELECT * FROM $dbBiographyTable WHERE approval = 1  ORDER BY title ASC";
         $result = mysql_query( $query ) or die ( "Error on db query biographies.php" );
         mysql_close( $session );
         $num_rows = mysql_num_rows( $result );   
               
         if(mysql_num_rows($result) == 0) {
            echo("Nothing to Display!");
         } else {
             while($row = mysql_fetch_array($result)) {
                   $rows[] = $row;              
             }
         }
         
         
         foreach($row as $value) {
             $title = $value['title'];
             $namesarray[$title[0]] = array('title' => $value['title'], 'id' => $value['id']);
         }
                  
         foreach($namesarray as $letter => $title {
             echo "
" . "$letter" . "
";
             foreach ($title as $value) {
                 echo '' . $value['title'] . '';               
             }
         }

[\code]

[magicfun]

Hmm, I think I am getting what is happening here...but for some reason it isn't rendering anything. The syntax makes sense...as far  as I can tell.

[magicfun]

I've checked and rechecked the code above..but it doesnt seem to be working. Any ideas why it doesn't work?

[sasa]

try

<?php
/*
$namesarray = array();
foreach(range('a', 'z') as $value) {
    $namesarray[$value] = array();
}

$names = array();
*/
$query = "SELECT * FROM $dbBiographyTable WHERE approval = 1  ORDER BY title ASC";
$result = mysql_query( $query ) or die ( "Error on db query biographies.php" );
mysql_close( $session );
$num_rows = mysql_num_rows( $result );   
         
if(mysql_num_rows($result) == 0) {
   echo("Nothing to Display!");
} else {
$letter = 'A';
    while($row = mysql_fetch_array($result)) {
        $letter1 = strtoupper(substr($row['title'],0,1));
        while ($letter <= $letter1)	echo $letter++,"<br />\n";
        echo '--', $row['title'],"<br />\n";
    }
    while (strlen($letter) < 2)	echo $letter++,"<br />\n";
}
/*

foreach($names as $value) {
    $namesarray[$value[0]][] = $value;
}


foreach($namesarray as $letter => $names) {
    echo "$letter \n";
    foreach ($names as $name) {
        echo "$name \n";
    }
}
*/
?>

[gizmola]

Probably has a bug in the code.  Did you check the web error log? 

 

You could also try adding this code temporarily at the top of your script:

 

error_reporting(E_ALL);

ini_set("display_errors", 1);