Taking from mysql

[akademik]

Hello everyone,

I want to take all data from one table and print them in specific order. For example, there are two columns; first column specify the car factory and second column specify car model so table looks as follows:

company ¦ model
------- ¦ -----
mercedes ¦ cl500
toyota ¦ yaris
opel ¦ vectra
toyota ¦ corolla
mercedes ¦ e320
toyota ¦ rav 4

Now I want to select and print data like this:

opel:
vectra

mercedes:
cl500
e320

toyota:
corolla
yaris
rav 4

but I must use only one query. If I use this query:

$q = "SELECT *, COUNT(company)"
."FROM ".TBL_CARS." GROUP BY company";

it will just count the number of items in each group:

opel 1
mercedes 2
toyota 3

but I actually need to print the model name...

Is this query is OK or should I use some other way, is "COUNT(company)" can work in my case?

Thanks...

[John_Keates]

If you count you get a number...

[akademik]

OK, so what is the right way for this?

[John_Keates]

I'm not an sql expert but I don't know if what you want to do is possible in 1 query... You can 1st select all companies and then select from the list of companies the model.

What I'd do is make separate tables since you can just ID both of them but that's only efficient if there is some information stored about the company.

I just think you should query per-company. Like:

$a = "SELECT * FROM TBL_CARS WHERE company='company'"

[akademik]

I was thinking about that, but the problem is that there is many companies and not only for cars, I just gave simple example. It would use to many queries to get all data...

I will try to use some php code to get this thing to work.

Thanks any way...

[John_Keates]

Maybe this is a question for Databases. Check out that forum :-)

[WesleyC]

Just about the only way to do this with one query would be...

"SELECT company, model FROM ".TBL_CARS." ORDER BY company ASC"

Then, companies will at least be next to each other in the returned results. You can then programmatically parse the results and simply check if the current car has the same company as the previous car...

$company = ""; 
while ( ( $row = mysql_fetch_assoc( $queryResult ) )!== false ) 
{ 
 if ( $company!= $row["company"] ) 
 { 
 echo $row["company"].":<br />\n"; 
 $company = $row["company"]; 
 } 
 echo $row["model"]."<br />\n"; 
} 

[edited by: WesleyC at 1:28 pm (utc) on July 27, 2007]

[akademik]

Thanks WesleyC

I will try this solution as soon as possible.