MS Access ODBC query via PHP

[shocker-z]

Hi thanks for looking at my post,

Basically I'm writing a script to pull out info from a MS Access database that we use at work and im having trouble getting the STAFF ID into name as it's in different tables.

My Query:


SELECT Maintenance_Dbf.Maintenance_ID, Maintenance_Dbf.Fault,
Maintenance_Dbf.[Date Allocated], Maintenance_Dbf.[Time Allocated],
Maintenance_Dbf.[Allocated To], Maintenance_Dbf.Client, Maintenance_Dbf.[Date Closed]
FROM Maintenance_Dbf
WHERE ((Not (Maintenance_Dbf.Client)='West Nottinghamshire College (BKSB)') AND ((Maintenance_Dbf.[Date Closed]) Is Null))
ORDER BY Maintenance_Dbf.[Maintenance_ID] DESC, Maintenance_Dbf.[Time Allocated] DESC


The query alone works fine but Maintenance_Dbf.[Allocated To] is a number which refers to a table called Staff_Dbf which is a list of all staff with a unique ID which of course is the number of Maintenance_Dbf.[Allocated To] how can i either join this or run a query within a loop? i know with MySQL i could just run another query within the loop to query the staff_dbf table but with access it all seems a bit different and not working how i would like.

Full code so far (staff_dbf query not working)


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>MSAccess Query</title>
</head>
<?php
$conn=odbc_connect('pnctables','','');
if (!$conn) {
exit("Connection Failed: " . $conn);
}

$sql="SELECT Maintenance_Dbf.Maintenance_ID, Maintenance_Dbf.Fault,
Maintenance_Dbf.[Date Allocated], Maintenance_Dbf.[Time Allocated],
Maintenance_Dbf.[Allocated To], Maintenance_Dbf.Client, Maintenance_Dbf.[Date Closed]
FROM Maintenance_Dbf
WHERE ((Not (Maintenance_Dbf.Client)='West Nottinghamshire College (BKSB)') AND ((Maintenance_Dbf.[Date Closed]) Is Null))
ORDER BY Maintenance_Dbf.[Maintenance_ID] DESC, Maintenance_Dbf.[Time Allocated] DESC";
$rs=odbc_exec($conn,$sql);
if (!$rs)
{exit("Error in SQL";}
$i=0;
while (odbc_fetch_row($rs))
{
$i++;
$maintID=odbc_result($rs,1);
$fault=odbc_result($rs,2);
$dateallocated=odbc_result($rs,3);
$timeallocated=odbc_result($rs,4);
$allocatedto=odbc_result($rs,5);
$client=odbc_result($rs,6);
$ID2USR="SELECT Staff_Dbf.[Staff ID], Staff_Dbf.[First Name], Staff_Dbf.[Last Name] FROM Staff_Dbf WHERE Staff_Dbf.[Staff ID] = $allocatedto";
$usr=odbc_exec($conn2,$ID2USR);
odbc_fetch_row($usr);
$forename=odbc_result($usr,1);
$surname=odbc_result($usr,2);
echo "Maint ID: $maintID <Br>
Allocated to: $forename $surname<Br>
Client: $client <Br>
- - - - - - - - - - - - - - - - - - - - -<Br>";
}
odbc_close($conn);
?>

<body>
</body>
</html>



Regards
Liam

[john_p]

shocker-z said:

Hi thanks for looking at my post,

Basically I'm writing a script to pull out info from a MS Access database that we use at work and im having trouble getting the STAFF ID into name as it's in different tables.

My Query:


SELECT Maintenance_Dbf.Maintenance_ID, Maintenance_Dbf.Fault,
Maintenance_Dbf.[Date Allocated], Maintenance_Dbf.[Time Allocated],
Maintenance_Dbf.[Allocated To], Maintenance_Dbf.Client, Maintenance_Dbf.[Date Closed]
FROM Maintenance_Dbf
WHERE ((Not (Maintenance_Dbf.Client)='West Nottinghamshire College (BKSB)') AND ((Maintenance_Dbf.[Date Closed]) Is Null))
ORDER BY Maintenance_Dbf.[Maintenance_ID] DESC, Maintenance_Dbf.[Time Allocated] DESC


The query alone works fine but Maintenance_Dbf.[Allocated To] is a number which refers to a table called Staff_Dbf which is a list of all staff with a unique ID which of course is the number of Maintenance_Dbf.[Allocated To] how can i either join this or run a query within a loop? i know with MySQL i could just run another query within the loop to query the staff_dbf table but with access it all seems a bit different and not working how i would like.

something like
SELECT (whatever),Staff_Dbf.[Name] FROM Maintenance_Dbf,Staff_Dbf WHERE Maintenance_Dbf.[Allocated To]=Staff_Dbf.[Staff ID] AND (whatever other where stuff) ORDER BY (whatever)

should work. This assumes all Allocated To values have a corresponding entry in the Staff_Dbf table.

[shocker-z]

I got it working with a sub query but this way is alot better so will give it a blast i thought about it this way then mind just went blank (I get use to this after time) :0

cheers mate


Liam

[john_p]

Yeah it's definitely worth reading up on the different types of JOINs and subqueries (if Access does it) as it's usually much faster and easier to do it all in a single SQL query rather than PHP. Sometimes the fastest ways are the best ways though

[shocker-z]

im using PHP to run the queries as im producing extra addons which are much better on a web interface and also will eventualy be emailing clients with there reference number and so on, beleive it or not the ODBC queries are running pretty damn smooth atm.

Only issue that i've come across is using WHERE to link the engineers name it will only select where an engineer is already allocated anyway of getting all results and just the engineers name where there is an ID? as the default it returns is 0 for the ID meaning none is allocated e.g. NULL


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>MSAccess Query</title>
</head>
List Calls 2<Br>
<?php
$conn=odbc_connect('pnctables','','');
if (!$conn) {
exit("Connection Failed: " . $conn);
}

$sql="SELECT Maintenance_Dbf.Maintenance_ID, Maintenance_Dbf.Fault,
Maintenance_Dbf.[Date Allocated], Maintenance_Dbf.[Time Allocated],
Maintenance_Dbf.[Allocated To], Maintenance_Dbf.Client,
Maintenance_Dbf.[Date Closed], Staff_Dbf.[First Name],
left(Staff_Dbf.[Last Name],1), Maintenance_Dbf.[Date Logged],
Maintenance_Dbf.[Time Logged], [Workshop Status dbf].[Status]

FROM Maintenance_Dbf, staff_DBf, [Workshop Status dbf]

WHERE Maintenance_Dbf.[Status]=[Workshop Status dbf].[ID] AND Maintenance_Dbf.[Allocated To]=Staff_Dbf.[Staff ID] AND ((Not (Maintenance_Dbf.Client)='West Nottinghamshire College (BKSB)') AND ((Maintenance_Dbf.[Date Closed]) Is Null))

ORDER BY Maintenance_Dbf.[Maintenance_ID] DESC, Maintenance_Dbf.[Time Allocated] DESC";
$rs=odbc_exec($conn,$sql);
if (!$rs)
{exit("Error in SQL ".odbc_errormsg());}
$i=0;
while (odbc_fetch_row($rs))
{
$i++;
$maintID=odbc_result($rs,1);
$fault=odbc_result($rs,2);
$dateallocated=odbc_result($rs,3);
$timeallocated=odbc_result($rs,4);
$allocatedto=odbc_result($rs,5);
$client=odbc_result($rs,6);
$forename=odbc_result($rs,8);
$surname=odbc_result($rs,9);
$datelogged=odbc_result($rs,10);
$timelogged=odbc_result($rs,11);
$status=odbc_result($rs,12);

echo "Maint ID: $maintID <Br>
Allocated to: $forename $surname<Br>
Client: $client <Br>
Status: $status <Br>
- - - - - - - - - - - - - - - - - - - - -<Br>";
}
odbc_close($conn);
?>

<body>
</body>
</html>




Now that all works great except i need to view records with no current status or engineer also.. Would i be able to use OR instead of AND?

Any extra info you need just ask


Thanks a lot people

Regards
Liam