Need help in fetching the record - MySql

Databases
1

Hi

I had a table name tbl_purchase

CREATE TABLE IF NOT EXISTS tbl_purchase (
pur_id bigint(20) NOT NULL auto_increment,
pur_code varchar(100) NOT NULL,
pur_tot_prod varchar(100) NOT NULL,
pur_prod_code text NOT NULL,
pur_dt date NOT NULL,
pur_status char(1) NOT NULL,
PRIMARY KEY (pur_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Records in the table :

|---------------------------------------------------------------------------------------------|
| Pur_id | pur_code | pur_tot_prod | pur_prod_code | pur_dt | pur_status |
|---------------------------------------------------------------------------------------------|

1 PU1001 3 P2001|||P2002|||P2003 2011-02-03 Y
2 PU1002 2 P2001|||P2003 2011-02-04 Y
3 PU1002 1 P2004 2011-02-04 Y

This is how the values stored. Now i want to generate an report using product code.

If the user types P2003 it should fetch 2 records. Since the product code are stored like P2001|||P2002|||P2003.

How can i fetch ? Please help me…

Thanks & Regards
P.Balakrishnan

2

Don’t store them like that. Normalize your database. Get rid of those multiple values in one column, and use a purchase_products table instead. How are you storing a purchase of more than one unit of the same article right now anyway?

3

My old developer developed the project by storing values like this. I cannot able to change the code now. I want to write a query to fetch the values for generating the reports. Is there anyway to fetch the values using these values…

Help me :frowning:

4

WHERE pur_prod_code like ‘%P2003%’

And never hire that developer again :wink:

5

of course you can

what you actually meant to say is that you don’t want to