Hi everyone.

I'm having some trouble with deciding how best to put the PHP
timezones into a MySQL database. The database should server two
purposes. Firstly, it should be able to list the continents, (Africa,
Australia, Europe etc.)... I plan on having an Ajax interface, so that
when Africa is clicked upon, any children are called from the server
and displayed. If any of those children have children, then it will do
the same again. I would like to make this system work with a fairly
unlimited child-depth, so I decided to break the timezones up, give
them IDs, and also a field linking to the id of the parent (if there
is one).

Uh-oh... Major downfall. When the last child is clicked upon, I need
to get the whole string for example: America/Kentucky/Louisville
Kentucky being the parent of Louisville, and America being the parent
of Kentucky. Drats. Well, I guess I can work with this if I knew for
sure that there would be no more than a certain number of parents
involved. Perhaps I can do this, but I can't think how I might do it
without too many queries. The plus-side of doing it this way, would
mean that it cuts down on data redundancy, which is why I like it, but
it's really hard to get the data in the database the way it needs to
be in a timely manner.

I decided to see what I can do with just putting the whole string in
which just an ID. OK, now I can get the whole string using an ID,
which is quite useful, but I can't break it up into continents,
countries/cites etc... Remember, the initial Web page should show only
the continents, and then clicking on them will load their children,
and clicking the children will load their's (if any) etc etc...
Strewth! So The first method allows me to send that data how I want,
and the second method allows me to retrieve the correct string by the
appropriate ID.

Do I really need two tables running here, or would someone please be
able to suggest something?

I hope my post makes sense. If it doesn't, please let me know and I'll
be happy to clarify. I am pulling my hair out here, as a supposedly
simple task keeps tackling me to the ground and kicking me whilst I'm
down. Perhaps I shouldn't use a database, and I should just try and
use PHP arrays?

Many thanks in advance.

Daz.

On 27 May 2007 12:17:05 -0700, Daz <EMAIL REMOVED> wrote:

>Hi everyone.
>
>I'm having some trouble with deciding how best to put the PHP
>timezones into a MySQL database. The database should server two
>purposes. Firstly, it should be able to list the continents, (Africa,
>Australia, Europe etc.)... I plan on having an Ajax interface, so that
>when Africa is clicked upon, any children are called from the server
>and displayed. If any of those children have children, then it will do
>the same again. I would like to make this system work with a fairly
>unlimited child-depth, so I decided to break the timezones up, give
>them IDs, and also a field linking to the id of the parent (if there
>is one).
>
>Uh-oh... Major downfall. When the last child is clicked upon, I need
>to get the whole string for example: America/Kentucky/Louisville
>Kentucky being the parent of Louisville, and America being the parent
>of Kentucky. Drats. Well, I guess I can work with this if I knew for
>sure that there would be no more than a certain number of parents
>involved. Perhaps I can do this, but I can't think how I might do it
>without too many queries. The plus-side of doing it this way, would
>mean that it cuts down on data redundancy, which is why I like it, but
>it's really hard to get the data in the database the way it needs to
>be in a timely manner.
>
>I decided to see what I can do with just putting the whole string in
>which just an ID. OK, now I can get the whole string using an ID,
>which is quite useful, but I can't break it up into continents,
>countries/cites etc... Remember, the initial Web page should show only
>the continents, and then clicking on them will load their children,
>and clicking the children will load their's (if any) etc etc...
>Strewth! So The first method allows me to send that data how I want,
>and the second method allows me to retrieve the correct string by the
>appropriate ID.
>
>Do I really need two tables running here, or would someone please be
>able to suggest something?
>
>I hope my post makes sense. If it doesn't, please let me know and I'll
>be happy to clarify. I am pulling my hair out here, as a supposedly
>simple task keeps tackling me to the ground and kicking me whilst I'm
>down. Perhaps I shouldn't use a database, and I should just try and
>use PHP arrays?
>
>Many thanks in advance.
>
>Daz.

I think your problem is a complex one and your description is
sometimes hard to understand. I don't really get exactly what you want
to do.

> I would like to make this system work with a fairly
>unlimited child-depth, so I decided to break the timezones up, give
>them IDs, and also a field linking to the id of the parent (if there
>is one).

What could be the parent of a TZ ?

>Uh-oh... Major downfall. When the last child is clicked upon, I need
>to get the whole string for example: America/Kentucky/Louisville

Well if you have an indefinite number of depth, two solutions :
either you store the depth level of the element, or else you have to
deal with recursion. Sometimes you can't store the depth level (or you
can but it's useless). Recursion is not beautiful because MySQL is not
done for it, but it's possible.
So having the ID of an element, you can do a procedure that will
output the path from top to that element, but be careful that you must
have a tree, not a net (only one parent for each child).

>I decided to see what I can do with just putting the whole string in
>which just an ID. OK, now I can get the whole string using an ID,
>which is quite useful, but I can't break it up into continents,

I don't know what other people would think about it, but I take this
as a big mistake : every time you want to change one thing, you have
to change a LOT of things (a little mistake in the name of a country
and you're damn ****ed : you'll spend two hours trying to get that
"UPDATE WHERE REGEXP"-query right, only to find at the end that a lot
of cities have the name of this first mispelled country, and then
you'll come on usenet and ask more questions and people will tell you
about normalization until you die).

>I hope my post makes sense. If it doesn't, please let me know and I'll
>be happy to clarify.

That would help.
Please describe exactly what you want to achieve, and only after how
you tried to do it. In your description, both are mixed and it doesn't
help sort out the whole purpose.

> I am pulling my hair out here, as a supposedly
>simple task keeps tackling me to the ground and kicking me whilst I'm
>down.

Haha, ok, I think you are kicking yourself (very limber). Take it
piece by piece and it might be done soon, but just remember that
'supposedly simple' is a pleonasm.

> Perhaps I shouldn't use a database, and I should just try and
>use PHP arrays?

I wouldn't say so. If you are willing to do some concessions, a DB is
a better way to keep your system up to date (far easier to maintain).

On May 28, 6:41 am, subtenante <zzsubtenant...@gmail.com> wrote:

> I think your problem is a complex one and your description is
> sometimes hard to understand. I don't really get exactly what you want
> to do.

I think it is, too. lol

> > I would like to make this system work with a fairly
> >unlimited child-depth, so I decided to break the timezones up, give
> >them IDs, and also a field linking to the id of the parent (if there
> >is one).
>
> What could be the parent of a TZ ?

Ok, Here's a PHP timezone:

America/Kentucky/Louisville

Now, as far as the database is concerned, the parent-child
relationship would be something like this:

America < Kentucky < Louisville

America is the parent of Kentucky (and lots of other timezones).
Kentucky is the parent of Louisville.
I hope that makes sense.

When the user first downloads the page, They should see continents:
Africa
America
Australia
Europe
....

Next, when they click on a continent, the database should be queried
(by id). Say we clicked on Australia, we should receive a list back
like this:
ACT
Adelaide
Brisbane
Broken_Hill
Canberra
Currie
Darwin
Hobart
LHI
Lindeman
Lord_Howe
Melbourne
North
NSW
Perth
Queensland
South
Sydney
Tasmania
Victoria
West
Yancowinna

This list will be inserted on the page dynamically, but I should also
be able to cope with unlimited sub-timezones. Although the maximum
size of a PHP is 3 (America/Kentucky/Louisville contains 3 "items"),
I'd like to be able to cater for almost unlimited children. This will
work fine in reality, but when the last sub-timezone is clicked, the
database needs to backtrack, and compile the timezone string. So it we
clicked on "West", the server should query the database, get it's
parent and use that string for the time zone. The resulting string
should be Australia/West.

Don't forget, however, I want to do this recursively, but I'm unsure
if MySQL supports it. so essentially we could end up with a string
like this parent/child/nextchild/nextchild etc...

> >Uh-oh... Major downfall. When the last child is clicked upon, I need
> >to get the whole string for example: America/Kentucky/Louisville
>
> Well if you have an indefinite number of depth, two solutions :
> either you store the depth level of the element, or else you have to
> deal with recursion. Sometimes you can't store the depth level (or you
> can but it's useless). Recursion is not beautiful because MySQL is not
> done for it, but it's possible.

I suspected that. I guess I could query the database each time, and
concatenate the results. The query would get the timezone by id, and
check if it has a parent. If it does, then it will get that item, and
check it it has a parent. If it does then it will get that item, and
check if it has a parent. etc..

As I am certain there won't ever be the need for anymore than say 5
queries to this table, it shouldn't be an issue, although it would
sure be nice to be able to do this in a single query.

> So having the ID of an element, you can do a procedure that will
> output the path from top to that element, but be careful that you must
> have a tree, not a net (only one parent for each child).

Yes, a tree is exactly the structure I wanted to use. With that said,
I am a bit stuck with how to initially get the data into the database
correctly. I've tried making a JavaScript app to do it, but I keep
going round in circles. I need to use recursion, but I'm stuck. Any
ideas? I think I might just do it manually with a spreadsheet

> >I decided to see what I can do with just putting the whole string in
> >which just an ID. OK, now I can get the whole string using an ID,
> >which is quite useful, but I can't break it up into continents,
>
> I don't know what other people would think about it, but I take this
> as a big mistake : every time you want to change one thing, you have
> to change a LOT of things (a little mistake in the name of a country
> and you're damn ****ed : you'll spend two hours trying to get that
> "UPDATE WHERE REGEXP"-query right, only to find at the end that a lot
> of cities have the name of this first mispelled country, and then
> you'll come on usenet and ask more questions and people will tell you
> about normalization until you die).

I agree, but it solved the problem about pulling out the whole
timezone string to use for PHP. Perhaps I should two tables? One
containing the entire string and ID, and one containing the broken
down timezones with ids, and links to parents. This would solve my
problem, but it's hardly normalised... This was my initial idea, but I
want to get it right.

>
> >I hope my post makes sense. If it doesn't, please let me know and I'll
> >be happy to clarify.
>
> That would help.
> Please describe exactly what you want to achieve, and only after how
> you tried to do it. In your description, both are mixed and it doesn't
> help sort out the whole purpose.

Hopefully the above described what I want to do better. The server
will simply send out the correct time according to the requested
timezone. I haven't "tried" anything yet, as it's pointless if there
are flaws in all the ideas.
>
> > I am pulling my hair out here, as a supposedly
> >simple task keeps tackling me to the ground and kicking me whilst I'm
> >down.
>
> Haha, ok, I think you are kicking yourself (very limber). Take it
> piece by piece and it might be done soon, but just remember that
> 'supposedly simple' is a pleonasm.
>
> > Perhaps I shouldn't use a database, and I should just try and
> >use PHP arrays?
>
> I wouldn't say so. If you are willing to do some concessions, a DB is
> a better way to keep your system up to date (far easier to maintain).
Agreed. Also, it's a way of getting only the data you need, and not
having to do too much sorting/searching.

Thanks for your input.

Daz wrote:
> On May 28, 6:41 am, subtenante <zzsubtenant...@gmail.com> wrote:
>
>> I think your problem is a complex one and your description is
>> sometimes hard to understand. I don't really get exactly what you want
>> to do.
>
> I think it is, too. lol
>
>>> I would like to make this system work with a fairly
>>> unlimited child-depth, so I decided to break the timezones up, give
>>> them IDs, and also a field linking to the id of the parent (if there
>>> is one).
>> What could be the parent of a TZ ?
>
> Ok, Here's a PHP timezone:
>
> America/Kentucky/Louisville
>
> Now, as far as the database is concerned, the parent-child
> relationship would be something like this:
>
> America < Kentucky < Louisville
>
> America is the parent of Kentucky (and lots of other timezones).
> Kentucky is the parent of Louisville.
> I hope that makes sense.
>
> When the user first downloads the page, They should see continents:
> Africa
> America
> Australia
> Europe
> ...
>
> Next, when they click on a continent, the database should be queried
> (by id). Say we clicked on Australia, we should receive a list back
> like this:
> ACT
> Adelaide
> Brisbane
> Broken_Hill
> Canberra
> Currie
> Darwin
> Hobart
> LHI
> Lindeman
> Lord_Howe
> Melbourne
> North
> NSW
> Perth
> Queensland
> South
> Sydney
> Tasmania
> Victoria
> West
> Yancowinna
>
> This list will be inserted on the page dynamically, but I should also
> be able to cope with unlimited sub-timezones. Although the maximum
> size of a PHP is 3 (America/Kentucky/Louisville contains 3 "items"),
> I'd like to be able to cater for almost unlimited children. This will
> work fine in reality, but when the last sub-timezone is clicked, the
> database needs to backtrack, and compile the timezone string. So it we
> clicked on "West", the server should query the database, get it's
> parent and use that string for the time zone. The resulting string
> should be Australia/West.
>
> Don't forget, however, I want to do this recursively, but I'm unsure
> if MySQL supports it. so essentially we could end up with a string
> like this parent/child/nextchild/nextchild etc...
>
>>> Uh-oh... Major downfall. When the last child is clicked upon, I need
>>> to get the whole string for example: America/Kentucky/Louisville
>> Well if you have an indefinite number of depth, two solutions :
>> either you store the depth level of the element, or else you have to
>> deal with recursion. Sometimes you can't store the depth level (or you
>> can but it's useless). Recursion is not beautiful because MySQL is not
>> done for it, but it's possible.
>
> I suspected that. I guess I could query the database each time, and
> concatenate the results. The query would get the timezone by id, and
> check if it has a parent. If it does, then it will get that item, and
> check it it has a parent. If it does then it will get that item, and
> check if it has a parent. etc..
>
> As I am certain there won't ever be the need for anymore than say 5
> queries to this table, it shouldn't be an issue, although it would
> sure be nice to be able to do this in a single query.
>
>> So having the ID of an element, you can do a procedure that will
>> output the path from top to that element, but be careful that you must
>> have a tree, not a net (only one parent for each child).
>
> Yes, a tree is exactly the structure I wanted to use. With that said,
> I am a bit stuck with how to initially get the data into the database
> correctly. I've tried making a JavaScript app to do it, but I keep
> going round in circles. I need to use recursion, but I'm stuck. Any
> ideas? I think I might just do it manually with a spreadsheet
>
>>> I decided to see what I can do with just putting the whole string in
>>> which just an ID. OK, now I can get the whole string using an ID,
>>> which is quite useful, but I can't break it up into continents,
>> I don't know what other people would think about it, but I take this
>> as a big mistake : every time you want to change one thing, you have
>> to change a LOT of things (a little mistake in the name of a country
>> and you're damn ****ed : you'll spend two hours trying to get that
>> "UPDATE WHERE REGEXP"-query right, only to find at the end that a lot
>> of cities have the name of this first mispelled country, and then
>> you'll come on usenet and ask more questions and people will tell you
>> about normalization until you die).
>
> I agree, but it solved the problem about pulling out the whole
> timezone string to use for PHP. Perhaps I should two tables? One
> containing the entire string and ID, and one containing the broken
> down timezones with ids, and links to parents. This would solve my
> problem, but it's hardly normalised... This was my initial idea, but I
> want to get it right.
>
>>> I hope my post makes sense. If it doesn't, please let me know and I'll
>>> be happy to clarify.
>> That would help.
>> Please describe exactly what you want to achieve, and only after how
>> you tried to do it. In your description, both are mixed and it doesn't
>> help sort out the whole purpose.
>
> Hopefully the above described what I want to do better. The server
> will simply send out the correct time according to the requested
> timezone. I haven't "tried" anything yet, as it's pointless if there
> are flaws in all the ideas.
>>> I am pulling my hair out here, as a supposedly
>>> simple task keeps tackling me to the ground and kicking me whilst I'm
>>> down.
>> Haha, ok, I think you are kicking yourself (very limber). Take it
>> piece by piece and it might be done soon, but just remember that
>> 'supposedly simple' is a pleonasm.
>>
>>> Perhaps I shouldn't use a database, and I should just try and
>>> use PHP arrays?
>> I wouldn't say so. If you are willing to do some concessions, a DB is
>> a better way to keep your system up to date (far easier to maintain).
> Agreed. Also, it's a way of getting only the data you need, and not
> having to do too much sorting/searching.
>
> Thanks for your input.
>

Maybe something like:

id: unique id for this item
parent_id: null for no parent
data: text to be displayed
key: key to use (if it exists for this entry)

Some entries:

1, null, 'North America', null
2, null, 'Europe', null
15, 1, 'United States', null
30, 15, 'Kentucky', 'America/Kentucky/Louisville

This is ***uming you want more informative text displayed than is in the
TZ setting code (personally, I don't like
'America/Kentucky/Louisville'). If there is a fourth field, it contains
the key. If not, it doesn't.

Not an idea situation, I agree. But more flexible than displaying the
txt from the timezone.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
EMAIL REMOVED
==================

On May 28, 2:42 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Daz wrote:
> > On May 28, 6:41 am, subtenante <zzsubtenant...@gmail.com> wrote:
>
> >> I think your problem is a complex one and your description is
> >> sometimes hard to understand. I don't really get exactly what you want
> >> to do.
>
> > I think it is, too. lol
>
> >>> I would like to make this system work with a fairly
> >>> unlimited child-depth, so I decided to break the timezones up, give
> >>> them IDs, and also a field linking to the id of the parent (if there
> >>> is one).
> >> What could be the parent of a TZ ?
>
> > Ok, Here's a PHP timezone:
>
> > America/Kentucky/Louisville
>
> > Now, as far as the database is concerned, the parent-child
> > relationship would be something like this:
>
> > America < Kentucky < Louisville
>
> > America is the parent of Kentucky (and lots of other timezones).
> > Kentucky is the parent of Louisville.
> > I hope that makes sense.
>
> > When the user first downloads the page, They should see continents:
> > Africa
> > America
> > Australia
> > Europe
> > ...
>
> > Next, when they click on a continent, the database should be queried
> > (by id). Say we clicked on Australia, we should receive a list back
> > like this:
> > ACT
> > Adelaide
> > Brisbane
> > Broken_Hill
> > Canberra
> > Currie
> > Darwin
> > Hobart
> > LHI
> > Lindeman
> > Lord_Howe
> > Melbourne
> > North
> > NSW
> > Perth
> > Queensland
> > South
> > Sydney
> > Tasmania
> > Victoria
> > West
> > Yancowinna
>
> > This list will be inserted on the page dynamically, but I should also
> > be able to cope with unlimited sub-timezones. Although the maximum
> > size of a PHP is 3 (America/Kentucky/Louisville contains 3 "items"),
> > I'd like to be able to cater for almost unlimited children. This will
> > work fine in reality, but when the last sub-timezone is clicked, the
> > database needs to backtrack, and compile the timezone string. So it we
> > clicked on "West", the server should query the database, get it's
> > parent and use that string for the time zone. The resulting string
> > should be Australia/West.
>
> > Don't forget, however, I want to do this recursively, but I'm unsure
> > if MySQL supports it. so essentially we could end up with a string
> > like this parent/child/nextchild/nextchild etc...
>
> >>> Uh-oh... Major downfall. When the last child is clicked upon, I need
> >>> to get the whole string for example: America/Kentucky/Louisville
> >> Well if you have an indefinite number of depth, two solutions :
> >> either you store the depth level of the element, or else you have to
> >> deal with recursion. Sometimes you can't store the depth level (or you
> >> can but it's useless). Recursion is not beautiful because MySQL is not
> >> done for it, but it's possible.
>
> > I suspected that. I guess I could query the database each time, and
> > concatenate the results. The query would get the timezone by id, and
> > check if it has a parent. If it does, then it will get that item, and
> > check it it has a parent. If it does then it will get that item, and
> > check if it has a parent. etc..
>
> > As I am certain there won't ever be the need for anymore than say 5
> > queries to this table, it shouldn't be an issue, although it would
> > sure be nice to be able to do this in a single query.
>
> >> So having the ID of an element, you can do a procedure that will
> >> output the path from top to that element, but be careful that you must
> >> have a tree, not a net (only one parent for each child).
>
> > Yes, a tree is exactly the structure I wanted to use. With that said,
> > I am a bit stuck with how to initially get the data into the database
> > correctly. I've tried making a JavaScript app to do it, but I keep
> > going round in circles. I need to use recursion, but I'm stuck. Any
> > ideas? I think I might just do it manually with a spreadsheet
>
> >>> I decided to see what I can do with just putting the whole string in
> >>> which just an ID. OK, now I can get the whole string using an ID,
> >>> which is quite useful, but I can't break it up into continents,
> >> I don't know what other people would think about it, but I take this
> >> as a big mistake : every time you want to change one thing, you have
> >> to change a LOT of things (a little mistake in the name of a country
> >> and you're damn ****ed : you'll spend two hours trying to get that
> >> "UPDATE WHERE REGEXP"-query right, only to find at the end that a lot
> >> of cities have the name of this first mispelled country, and then
> >> you'll come on usenet and ask more questions and people will tell you
> >> about normalization until you die).
>
> > I agree, but it solved the problem about pulling out the whole
> > timezone string to use for PHP. Perhaps I should two tables? One
> > containing the entire string and ID, and one containing the broken
> > down timezones with ids, and links to parents. This would solve my
> > problem, but it's hardly normalised... This was my initial idea, but I
> > want to get it right.
>
> >>> I hope my post makes sense. If it doesn't, please let me know and I'll
> >>> be happy to clarify.
> >> That would help.
> >> Please describe exactly what you want to achieve, and only after how
> >> you tried to do it. In your description, both are mixed and it doesn't
> >> help sort out the whole purpose.
>
> > Hopefully the above described what I want to do better. The server
> > will simply send out the correct time according to the requested
> > timezone. I haven't "tried" anything yet, as it's pointless if there
> > are flaws in all the ideas.
> >>> I am pulling my hair out here, as a supposedly
> >>> simple task keeps tackling me to the ground and kicking me whilst I'm
> >>> down.
> >> Haha, ok, I think you are kicking yourself (very limber). Take it
> >> piece by piece and it might be done soon, but just remember that
> >> 'supposedly simple' is a pleonasm.
>
> >>> Perhaps I shouldn't use a database, and I should just try and
> >>> use PHP arrays?
> >> I wouldn't say so. If you are willing to do some concessions, a DB is
> >> a better way to keep your system up to date (far easier to maintain).
> > Agreed. Also, it's a way of getting only the data you need, and not
> > having to do too much sorting/searching.
>
> > Thanks for your input.
>
> Maybe something like:
>
> id: unique id for this item
> parent_id: null for no parent
> data: text to be displayed
> key: key to use (if it exists for this entry)
>
> Some entries:
>
> 1, null, 'North America', null
> 2, null, 'Europe', null
> 15, 1, 'United States', null
> 30, 15, 'Kentucky', 'America/Kentucky/Louisville
>
> This is ***uming you want more informative text displayed than is in the
> TZ setting code (personally, I don't like
> 'America/Kentucky/Louisville'). If there is a fourth field, it contains
> the key. If not, it doesn't.
>
> Not an idea situation, I agree. But more flexible than displaying the
> txt from the timezone.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

That's pretty much what I had in mind, only I was going to use 2
tables. What is bugging me is that I can't seem to get it to adhere to
normalisation specs. But in the name of keeping it simple, and fast, I
think I just might settle for your suggestion. In the defense of that
idea, the database shouldn't expand much more beyond the size it
already is, and it's hardly m***ive, so I think it's a reasonable
compromise. Thanks for your input Jerry.

Why not just use tables and keep the "history" in php? That is, you have a
table for each content and then each of those tables has a tables for the
different states, countries, counties, or whatever...


But just "remember" in php the path that the user chose instead of trying to
do that in sql. Should be a very simple thing to do.

Jon

"Perhaps I shouldn't use a database, and I should just try and
use PHP arrays?"

Why not? It would be quicker and its something that you only have to set up
once. (you still have to enter the data by hand anyways, right?)

By using php arrays or even sql you can convert to the other pretty easily
so you can actually have both if you needed. I don't know anyting about the
TZ/locale stuff but chances are someone has already done what you want and
you can use it for free.