homepage Welcome to WebmasterWorld Guest from 107.21.187.131
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Visit PubCon.com
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




msg:1258690
 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




msg:1258691
 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




msg:1258692
 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




msg:1258693
 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




msg:1258694
 9:41 pm on Mar 4, 2004 (gmt 0)

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

justageek




msg:1258695
 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




msg:1258696
 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




msg:1258697
 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




msg:1258698
 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




msg:1258699
 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




msg:1258700
 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




msg:1258701
 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




msg:1258702
 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