0

I have a product sales table(sales to walk-in customers). table row value like

invoice_no customer_name     product_name             item_code           rate   total etc..
 001           abcd        jasmine,rose,marigold    JS001,R001,MG001    5,6,3
 002           xyz         jasmine,rose,marigold    JS001,R001,MG001    5,6,3

Now i want to display and comma(,) to be removed

   $query="select * from product_sales where invoice_no='".$invoice_no."' ";

   Customer Name:abcd

  s.no       product_name    item_code     rate
   1          jasmine        JS001          5
   2          rose           R001           6
   3          marigold       MG001          3

So how can I fetch vaulues from table using where clause.

karthik
  • 110
  • 3
  • 18
  • [Please see this quesion already resolved][1] [1]: http://stackoverflow.com/questions/28643017/how-to-add-edit-and-delete-comma-separated-value-of-database/28643142?noredirect=1#comment45584619_28643142 – Anil Baweja Feb 21 '15 at 07:23
  • ok thanks Anil baweja – karthik Feb 21 '15 at 07:25

1 Answers1

1

Fix the cause, not the symptom.

Redesign your table structure. Something like this:

Two tables: Master & Details

Master table will contain common values. Like,

MasterID   InvoiceNo   CustomerName   BillDiscount  GrandTotal

Details table will contain details of each item. Like,

DetailID   MasterID    ItemID    Qty    UnitPrice    Discount   NetAmount

Now you can fetch the required data:

SELECT * FROM Master WHERE InvoiceNo=100

SELECT D.* 
FROM Details D JOIN 
     Master M ON M.MasterID=D.Master 
 WHERE M.InvoiceNo=100
Raging Bull
  • 18,593
  • 13
  • 50
  • 55
  • yes, i have customer table separately and using unique ID for each customer, the above table just display Customer ID or name and display product details in table where what they purchased. i know only little bit english – karthik Feb 21 '15 at 07:30
  • @karthik: Your english is pretty good, don't worry about that. I am just saying that you should change the table design you are currently following. Instead of storing different items in one single record, you should use an additional table (Details) just like I said. You will know the flexibility of this kind of design when you work with it. – Raging Bull Feb 21 '15 at 07:33
  • My question is how to remove comma(,) and display the product name. Each product name should be follow the quantity, rate and total. – karthik Feb 21 '15 at 07:36
  • @karthik: For splitting it with comma as delimiter, please refer [**this link**](http://stackoverflow.com/questions/22163595/comma-separated-string-into-tables-column-in-sql-server). I should say, you will have to use very complicated query for the result if you follow the same table design. That is why I insist you to change the design. – Raging Bull Feb 21 '15 at 07:44
  • ok, i'll refer it. thank you – karthik Feb 21 '15 at 08:26