Jump to content


Photo

select syntax help


  • Please log in to reply
6 replies to this topic

#1 thejayjay

thejayjay
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 22 January 2003 - 12:14 PM

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.

#2 ToniVR

ToniVR
  • New Members
  • Pip
  • Newbie
  • 6 posts
  • LocationBelgium

Posted 22 January 2003 - 12:24 PM

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.

#3 thejayjay

thejayjay
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 22 January 2003 - 12:29 PM

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

#4 ToniVR

ToniVR
  • New Members
  • Pip
  • Newbie
  • 6 posts
  • LocationBelgium

Posted 22 January 2003 - 12:40 PM

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]

#5 thejayjay

thejayjay
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 22 January 2003 - 12:46 PM

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...)

#6 ToniVR

ToniVR
  • New Members
  • Pip
  • Newbie
  • 6 posts
  • LocationBelgium

Posted 22 January 2003 - 05:29 PM

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.

#7 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 23 January 2003 - 11:03 AM

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
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users