Solved

Trying to get a count using a SQL query on an Access DB

Posted on 2007-11-20
10
578 Views
Last Modified: 2012-06-27
I am trying to get a count of the total number of hours and minutes per extension in my phone system database. The database is in Access, and I am writing some SQL queries in PHP to access them.

I have the code seen in the "Attach Code Snippet" area below, and what it is returning is the total number of OUTBOUND calls made on that extension rather than the total amount of talk time on that extesion.

The Date is getting passed by user input, but I am hardcoding the extension (for now). The field in the DB that I need it to count is "DURATION" and the data in that field is in minutes (000).

Once I get this working, I would like to use this function to translate it to hours and minutes (0:00):
*************************************************************************************
// Transform hours like "1:45" into the total number of minutes, "105".
function hoursToMinutes($hours)
{
    $minutes = 0;
    if (strpos($hours, ':') !== false)
    {
        // Split hours and minutes.
        list($hours, $minutes) = explode(':', $hours);
    }
    return $hours * 60 + $minutes;
}

// Transform minutes like "105" into hours like "1:45".
function minutesToHours($minutes)
{
    $hours = (int)($minutes / 60);
    $minutes -= $hours * 60;
    return sprintf("%d:%02.0f", $hours, $minutes);
}
*************************************************************************************

So what is wrong with my query, why is it returning number of calls rather than total talk time and how do I fix it?

Thanks in advance for the help.
$time = "SELECT Date, Extension, Count(Duration) AS CountOfDuration2 
	FROM AllInfo 
	GROUP BY Date, Extension, IO 
	HAVING Extension = '3908' AND IO = 'Out' AND Date=#".$id."#" ;

Open in new window

0
Comment
Question by:TTCLIVE
  • 6
  • 4
10 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 20323695
2 things:
* prefer WHERE over HAVING
* use SUM() instead of COUNT()

$time = "SELECT Date, Extension, sum(Duration) AS CountOfDuration2 
	FROM AllInfo 
	WHERE Extension = '3908' 
        AND IO = 'Out' 
        AND Date=#$id#
	GROUP BY Date, Extension, IO 
   ";

Open in new window

0
 

Author Comment

by:TTCLIVE
ID: 20323783
That kicked out this error :

Warning: odbc_exec() [function.odbc-exec.html]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in C:\Inetpub\wwwroot\Queries\test\DailyCallTotals_SelectDate.php on line 97
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

Line 97 is shown below.
$timequery = odbc_exec($odbc,$time) or die(odbc_errormsg());

Open in new window

0
 

Author Comment

by:TTCLIVE
ID: 20323792
And this is the part that is writing to the page:


while($row = odbc_fetch_array($timequery)) {
echo "<td><center>".$row["CountOfDuration2"]."</center></td>";	}

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20323815
what about this:

$time = "SELECT [Date], Extension, sum(Duration) AS CountOfDuration2
        FROM AllInfo
        WHERE Extension = 3908  
        AND IO = 'Out'
        AND [Date]=#$id#
        GROUP BY [Date], Extension
  ";

assuming Extension being a numerical data type.
is $id really the correct variable for the date value?
0
 

Author Comment

by:TTCLIVE
ID: 20323868
Same error on that as well.

>is $id really the correct variable for the date value?
Yes, this part of the query works fine, it calls the date put in by the user on a form and has not returned any errors up to this point.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20323889
can you isolate on which part of the query it errors out?
by removing parts of it until the error goes away?


0
 

Author Comment

by:TTCLIVE
ID: 20324026
If I remove any the last line, I get "You tried to execute a query that does not include the specified expression 'Date' as part of an aggregate function."

If I take out

AND IO = 'Out'
AND [Date]=#$id#

I get "Data type mismatch in criteria expression"

If I take out the

FROM AllInfo

I get "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

I'm not sure exacly what I'm looking for, I'm kind of a newbe. I wrote this based on the query that I have that returns the total number of calls. THat query is as follows:

$sql2 = "SELECT Count(*) AS TotalNumberOfCalls2 FROM AllInfo WHERE Extension = '3908' AND Date=#".$id."#";

It works perfectly fine, so I was trying just to recreate the same type of deal to get the total out of the DURATION column but that's where I ran into this problem. Does this need to be rewritten?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20325781
ok, what about this:

$sql2 = "SELECT sum(Duration) AS TotalNumberOfCalls2 FROM AllInfo WHERE Extension = '3908' AND Date=#".$id."#";

what is the data type of the column Duration?

0
 

Author Comment

by:TTCLIVE
ID: 20327968
That actually returned a data type mismatch error. I looked at the database again, and I think instead of Duration I need to use DurationS. Duration has the total amount of talk time per call formatted like 0:00:00 and DurationS is the same duration in seconds like 0.

So I changed the query to DurationS and it's still returning the number of dials rather than talk time. So I went back to you original code and changed Duration to DurationS and I am now getting the total number of seconds. That seemed to do it. Now I just have to figure out how to get the seconds to translate to hours and minutes, but I guess that should be a different post.

Final Code below.
$time = "SELECT Date, Extension, sum(DurationS) AS CountOfDuration2 
	FROM AllInfo 
	WHERE Extension = '3908' 
        AND IO = 'Out' 
        AND Date=#$id#
	GROUP BY Date, Extension, IO 
   ";

Open in new window

0
 

Author Closing Comment

by:TTCLIVE
ID: 31410238
Excelent response time and knowledge!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Foolproof security solutions has become one of the key necessities of every e-commerce or Internet banking website. If you too own an online shopping site then its vital for you to equip your web portal with customer security features that can allow…
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…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

806 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