help newbee converting access query to php

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
land_down_under
Forum Newbie
Posts: 3
Joined: Thu Apr 27, 2006 4:46 pm

help newbee converting access query to php

Post by land_down_under »

Hi guys
Newbee here and newbee when it comes to php.
Need help converting a complex access query to put in php

Code: Select all

SELECT fieldtable.Name, [unitstable]![Industry] & [unitstable]![FieldNo] & "." & [unitstable]![UnitNo] & [unitstable]![Version] AS UnitNumber, unitqual.QualID, unitstable.UnitName, unitstable.UnitWeight, unitstable.NoOfPathsB, unitqual!Mandatory Or unitqual!Stream Or unitqual!Optional Or unitqual!Optional2 Or unitqual!Optional3 AS Expr1
FROM (unitqual INNER JOIN unitstable ON unitqual.UnitID = unitstable.UnitID) INNER JOIN fieldtable ON unitstable.FieldNo = fieldtable.FieldNo
WHERE (((unitqual.QualID)="MEM10105") AND (([unitqual]![Mandatory] Or [unitqual]![Stream] Or [unitqual]![Optional] Or [unitqual]![Optional2] Or [unitqual]![Optional3])=True))
ORDER BY unitstable.FieldNo, unitstable.UnitNo;
I assume that the [] parenthesis have to be converted to () and "MEM10105" to 'MEM10105', but as for the rest.... its way over my head.
If someone is able to help me in the right direction, that would be most helpful.

Cheers
-Craig :oops:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What database does this need to be translated to?
land_down_under
Forum Newbie
Posts: 3
Joined: Thu Apr 27, 2006 4:46 pm

Database type

Post by land_down_under »

Opps.. sorry ... its destination is mySql
-C
land_down_under
Forum Newbie
Posts: 3
Joined: Thu Apr 27, 2006 4:46 pm

The answer

Post by land_down_under »

After much testing, i ended up breaking it into smaller manageable chunks
I am posting the result here in case anyone had similar dilemmas

Code: Select all

$sselect = "fieldtable.Name, CONCAT(unitstable.Industry, unitstable.FieldNo, '.', unitstable.UnitNo, unitstable.Version) AS UnitNumber, unitqual.QualID, unitstable.UnitName, unitstable.UnitWeight, unitstable.NoOfPathsB, unitqual.Mandatory";
        $from = "(unitqual INNER JOIN unitstable ON unitqual.UnitID = unitstable.UnitID) INNER JOIN fieldtable ON unitstable.FieldNo = fieldtable.FieldNo";
        $orderby = "unitstable.FieldNo, unitstable.UnitNo";
        $where = "unitqual.QualID='MEM10105' AND ((unitqual.Mandatory OR unitqual.Stream OR unitqual.Optional OR unitqual.Optional2 OR unitqual.Optional3) = '1')";
        $query = sprintf("SELECT ".$sselect." FROM ".$from." WHERE ".$where." ORDER BY ".$orderby."");
:-)

-C
Post Reply