Support Spidean

Do you like our FREE downloads? How about the FREE support for the FREE downloads? Please help us out and donate any amount! It's fast and easy through PayPal.

Spidean Forums

Board Index > PHP > MySQL 3 table query help

< Last Thread Next Thread >

New Topic Post Reply

MASsIVE

Senior Member

Posts: 106
Registered: 2/15/2003
Status: Offline

posted on 2/14/2007 at 09: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
[url=http://www.themelabs.com/]Theme Labs[/url]

[b]Power Themes For Your CMS Or Cart![/b]

Shawn

Administrator

Posts: 4608
Registered: 10/7/2002
Status: Online

posted on 2/14/2007 at 11: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/15/2007 at 02:16 AM

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

[url=http://www.themelabs.com/]Theme Labs[/url]

[b]Power Themes For Your CMS Or Cart![/b]

Shawn

Administrator

Posts: 4608
Registered: 10/7/2002
Status: Online

posted on 2/15/2007 at 02:27 AM

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: 4608
Registered: 10/7/2002
Status: Online

posted on 2/15/2007 at 02:33 AM

$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/15/2007 at 02:36 AM

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