 | | | Select/Update statements with multiple references and/or a procedure | Select/Update statements with multiple references and/or a procedure 2005-02-11 - By Graham Reeds
Back
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)
)
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).
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?
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.
Thanks, Graham Reeds.
--
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.
|
|
|