[SOLVED] Time range test

bajangerry · August 17, 2009

Hi Guys,

I have a problem comparing data in two tables using a TIME column as one of the variables that need to match for a true result. The problem is that many times the TIME column data can vary by a couple of minutes and yet the result still needs to be true.

i.e. if I have two tables such as:

Number Time Caller

1234567 07:46 Mark

and

Number Time Cost

1234567 07:47 0.56

My query result still needs to be TRUE although the times are slightly different due to the tables being updated from different sources. I know how to use the BETWEEN x AND y in a query but that does not seem to be the way for me to go here unless I am missing something.

Any suggestions?

bajangerry · August 17, 2009

Guess I have you all stumped this time

moonman89 · August 17, 2009

How are you selecting the data? What is the source?

bajangerry · August 17, 2009

Thanks for replying moonman,

I have a query that select all columns from the first table based on dates(sorry missed out date columns in example tables). I then use the the result to select rows from the following table in a while loop. This way I can find every instance of a number that occurs in a certain date range and compare it with the data in the second table. What I need to do is to break this comparison down by time as well as date but the time can vary by a few minutes either way.

$query = "SELECT * FROM `$provider` WHERE `date` BETWEEN '$startdate' AND '$enddate'";

$result = mysql_query($query);

while($row = mysql_fetch_object($result)) {

$importq = "SELECT * FROM `import` WHERE `calledno` = '{$row->number}' AND `date` = {$row->date}'";

$importr = mysql_query($importq)or die ("Error in query: $query. ".mysql_error());

$importp = mysql_fetch_object($importr);

echo ($importp->number);

etc

}

The importq query is where I wish to add the time variable so that the selection will be broken down further by the time field as there can be more than one entry for a day.

bajangerry · August 17, 2009

Couple of corrections for clarity...

tables1:

Number Time Caller Date

1234567 07:46 Mark 01/05/2009

and

table2

Number Time Cost Date

1234567 07:47 0.56 01/05/2009

Code:

$startdate = 01/05/2009;

$enddate = 01/05/2009;

$query = "SELECT * FROM `table1` WHERE `Date` BETWEEN '$startdate' AND '$enddate'";

$result = mysql_query($query);

while($row = mysql_fetch_object($result)) {

$importq = "SELECT * FROM `table2` WHERE `Number` = '{$row->Number}' AND `Date` = $row->Date}'";

$importr = mysql_query($importq)or die ("Error in query: $query. ".mysql_error());

$importp = mysql_fetch_object($importr);

moonman89 · August 17, 2009

I know what you are trying to do but don't know the code off the top of my head.

Let me just figure out exactly what you are trying to do, there might be an easier way to do it all in SQL.

Why are you using a while there?

I understand the first query you are selecting everything for those dates, and then using the returned data from that to do another query.

Are you aware of mysql sub selects?

EG:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2 WHERE ETC.........);

so to select everything we have now you would use:

SELECT * FROM `import` WHERE `calledno` = (SELECT Number from `table1` WHERE `Date` BETWEEN '$startdate' AND '$enddate')

Is that what you are trying to do?

moonman89 · August 17, 2009

Is that what you are trying to do?

We can add the time bit in a minute

bajangerry · August 17, 2009

I am not aware of the sub select... guess it is just s neater way of doing what I was doing.

I am using the while loop because there are a number of rows in the first table that I will be comparing to the second table to produce a report, this is not a single row table. Therefore, for each row in table1 I need to get all the matching results from table2 and produce a report on them.

I have that section working fine and it is doing exactly what I need, I just need to add the TIME range value to break the search down more specifically.

bajangerry · August 17, 2009

This is my full (probably quite ugly) code including the HTML page. Parameters are passed to this page from another and a report is printed to webpage:

<?php

include("lib/config.php");

top();

$clear = "TRUNCATE TABLE `temp`";

$clearresult = mysql_query($clear);

$rowno = 0;

$day=$_GET['dayvar'];

$day1=$_GET['dayvar1'];

$month= $_GET['monthvar'];

$month1= $_GET['monthvar1'];

$year=$_GET['yearvar'];

$year1=$_GET['yearvar1'];

$prov=$_GET['provider'];

$startdate = $year . "-" . $month . "-" . $day;

$enddate = $year1 . "-" . $month1 . "-" . $day1;

if ($prov == 'LIME'){$provider = 'unlime';}else{$provider = 'undigicel';}

$query = "SELECT * FROM `$provider` WHERE `date` BETWEEN '$startdate' AND '$enddate'";

$result = mysql_query($query);

?>

<html>

<head>

<title>SMDR reporting</title>

<!--

table {

background-color:#FFF;

width:100%;

border-collapse:collapse;

}

td {

background-color:#FFF;

/* border:1px solid black;*/

padding:5px;

}

-->

</style>

</head>

<html>

<body>

<tr>

<td nowrap><div align="center"> <font size="-1"><strong>Call Length</strong></font></div></td>

<td nowrap><div align="center"> <font size="-1"><strong>Called No</strong></font></div></td>

<td nowrap><div align="center"> <font size="-1"><strong>Caller</strong></font></div></td>

<td nowrap><div align="center"> <font size="-1"><strong>Account Code</strong></font></div></td>

<td nowrap><div align="center"> <font size="-1"><strong>Agency</strong></font></div></td>

<td nowrap><div align="center"> <font size="-1"><strong>Project Code</strong></font></div></td>

<td nowrap><div align="center"> <font size="-1"><strong>Project</strong></font></div></td>

</tr>

<?php

while($row = mysql_fetch_object($result)) {

$rowno += 1;

$hrstrip=substr(($row->time),0,2);

$numberstrip=substr(($row->number),0,1);

$numberlth = strlen($row->number);

if ($numberstrip != 1){

$callnumber = "011".$row->number;

}

else {

$callnumber = ($row->number);

}

$importq = "SELECT * FROM `import` WHERE `calledno` = '$callnumber' AND `day` = '{$row->day}' AND

`month` = '{$row->month}'AND `time`LIKE '$hrstrip%'";

$importr = mysql_query($importq)or die ("Error in query: $query. ".mysql_error());

$importp = mysql_fetch_object($importr);

$persquery = "SELECT * FROM `accountcode` WHERE `accountcode` = '{$importp->accountcode}'";

$persresult = mysql_query($persquery);

$persprint = mysql_fetch_object($persresult);

$projquery = "SELECT * FROM `projectcode` WHERE `projectcode` = '{$importp->percode}'";

$projresult = mysql_query($projquery);

$projprint = mysql_fetch_object($projresult);

$update = "INSERT INTO `un`.`temp`

(`date`,`time`,`lenght`,`number`,`caller`,`accountcode`,`agency`,`projectcode`,`amt`)VALUES

('{$row->date}', '{$row->time}', '{$row->mins}', '{$row->number}', '{$importp->callingparty}',

'{$importp->accountcode}', '{$persprint->agency}', '{$importp->percode}', '{$row->amt}'

)";

$upresult = mysql_query($update) or die(mysql_error());

?>

<tr>

<td><div align="center"><font size="-1"><?php echo ($row->number);?> <?php echo ($row->destination);?></font></div></td>

<td><div align="center"><font size="-1"><?php echo ($extprint->first);?> <?php echo ($importp->callingparty);?></a></font></div></td>

<td><div align="center"><font size="-1"><? echo ($persprint->name);?>: <?php echo ($importp->accountcode);?></font></div></td>

<td><div align="center"><font size="-1"><?php echo ($persprint->agency);?></font></div></td>

<td><div align="center"><font size="-1"><?php echo ($importp->percode);?></font></div></td>

<td><div align="center"><font size="-1"><?php echo ($projprint->name);?><?php echo ($projprint->agency);?></font></div></td>

<td><div align="center"><font size="-1"><?php echo "$"?><?php echo

($row->amt);?></font></div></td>

</tr>

<?php

}

?>

</table>

</FORM>

</body>

</html>

moonman89 · August 17, 2009

$importq = "SELECT * FROM `import` WHERE `calledno` = '{$row->number}' AND `date` = {$row->date}' AND SUBTIME(`time`,'{$row->time}') >= $time_diff";
[/code]

Something like that is what you want I think, I have not tested this...

If you are working with a lot of data, you can most likely produce this whole report in SQL a lot more effectively.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_subtime

I would also seriously recommend you have a look at this:

http://dev.mysql.com/doc/refman/5.0/en/join.html

Its what you are doing 100000 times easier.

bajangerry · August 17, 2009

moonman,

Thanks for that, will give it a go later... unfortunately I have to head out of the office now so i can't play with it right away... will let you know when I do.

Thanks again for your input.

bajangerry · August 18, 2009

Ok, I created the following and am getting results that I like so far...

<?php

include("lib/config.php");

top();

$result = mysql_query("SELECT * FROM unlime JOIN import ON unlime.number=import.calledno AND TIMEDIFF(unlime.time, import.time) BETWEEN -2 and 2");

while ($result = mysql_fetch_object($result)){

?>

<p>

<?

echo ($row->number);

echo " ";

echo ($row->destination);

echo " ";

echo ($row->calledno);

echo " ";

echo ($row->time);

echo " ";

echo ($row->mins);

echo " ";

echo ($row->amt);

echo " ";

}

?>

</p>

My question now though is how do I display both of the times from either table? Unfortunately both tables have the same column heading for time so I am a little confused about how to separate them for the purpose of displaying them.

Sign In

[SOLVED] Time range test

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information