0-9 and A, B, C, D...Z Query

[bbmak]

I want to do a query of each letter, but somehow, I want to group them like this

0-9 and A, B, C,D...Z

In 0-9, I want all the 0, 1, 2, 3...9 groups in 0-9, and A-Z, each of them have them own page.

I can do them separately , but I want to do them in one page.

 

This is what I have, but it is not working. I hope somebody can help me.

$letter = $_GET['letter'];


if ($letter == '0-9')
{
foreach (range('0','9') as $letter0_9) {
$merchant_09az_query = mysql_query("SELECT * FROM merchant WHERE merchant_name LIKE '$letter0_9%' ORDER BY 'merchant_name' ASC ");
echo '<font size=+2><b><a name=' .$letter0_9. '>' .$letter0_9. '</a></b></font>';
}
}



if ($letter !== '0-9')
{
foreach (range('A','Z') as $letterA_Z) {

$merchant_09az_query = mysql_query("SELECT * FROM merchant WHERE merchant_name LIKE '$letterA_Z%' ORDER BY 'merchant_name' ASC ");
echo '<font size=+2><b><a name=' .$letterA_Z. '>' .$letterA_Z. '</a></b></font>';
}
}

while(...)
...

 

 

[darkfreaks]

can you tell us what is wrong with it so we can have a better time understanding what the code is and is not doing

 

 

also try using === instead of ==

[darkfreaks]

also instead of making it equal zero through nine use  PHP REGEX to match numbers.

 

 

if(preg_match('/[^0-9]/',$letter)) { //if match zero through nine code here

}

 

 

alternatively you could use is_numeric or intval

[bbmak]

Thank you for reply.

 

Actually, it works if I put the while($row fetch->array) in the if statement.

 

it works, but I have to type everything twice, 0-9 and A-Z

if ($letter == '0-9')
{
foreach (range('0','9') as $letter0_9) {
$merchant_09az_query = mysql_query("SELECT * FROM merchant WHERE merchant_name LIKE '$letter0_9%' ORDER BY 'merchant_name' ASC ");
echo '<font size=+2><b><a name=' .$letter0_9. '>' .$letter0_9. '</a></b></font>';

while (){...}
}
}

 

but I want to put the while fetch_array->() outside the if statement, so I can just type once.

So, I just wonder are there any other way to do this. In a shorter and better way.

[scootstah]

You realize that you are running 36 queries minimum, right? That's ridiculous when you could do this in one query.

 

MySQL supports regex. It's a little slow, but probably better than 36 queries.

 

Something like this: SELECT * FROM merchant WHERE merchant_name REGEXP '^[0-9a-z]' ORDER BY merchant_name ASC

 

EDIT: I'm not sure what you mean by grouping, but you can use GROUP BY to group rows or group them into arrays with PHP.

[bbmak]

In my front page, I have this 0-9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Generated with this

foreach(array_merge(array('0-9'),range('A','Z')) as $alphabet) {
echo ' <a href=?menu=merchant_letter&letter=' .$alphabet. '>' .$alphabet. '</a> ';
}

 

when I click each letter, I want to only show merchants start with A, B, C...Z, however, 0-9 is not going to return anything, I have to make the query to 0 1 2 3 4 5...9 because in the database is like

1store, 2store, 3store...

[bbmak]

You realize that you are running 36 queries minimum, right? That's ridiculous when you could do this in one query.

 

MySQL supports regex. It's a little slow, but probably better than 36 queries.

 

Something like this: SELECT * FROM merchant WHERE merchant_name REGEXP '^[0-9a-z]' ORDER BY merchant_name ASC

 

 

 

EDIT: I'm not sure what you mean by grouping, but you can use GROUP BY to group rows or group them into arrays with PHP.

 

Actually thank you very much, I actually use your method to solve the problem, REGEXP '^[0-9]'

[darkfreaks]

don't forget to click topic solved

[bbmak]

Thank

[xyph]

Do it separately. You're not accomplishing anything by grabbing them all in a single query.

 

It can be done on a single page as well, simply use a GET variable. getData.php?letter=a

 

If you must get them all in a single query, use ORDER BY instead. Parse the resulting data with PHP, keeping track of the first letter in the previous row. When the current first letter differs from the last, you know you need to make a new 'group'

 

If you want to run a query to find out if any values exist that start with a given letter (so you aren't linking to empty lists) use something like

 

SELECT DISTINCT IF(

LOWER(SUBSTR(yourColumn FROM 1 FOR 1)) REGEXP '[^a-z]',

        '#',

                LOWER(SUBSTR(yourColumn FROM 1 FOR 1))

        ) as letter

FROM yourTable

ORDER BY letter

[scootstah]

Parse the resulting data with PHP, keeping track of the first letter in the previous row. When the current first letter differs from the last, you know you need to make a new 'group'

 

I haven't weighed in all the options, but you could use the first letter as the index key. Then you don't need to make any comparisons.

 

$array = array('amber', 'billy', 'bob', 'charlie', 'chester', 'damian', 'edgar', 'earl');

foreach($array as $a)
{
$foo[strtolower($a[0])][] = $a;
}

echo '<pre>' . print_r($foo,true) . '</pre>';

 

Array
(
    [a] => Array
        (
            [0] => amber
        )

    [b] => Array
        (
            [0] => billy
            [1] => bob
        )

    [c] => Array
        (
            [0] => charlie
            [1] => chester
        )

    [d] => Array
        (
            [0] => damian
        )

    [e] => Array
        (
            [0] => edgar
            [1] => earl
        )
)

[xyph]

Much easier that way