Re: ForEach Range Problems

On Fri, Jul 31, 2009 at 4:51 PM, Miller, Terion<tmiller@springfi.gannett.com> 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