Page 1 of 1

Alphabetical Listing

Posted: Fri Jul 18, 2003 5:44 am
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......

Posted: Fri Jul 18, 2003 7:12 am
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

Posted: Sun Jul 20, 2003 3:21 am
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

...

Posted: Sun Jul 20, 2003 10:23 pm
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.