homepage Welcome to WebmasterWorld Guest from 54.166.53.169
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
UNIQUE or DISTINCT MySQL/PHP Queries
How can I cull out the lowest cost item from a list of similar items
Internet Engineer

10+ Year Member



 
Msg#: 3078 posted 8:52 pm on Mar 4, 2004 (gmt 0)

Hello, all!

I have a product database (provided by a partner site) which has 10,000+ items. These items are identified by a unique ID number, which in this case is the manufacturers' product items. To do away with EXTENSIVE "massaging" of the data, I am "dumping" the raw text-delimited data into our MySQL database, in its own unique table. I query the database to display different types of items, and want to do it MORE EFFICIENTLY than the structure of the data allows.

For example, I want to show all the "cars." A user clicks on the "cars" page. I then show a list of brands, such as "Ford" and "Dodge." The user selects a brand, and then I show individual models, such as "Mustang," and "Escort," and "Thunderbird."

Here's the conundrum: The database may have several dozen "versions" of a "Mustang." When I show the different individual models, I want to JUST show the lowest priced model. For example, I just want to show the $8999 Escort, the $19999 Mustang, etc., NOT all the variants (which introduce a lot of options and stuff which takes longer to query and display).

The data, as it's "dumped" into the MySQL table, has nothing to distinguish the lowest price variant from the highest price variant, except for two key fields: The price, and the "product line." The Price is the different value which I can order my query by, and the product line is COMMON to all the variants of a particular model. So, you can have 20 "variants" of an Escort, each delineated by price, but all having a common "product line" description of "Ford Escort."

I need to group them by product line, THEN pick ONLY the lowest price version from the list. Is this possible using a UNIQUE or DISTINCT modifier on the "where" clause of the MySQL query? If so, what is the proper format?

Thanks in advance for any help here!

 

Internet Engineer

10+ Year Member



 
Msg#: 3078 posted 9:04 pm on Mar 4, 2004 (gmt 0)

Maybe I can clarify, by showing a small, sample database (here, comma-delimited):

MAKE, MODEL, PRICE, PRODUCT_LINE
Ford, Escort A, $8999, Ford Escort
Ford, Escort B, $9395, Ford Escort
Ford, Excort C, $10299, Ford Escort
Ford, Escort Z, $12199, Ford Escort
Ford, Mustang A, $18999, Ford Mustang
Ford, Mustang B, $21999, Ford Mustang
Ford, Mustang C, $23999, Ford Mustang
Ford, Mustang Z, $27999, Ford Mustang

In my query, I want to JUST show the "Escort A" and the "Mustang A" models on the same page, because they are the lowest price. I save the B, C and Z models to display on a following page if the user clicks on the Escort or Mustang link.

How do I sort through the data to JUST list the Escort A and Mustang A, and disregard the rest, (based on low price and unique product line description)?

Hope that clarifies!

slade7

10+ Year Member



 
Msg#: 3078 posted 9:28 pm on Mar 4, 2004 (gmt 0)

the way you have it there in mysql 3.23.36 this would work I think:

SELECT product_line, MIN(price)
FROM mytable
GROUP BY product_line
ORDER BY whatever

better to change your table info around if possible. The product_line field is redundant.. would be better like this:

ITEM_ID (unique)
MAKE
MODEL
PRICE

Then use:
SELECT make, model, item_id, MIN(price)
FROM mytable
GROUP BY make, model
ORDER BY whatever

If that doesn't work as expected, you may need to do:

SELECT product_line,
MIN(price) as minprice
FROM mytable
GROUP BY product_line
HAVING price = minprice
ORDER BY whatever

justageek

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3078 posted 9:33 pm on Mar 4, 2004 (gmt 0)

Should just be:

select distinct PRODUCT_LINE, anything_else_you_want from the_table order by PRICE

JAG

slade7

10+ Year Member



 
Msg#: 3078 posted 9:41 pm on Mar 4, 2004 (gmt 0)

Yah - that'll do it too with a: limit 0,1

justageek

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3078 posted 9:43 pm on Mar 4, 2004 (gmt 0)

The distinct will only return 1 so no need for the limit ;-)

JAG

<added>That is if that's the only data you're bringing back otherwise you do need the limit as slade7 said</added>

coopster

WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3078 posted 10:26 pm on Mar 4, 2004 (gmt 0)

LIMIT will constrain the number of rows returned from the result set, so don't use that. The way it is stated here, you will only get one row back, period.

If you want the product line and price (or any other columns), use the format suggested by slade7 in msg #3:

SELECT 
DISTINCT PRODUCT_LINE,
MIN(PRICE)
FROM mytable
GROUP BY PRODUCT_LINE
ORDER BY PRODUCT_LINE
;

If you are just building a unique list, use DISTINCT as suggested by JAG. However, he is correct in that you want to use it without any other columns, otherwise you are going to need a GROUP BY clause (not a LIMIT clause). JAG was right, but should remove the anything_else_you_want from his statement there:
SELECT 
DISTINCT PRODUCT_LINE
FROM mytable
ORDER BY PRODUCT_LINE
;

Nice tips by the way, slade7 and JAG.

slade7

10+ Year Member



 
Msg#: 3078 posted 2:05 am on Mar 5, 2004 (gmt 0)

Thanks coopster - I seldom use distinct.. but often use COUNT(DISTINCT column) etc...

That explains why I get errors sometimes with distinct.

<add>When I come up with a query off the top of my head, it's usually no more (or less) than 4 error messages away from working - so keep that in mind :)</add>

Netizen

10+ Year Member



 
Msg#: 3078 posted 12:39 pm on Mar 5, 2004 (gmt 0)

Won't the price be ordered incorrectly as it is a string field, not an integer field?

justageek

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3078 posted 12:47 pm on Mar 5, 2004 (gmt 0)

I just assume that since the sample is a text file that the data will be in the correct format when pulled into the database. I would hope so anyway :-)

JAG

coopster

WebmasterWorld Administrator coopster us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3078 posted 1:19 pm on Mar 5, 2004 (gmt 0)

True, Netizen. I seen the dollar sign in the data, but, like JAG, I made an assumption that the data was actually being stored as numeric. You can still get the sort the way you want, you just have to get a bit fancier and use MySQL STRING functions:
SELECT 
DISTINCT product_line,
MIN(ABS(SUBSTRING_INDEX(price,'$',-1))) AS price
FROM cars2
GROUP BY product_line
ORDER BY product_line
;

And if you want the dollar sign back on:
SELECT 
DISTINCT product_line,
CONCAT('$',MIN(ABS(SUBSTRING_INDEX(price,'$',-1)))) AS price
FROM cars2
GROUP BY product_line
ORDER BY product_line
;

If there were commas and decimal points in the price column, it gets even more complex. I'm with JAG, store the data as decimal and format the output. Much easier.

Internet Engineer

10+ Year Member



 
Msg#: 3078 posted 6:06 pm on Mar 5, 2004 (gmt 0)

Thanks, all, for the GREAT help!

I am now "tweaking" it to get it just right. BTW, the price data IS in numeric form (as a double(10,2) value), and I put the dollar sign in on the PHP page code.

Again, thanks!

Netizen

10+ Year Member



 
Msg#: 3078 posted 7:34 pm on Mar 5, 2004 (gmt 0)

Just double checking - I don't like to make assumptions and it is a common mistake to order string fields as numbers.

Good luck with the rest.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved