Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

    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
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    as OLdPedant pointed out the other day(s), the 'where' part for dependant table should be in join part
    so this:
    Code:
    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
    should be this:

    Code:
    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)  and p.item_code = '$mat_code' and p.lot_number LIKE '%$lot_number%';
    also try left join if upper does not readily work.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Actually, for an INNER JOIN, it doesn't matter if the condition is in the ON or the WHERE. It only matters when using an OUTER JOIN.

    The real problem here is that the entire request makes no sense.

    There is no way that
    Code:
           p.lot_number LIKE '%LO130318001LO130318002%';
    can possibly be used to find a lot number of
    Code:
           LO130318001-LO130318002
    That's essentially the same thing as saying you want to use
    Code:
          name LIKE '%JoeSmith%'
    to find a name of
    Code:
          Joe Smith
    You don't see the difference? With '%JoeSmith%' there is no space between "Joe" and "Smith". MySQL (or any database or any computer) is *NOT* a mind reader. It doesn't know you want to find the name with or without the space.

    And the same is true for
    Code:
           LO130318001LO130318002
    versus
           LO130318001-LO130318002
    That dash (hyphen) in there makes those two strings *COMPLETELY DIFFERENT* so far as any computer language I know of would be able to tell.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    If you wanted MySQL to ignore the dash, you could do
    Code:
        REPLACE( p.lot_number,'-','') LIKE '%LO130318001LO130318002%'
    And lots of other possibilities. But you can't expect MySQL to read your mind.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    newphpcoder (03-19-2013)

  • #5
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    I already resolved my problem by this query:
    Code:
    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 REPLACE(p.lot_number,'-','') LIKE '%$lot_number%'
    Thank you so much for your help.


  •  

    Posting Permissions

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