Display Records From Database

shoaibmunir · 08-15-2009, 12:35 PM

I wana show records from database

All data starts with "A" under Alphabet A

All data starts with "B" Under Alphabet B

formate should be minimum 2 column.

Like that exmple i have upload please open below link

http://shoaibmunir.com/talkphp1.htm

i will be please by helping you

JaoudeStudios · 08-15-2009, 01:33 PM

SELECT * FROM tableA WHERE column1 LIKE 'A%'

shoaibmunir · 08-15-2009, 01:38 PM

thank's for your reply but i want to display all a-z records.

did you check link please check then maybe you will got my point.

JaoudeStudios · 08-15-2009, 01:53 PM

Well you could run this query 26 times - but I wont recommend it.

You have a few options:
- (MYSQL) You could use a stored procedure
- (MYSQL) Use CASE & CONCAT in mysql
- (PHP) Loop each result and substr the 1st letter and append an array accordingly

The php option is probably the simplest to implement but maybe not the most efficient.

shoaibmunir · 08-15-2009, 01:55 PM

how can i do can you explain me with write php code?

adamdecaf · 08-15-2009, 05:59 PM

Quote:

Originally Posted by shoaibmunir

how can i do can you explain me with write php code?

PHP Code:


			
mysql_connect....



$result = mysql_query("SELECT * FROM tableA WHERE column1 LIKE 'A%'");



$count = mysql_num_rows($result);



for ($n = 0; $n < $count; $n++) {

   $tmp = mysql_fetch_array($result, MYSQL_ASSOC);

   $ltr = substr($tmp['name'], 0, 1);

   $data[$ltr][$n] = $tmp;

}

This is oversimplified but it should give you some footing on the problem.

Village Idiot · 08-15-2009, 06:58 PM

A query like this should work:

Code:

SELECT 
    name,
    substring(name,1) as first_letter
FROM
    table
WHERE
    [conditions]
ORDER BY 
    name

Sample result (each space separates columns)

Code:

Apple A
Apricot A
Bannana B
Guava G

This will give you each name in alphabetical order and the letter it starts with (so save you some data processing on the script end). There are countless ways to work with this, I'll let you figure which one you want out. But here is one way to sort it into an array (my PHP is very rusty and this is not tested, so look at it as more of a concept):

PHP Code:


			
<?
foreach($array_results as $x)
{
    $results[$x["first_letter"][] = $x["name"];

}
?>

Dont run 26 queries for each letter of the alphabet, it will be much much faster to grab all the data in an orderly fashion then work with it once you have it.