 | | Re: Select/Update statements with multiple references and/or a
procedure | Re: Select/Update statements with multiple references and/or a
procedure 2005-02-11 - By Jan Theodore Galkowski
Back
Hey Graham,
I've done a fair bit of medical work, both for clients and for my wife
who needs to manage an inventory.
What's exactly the problem with using Excel?
On Fri, 11 Feb 2005 22:31:38 +0000, "Graham Reeds"
<grahamr@(protected)> said:
> I am writing a simple webapp for my house mate who is diabetic.
> Currently she is using a Excel spreadsheet for it but it is problematic.
> I said I could write something better so here I am.
>
> The app only uses two tables which represent a single day's glucose
> levels and insuling taken:
>
> CREATE TABLE readings
> (
> `ID` int(10) unsigned NOT NULL auto_increment,
> `Glucose` float NOT NULL default '0',
> `Insulin` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (ID)
> )
>
What's the *OBJECT* here that's being tracked with the ID? Is it a
day's treatment? A reading? Why are they keyed? Simply to be able to
associate them with the "records" table?
> and
>
> CREATE TABLE `records`
> (
> `DateOfReadings` date NOT NULL default '0000-00-00',
> `BB_id` int(11) unsigned NOT NULL default '0',
> `BL_id` int(11) unsigned NOT NULL default '0',
> `BT_id` int(11) unsigned NOT NULL default '0',
> `BBd_id` int(11) unsigned NOT NULL default '0',
> PRIMARY KEY (`DateOfReadings`)
> )
>
> The query will select the records based on the date and then get the
> relevant glucose/insulin readings from the relevant referenced ID's.
> Things are further complicated that they may be some, none or all
> readings have been filled in (usually though they will be entered as a
> single block).
Why not have just a readings table and characterize a kind of reading as
BB, BL, BT, and BBd?
Can date or time stamp each of these. That is,
CREATE TABLE readings (
DateOfReading TIMESTAMP NOT NULL DEFAULT,
TypeOfReading ENUM( 'no statement', 'BB', 'BL', 'BT', 'BBd' ) NOT
NULL,
Glucose FLOAT NOT NULL,
Insulin INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (DateOfReading, TypeOfReading)
)
>
>
> I was thinking of a select procedure that will first see if there is a
> record for the day. If not then it will create the initial records with
> the default values and return those. However does MySQL have a PL/SQL
> style language?
Usually this is done outside of MySQL with something like PHP, but quite
a lot can be done with SQL itself.
Almost anything can be done once stored procedures show up, but that's
not yet.
>
> Currently I have:
>
> SELECT glucose, insulin
> FROM readings, records
> WHERE records.DateOfReadings='2005-02-10'
> AND readings.ID=records.BB_id;
>
> Which works for a single reading but I am having difficulty extending it
> to an entire day.
>
> It could be performed via 4 SELECT and 4 UPDATE statements but I would
> rather do this properly and fire off a single select statement and
> single update statement.
[snip]
--
Jan Theodore Galkowski (o?)
jtgalkowski@(protected)
http://www.smalltalkidiom.net
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=mysql@(protected)
Earn $52 per hosting referral at Lunarpages.
|
|
|