Page 1 of 1

help newbee converting access query to php

Posted: Thu Apr 27, 2006 4:58 pm
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:

Posted: Thu Apr 27, 2006 5:05 pm
by feyd
What database does this need to be translated to?

Database type

Posted: Thu Apr 27, 2006 5:08 pm
by land_down_under
Opps.. sorry ... its destination is mySql
-C

The answer

Posted: Thu Apr 27, 2006 6:31 pm
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