Searching Mysql Database for Alphabet Listing?

[goldensea80]

Yes, I dealing with this. Do you have idea? Links to articles?...
Thanks a lot!

[vectorgraphx]

Might be a bit more specific... what exactly are you trying to do?

select a group of records where field value begins with "A" or "B" etc... depending on user input?

if that's the case, use the % wildcard in your where clause.

sql = "SELECT * from yourtable WHERE yourfield LIKE '" & user_input_variable &"%'"

(notice, the wildcard is after the user input variable, not before. if you want to match any part of the field, put the wildcard both before and after the user variable.)

If you're just wanting to sort alpha, that part is much easier. Just use the ORDER clause with either asc (ascending) or desc (descending). if it is an alpha field, it will assume the ascending order is alpha.

sql = "SELECT * from yourtable ORDER by yourfield ASC"

Otherwise, we'll need more info if these examples don't fit your needs.


VG

[goldensea80]

Thanks,
Here is what I did and it work nicely so far:

$where="song_name REGEXP '^[$keywords]'";
$sql="SELECT * FROM tb_songs
WHERE $where
";


To check if a song associate with a keywords exists
function is_song_abc($keywords)
{
global $_CONNECTION;
$where="song_name REGEXP '^[$keywords]'";
$sql ='SELECT count(*) as total
FROM '.TB_SONGS."
WHERE $where";
$result = sql_query($sql);
return mysql_result($result,'total');
}
See the attachment!

[av1]

try this: select * from db where name like ('a%');
takes only those records that start with letter a

[goldensea80]

try this: select * from db where name like ('a%');
takes only those records that start with letter a
That's right.
But in case of the records that start with numbers (0-9), that won't works. But the method with REGEXP works. You can check my block for more detail

[av1]

hmm, for numbers i use select * from db where name like '0%' or name like '1%' or... u get the idea.
i wonder which is faster, ur regexp thing or my 10 or's

[Gmorkster]

REGEXP '^[a-z0-9]' ?

[av1]

dude, u totally missed the point, the guy wants to get a list of records starting with numbers, so it would be '^[0-9]'

[goldensea80]

dude, u totally missed the point, the guy wants to get a list of records starting with numbers, so it would be '^[0-9]'
That's right. I don't know what will be faster (Maybe I'll test it infuture). But the REGEXP code will be shorter for both number and character. You don't have to change the search code, and the keywords array will be: '0-9','a',...,'z'

[Gmorkster]

Guess I did miss the point, heh

Run a test with ab (it's in the Apache bin dir by default) to see which one's faster

[goldensea80]

* For REGEXP '^a'
1000 queries took: 0.48331189155579 second
For LIKE 'a%'
1000 queries took: 0.35055303573608
So that "LIKE" method is faster.

* For Fields started with Numbers
song_name REGEXP '^[0-9]'
1000 queries took: 0.48750901222229 seconds

song_name LIKE '0%' OR song_name LIKE '1%' OR song_name LIKE '2%' OR song_name LIKE '3%' OR song_name LIKE '4%' OR song_name LIKE '5%' OR song_name LIKE '6%' OR song_name LIKE '7%' OR song_name LIKE '8%' OR song_name LIKE '9%'
1000 queries took: 0.5047070980072 seconds

So that "REGEXP" method is faster.

[av1]

thnx for the numbers :)

[Gmorkster]

and this is how it should be, the REGEXP involves one (resource consuming) operation while the 10 LIKE's are 10 operations logically ORed