Jump to content

Using PDO to Connect to An MS Access DB


Recommended Posts

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.

Link to post
Share on other sites

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 by mongoose00318
Link to post
Share on other sites

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%'

 

Link to post
Share on other sites

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]?

Link to post
Share on other sites

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 
  

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.