On Fri, Jul 31, 2009 at 4:51 PM, Miller,
Terion<tmiller@xxxxxxxxxxxxxxxxxxxx> wrote:
> //Show all restaurants that start with $letter not
> between "A" and "Z"
>
> $other = ctype_digit($letter);
>
> foreach(range('0','9') as $other) {
>
> $sql = "SELECT DISTINCT ID, name, address
> FROM restaurants
> WHERE name LIKE '{$other}%' ";
>
> $result = mysql_query($sql) or die(mysql_error());
>
> while($row = mysql_fetch_assoc($result)){
>
> $name = $row['name'];
>
> printf(
> '<a href="view.php?ID=%s"><b>%s</b><br />%s'
> . '<br /><br /></a>',
> $row['ID'],
> $row['name'],
> $row['address']
> );
> }
> }
Why are you running 10 individual queries to search for restaurants
whose name begins with a number?
There are (at least) two simple, sargable alternatives available that
will work in MySQL to do the work in one shot:
SELECT DISTINCT ID, name, address
FROM restaurants
WHERE name LIKE '0%'
OR name LIKE '1%'
OR name LIKE '2%'
OR name LIKE '3%'
OR name LIKE '4%'
OR name LIKE '5%'
OR name LIKE '6%'
OR name LIKE '7%'
OR name LIKE '8%'
OR name LIKE '9%'
(It can use an index on the `name` column and it works, but it's
pretty verbose. Performance might suffer a little if it involves a
table scan because of the OR's, but I'm not sure about that.)
SELECT DISTINCT ID, name, address
FROM restaurants
WHERE name >= '0' AND name < 'A';
(Its a lot shorter because it takes advantage of string collation. Any
string that begins with the character '0' will be greater than (or
equal if the string is exactly '0') than '0'.)
Also, if your table is set up correctly, you do not need the DISTINCT
keyword in your query. Each restaurant should appear exactly once in
the `restaurants` table.
And just for fun... do you have any restaurants in your list whose
name begins with a punctuation mark or some other non-alphanumeric
character? :-)
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
