my sql maches

[lioslios]

guys i have 2 tables one is the product table and holds the id,productname,price

and second tables is the daysales and holds the slles of the day in the fiels id,productname,price,seller,datetime...

 

my script works as: if a saler sells somthing then the sale stores to the daysales table!

 

and in the end of day i  use a button to diplay the intire table on the webpage and this works too!!!

 

 

 

 

 

if(isset($_POST['sub11']))

            {

                                if ($_SESSION['read_data']==1)

 

                                                {

                            $result = mysql_query("SELECT * FROM $serv");

                           

                           

                            while ($row = mysql_fetch_assoc($result))

                                            {

                                                    echo "<tr>";

                                                foreach($row as $cell)

 

                                                    echo "<td ALIGN=\"center\">$cell<FONT></td>"; 

 

                                                echo "</tr>\n";

                                            }

                                }

           

           

            }

                               

 

 

BUT I WAND TO ADD  a second table that displays the "same products" e.g. if  the product car sells more than one time

 

then i wand to get the car is 3 times selled!!!

the boat 6 times

...

...

...

 

 

any idea for this?

 

 

thanks

[Muddy_Funster]

Use a SELECT COUNT(*) and GROUP BY productname

[lioslios]

ok this works fine!

 

one more question,

 

i get the similar products eg cars says 20 but in the records i have and the quantity field that says 5 cars for a record so the count must be productname * quantity for each record!

 

the result is xorrect only if quantity is 1

 

 

 

$result = mysql_query("SELECT  id,productname,price,quantity,finalprice,seller,datetime, COUNT(productname)AS num FROM orderhistory  GROUP BY productname ORDER BY num DESC");

                           

                           

                            while ($row = mysql_fetch_assoc($result))

                                            {

                                                    echo "<tr>";

                                                foreach($row as $cell)

 

echo "<td ALIGN=\"center\">$cell<FONT></td>"; 

 

                                                echo "</tr>\n";

                                            }

 

 

 

thanks

[Muddy_Funster]

I'm sorry....what?

[lioslios]

my english is no so good sorry

 

can i use this??

 

COUNT(productname*quantity)

 

sample tabe

 

quantity*******productname

    1                            car

    1                            car

    2                            boat

    1                            boat

    1                            car

 

 

so to get the resault

 

boat  = 3

car    = 3

 

as it is this time i get

boat  = 2

car    = 3

 

 

 

thanks

 

 

 

 

 

[DavidAM]

You didn't mention the quantity field in your first post. If you want the total of all quantities for a product, you would use SUM(quantity) instead of COUNT(product). Use the same GROUP BY, and you should be fine.

 

SELECT productname, SUM(quantity)
FROM daysales
GROUP BY productname