confusing query needed
Moderator: General Moderators
confusing query needed
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.
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.
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.
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.
THis will show all the DIFFERENT cpu's that have been ordered
or have i not fully understood you
Code: Select all
SELECT DISTINCT cpu FROM ordersIsn'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..)
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..)
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>"woohooo
*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
Thanks again for that
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