Page 1 of 1
confusing query needed
Posted: Wed Dec 10, 2003 7:32 am
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.
Posted: Wed Dec 10, 2003 7:57 am
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+
Posted: Wed Dec 10, 2003 8:28 am
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.
Posted: Wed Dec 10, 2003 8:51 am
by JayBird
THis will show all the DIFFERENT cpu's that have been ordered
or have i not fully understood you
Posted: Wed Dec 10, 2003 8:56 am
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..)
Posted: Wed Dec 10, 2003 9:05 am
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.
Posted: Wed Dec 10, 2003 9:20 am
by aquila125
SELECT count(cpu),cpu FROM orders GROUP BY cpu
?
Posted: Wed Dec 10, 2003 9:35 am
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.
Posted: Wed Dec 10, 2003 9:42 am
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...
woohooo
Posted: Thu Dec 11, 2003 3:17 am
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

Posted: Thu Dec 11, 2003 3:39 am
by aquila125
I think it's better to redesign your database structure.... read one of my previous posts with the 4 tables..
Posted: Thu Dec 11, 2003 4:12 am
by infolock