MASsIVE
Senior Member   Posts: 106 Registered: 2/15/2003 Status: Offline
|
posted on 2/14/2007 at 04:52 PM |
Hi all
I have 3 tables im pretty sure I need data from to achieve a top ten
selling items in a certain category if possible I would greatly appreciate
some assistance, comments and or solution.
Heres the table structures
product [table]
*************
productID
name
catID
price
approved
Which is related to:
sold [table]
******
productID
total [total sold]
and
category [table]
******
catID
catDesc
parentID
What Im attempting is to get the Top Ten products sold from a certain
category then duplicating the file and simply change the catID in the query
to get multiple blocks on my site to display Top Ten for several
categories.
Then connecting via php to give result
Any help would be greatly appreciated. ____________________
Thanks
Regards
MASsIVE
Theme Labs
Power Themes For Your CMS Or Cart!
|
| |
| |
Shawn
Administrator   Posts: 4538 Registered: 10/7/2002 Status: Online
|
posted on 2/14/2007 at 06:05 PM |
Well it depends upon what fields you want to get, but here is a quick try
assuming category 1:
$cat = 1;
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$query = "SELECT catDesc FROM category WHERE catID=$cat";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
echo $row['catDesc'];
$query = "SELECT * FROM product, sold WHERE
product.productID=sold.productID AND product.catID=$cat ORDER BY sold.total
DESC LIMIT 10";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
echo $row['name'] . "<br />";
echo $row['price'];
}
-Shawn
|
| |
MASsIVE
Senior Member Posts: 106 Registered: 2/15/2003 Status: Offline
|
posted on 2/14/2007 at 09:16 PM |
Seems awfully close
This is the result im looking for
quote:
1: E-VH-01 € 45.00
2: E-HostNet-O2 € 50.00
3: W-Injection € 55.00
4: G-Recon € 45.00
5: E-Ware € 45.00
6: G-FireIce € 45.00
7: G-Kemet € 50.00
8: W-Tungsten € 67.00
9: X-VoltII € 45.00
10: X-MistII € 45.00
Naturaly the order would be different
I don't need this $link = mysql_connect('localhost', 'mysql_user',
'mysql_password'); this is taken care of with API.
And CatID name appears at top
Your result which is so close I can taste it 
quote:
PostNukeW-Tungsten
67.00G-FireIce
50.00E-PCShop
35.00W-Lithium
50.00W-Injection
55.00E-Ware
50.00E-Datapacket
50.00X-DNA
45.00G-Recon
50.00G-Kemet
50.00
____________________
Thanks
Regards
MASsIVE
Theme Labs
Power Themes For Your CMS Or Cart!
|
| |
Shawn
Administrator Posts: 4538 Registered: 10/7/2002 Status: Online
|
posted on 2/14/2007 at 09:27 PM |
The only difference is the echo statements and how it is outputted. I KNOW
that you can organize some echo statements, I've seen you
-Shawn
|
| |
Shawn
Administrator Posts: 4538 Registered: 10/7/2002 Status: Online
|
posted on 2/14/2007 at 09:33 PM |
$cat = 1;
$query = "SELECT * FROM product, sold WHERE
product.productID=sold.productID AND product.catID=$cat ORDER BY sold.total
DESC LIMIT 10";
$result = mysql_query($query);
$num = 0;
while ($row = mysql_fetch_assoc($result)) {
$num++;
echo $num . ": " . $row['name'] . " GAY EURO SYMBOL " . $row['price']<br
/>";
}
[Edited on 2/15/2007 by Shawn]
|
| |
MASsIVE
Senior Member Posts: 106 Registered: 2/15/2003 Status: Offline
|
 posted on 2/14/2007 at 09:36 PM |
hahaaa funny
I got it working after I looked at code closer I couldnt figure this our
for the world of me, been tryin for 3 days obviously I need some query
books.
Thanks man this works like a charm, again you come through 
____________________
Thanks
Regards
MASsIVE
Theme Labs
/>
/>
Power Themes For Your CMS Or Cart!
|
| |
MASsIVE
Senior Member Posts: 106 Registered: 2/15/2003 Status: Offline
|
posted on 2/15/2007 at 09:58 AM |
Looking at this query I thought it would been so much more complex Shawn, I
was into INNER JOIN, LEFT JOIN gonna have to do some reading and testing
The basic queries I have no problem with
Thanks again, this has been a huge help ____________________
Thanks
Regards
MASsIVE
Theme Labs
/>
/>
/>
/>
Power Themes For Your CMS Or Cart!
|
| |
Printer Friendly Page
