Re: Time Difference
| From: | Doug Semig | Date: | Sat, 28 Oct 2000 09:59:02 +0000 |
| Subject: | Re: Time Difference | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-4007@lists.php.net to get a copy of this message | ||
This is it for Apache/PHP/MySQL.
Of course, your "date" column in both of your tables should not be called
"date" since that is an SQL reserved word. So in my columns they are
called "thedate". So you simply have to edit the two SELECT statements.
You'll want to convert all of the database function calls to work with your
database platform.
It's tested and it works on WIN32 platform running Apache/PHP/MySQL. I'm
trying out that platform **only** because I am helping out with a local
community college student web development project.
All disclaimers apply. Thanks, it was a fun quick little exercise.
Doug
<!-- ------------------Filename: test.phtml------------------ ->
<HTML>
<HEAD>
<TITLE>Test</TITLE>
</HEAD>
<BODY>
<?
$dbconn = mysql_connect("localhost");
$result = mysql_query("use test");
$empid=1;
$thedate='2000-10-30';
$result = mysql_query("SELECT start_time, end_time
FROM availability WHERE emp_id = $empid AND thedate = '$thedate'");
$data = mysql_fetch_row($result);
$beginfreeslot = $data[0];
$empendtime = $data[1];
$result = mysql_query("SELECT start_time, end_time
FROM appointments WHERE emp_id = $empid AND thedate='$thedate'
ORDER BY start_time");
$iy = 0;
$numappts = mysql_num_rows($result);
for ($ix = 0; $ix < $numappts; $ix++) {
$data = mysql_fetch_row($result);
if ($beginfreeslot < $data[0]) {
$freetimestart[$iy] = $beginfreeslot;
$freetimeend[$iy] = $data[0];
$beginfreeslot = $data[1];
$iy++;
}
else $beginfreeslot = $data[1];
}
if ($data[1] < $empendtime) {
$freetimestart[$iy] = $data[1];
$freetimeend[$iy] = $empendtime;
$iy++;
}
echo "<TABLE CELLSPACING=\"0\" CELLPADDING=\"0\"
BORDER=\"0\"";
echo "WIDTH=\"25%\">\n";
echo "<TR><TD COLSPAN=\"2\" ALIGN=\"CENTER\">";
echo "Free time employee #" . $empid;
echo "</TD></TR>\n";
for ($ix = 0; $ix < $iy; $ix++) {
echo "<TR>\n<TD WIDTH=\"50%\">";
echo $freetimestart[$ix];
echo "</TD>\n<TD WIDTH=\"50%\">";
echo $freetimeend[$ix];
echo "</TD>\n</TR>";
}
echo "</TABLE>\n";
?>
</BODY>
</HTML>
At 01:28 AM 10/28/00 -0700, Mike E wrote:
>I have the following setup:
>
>appt=# select * from availability \g
> emp_id | date | start_time | end_time
>--------+------------+------------+----------
> 1 | 2000-10-30 | 08:00:00 | 14:30:00
>
>appt=# select * from appointments \g
> start_time | end_time | emp_id | cus_id | services | date
>------------+----------+--------+--------+----------+------------
> 09:00:00 | 11:30:00 | 1 | 2 | {1,2,3} | 2000-10-30
>
>Now, what I would like to get is the following:
>
> start_time | end_time
>------------+----------
> 08:00:00 | 09:00:00
> 11:30:00 | 14:30:00
>
>I'm trying to generate a result set of all the free time slots in
>availability by taking the difference of the two. I've so far been
>unable to generate anything that works. Is this possible?
>
>Mike
>
>--
>Mike Erickson <mee@quidquam> http://www.quidquam.com/
>"Hatred is the coward's revenge for being intimidated" - George Bernard Shaw
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net
>For additional commands, e-mail: php-db-help@lists.php.net
>To contact the list administrators, e-mail: php-list-admin@lists.php.net
>
>
>