TalkPHP - Display Records From Database

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - Display Records From Database (http://www.talkphp.com/general/4852-display-records-database.html)

shoaibmunir

08-15-2009 12:35 PM

Display Records From Database

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 (Post 27773)

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.

All times are GMT. The time now is 10:33 PM.