www.webdeveloper.com
Results 1 to 5 of 5

Thread: Need help on using LIKE

  1. #1
    Join Date
    Oct 2011
    Posts
    350

    Need help on using LIKE

    Hi,

    Good day!

    I encountered difficulties in checking of the data is like on the data from my table.

    I have table

    wms_picking

    and I have lot_number field and value = 'LO130318001-LO130318002' and item_code = 'MAT-CHE-0010'
    then I have variable:

    $lot_number = 'LO130318001LO130318002';
    $mat_code = 'MAT-CHE-0010';

    I created query:

    select s.sr_id, p.item_code, p.lot_number from sr_chemicals AS s JOIN wms_picking AS p ON (s.sr_id = p.sr_id)
    WHERE p.item_code = '$mat_code' and p.lot_number LIKE '%$lot_number%';


    select s.sr_id, p.item_code, p.lot_number from sr_chemicals AS s JOIN wms_picking AS p ON (s.sr_id = p.sr_id)
    WHERE p.item_code = 'MAT-CHE-0010' and p.lot_number LIKE '%LO130318001LO130318002%';

    and I got no result, but I just want to get the result of lot_number field field and value = 'LO130318001-LO130318002' and item_code = 'MAT-CHE-0010'.

    Any help is highly appreciated.


    Thank you so much.

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,623
    'LO130318001LO130318002' is not like 'LO130318001-LO130318002', although 'LO130318001%LO130318002' is like 'LO130318001-LO130318002'.

    What the ultimate solution should be here is difficult to say for sure, without perhaps a better understanding of the "rules" that would explain what the expected format/structure of the values to compared will be (e.g.: are they of a known fixed length, will the hyphen always be in the same place, etc.?).
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Oct 2011
    Posts
    350
    Sorry, but the hypen is only added to save the lot_number.

    Thank you

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,623
    That still doesn't really explain to me what the naming rules are for the value in the DB versus the value you want to compare to it. The most efficient search for the current DB schema and value would be to simply insert the hyphen into the string to be compared to the DB value. But this would assume that there is something standard/fixed about the value so that you could determine exactly where to stick the hyphen.

    For example, if it's always before the "LO", then you could use a fairly simple regex:
    PHP Code:
    $lot_number_to_use_in_query preg_replace('/(?<!^)LO/''-LO'$lot_number); 
    If instead the best algorithm is that it's a fixed length string and the hyphen always goes in a specific location, then substr_replace() could be your friend.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Oct 2011
    Posts
    350
    I got the solution:

    instead of using LIKE, it works when I used this :
    AND REPLACE(p.lot_number,'-','') = '%$lot_number%';

    Thank you

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles