Re: Alphabetical pagination

On Wed, Jul 15, 2009 at 3:28 AM, Ashley Sheridan<ash@ashleysheridan.co.uk> wrote: > On Wednesday 15 July 2009 06:35:04 Jim Lucas wrote: >> Andrew Ballard wrote: >> > On Tue, Jul 14, 2009 at 3:38 PM, Miller, >> > >> > Terion<tmiller@springfi.gannett.com> wrote: >> >> I am trying to make a page that displays a-z like a b c d e etc as links >> >> then when you click open one it reloads itself and shows only the query >> >> results that go with that letter...i'm not getting it....I get a page >> >> that says ARRAY over and over... >> >> >> >> What I have so far: >> >>                             <?php >> >> >> >> if(!isset($_SESSION['RestaurantList'])) >> >> >> >> { >> >>                                      // List not grabbed yet, >> >> so run >> >> query and store in $_SESSION >> >> >> >>                                                       >> >>  $sql = "SELECT >> >> DISTINCT name FROM restaurants  GROUP BY name DESC"; >> >> >> >> $result = mysql_query($sql) or die(mysql_error()) ; >> >> >> >> $count = mysql_num_rows($result); >> >> >> >> echo $count; >> >> >> >> >> >> while($row = mysql_fetch_array($result)) { >> >> >> >> >> >> >> >> $name=array($row['name'],0,1); >> > >> > Why are you setting the value of $name to an array? If you try to echo >> > $name after this statement (as you are below), PHP will echo the word >> > "Array". >> > >> >> //$name = array('name'); >> >> >> >>                                                       >> >>  echo "<a >> >> href=\"page.php?name=" .$name. "\"> >> >> $name</a>\n"; >> >> >> >> } >> >> >> >> >> >>                                                       >> >>              } >> >> >> >> >> >> >> >> $alphabet = range('A', 'Z'); >> >> >> >> foreach ($alphabet as $letter) { >> >> >> >> echo '<a href="' . $_SERVER['PHP_SELF'] >> >> >> >> . '?letter=' . $letter . '">' . $letter . >> >> '</a>'; >> >> >> >> } >> >> >> >> >> >> >> >> >> >> >> >> >> >> ?> >> > >> > If the list of restaurants is very long, I wouldn't store it in a >> > session variable. It is the same for every person who visits your >> > site, so there is little use storing separate redundant copies in >> > session scope where it will needlessly fill up disk space and/or >> > memory. >> > >> > As far as the query is concerned, you could do this: >> > >> > <?php >> > >> > $index_letter = $_GET['letter']; >> > >> > if (preg_match('/^[A-Za-z]$/', $index_letter)) { >> > >> >     $sql = "SELECT DISTINCT name FROM restaurants WHERE name LIKE >> > '$index_letter%' GROUP BY name DESC"; >> > >> > //.... >> > ?> >> > >> > I would also consider whether you really need the keyword DISTINCT in >> > the query. In a properly normalized table, name should probably >> > already be distinct (and constrained by a UNIQUE index on that >> > column). >> > >> > Andrew >> >> Also, since this is MySQL, you need to make sure you make it non >> case-sensitive by using ILIKE instead of LIKE in that SELECT statement. >> >> $sql = "SELECT DISTINCT name FROM restaurants WHERE name iLIKE >> '$index_letter%' GROUP BY name DESC"; >> >> -- >> Jim Lucas > > You only have to use that if your character set is not latin1 or > latin1_swedish_ci. Using LIKE on those character sets is case-insensitive > too. > > -- > Thanks, > Ash > http://www.ashleysheridan.co.uk > ... or any of the other *_ci character set collations. ;-) I generally use utf8_unicode_ci in MySQL. Andrew