1

I have a table with all product sales, the table contain:

id
date
product
make
model
cost
taxes
invoice

Each product sold gets individually inserted to the table so if the customer purchased 3 different products with the same invoice number lets say ( 0009 ) then the table will look like this

product = example one
make = samsung
model = demo one
cost = 100
taxes = 19
invoice = 0009

second inserted product

product = example two
make = samsung
model = demo two
cost = 200
taxes = 38
invoice = 0009

Now the problem is:

I need to show all emited invoices during the day or week or month so I use the follow code:

$invoices = mysqli_query($db, "SELECT * FROM `sales` WHERE `date` = '$today'");

The result I get is all products sold during this date. But I need to show only 1 product per invoice ( because I only need to display the invoice numbers ) and if the invoice has more than 1 product it gets repeated. I know this is because I am not filtering the query but the thing is I do not really know how to achieve this. Anyone know how to do this?

I tried with LIMIT 1 but I get only 1 result ...

EDIT NOTE PLEASE

Some products has no invoice number because I sold those products to friends so the field ( invoice ) is empty, thats why I need to even filter this, that product sales with empty invoice in column do not get displayed

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Kamikaza
  • 1
  • 1
  • 18
  • Are there any constraints on the table? – Tripp Kinetics Jul 21 '15 at 15:00
  • you mean restrictions? No no one, because we are in test mode I have set all privs to the mysql user – Kamikaza Jul 21 '15 at 15:02
  • 3
    Your question is unclear. Which columns do you want to appear in your result set? Can you show us a sample desired output? – Tim Biegeleisen Jul 21 '15 at 15:04
  • Do you only want to see unique invoice numbers? Does it matter if any products are reported back? – the_pete Jul 21 '15 at 15:07
  • Exactly, to show unique invoice number only once per sale no matter how many products sold by under the same invoice it only gets displayed once and if there is no invoice ( is empty in database ) then it gets no displayed – Kamikaza Jul 21 '15 at 15:14

1 Answers1

1

try 

"SELECT * FROM `sales` WHERE `date` = '$today' GROUP BY `invoice`"

In any case, I think you should always have an invoice number, maybe a different kind, even when you sold them to friends. :)

If you don't want empty invoice numbers:

// Assuming by empty you mean NULL, please modify accordingly
"SELECT * FROM `sales` WHERE `date` = '$today' AND `invoice` IS NOT NULL GROUP BY invoice"
Jason J. Nathan
  • 7,422
  • 2
  • 26
  • 37
  • if the invoice field is empty it will not be displayed right? – Kamikaza Jul 21 '15 at 15:10
  • It will still show up in the above result set, but only once, I'll update the answer – Jason J. Nathan Jul 21 '15 at 15:11
  • Just a small thing. How is the correct form is this query? It gives me error FROM `sales` WHERE `invoice` = .$show[invoice]."); // this show invoice is giving me error – Kamikaza Jul 21 '15 at 15:45
  • For PHP variables, you can access it within double quotes, but not array values. See my answer to this question http://stackoverflow.com/questions/20165804/parse-error-syntax-error-unexpected-t-string-in-on-line-17/20165859#20165859 – Jason J. Nathan Jul 21 '15 at 16:58