Jump to content

Using PDO to Connect to An MS Access DB


mongoose00318
 Share

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 comment
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 comment
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 comment
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 comment
Share on other sites

This thread is more than a year old.

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.

 Share

×
×
  • 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.