Hank,

Thanks again.  I think the select_distinct is exactly what I need.  I think
that the caching and building of tables to hold drill down data may be going
a little past my abilities.  My main consideration here is that I want to
help keep down the number of clicks for the user, but also need to only
present data that corresponds to other data.

For instance, certain models of Buicks may only come in certain years.  I
don't want to allow the user to select, on the first screen, 1980 Buick
Skylark if Buick Skylarks weren't made in 1980.  My initial thoughts are to
lead the user through a series of drill down links, consolidating choices
when possible, but ensuring that a user cannot make a choice of combinations
that do not exist.  Of course, in the interest of simplicity, I suppose I
could simply return an error message stating that "Your combination of
year/make/model, and option does not appear to be valid.  Please recheck
your information and resubmit".  This has the potential of substantially
reducing the drilling process by allowing me to create dropdown boxes on one
screen and results on the next.

Any ideas regarding that?

Thanks,
Keith D Sellars
WebGraffix
www.webgraffix.com

"Making database sites seem easy"

----- Original Message -----
From: "Hank Marquardt" <hmarq(at)yerpso.net>
To: "Keith Sellars" <Keith(at)webgraffix.com>
Cc: <hwg-languages(at)hwg.org>
Sent: Monday, September 23, 2002 4:11 PM
Subject: Re: mysql field types


> 'select distinct' is your friend:
>
> http://www.mysql.com/doc/en/Selecting_columns.html
>
> Of course to reduce load I'd probably setup some caching on the queries
> that build your SELECT boxes so that you don't run the same query over
> and over on the database when the underlying data doesn't change. ... or
> you could even build some tables to hold the drill down data and rebuild
> them from the whole dataset when it changes.
>
> On Mon, Sep 23, 2002 at 03:47:42PM -0400, Keith Sellars wrote:
> > Hank,
> >
> > Thanks, that was precisely the information I was hoping to get.  I've
been
> > looking at the MySQL online manual and it lists everything but does go
into
> > specifics on the actual implementation of some things.  It "may" do so,
but
> > is not readily apparent to me if it does.
> >
> > One more question, for you or anyone.  I have inserted over 17,000 rows
into
> > the table I just created.  Each row has 8 columns (the table list all
makes,
> > models, and types of cars since 1980 and tells what their tire size and
> > front and rear recommended tire pressure is).
> >
> > I can construct basic queries and results,etc. but how do I do this -
How
> > do I build an array (I assume this is what I need) or a query that will
look
> > for, and grab, only one occurence of an item if it occurs multiple
times?
> > For instance, I want to list all of the available makes of cars.  I may
have
> > 300 Buicks (different models, but not a concern at this point) and 240
> > Hondas.  I want to be able to allow the user to select from, in this
case, a
> > Honda or a Buick. My goal here is to create a series of screens that
will
> > allow the user to drill down to their car type without actually typing
in
> > information (since some of the options for these vehicles are multi-word
and
> > I want to have tight control over the returned items).
> >
> > Eventually the user will be shown a screen that will echo the values of
the
> > following fields for one specific car (one row of the database table):
> >
> > year, make, model, option, tire_pressure, front_pz, rear_pz (the actual
> > names of the columns)
> >
> > Thanks,
> > Keith D Sellars
> > WebGraffix
> > www.webgraffix.com
> >
> > "Making database sites seem easy"
> >
> > ----- Original Message -----
> > From: "Hank Marquardt" <hmarq(at)yerpso.net>
> > To: "Keith Sellars" <Keith(at)webgraffix.com>
> > Cc: <hwg-languages(at)hwg.org>
> > Sent: Monday, September 23, 2002 3:34 PM
> > Subject: Re: mysql field types
> >
> >
> > > On Mon, Sep 23, 2002 at 03:05:29PM -0400, Keith Sellars wrote:
> > > > I'm teaching myself more and more about php and mysql as time
permits.
> > I've
> > > > in the process of setting up a new table on an existing mysql
database.
> > I
> > > > have a few questions:
> > > >
> > > > When should I use "varChar" instead of, say, "text" or "blob"?
> > >
> > > Space, indexing and storage effiency are the things you're balancing
> > > here -- not to mention, 'the right tool for the job'
> > >
> > > char v. varchar -- if you have a known length text string, use char (2
> > > char state codes, 32 byte digital signatures).  I you have varying
> > > character length to some determined maximum which is shorter than the
> > > type can hold varchar is your friend.  Names, addresses, short text
> > > fields ...
> > >
> > > text is an unlimited version of varchar from the standpoint of
> > > implementation -- the difference is that the entry itself is stored
> > > separately from the record and different indexing routines must be
used.
> > > It is space efficient, but query inefficient.
> > >
> > > blob = "binary large object" -- most commonly used for storing
pictures
> > > in practice, though any binary data is appropriate.  blobs are evil
(my
> > > thought) ... they are implentation specific and require special
> > > retrieval/display code; conversions between db engines are messy ...
> > > they have their place; but you need to know when and when not to use
> > > them.
> > >
> > > >
> > > > When should I use "int" instead of "tinyInt" or vice versa?
> > > >
> > >
> > > Just use int and be happy:) ... generally the only time you'll violate
> > > this is if you *know* you need to and then it'll usually be on the
high
> > > end that you violate (where you need a value greater than a normal
> > > integer can hold)
> > >
> > > > What are the advantages in the cases above of one or the other, or
> > > > advantages for other similar field types?
> > > >
> > >
> > > Speed, storage efficiency and indexing are the things you're balancing
> > > ... for small datasets, you can usually ignore these considerations
and
> > > just use what's comfortable.  As dataset size grows you need to worry
> > > more particularly about indexing
> > >
> > > > When should I make a field "not null" or "null"?  How important is
this
> > to
> > > > be set either way?
> > > >
> > >
> > > Data integrity -- the more general issue is how to use constraints
> > > within the database -- mysql is pretty weak in this area; go look at
the
> > > manual for postgresql and you'll see what can be done in this area.
In
> > > specific null vs. not null is saying "Must this field have data?" ...
if
> > > the answer is yes, then you'll "not null" is the answer --*AND*-- if
you
> > > try to insert a record with that field unset, it will trigger a
database
> > > error and the insert will fail.
> > >
> > > > Thanks,
> > > > Keith D Sellars
> > > > WebGraffix
> > > > www.webgraffix.com
> > > >
> > > > "Making database sites seem easy"
> > > >
> > >
> > > --
> > > Hank Marquardt <hank(at)yerpso.net>
> > > http://web.yerpso.net
> > > GPG Id: 2BB5E60C
> > > Fingerprint: D807 61BC FD18 370A AC1D  3EDF 2BF9 8A2D 2BB5 E60C
> > > *** Web Development: PHP, MySQL/PgSQL - Network Admin: Debian/FreeBSD
> > > *** PHP Instructor - Intnl. Webmasters Assn./HTML Writers Guild
> > > *** Beginning PHP && PHP II -- Starting March 25, 2002
> > > *** See /services/classes
> > >
> >
>
> --
> Hank Marquardt <hank(at)yerpso.net>
> http://web.yerpso.net
> GPG Id: 2BB5E60C
> Fingerprint: D807 61BC FD18 370A AC1D  3EDF 2BF9 8A2D 2BB5 E60C
> *** Web Development: PHP, MySQL/PgSQL - Network Admin: Debian/FreeBSD
> *** PHP Instructor - Intnl. Webmasters Assn./HTML Writers Guild
> *** Beginning PHP && PHP II -- Starting March 25, 2002
> *** See /services/classes
>

HWG: hwg-languages mailing list archives, maintained by Webmasters @ IWA