confusing query needed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

confusing query needed

Post by irealms »

I have an orders table with listing for things like cpu,memory and the like.

I need a query to determine how many different cpu's are entered and how many of each type there are.

What would be the best way to go about this?

I'll need to run a query for each type of component too.

I know i could do a DISTINCT search on the fields but that would just return one of each different entry, i need it to do that and then count how many of each distinct entry there is.
User avatar
aquila125
Forum Commoner
Posts: 96
Joined: Tue Dec 09, 2003 10:39 am
Location: Belgium

Post by aquila125 »

SELECT Count('component') as nr_in_stock, name FROM component_table GROUP BY component

with componentn being something like CPU and name ATHLON XP 2400+
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post by irealms »

i'll explain more clearly.

I have a table called orders, within that table are fields called cpu,memory and others. For now lets think of cpu.

In the cpu table are various types of processor like p4 3g p4 2.6 and others.

I need to run a query to find out how many of each cpu are in the table. So if there are 3 p4 3ghz and 2 p4 2.8ghz i will get a return saying how many different cpu's have been ordered and how many of each type have been ordered.

The problem is the person i am doing this for has products in a text file as his knowledge of php is minimal and he wants to just edit a text file and a form to add new products. So i don't have a database of products to check against.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

THis will show all the DIFFERENT cpu's that have been ordered

Code: Select all

SELECT DISTINCT cpu FROM orders
or have i not fully understood you
User avatar
aquila125
Forum Commoner
Posts: 96
Joined: Tue Dec 09, 2003 10:39 am
Location: Belgium

Post by aquila125 »

Isn't your database structure a bit weird?
I'd use 4 tables

table 1: userdata (userid,name, address,...)
table 2: orderdata (order_id,userid,date_ordered, date_payed, deliverd, whatever)
table 3: components (component_id, type (eg CPU), exact_name (Athlon XP2400+)
table 4: order_component_link (order_id, component_id, nr_of_components)

isn't this easier to work with? (or perhaps i misunderstood the problem..)
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post by irealms »

bech yeah that will give me each different cpu then i need it to say how many of each different cpu there are.
User avatar
aquila125
Forum Commoner
Posts: 96
Joined: Tue Dec 09, 2003 10:39 am
Location: Belgium

Post by aquila125 »

SELECT count(cpu),cpu FROM orders GROUP BY cpu

?
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

Post by irealms »

how do i then echo that out onto a page though once i have them?

so it shows the name of the cpu groups and how many in each group.
User avatar
aquila125
Forum Commoner
Posts: 96
Joined: Tue Dec 09, 2003 10:39 am
Location: Belgium

Post by aquila125 »

Code: Select all

$query="SELECT orderid,count(cpu) as amount, cpu FROM orders GROUP BY cpu";
if (!$result=mysql_query($query)) echo "<br>".mysql_error()."<br>";
echo "<table><tr><td>orderid</td><td>cpu type</td><td>amount</td></tr>";
while ($row=mysql_fetch_array($result)){

echo "<tr><td>".$row['orderid']."</td><td>".$row['cpu']."</td><td>".$row['amount']."</td></tr>";


}

echo "</table>"
gives you a table with the data you requested.. not sure if you needed the orderid...
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

woohooo

Post by irealms »

*bows down to aquila*

Thanks again for that :) worked perfectly!

I've never used count before and wasn't sure how to go about using it, but that shows it nice and easy for me.

Still learning PHP here and with you guys helping i learn more each day :)

Just got to write a query for each type in the database now, hehe.

Thanks again :)
User avatar
aquila125
Forum Commoner
Posts: 96
Joined: Tue Dec 09, 2003 10:39 am
Location: Belgium

Post by aquila125 »

I think it's better to redesign your database structure.... read one of my previous posts with the 4 tables..
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

see if this link doesn't help you :

http://www.onlamp.com/pub/a/onlamp/exce ... ?page=last
Post Reply