Paging Alphabetically -- PHP/MySQL

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
Chrisman
Forum Newbie
Posts: 1
Joined: Sat Oct 25, 2008 10:08 pm

Paging Alphabetically -- PHP/MySQL

Post by Chrisman »

Hello everyone, this is my first post here...Hope you can help me :)

How would I go about paging alphabetically? Like page one contains items beginning with A-G, page two contains items beginning with H-O, etc...? I've just hit a brick wall with this one. Googled it first thing, but wording this is kinda difficult lol.

I'm pretty sure I would use wildcards, but I only know how to do a query for one letter...not multiple. :?

------------------
Edit: Solved Problem. Here's the solution for anyone in the future...

Code: Select all

SELECT * FROM tablename
WHERE columnname LIKE '%[abcdefg]'
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: Paging Alphabetically -- PHP/MySQL

Post by novice4eva »

you would need to make query like:

Code: Select all

 
SELECT * FROM yourTable WHERE coulmnName LIKE 'A%' OR  coulmnName LIKE 'B%' OR ...... coulmnName LIKE 'G%' 
 
Now to make this SQL in PHP:

Code: Select all

 
$arr = array();
$Sql='SELECT * FROM yourTable WHERE 1 = 1 AND ';
foreach(range(A,G) as $data)
   array_push($arr,"coulmnName LIKE '".$data."%'";
$Sql.=implode(' OR ',$arr);
 
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Paging Alphabetically -- PHP/MySQL

Post by VladSun »

It's far better to use string comparison in this case:
[sql]WHERE    name>='A'    AND    name<'H'[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
Hannes2k
Forum Contributor
Posts: 102
Joined: Fri Oct 24, 2008 12:22 pm

Re: Paging Alphabetically -- PHP/MySQL

Post by Hannes2k »

Hi,
or using the regex capabilities of MySQL. (just use Google: MySQL + Regex)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Paging Alphabetically -- PHP/MySQL

Post by VladSun »

Hannes2k wrote:Hi,
or using the regex capabilities of MySQL. (just use Google: MySQL + Regex)
It's even slower than LIKE ...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Paging Alphabetically -- PHP/MySQL

Post by VladSun »

In fact, column LIKE 'A%' is processed as column >='A' and column<'B', so this way an index could be used (BTREE, not HASH).
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply