...

View Full Version : Need help on using LIKE



newphpcoder
03-18-2013, 03:48 AM
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.

BubikolRamios
03-18-2013, 07:12 PM
as OLdPedant pointed out the other day(s), the 'where' part for dependant table should be in join part
so this:


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:



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.

Old Pedant
03-18-2013, 08:20 PM
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

p.lot_number LIKE '%LO130318001LO130318002%';

can possibly be used to find a lot number of

LO130318001-LO130318002

That's essentially the same thing as saying you want to use

name LIKE '%JoeSmith%'
to find a name of

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

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.

Old Pedant
03-18-2013, 08:23 PM
If you wanted MySQL to ignore the dash, you could do


REPLACE( p.lot_number,'-','') LIKE '%LO130318001LO130318002%'

And lots of other possibilities. But you can't expect MySQL to read your mind.

newphpcoder
03-19-2013, 02:44 AM
I already resolved my problem by this 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 REPLACE(p.lot_number,'-','') LIKE '%$lot_number%'


Thank you so much for your help.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum