MySQL ODBC Connector - Calendar table workaround

Author

Calendar table workaround

Jonathan Mangin

2006-01-04, 11:23 am

I created a calendar table (date only), but all
where clauses include a uid. Is the following a
sane workaround to get a usable calendar table?
Anything else I can do?

my $sth = $dbh->prepare("
create table $temp_tbl
(date date,
uid varchar(14))
select date,
? as uid
from calendar
where date between ? and ?");
$sth->execute($uid, $bdate, $edate);

Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jonathan Mangin

2006-01-04, 8:24 pm

----- Original Message -----
From: "Rhino" <rhino1@sympatico.ca>
To: "Jonathan Mangin" <jon.mangin@comcast.net>
Sent: Wednesday, January 04, 2006 1:25 PM
Subject: Re: Calendar table workaround

>
> ----- Original Message -----
> From: "Jonathan Mangin" <jon.mangin@comcast.net>
> To: <mysql@lists.mysql.com>
> Sent: Wednesday, January 04, 2006 10:45 AM
> Subject: Calendar table workaround
>
>
>
> I have no idea what you are asking, which may explain why no one has
replied
> to your question yet.
>
> I've been working with relational databases for 20 years and I've never
> heard the term "calendar table". What are you trying to accomplish? If you
> describe clearly what you are trying to do, perhaps someone can help you
> devise a way to do it in MySQL.
>
> Rhino
>
>
A table of dates to which to join other tables,
ensuring reports that reflect days for which no
data is available.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Jay Paulson \

2006-01-04, 8:24 pm

------ _=_NextPart_001_01C6
1165.C946DD37
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

> I have no idea what you are asking, which may explain why no one has
replied
> to your question yet.
>
> I've been working with relational databases for 20 years and I've =
never

> heard the term "calendar table". What are you trying to accomplish? If =
you
> describe clearly what you are trying to do, perhaps someone can help =
you
> devise a way to do it in MySQL.
>
> Rhino
>
>
>A table of dates to which to join other tables,
>ensuring reports that reflect days for which no
>data is available.

I forget the query but I know it can be done. But can't you just have a =
table (called calendar?) with each entry having it's own row with a date =
column that gives whatever date you need in it and then other columns =
for any other details you need to have to go along with the date. =20

After you have a table full of dates you can just do a query that will =
grab all the information and display it like a calendar and show you =
days that have information and days that don't have anything.

I'll see if I can't find that query for you and explain it to you. =20

jay

------ _=_NextPart_001_01C6
1165.C946DD37--

Peter Brawley

2006-01-04, 8:24 pm

--=======AVGMAIL-43BC269F72A9=======
Content-Type: multipart/alternative; boundary=------------ 00010505070509030406
0708

-------------- 00010505070509030406
0708
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jonathan,

I understand what you mean by a calendar table, but like Rhino I've no idea
what you're asking. How to link the calendar table to other tables
depends on
your other tables. For a fairly simple & generic example of a calendar
table see http://www.artfulsoftware.com/queries.php#20.

PB

-----

Jonathan Mangin wrote:

>----- Original Message -----
>From: "Rhino" <rhino1@sympatico.ca>
>To: "Jonathan Mangin" <jon.mangin@comcast.net>
>Sent: Wednesday, January 04, 2006 1:25 PM
>Subject: Re: Calendar table workaround
>
>
>
>
>replied
>
>
>A table of dates to which to join other tables,
>ensuring reports that reflect days for which no
>data is available.
>
>
>
>

-------------- 00010505070509030406
0708
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Jonathan, 
 
I understand what you mean by a calendar table, but like Rhino I've no
idea 
what you're asking. How to link the calendar table to other tables
depends on 
your other tables. For a fairly simple & generic example of a
calendar table see <a class="moz-txt-link-freetext" href="http://www.artfulsoftware.com/queries.php#20">http://www.artfulsoftware.com/queries.php#20</a>. 
 
PB 
 
----- 
 
Jonathan Mangin wrote:
<blockquote cite=" mid00fc01c61164$2c30
4300$7a01a8c0@rover" type="cite">
<pre wrap="">----- Original Message -----
From: "Rhino" <a class="moz-txt-link-rfc2396E" href=" mailto:rhino1@sympat
ico.ca"><rhino1@sympatico.ca></a>
To: "Jonathan Mangin" <a class="moz-txt-link-rfc2396E" href="mailto:jon.mangin@comcast.net"><jon.mangin@comcast.net></a>
Sent: Wednesday, January 04, 2006 1:25 PM
Subject: Re: Calendar table workaround

</pre>
<blockquote type="cite">
<pre wrap="">----- Original Message -----
From: "Jonathan Mangin" <a class="moz-txt-link-rfc2396E" href="mailto:jon.mangin@comcast.net"><jon.mangin@comcast.net></a>
To: <a class="moz-txt-link-rfc2396E" href="mailto:mysql@lists.mysql.com"><mysql@lists.mysql.com></a>
Sent: Wednesday, January 04, 2006 10:45 AM
Subject: Calendar table workaround

</pre>
<blockquote type="cite">
<pre wrap="">I created a calendar table (date only), but all
where clauses include a uid. Is the following a
sane workaround to get a usable calendar table?
Anything else I can do?

my $sth = $dbh->prepare("
create table $temp_tbl
(date date,
uid varchar(14))
select date,
? as uid
from calendar
where date between ? and ?");
$sth->execute($uid, $bdate, $edate);

</pre>
</blockquote>
<pre wrap="">I have no idea what you are asking, which may explain why no one has
</pre>
</blockquote>
<pre wrap="">replied
</pre>
<blockquote type="cite">
<pre wrap="">to your question yet.

I've been working with relational databases for 20 years and I've never
heard the term "calendar table". What are you trying to accomplish? If you
describe clearly what you are trying to do, perhaps someone can help you
devise a way to do it in MySQL.

Rhino

</pre>
</blockquote>
<pre wrap="">A table of dates to which to join other tables,
ensuring reports that reflect days for which no
data is available.

</pre>
</blockquote>
</body>
</html>

-------------- 00010505070509030406
0708--
--=======AVGMAIL-43BC269F72A9=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"

No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.12/220 - Release Date: 1/3/2006

--=======AVGMAIL-43BC269F72A9=======
Content-Type: text/plain; charset=us-ascii

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
--=======AVGMAIL-43BC269F72A9=======--

Jonathan Mangin

2006-01-04, 8:24 pm

------ =_NextPart_000_0122_
01C61142.634DE1C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

RE: Calendar table workaround>
>A table of dates to which to join other tables,
>ensuring reports that reflect days for which no
>data is available.

I forget the query but I know it can be done. But can't you just have a =
table (called calendar?) with each entry having it's own row with a date =
column that gives whatever date you need in it and then other columns =
for any other details you need to have to go along with the date.=20

-----
Sorry, I thought this was a very common situation. And, therefore,
instantly recognizable. I'll include the full story.

my $bdate =3D '2005-08-01';
my $edate =3D '2005-08-14';
my $uid =3D 'george';
my $temp_tbl =3D 'calendar_' . $uid;

my $sth =3D $dbh->prepare("
create table $temp_tbl
(date date,
uid varchar(14))
engine =3D memory
select date,
? as uid
from calendar
where date between ? and ?");
$sth->execute($uid, $bdate, $edate);

$sth =3D $dbh->prepare("
(select
$temp_tbl.date as date,
concat(type,seq) as event,
time_format(time,'%H
:%i'),
value as val1,
'' as val2
from $temp_tbl
left join table1
on table1.date =3D $temp_tbl.date
where $temp_tbl.uid =3D ?
and $temp_tbl.date between ? and ?)
union
(select
$temp_tbl.date,
concat(type,seq),
time_format(time,'%H
:%i'),
t1_val,
t2_val
from $temp_tbl
left join table2
on table2.date =3D $temp_tbl.date
where $temp_tbl.uid =3D ?
and $temp_tbl.date between ? and ?)
order by date, event");
$sth->execute($uid, $bdate, $edate, $uid, $bdate, $edate);

These are $uid-specific reports (where .uid =3D ?) and
uid, of course, doesn't exist in my standard 'calendar table.'

The question: Is creating another "temporary" table (that does
include both date and uid) the best thing to do here?

Thanks.

------ =_NextPart_000_0122_
01C61142.634DE1C0--

Sponsored Links