SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help in fetching the record - MySql

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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. #3
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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?
    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 :-(

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    WHERE pur_prod_code like '%P2003%'

    And never hire that developer again

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rishibala143 View Post
    I cannot able to change the code now.
    of course you can

    what you actually meant to say is that you don't want to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •