PHP Access Queries

i agree with m1tk4, that you will not gain very much, if you are not already familar with php.
i recommend to invest your time in learning some XML/XSLT

you can export the access-datatables as XML-files on the server
there you can query them trough htmlpages, which use the the xml-processor.
this client-XML/XSLT handling is straightforward in the LAN
if you have XML-filesizes above 10MB you will need servside XML/XSLT processing, what is more complex. in this case it will be less work to go into PHP/MYSQL

i am afraid, that there are no simple ready to use tools for your task

regards, Franz

look into phpmyadmin. i think that may help a bit but as im not sure what you want to query and if your trying to display it publicly im not sure.

what sort of extra information are you looking to obtain?

Okay. What I am trying to do is display reports on our intranet for call statistics including talk time, calls by extension, overall performance, graphs and so on.

The program (Panalog) grabs data and puts it into an Access Database (which I know nothing about) with information like this:

Rows: Date  Time Ext I/O Phone Number Duration
Data: 2007-07-24 00:00:00, 1899-12-30 12:24:00, 3885,  Out,  812-275-1547, 00:00'03
 
Now, rather than having to use Access, and let everyone into the database (which is getting updated every time omeone makes a call), I am trying to make some custom web pages on our intranet to run reports on the information in the database.

I need to know how to:
1) convert the date and time formats into more user friendly information
2) query the database to just return results from a certain ext (3885 in the data above)
3) do some calculating on the data to display a summary or graph (total calls, total talk time, average time per call, average per day for the last month, etc..)
4) run any other types of reports management may need "on the fly" rather than having to go into the database and look up the information or use the Panalog program that does not do everything I want it to do.

What I'm running into is that everything I look at (online) is a query on a Mysql databse, and I always get errors on the query (like invalid syntax).

I need to know what queries I can run on the database to do what I need to do and then how to calcualte and minipulate the data to display on the pages accordingly. I can learn well by example and trial and error, but I can't find any examples that do what I want it to do, and having a list of functions doesn't help me because I don't know enough about the language yet to be able to know where those functions go in the query or how to make them linguisticly correct.

I'm increasing the point value on this, I did not realize it was this difficult. There has to be some resources out there that show me examples of code that will work in this instance.

Once again, stick to Access. You can create a separate Access application with all reports you need in about an hour, that would only read the data read only from the actual Access data file.

If you are not familiar with either PHP or Access, you have a choice of either spending 3 weeks on figuring out how to do it in PHP or a week to figure out how to do it in Access, your call.

Any "Access for dummies" book in your local bookstore for $10-$15 would have all the samples and directions you need, plus your access reports will be much easier to customize later on than PHP ones.

That was helpful. Once again, I do not want to use access, I want it web based (partially because I do not have or want Access on every computer that needs to have access to these reports). And I do not want to have to teach all the managers how to use Access when I can have them click a button on a web page.

I don't need custom code, I just need some understanding of how to do this, maybe some links to websites that I can't find that tell me how to minipulate data queried from an Access database. I don't think this is something that's impossible, people do it all the time using ASP or programs that are designed to interface with Access. I just want a push in the right direction so I can start learning this language.

I have basic html/xml/flash/javascript understanding and have designed some websites.

I have got the data queried out of the database using this code:

$query = odbc_exec($odbc, "SELECT TOP 10 * FROM AllInfo") or die (odbc_errormsg());
while($row = odbc_fetch_array($query))

{

    echo '<table border="1">';
    echo '<td>';
    echo ''.$row['Date'].'<br />';
    echo '</td><td>';
    echo ''.$row['Time'].'<br />';
    echo '</td><td>';
    echo ''.$row['Extension'].'<br />';
    echo '</td><td>';
    echo ''.$row['IO'].'<br />';
    echo '</td><td width="135">';
    echo ''.$row['AC'].'-';
    echo ''.$row['PhN'].'<br />';
    echo '</td><td>';
    echo 'Duration: '.$row['Duration'].'<br />';
    echo '</table>';

}
odbc_close($odbc);
?>

So what do I need to do to minipilate the data now? Instead of just displaying it in a table, how do I tell it to sort the data by extension, or all all the durations and give me a total and an average based on number of calls? This is where I am running into problems. I can get the data, but I don't know how to minipulate it to show what I want it to.

>>>1. people do it all the time using ASP or >>>2.  programs that are designed to interface with Access

1. people, who use ASP, have to use also MS-IIS. ASP-Programming is again a thing to invest time.
2. thera are no such programms for a webserver, where you do not need application-specific customizing

but, maybe you ara looking for an OLAP-software which is designed to hold data ready for drill down queries. Most of them are commercial, but there is a opensource-product, you may find interesting as it has excel-orientation, maybe even a MS-access-interface: http://www.jedox.com/en/enterprise-spreadsheet-server/excel-olap-server/palo-server.html

this may come to close, to what you had in mind. I evaluated this for a project of a client, but it is not in realisation yet.
 
regards, Franz

>> So what do I need to do to minipilate the data now? Instead of just displaying it in a table, how do I tell it to sort the data by extension, or all all the durations and give me a total and an average based on number of calls?

Exactly my point. Get a good PHP book and start hacking.

>>This is where I am running into problems. I can get the data, but I don't know how to minipulate it to show what I want it to.
back to top.

This is where you are running into writing a PHP application which would translate customer requests (sorting, totals) into SQL queries. In Access you could just click the column header then sort button, and put together a report for the totals in about half an hour.

And no, you don't need Access licenses on every computer to run it - there is Access Runtime in Office Pro.

I said nothing about licensing Access, I just don't want to have to teach and support another program in-house.

So it is possible to do what I want, that's what you are saying M1tk4.

Now that someone has finally agreed that it is possible to do what I want to do without having to use Access or export the data to a different type of database , or use another program to store the data so I can call it when I need it....lets get back to my original question.

Without buying a book or EVER getting into the Access databse direcly through the Access program:

**I ...need to have some resources to reference for query syntax and prefereably some predefined PHP code I can edit to work with my database...**

So the only useful response I have gotten is "buy a book"

Anyone Else?

So aside from using Access query builder, anyone know some good websites that will actually teach me to write queries?

Okay, m1tk4 I started creating queries in Access and trying to put them onto a php page.
Here is the query that works in Access:

SELECT Count(*) AS TotalNumberOfCalls
FROM AllInfo
WHERE (((AllInfo.Extension)="3882") AND ((AllInfo.Date)=#8/23/2007#));

This is what I get in the web page:

Parse error: syntax error, unexpected T_LNUMBER in C:\Xampp\htdocs\smarty\querydb.php on line 16

Here is my code:

$query = odbc_exec($odbc, "SELECT Count(*) AS TotalNumberOfCalls
FROM AllInfo
WHERE (((AllInfo.Extension)="3882") AND ((AllInfo.Date)=#8/23/2007#));            */ LINE 16 /*
or die (odbc_errormsg());
while($row = odbc_fetch_array($query))

Any Ideas what the problem is here?

you need to escape your double-quotes:

WHERE (((AllInfo.Extension)=\"3882\") AND ((AllInfo.Date)=#8/23/2007#));            

isn't it a completely new question, by the way?