Using PDO to Connect to An MS Access DB

[mongoose00318]

I have successfully connected to the access database and the queries seem to be surprisingly fast but I'm having some issues getting some queries to work. I can't figure out if it is a difference in syntax between MySQL and MSSQL? For examples:

                                        SELECT
						[PROD SCHD].[WORK ORDER],
						[PROD SCHD].[ENTERPRISE],
						[PROD SCHD].[PART NUMBER],
						[PPBOMFCB].[ITEM_NUMBER_BOM],
						[PPBOMFCB].[CATALOGUE_NUMBER_BOM],
						[PPBOMFCB].[DESCRIPTION_BOM]
					FROM
						[PROD SCHD]
					LEFT JOIN
						[PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM]
					WHERE
						[PROD SCHD].[WORK ORDER] IS NOT NULL

This works fine. But when I add another condition to the where clause like so:

                                        SELECT
						[PROD SCHD].[WORK ORDER],
						[PROD SCHD].[ENTERPRISE],
						[PROD SCHD].[PART NUMBER],
						[PPBOMFCB].[ITEM_NUMBER_BOM],
						[PPBOMFCB].[CATALOGUE_NUMBER_BOM],
						[PPBOMFCB].[DESCRIPTION_BOM]
					FROM
						[PROD SCHD]
					LEFT JOIN
						[PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM]
					WHERE
						[PROD SCHD].[WORK ORDER] IS NOT NULL AND
						[PPBOMFCB].[PART NUMBER] LIKE '%FA%'

It won't work at all? The query works fine in Access though.

[mac_gyver]

found this online -

Quote

Note: MS Access uses an asterisk (*) instead of the percent sign (%), and a question mark (?) instead of the underscore (_).

 

[mongoose00318]

Okay I changed my query to use the asterisk but still it's not working right. For example if I remove the LIKE from the where clause here is part of the data I get back.

37 => 
    array (size=14)
      'WORK ORDER' => string '23601611  0001' (length=14)
      0 => string '23601611  0001' (length=14)
      'ENTERPRISE' => string 'BURGER' (length=6)
      1 => string 'BURGER' (length=6)
      'PART NUMBER' => string 'BK4016LD.SFG' (length=12)
      2 => string 'BK4016LD.SFG' (length=12)
      'ITEM_NUMBER_BOM' => string '007' (length=3)
      3 => string '007' (length=3)
      'CATALOGUE_NUMBER_BOM' => string 'FA4016BK.SFGTC' (length=14)
      4 => string 'FA4016BK.SFGTC' (length=14)
      'DESCRIPTION_BOM' => string 'ROUTD FACE 16" FLAME GRILLING SINCE 1954' (length=40)
      5 => string 'ROUTD FACE 16" FLAME GRILLING SINCE 1954' (length=40)
      'RELEASE_WO_BOM' => string '23601611  0001' (length=14)
      6 => string '23601611  0001' (length=14)

The 'CATALOGUE_NUMBER_BOM clearly starts with FA but if I run this query: 

SELECT
	[PROD SCHD].[WORK ORDER],
	[PROD SCHD].[ENTERPRISE],
	[PROD SCHD].[PART NUMBER],
	[PPBOMFCB].[ITEM_NUMBER_BOM],
	[PPBOMFCB].[CATALOGUE_NUMBER_BOM],
	[PPBOMFCB].[DESCRIPTION_BOM]
FROM
	[PROD SCHD]
LEFT JOIN
	[PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM]
WHERE
	[PROD SCHD].[WORK ORDER] IS NOT NULL AND
	[PPBOMFCB].[CATALOGUE_NUMBER_BOM] LIKE 'FA*'

I don't get any results?

Edited November 13, 2020 by mongoose00318

[requinix]

You have an asterisk, yes, but what about second asterisk?

[mongoose00318]

Sorry just updated the placement of the asterisk..quick typo on my part. When I run the query with the asterisk after the FA I get nothing back.

[mongoose00318]

@requinix It still returns nothing? 

[mongoose00318]

Weird...okay this did it...

SELECT
	[PROD SCHD].[WORK ORDER],
	[PROD SCHD].[ENTERPRISE],
	[PROD SCHD].[PART NUMBER],
	[PPBOMFCB].[ITEM_NUMBER_BOM],
	[PPBOMFCB].[CATALOGUE_NUMBER_BOM],
	[PPBOMFCB].[DESCRIPTION_BOM]
FROM
	[PROD SCHD]
LEFT JOIN
	[PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM]
WHERE
	[PROD SCHD].[WORK ORDER] IS NOT NULL AND
	[PPBOMFCB].[CATALOGUE_NUMBER_BOM] LIKE 'FA%'

 

[mongoose00318]

Maybe PDO converts the % to an *?

[requinix]

If %s work for some reason then maybe your first attempt didn't work because you were looking for "FA" in [PART NUMBER] instead of [CATALOGUE_NUMBER_BOM]?

[mongoose00318]

Yea....🙄

...my bad...

[Barand]

WHERE's don't work correctly on table that you LEFT JOIN to.

You need to put the condition in the JOIN

Try

SELECT
    [PROD SCHD].[WORK ORDER],
    [PROD SCHD].[ENTERPRISE],
    [PROD SCHD].[PART NUMBER],
    [PPBOMFCB].[ITEM_NUMBER_BOM],
    [PPBOMFCB].[CATALOGUE_NUMBER_BOM],
    [PPBOMFCB].[DESCRIPTION_BOM]
FROM
    [PROD SCHD]
LEFT JOIN
    [PPBOMFCB] ON [PROD SCHD].[WORK ORDER] = [PPBOMFCB].[RELEASE_WO_BOM] AND
    [PPBOMFCB].[CATALOGUE_NUMBER_BOM] LIKE 'FA%'
WHERE
    [PROD SCHD].[WORK ORDER] IS NOT NULL 
  

 

[mongoose00318]

@Barand Awesome. Thanks for the tip. Just curious...what causes the WHERE to not function properly?

[Barand]

You are trying to search for conditions in records that may not exist. As a result the query behaves as though an INNER JOIN were used intead of a LEFT JOIN.

[mongoose00318]

@Barand Interesting. Thanks for the clarity and I will continue to research it further.