Damian
|
| Posted: 07/22/2004, 12:31 AM |
|
Howdy All,
I have a Registration Form which requires users to enter a whole swag of
details about themselves.
A number of the steps you are required to choose from a list box (or radio
button) such as:
(Male, Female)
Country (Table....)
State (Table...)
To use the db more efficiently, less space (and maybe less memory?) I have
stored the ID values instead of teh text values, eg, Male=1, Female=2,
Australia=100, India=101, USA=102,
If I want to display the Text Values instead of the stored Numeric IDs what
sort of Join Types do I use and between what values?
eg.
table1=members(id, first,last,login,pass,,region1,region2)
table2=(id,)
table3=region1(id,region1)
table4=region2(id,region1id, region2)
Where
members.=.id
members.region1 = region1.id
members.region2=region2.id
region2.region1id=region1.id
So in my GRID table, is it possible to display the text Values, eg
region1.region1 and region2.region2 instead of the ID values that are stored
in the members table?
OR....
Should I just store the text values in members (I know this works... but my
DB may grow very large....).
And while Im at it... big thanks to Walter for all his help on the multiple
list box issue I posted last week.
Damian
|
|
|
 |
Walter Kempees
|
| Posted: 07/22/2004, 1:27 AM |
|
So here we are again then.
I think there are two solutions and I leave it tho others to comment on the
best method.
Method 1 is 'joining' the tables in the Grid's select.
Method 2 is in the grid display the Text data, 'looking up the Text by its
Integere value.
Both methods work.
Performance and designers effort might make the difference.
Method 1 SQL snippet
select mbr.firstname,
mbr.middlename,
mbr.familyname,
.,
r1.region1,
r2.region2,
r3.region3,
r4.region4
from members as mbr
left join as on .id = mbr.
left join region1 as r1 on r1.id = mbr.region1
left join region2 as r2 on r2.id = mbr.region2
left join region3 as r3 on r3.id = mbr.region3
left join region4 as r4 on r4.id = mbr.region4
This you can do by hand (in a separate query tool) or do it directly in the
Grids Query builder.
(Private note: your members. is a varchar(2) while the .id is an
int(5), don't really like to talk about your memebers.)
can be a enum field or a set, but that's another.........
Method 1 gives you the fields in the grid, as the VQB (visual Query Builder)
gives you the ability to check fields you want to use.
This is an easy way.
Your example is adapted at http://213.10.55.10/damian zip file also, look at
the List of D_Members.
Method 2
Is doing it, my opinion, the hard way adding labels or fields to the grid
and lookups to the grid.
Just as a note to all those that are going to shoot at all the joining going
on.
If you have to choose between doing work and letting the database do the
work for you a I lways choose the latter (99%).
Walter
"Damian" <codecharge@sydneyit.no.com.junk> schreef in bericht
news:cdnqh1$5gv$1@news.codecharge.com...
> Howdy All,
>
> I have a Registration Form which requires users to enter a whole swag of
> details about themselves.
>
> A number of the steps you are required to choose from a list box (or radio
> button) such as:
>
> (Male, Female)
> Country (Table....)
> State (Table...)
>
> To use the db more efficiently, less space (and maybe less memory?) I have
> stored the ID values instead of teh text values, eg, Male=1, Female=2,
> Australia=100, India=101, USA=102,
>
> If I want to display the Text Values instead of the stored Numeric IDs
what
> sort of Join Types do I use and between what values?
>
> eg.
> table1=members(id, first,last,login,pass,,region1,region2)
> table2=(id,)
> table3=region1(id,region1)
> table4=region2(id,region1id, region2)
>
> Where
> members.=.id
> members.region1 = region1.id
> members.region2=region2.id
> region2.region1id=region1.id
>
> So in my GRID table, is it possible to display the text Values, eg
> region1.region1 and region2.region2 instead of the ID values that are
stored
> in the members table?
>
> OR....
>
> Should I just store the text values in members (I know this works... but
my
> DB may grow very large....).
>
>
> And while Im at it... big thanks to Walter for all his help on the
multiple
> list box issue I posted last week.
>
> Damian
>
>
|
|
|
|
Damian
|
| Posted: 07/22/2004, 5:26 AM |
|
Ummm... not sure if the Private Note is all a joke!
members. is a variable character...
ok, and .id is an int(5) - this confuses me. Are you pointing out that
the field sizes are different and that that could cause an issue? If so,
does it matter whether I use the INT or the TEXT in my members database?
Damian
"Walter Kempees" <kempe819@planet.nl> wrote in message
news:cdntpr$114$1@news.codecharge.com...
> So here we are again then.
> I think there are two solutions and I leave it tho others to comment on
the
> best method.
>
> Method 1 is 'joining' the tables in the Grid's select.
> Method 2 is in the grid display the Text data, 'looking up the Text by its
> Integere value.
> Both methods work.
> Performance and designers effort might make the difference.
>
> Method 1 SQL snippet
>
> select mbr.firstname,
> mbr.middlename,
> mbr.familyname,
> .,
> r1.region1,
> r2.region2,
> r3.region3,
> r4.region4
> from members as mbr
> left join as on .id = mbr.
> left join region1 as r1 on r1.id = mbr.region1
> left join region2 as r2 on r2.id = mbr.region2
> left join region3 as r3 on r3.id = mbr.region3
> left join region4 as r4 on r4.id = mbr.region4
>
> This you can do by hand (in a separate query tool) or do it directly in
the
> Grids Query builder.
> (Private note: your members. is a varchar(2) while the .id is an
> int(5), don't really like to talk about your memebers.)
> can be a enum field or a set, but that's another.........
> Method 1 gives you the fields in the grid, as the VQB (visual Query
Builder)
> gives you the ability to check fields you want to use.
> This is an easy way.
> Your example is adapted at http://213.10.55.10/damian zip file also, look
at
> the List of D_Members.
>
>
> Method 2
> Is doing it, my opinion, the hard way adding labels or fields to the grid
> and lookups to the grid.
>
> Just as a note to all those that are going to shoot at all the joining
going
> on.
> If you have to choose between doing work and letting the database do the
> work for you a I lways choose the latter (99%).
>
> Walter
>
>
>
>
> "Damian" <codecharge@sydneyit.no.com.junk> schreef in bericht
>news:cdnqh1$5gv$1@news.codecharge.com...
> > Howdy All,
> >
> > I have a Registration Form which requires users to enter a whole swag of
> > details about themselves.
> >
> > A number of the steps you are required to choose from a list box (or
radio
> > button) such as:
> >
> > (Male, Female)
> > Country (Table....)
> > State (Table...)
> >
> > To use the db more efficiently, less space (and maybe less memory?) I
have
> > stored the ID values instead of teh text values, eg, Male=1, Female=2,
> > Australia=100, India=101, USA=102,
> >
> > If I want to display the Text Values instead of the stored Numeric IDs
> what
> > sort of Join Types do I use and between what values?
> >
> > eg.
> > table1=members(id, first,last,login,pass,,region1,region2)
> > table2=(id,)
> > table3=region1(id,region1)
> > table4=region2(id,region1id, region2)
> >
> > Where
> > members.=.id
> > members.region1 = region1.id
> > members.region2=region2.id
> > region2.region1id=region1.id
> >
> > So in my GRID table, is it possible to display the text Values, eg
> > region1.region1 and region2.region2 instead of the ID values that are
> stored
> > in the members table?
> >
> > OR....
> >
> > Should I just store the text values in members (I know this works... but
> my
> > DB may grow very large....).
> >
> >
> > And while Im at it... big thanks to Walter for all his help on the
> multiple
> > list box issue I posted last week.
> >
> > Damian
> >
> >
>
>
|
|
|
|
Damian
|
| Posted: 07/22/2004, 6:03 AM |
|
Hey it works!
Although... in the Visual Query Builder I had to do RIGHT joins to get the
SQL statements to say LEFT join!
I also had to change the DATA TYPE to Text (Doh!)...
Im still not too sure about that variable character!
Damian
"Walter Kempees" <kempe819@planet.nl> wrote in message
news:cdntpr$114$1@news.codecharge.com...
> So here we are again then.
> I think there are two solutions and I leave it tho others to comment on
the
> best method.
>
> Method 1 is 'joining' the tables in the Grid's select.
> Method 2 is in the grid display the Text data, 'looking up the Text by its
> Integere value.
> Both methods work.
> Performance and designers effort might make the difference.
>
> Method 1 SQL snippet
>
> select mbr.firstname,
> mbr.middlename,
> mbr.familyname,
> .,
> r1.region1,
> r2.region2,
> r3.region3,
> r4.region4
> from members as mbr
> left join as on .id = mbr.
> left join region1 as r1 on r1.id = mbr.region1
> left join region2 as r2 on r2.id = mbr.region2
> left join region3 as r3 on r3.id = mbr.region3
> left join region4 as r4 on r4.id = mbr.region4
>
> This you can do by hand (in a separate query tool) or do it directly in
the
> Grids Query builder.
> (Private note: your members. is a varchar(2) while the .id is an
> int(5), don't really like to talk about your memebers.)
> can be a enum field or a set, but that's another.........
> Method 1 gives you the fields in the grid, as the VQB (visual Query
Builder)
> gives you the ability to check fields you want to use.
> This is an easy way.
> Your example is adapted at http://213.10.55.10/damian zip file also, look
at
> the List of D_Members.
>
>
> Method 2
> Is doing it, my opinion, the hard way adding labels or fields to the grid
> and lookups to the grid.
>
> Just as a note to all those that are going to shoot at all the joining
going
> on.
> If you have to choose between doing work and letting the database do the
> work for you a I lways choose the latter (99%).
>
> Walter
>
>
>
>
> "Damian" <codecharge@sydneyit.no.com.junk> schreef in bericht
>news:cdnqh1$5gv$1@news.codecharge.com...
> > Howdy All,
> >
> > I have a Registration Form which requires users to enter a whole swag of
> > details about themselves.
> >
> > A number of the steps you are required to choose from a list box (or
radio
> > button) such as:
> >
> > (Male, Female)
> > Country (Table....)
> > State (Table...)
> >
> > To use the db more efficiently, less space (and maybe less memory?) I
have
> > stored the ID values instead of teh text values, eg, Male=1, Female=2,
> > Australia=100, India=101, USA=102,
> >
> > If I want to display the Text Values instead of the stored Numeric IDs
> what
> > sort of Join Types do I use and between what values?
> >
> > eg.
> > table1=members(id, first,last,login,pass,,region1,region2)
> > table2=(id,)
> > table3=region1(id,region1)
> > table4=region2(id,region1id, region2)
> >
> > Where
> > members.=.id
> > members.region1 = region1.id
> > members.region2=region2.id
> > region2.region1id=region1.id
> >
> > So in my GRID table, is it possible to display the text Values, eg
> > region1.region1 and region2.region2 instead of the ID values that are
> stored
> > in the members table?
> >
> > OR....
> >
> > Should I just store the text values in members (I know this works... but
> my
> > DB may grow very large....).
> >
> >
> > And while Im at it... big thanks to Walter for all his help on the
> multiple
> > list box issue I posted last week.
> >
> > Damian
> >
> >
>
>
|
|
|
|
Walter Kempees
|
| Posted: 07/22/2004, 6:58 AM |
|
good for you, but then........ CCS right ?!
All I said was that in the table definitions you send me
in members table field is a char(5)
you also have a table containing 'male'and 'female', although I think
this table is overkill, if you use it as
a lookup for member. then the id in (int(2)) should also be int(2) in
member..
Now you are doing a (socalled) lookup by value using .id on member.
both of different type.
This can (and will) result in unpredictable results.
Better allover is to make the member. a field containing 'Male','Female',
Unkwn' thgen it can stay char(5).
build a listbox in the form containing the three possible values and
substitute those.
Even better is an enum type or set, but that will take some MySql study.
GoodyBye
"Damian" <codecharge@sydneyit.no.com.junk> schreef in bericht
news:cdodv9$6q2$1@news.codecharge.com...
> Hey it works!
>
> Although... in the Visual Query Builder I had to do RIGHT joins to get the
> SQL statements to say LEFT join!
>
> I also had to change the DATA TYPE to Text (Doh!)...
>
> Im still not too sure about that variable character!
>
> Damian
>
> "Walter Kempees" <kempe819@planet.nl> wrote in message
>news:cdntpr$114$1@news.codecharge.com...
> > So here we are again then.
> > I think there are two solutions and I leave it tho others to comment on
> the
> > best method.
> >
> > Method 1 is 'joining' the tables in the Grid's select.
> > Method 2 is in the grid display the Text data, 'looking up the Text by
its
> > Integere value.
> > Both methods work.
> > Performance and designers effort might make the difference.
> >
> > Method 1 SQL snippet
> >
> > select mbr.firstname,
> > mbr.middlename,
> > mbr.familyname,
> > .,
> > r1.region1,
> > r2.region2,
> > r3.region3,
> > r4.region4
> > from members as mbr
> > left join as on .id = mbr.
> > left join region1 as r1 on r1.id = mbr.region1
> > left join region2 as r2 on r2.id = mbr.region2
> > left join region3 as r3 on r3.id = mbr.region3
> > left join region4 as r4 on r4.id = mbr.region4
> >
> > This you can do by hand (in a separate query tool) or do it directly in
> the
> > Grids Query builder.
> > (Private note: your members. is a varchar(2) while the .id is an
> > int(5), don't really like to talk about your memebers.)
> > can be a enum field or a set, but that's another.........
> > Method 1 gives you the fields in the grid, as the VQB (visual Query
> Builder)
> > gives you the ability to check fields you want to use.
> > This is an easy way.
> > Your example is adapted at http://213.10.55.10/damian zip file also,
look
> at
> > the List of D_Members.
> >
> >
> > Method 2
> > Is doing it, my opinion, the hard way adding labels or fields to the
grid
> > and lookups to the grid.
> >
> > Just as a note to all those that are going to shoot at all the joining
> going
> > on.
> > If you have to choose between doing work and letting the database do the
> > work for you a I lways choose the latter (99%).
> >
> > Walter
> >
> >
> >
> >
> > "Damian" <codecharge@sydneyit.no.com.junk> schreef in bericht
> >news:cdnqh1$5gv$1@news.codecharge.com...
> > > Howdy All,
> > >
> > > I have a Registration Form which requires users to enter a whole swag
of
> > > details about themselves.
> > >
> > > A number of the steps you are required to choose from a list box (or
> radio
> > > button) such as:
> > >
> > > (Male, Female)
> > > Country (Table....)
> > > State (Table...)
> > >
> > > To use the db more efficiently, less space (and maybe less memory?) I
> have
> > > stored the ID values instead of teh text values, eg, Male=1, Female=2,
> > > Australia=100, India=101, USA=102,
> > >
> > > If I want to display the Text Values instead of the stored Numeric IDs
> > what
> > > sort of Join Types do I use and between what values?
> > >
> > > eg.
> > > table1=members(id, first,last,login,pass,,region1,region2)
> > > table2=(id,)
> > > table3=region1(id,region1)
> > > table4=region2(id,region1id, region2)
> > >
> > > Where
> > > members.=.id
> > > members.region1 = region1.id
> > > members.region2=region2.id
> > > region2.region1id=region1.id
> > >
> > > So in my GRID table, is it possible to display the text Values, eg
> > > region1.region1 and region2.region2 instead of the ID values that are
> > stored
> > > in the members table?
> > >
> > > OR....
> > >
> > > Should I just store the text values in members (I know this works...
but
> > my
> > > DB may grow very large....).
> > >
> > >
> > > And while Im at it... big thanks to Walter for all his help on the
> > multiple
> > > list box issue I posted last week.
> > >
> > > Damian
> > >
> > >
> >
> >
>
>
|
|
|
|
|
