Alphabetical Listing

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Nunners
Forum Commoner
Posts: 89
Joined: Tue Jan 28, 2003 7:52 am
Location: Worcester, UK
Contact:

Alphabetical Listing

Post by Nunners »

Hi Folks,

Something quite simple, that I can't think how to solve.

I've got a list of PLACES, which I need to list in alphabetical order. That's the easy bit:

Code: Select all

$places_sql="select * from places where ".$reference."='".$reference_id."' ORDER BY PLACE";
	$places_result=mysql_query($places_sql,$connect);
	while ($places=mysql_fetch_array($places_result)) {
		echo("<li><a href="explore.php?type=place&PLACE_NO=".$places&#1111;"PLACE_NO"]."">".stripslashes($places&#1111;"PLACE"])."</a></li>\n");
	&#125;
What I'd like to do, is have at the top of the page, a menu, with letters A to Z, and using name/hrefs be able to jump up and down the list. The only way I can do it so far is:

Code: Select all

FOR ($counter=1;$counter<27;$counter++) &#123;
	echo("<li><a name="".$letter&#1111;$counter].""></a></li>");
	$places_sql="select * from from places where ".$reference."='".$reference_id."' AND PLACE LIKE '".$letter&#1111;$counter]."%' ORDER BY PLACE";
	$places_result=mysql_query($places_sql,$connect);
	while ($places=mysql_fetch_array($places_result)) &#123;
		echo("<li><a href="explore.php?type=place&PLACE_NO=".$places&#1111;"PLACE_NO"]."">".stripslashes($places&#1111;"PLACE"])."</a></li>\n");
	&#125;
	echo("<li>&nbsp;&nbsp;&nbsp;&nbsp;Return to <a href="#top">Top</a></li>");
&#125;
$letter[] is predefined with letters A to Z corresponding to 1 to 26.

I've found this is quite process intensive as it runs 26 different sql queries. Is there an easier way?

Hope someone can help......
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

We do this a bit on our site and have a little function for creating the a-z box with the links:

Code: Select all

/**
 * Used to create A to Z links
 *
 * Function creates a table containing A to Z links that link to an
 * alphabetical list on the same page. Allows user to determine which 
 * colours they wish table cells to be.
 * @param    string    $colour1     The colour of the odd numbered 
 *                                  table cells (hex value).
 * @param    string    $colour2     The colour of the even numbered 
 *                                  table cells (hex value).
 * @param    array     $excluded    Any letter(s) which must not have 
 *                                  a link attached.
 * @param    int       $display     Whether or not to echo the box 
 *                                  directly (defaults to on - 1).
 * @return   string    The HTML for the A to Z box.
 */
function element_create_a2z_box($colour1, $colour2, $excluded=array(), $display=1)
{
	$html = <<<END

<table border="0" cellspacing="0" cellpadding="0">
<tr>
END;
	for ($letter = 'A', $i = 1; $letter != 'AA'; $letter++) {
		$colour = element_alternate_row_colour($colour1, $colour2, $i);
		$html .= <<<END

	<td width="20" height="30" class="aligncentermiddle" style="background-color: $colour">
END;
		if (in_array($letter, $excluded)) {
			$html .= <<<END

		<b>$letter</b>
END;
		} else {
			$html .= <<<END

		<a href="#$letter"><b>$letter</b></a>
END;
		}
		$html .= <<<END

	</td>
END;
		if ($letter == 'M') {
			$html .= <<<END

</tr>
<tr>
END;
		}
	}
		
	$html .= <<<END

</tr>
</table>
END;
	if (!empty($display)) {
		echo $html;
	}
	return $html;
} // end func

/**
 * Function to be used to alternate row colours in a table.
 *
 * Uses the modulus to check which colour to use for the current row.
 * Takes a number and checks whether it is odd or even.
 * @param    string    $colour1     The colour of the odd numbered 
 *                                  table cells (hex value).
 * @param    string    $colour2     The colour of the even numbered 
 *                                  table cells (hex value).
 * @param    int       $i           Odd or even number which is 
 *                                  incremented by one for each test.
 * @return   string    The hex value of the colour of the row.
 */
function element_alternate_row_colour($colour1, $colour2, &$i)
{
	$colour = ((++$i % 2) == 0) ? $colour1 : $colour2;
	$colour =  (strpos($colour, '#') === false) ? '#'.$colour : $colour;

	return $colour;
} // end func
As for the list itself - you don't need 26 separate queries, just the one and then some logic in your listing code to determine whether or not the next record has the same starting letter as the previous one.

Mac
Nunners
Forum Commoner
Posts: 89
Joined: Tue Jan 28, 2003 7:52 am
Location: Worcester, UK
Contact:

Post by Nunners »

Hi Mac,

I'm not sure that does what I want it to?

The bit I have problems with is putting in the <a name="$letter"></a> bit.

I've simplied the creation of the "menu" at the top, by creating a FOR statement which works from A to Z (obviously)! The problem I have is when doing the mysql statement, I don't know how to check for the first letter.

Any ideas?

Thanks
Nunners
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

...

Post by kettle_drum »

Make a counter and then print the first <a name="a"> and then print your results - each time you print a result check to see if its first letter is what the counter is set to. If it is then dont do anything, if it isnt (the next letter has started) print the <a name>

A lot simpler thing to do would be to make the menu pass the letter you want to the url and then only show that letter on each page.

page.php?show=B

"SELECT * FROM blah WHERE name LIKE '$_GET%'"

This will make pages smaller and reduce load times for all the 56k.
Post Reply