select syntax help

[thejayjay]

hello, i am displaying a \"product list\" for a site i am working on and I have a question about distinct.

 

this is my current query to mysql

// mysql query

$query = \"select distinct name, brand, wheelsize, finish, price, packageprice, comments, picture, thumbnail from wheels where brand = \'$brand\';\";

 

First question, is that the correct usage of distinct... (obviously i don\'t need the whole data to be distinct but i need all of those fields from the db to display to the enduser)

 

Second question,

 

Say i have a product brand of

X1000

and inside the database i have two or more different \"names\" like

spiffy

awesome

 

While the spiffy and awesome can have multiple wheel sizes / finishes i want the information grouped together in a certain way such as this

 

START OF HTML TABLE

X1000 - spiffy

all the combos that exist of size / finish for the x1000 - spiffy

END OF HTML TABLE

 

START OF HTML TABLE

X1000 - awesome

all the combos that exist of size / finish for the x1000 - awesome

END OF HTML TABLE

 

it boils down to this, with that current select syntax it selects ALL the X1000\'s and they all get glued together. such that the awesome\'s and spiffy\'s are in the same html table together.

 

do i need to nest some select querys and query the database more than once?

 

if you guys need more code pasted from me i can do so.

[ToniVR]

The use of \'distinct\' is actually usefull when selecting a field from a table, but where that field can contain the same value over the entire table (not all field, but some).

When using \'distinct\' in a \'select\', you will only get one result, instead of all matching results (assuming you only select 1 field off course).

So in your example, the \'distinct\' applies to all field you request. In that way, you won\'t get duplicated results. If that is what you want: it\'s the correct syntax.

 

For your second question, you can use different approaches.

1) Sort the result on the \'name\', and use a sort of $current_name ; $pervious_name mechanism in your code to separate the different \'names\'.

2) Use 2 SQL queries instead of one.

3) erm ... there are more, but I cannot get them out of my mind :wink:

 

Hope this helps,

 

Grtz,

Toni.

[thejayjay]

if i was going to use multiple sql queries how would i go about doing it?

 

i just tried nesting some queries but it doesn\'t appear to work

[ToniVR]

Well, multiple SQL queries only works if you know how much different names are in the database, which is quite a stupid solution.

 

Possible solution (and I\'ll use just <div>\'s):

$res = mysql_query("SELECT * FROM wheels WHERE brand=\'$brand\'");

$old_name = "";

$new_name = "";

echo "<div>title";

while ( $info = mysql_fetch_array($res) )

{

   $new_name = $info["name"];

   if ( $old_name != $new_name )

   {

       $old_name = $new_name;

       echo "</div><div>";

   }

   echo $info["brand"]." : ".$info["name"]; # the line showing the wheel-info

}

echo "</div>";

This is off-course imperfect code, but I hope it makes the way of working a bit clearer.

 

Grtz,

Toni.[/code]

[thejayjay]

thanks a ton for your help, i found an easy solution to the problem, but am still lacking one thing

 

if i select all my names / brands first like so

query = \"select distinct name, brand where brand = \'$brandfromform\';\";

 

i therefore get what i need if i nest another query below that that only pulls out the same name\'s

easy as that =]

 

i sorta answered my own question when i typed out what i wanted in plain nasty form hah!

 

thanks a ton for your help...

 

now my only question is howto pull other fields out during my distinct query that i DO NOT want to be distinct (i need two more fields at the same time as doing the name brand...)

[ToniVR]

I quickly jumped into my phpMyAdmin website and executed somthing like

SELECT DISTINCT(manufacturer), register_size FROM processors

and it gave me a perfect list of all manufacturers once, with the matching register-size. When the register-size has 2 values, it is returned twice (man-id the same, reg-id different).

I think that\'s the solution.

[pallevillesen]

I quickly jumped into my phpMyAdmin website and executed somthing like

SELECT DISTINCT(manufacturer), register_size FROM processors

and it gave me a perfect list of all manufacturers once, with the matching register-size. When the register-size has 2 values, it is returned twice (man-id the same, reg-id different).

I think that\'s the solution.

 

There is no difference between:

 

SELECT DISTINCT(manufacturer), register_size FROM processors

 

and

 

SELECT DISTINCT manufacturer, register_size FROM processors

 

They both return distinct rows.

 

P., denmark