Solved

Temp Table Query Problems

Posted on 2008-10-17
11
568 Views
Last Modified: 2013-12-13
I have these two Access queries that I need to put in a PHP page. The problem is that i cannot figure out how to even get the page to load. I think it's a syntax problem, but this is the first time I've ever tried to use a temp table in Access or from a sql query outside of access. Can you help?


Access Queries:
SELECT AllInfo.* INTO tempTable
FROM AllInfo
WHERE AllInfo.Extension=[Enter Extension Number]
And AllInfo.Date=[Enter Date];
 
SELECT tempTable.Extension, tempTable.Date, tempTable.Time, tempTable.IO, tempTable.AC, tempTable.PhN, tempTable.Duration, (select max([Time]) from tempTable as A where A.Extension =tempTable.extension And  A.Time<tempTable.time and A.[Date]=tempTable.[Date]) AS Time2, format(nz([Time]-[Time2],0),"hh:nn:ss") AS ElapsedTime
FROM tempTable;
 
-------------------------------------------------------------------
PHP Page SQL queries
 
$sql0 = "SELECT * From AllInfo INTO tempTable WHERE Extension = '$ex' AND Date=#$id#";
 
$queryTempTable = "SELECT tempTable.Extension, tempTable.Date, tempTable.Time, tempTable.IO, tempTable.AC, tempTable.PhN, tempTable.Duration, (select max([Time]) from tempTable as A where A.Extension =tempTable.extension And  A.Time<tempTable.time and A.[Date]=tempTable.[Date]) AS Time2, format(nz([Time]-[Time2],0),"hh:nn:ss") AS ElapsedTime
FROM tempTable"
ORDER BY DateTime, Val(Left(Time,2)), Val(Right(Time,2));
 
$sql0 = odbc_exec($odbc,$sql0) or die(odbc_errormsg());   
$queryTempTable = odbc_exec($odbc,$queryTempTable) or die(odbc_errormsg());  
 
echo "<center><table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" class=\"borderTable\"><tr><th class=\"borderTable\">   Extension   </th><th class=\"borderTable\">     Time     </th><th class=\"borderTable\">In/Out</th><th class=\"borderTable\">Phone Number</th><th class=\"borderTable\">Duration</th><th class=\"borderTable\">Duration</th></tr>";
 
while($row = odbc_fetch_array($queryTempTable)) 
{ 
echo "<tr>";
echo '<td class=\"borderTable\"><center>'.$row['Extension'].'</center></td class=\"borderTable\">'; 
echo '<td><center>'.date("h:i", strtotime($row['DateTime'])).'</center></td>';  
echo '<td class=\"borderTable\"><center>'.$row['IO'].'</center></td class=\"borderTable\">';
echo '<td class=\"borderTable\"><center>('.$row['AC'].') ';
echo ''.$row['PhN'].'</center></td class=\"borderTable\">';
echo '<td class=\"borderTable\"><center>'.$row['Duration'].'</center></td class=\"borderTable\">';
echo '<td class=\"borderTable\"><center>'.$row['ElapsedTime'].'</center></td class=\"borderTable\">';
 
} // end while

Open in new window

0
Comment
Question by:TTCLIVE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22743290
Your PHP doesn't quite match your access queries.  For example, the INTO statement of a SELECT...INTO should preceed the FROM statement/clause.

Try this:
PHP Page SQL queries
 
$sql0 = "SELECT * INTO tempTable From AllInfo WHERE Extension = '$ex' AND Date=#$id#";
 
$queryTempTable = "SELECT tempTable.Extension, tempTable.Date, tempTable.Time, tempTable.IO, tempTable.AC, tempTable.PhN, tempTable.Duration, (select max([Time]) from tempTable as A where A.Extension =tempTable.extension And  A.Time<tempTable.time and A.[Date]=tempTable.[Date]) AS Time2, format(nz([Time]-[Time2],0),"hh:nn:ss") AS ElapsedTime
FROM tempTable
ORDER BY DateTime, Val(Left(Time,2)), Val(Right(Time,2))";
 
$sql0 = odbc_exec($odbc,$sql0) or die(odbc_errormsg());   
$queryTempTable = odbc_exec($odbc,$queryTempTable) or die(odbc_errormsg());  
 
echo "<center><table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" class=\"borderTable\"><tr><th class=\"borderTable\">   Extension   </th><th class=\"borderTable\">     Time     </th><th class=\"borderTable\">In/Out</th><th class=\"borderTable\">Phone Number</th><th class=\"borderTable\">Duration</th><th class=\"borderTable\">Duration</th></tr>";
 
while($row = odbc_fetch_array($queryTempTable)) 
{ 
echo "<tr>";
echo '<td class=\"borderTable\"><center>'.$row['Extension'].'</center></td class=\"borderTable\">'; 
echo '<td><center>'.date("h:i", strtotime($row['DateTime'])).'</center></td>';  
echo '<td class=\"borderTable\"><center>'.$row['IO'].'</center></td class=\"borderTable\">';
echo '<td class=\"borderTable\"><center>('.$row['AC'].') ';
echo ''.$row['PhN'].'</center></td class=\"borderTable\">';
echo '<td class=\"borderTable\"><center>'.$row['Duration'].'</center></td class=\"borderTable\">';
echo '<td class=\"borderTable\"><center>'.$row['ElapsedTime'].'</center></td class=\"borderTable\">';
 
} // end while
 

Open in new window

0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 22743355
You may also need to escape the double quotes used here:
"hh:nn:ss"

Think in PHP you can just change the outer most quotes to single quote (') then you can use the double quotes (") in query as normal.
0
 

Author Comment

by:TTCLIVE
ID: 22743639
I fixed the queries with your code and changed the syntax, but I apparently cannot create the temptable in the database because it is read only. So I created a link database so that I can create the table in a separate database but still query the table I need to.

Then when I run the query, it tells me:

[Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.

How do I make the database I created editable so I can run this, and what is the code to drop the table after I create it so I can run this whenever I need to?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22746859
DROP TABLE tempTable

The above is the syntax for dropping the table.  I don't believe I have had that issue, so I will check and see what I find.  In the meantime, you can double check database security settings and NTFS rights to the MDB file itself as with standard file and default security I usually connect to MDB files for read/write with no issues; however, it has been a while since can use SQL Express in place of Access these days.
0
 

Author Comment

by:TTCLIVE
ID: 22758247
I checked the NTFS permissions, and just in case while testing added "Everyone" and gave them All permissions.  

Do I need to preface that "DROP TABLE tempTable" with anything in a PHP page, or just drop it at the end of the page inside the "?>" ?

I still get the same error when trying to run the query to either create the temp table or drop it.
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 22758337
You would run the DROP TABLE sql statement the same way you do the other through an:
odbc_exec(...) or die(...)
0
 

Author Comment

by:TTCLIVE
ID: 22758606
Okay, so once I get this working I will get the Drop Table working as well. Any ideas on how to allow access to create or drop a table in a link database without getting the "Database or object is read-only." error?
0
 

Author Comment

by:TTCLIVE
ID: 22762671
It's almost like it's trying to create the temp table in the main database, not the Link Database I created to run all the queries...is there a way to tell is to create the table in this link database rather than trying to do it in the actual database where it doesn't have permissions?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22764119
This should be trying to create the table in whichever database is connected to through the connection string from my understanding.
0
 

Author Comment

by:TTCLIVE
ID: 22767747
That's what I thought as well, but the database that I created and linked the tables to so that I could run queries is set in a folder that has NTFS permissions for Everyone set to All and there is no password on the database at all, and it still tells me the database is read only whenever I try to create the temp table.

I can run the query directly in Access, but not from an external page.  

So I'm back at square one with a query that works in access directly, but that I cannot run from a web page. Any other ideas on how to accomplish the same goal without trying to use a temp table anyone? Or maybe a way to specify where the temptable should be created?
0
 

Author Comment

by:TTCLIVE
ID: 22786937
I really need to get htis working, anyone have any ideas? I'm open to suggestions!!
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question