Can I do something different - Displaying records

[fruitwerks]

lots of info I'm not going to share, but I am working on updating my reptile records website.

here is the db structure so far.

--
-- Table structure for table `animals`
--

CREATE TABLE IF NOT EXISTS `animals` (
`idx` int(10) unsigned NOT NULL auto_increment,
`animal_id` mediumint(8) unsigned NOT NULL,
`bday` date NOT NULL,
`pdate` date default NULL,
`sdate` date default NULL,
`pprice` int(10) unsigned default NULL,
`sprice` int(10) unsigned default NULL,
`mom` smallint(6) default NULL,
`dad` smallint(6) default NULL,
`bin` varchar(10) NOT NULL,
`genetic` varchar(45) default NULL,
`aname` varchar(45) NOT NULL,
`foil` varchar(12) default NULL,
`feedfreq` tinyint(2) unsigned NOT NULL,
`species` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `animals`
--

INSERT INTO `animals` (`idx`, `animal_id`, `bday`, `pdate`, `sdate`, `pprice`, `sprice`, `mom`, `dad`, `bin`, `genetic`, `aname`, `foil`, `feedfreq`, `species`) VALUES
(2, 33, '2008-07-16', '2008-10-22', NULL, 500, NULL, NULL, NULL, 'm6104', 'kahl albino', 'frost', NULL, 7, 1),
(3, 28, '2007-07-16', NULL, NULL, 800, NULL, NULL, NULL, 'ap401', 'hypo het snow', 'lola', NULL, 14, 1);

-- --------------------------------------------------------

--
-- Table structure for table `events_ref`
--

CREATE TABLE IF NOT EXISTS `events_ref` (
`event_id` int(10) unsigned NOT NULL auto_increment,
`events_idx` smallint(5) unsigned NOT NULL,
`ev_name` varchar(45) NOT NULL,
PRIMARY KEY (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `events_ref`
--

INSERT INTO `events_ref` (`event_id`, `events_idx`, `ev_name`) VALUES
(1, 1, 'feeding'),
(2, 2, 'poo'),
(3, 3, 'pee'),
(4, 4, 'shed'),
(5, 5, 'weight'),
(6, 6, 'length'),
(7, 7, 'vet'),
(8, 8, 'notes');

-- --------------------------------------------------------

--
-- Table structure for table `feedings`
--

CREATE TABLE IF NOT EXISTS `feedings` (
`feedings_id` int(11) NOT NULL auto_increment,
`animal_id` smallint(6) NOT NULL,
`perm_id` smallint(6) NOT NULL,
`feed_date` date NOT NULL,
PRIMARY KEY (`feedings_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `feedings`
--

INSERT INTO `feedings` (`feedings_id`, `animal_id`, `perm_id`, `feed_date`) VALUES
(1, 28, 18, '2009-08-03'),
(2, 33, 9, '2009-07-06');

-- --------------------------------------------------------

--
-- Table structure for table `foodinv`
--

CREATE TABLE IF NOT EXISTS `foodinv` (
`foodinv_id` int(11) NOT NULL auto_increment,
`type` varchar(45) NOT NULL,
`qty` smallint(6) NOT NULL,
`weight` smallint(5) unsigned NOT NULL,
`perm_id` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`foodinv_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

--
-- Dumping data for table `foodinv`
--

INSERT INTO `foodinv` (`foodinv_id`, `type`, `qty`, `weight`, `perm_id`) VALUES
(18, 'bunny 1-1.25lbs', 3, 510, 16),
(19, 'med rat', 9, 130, 9),
(20, 'rat pinky', 8, 35, 15);

-- --------------------------------------------------------

--
-- Table structure for table `master_ev`
--

CREATE TABLE IF NOT EXISTS `master_ev` (
`ev_id` int(11) NOT NULL auto_increment,
`animal_id` smallint(5) unsigned NOT NULL,
`events_idx` tinyint(3) unsigned NOT NULL,
`ev_date` date NOT NULL,
PRIMARY KEY (`ev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `master_ev`
--

INSERT INTO `master_ev` (`ev_id`, `animal_id`, `events_idx`, `ev_date`) VALUES
(2, 28, 1, '2009-08-03'),
(3, 33, 3, '2009-07-06');

-- --------------------------------------------------------

--
-- Table structure for table `notes`
--

CREATE TABLE IF NOT EXISTS `notes` (
`notes_id` int(11) NOT NULL auto_increment,
`animal_id` mediumint(8) unsigned NOT NULL,
`note` text NOT NULL,
`note_date` date NOT NULL,
PRIMARY KEY (`notes_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `notes`
--


-- --------------------------------------------------------

--
-- Table structure for table `species`
--

CREATE TABLE IF NOT EXISTS `species` (
`species_id` int(11) NOT NULL auto_increment,
`species_idx` tinyint(4) NOT NULL,
`species` varchar(45) NOT NULL,
`subspecies` varchar(45) NOT NULL,
PRIMARY KEY (`species_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `species`
--

INSERT INTO `species` (`species_id`, `species_idx`, `species`, `subspecies`) VALUES
(1, 1, 'Boa Constrictor', '');


At the moment, the data in there is enough to poke around, I'm working on the rendering of the info before I start on 'client side' type input and fancy stuff.

Here is the output of the events record, this is where I am a bit hung up. Not sure if it is my code or the db design.

event food item aname date
pee 9 frost 2009-07-06
feeding 18 lola 2009-08-03

now here is the biggest problem I am having and I am not sure what way to approach. Even though the event type is not 1 (feeding) it shows a feeding record (code for the item anyway, not that far along yet).

here is the code for that page

<?php
include "dbcore.php";

// select food db order by weight

$query = "SELECT * FROM master_ev left join events_ref on master_ev.events_idx = events_ref.event_id
left join animals on master_ev.animal_id = animals.animal_id
left join feedings on master_ev.animal_id = feedings.animal_id oder by ev_date";

// error check

$result = mysql_query($query) or die(mysql_error());

// construct data

echo "<table border='1'>";
echo "<tr> <th>event</th> <th>food item</th> <th>aname</th> <th>date</th> </tr>";

// keeps getting the next row until there are no more to get

while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['ev_name'];
echo "</td><td>";
echo $row['perm_id'];
echo "</td><td>";
echo $row['aname'];
echo "</td><td>";
echo $row['ev_date'];
echo "</td></tr>";
}

echo "</table>";
?>

If there is a feeding record it will show and override the other event. I need to keep each event separate. I will eventually filter these results by animal, but not right now.

The feeding and notes event types are special because they require their own tables. Is there a better way I can structure my table to make this a bit better or does it need to be done with the coding? If it is an issue with the db structure please be specific.

Thanks!

[Fumigator]

That's what happens when you use LEFT JOIN... you're going to get every row in the first table back that meets your WHERE conditions, regardless of whether a match was found in the joined table(s). If you want to limit results to those rows that match the joined tables, you need to use an INNER join.

[fruitwerks]

would it be easier to use some JSON to filter the results. I think I will eventually be using some extjs for some of the results. But this is all new to me! I wanted to get a better grip on things and build the code from the basics.

[Old Pedant]

??? JSON is just a way to *describe* data.

Yes, you could then use JS code to filter the data sent to the browser via JSON.

But most of the time you'll be better off doing the filtering in your SQL queries.

You would only do browser-based filtering if you want to change *what* was displayed based on client choices without going back to the web server.

[fruitwerks]

Yes, I realized that after I posted. I was reaching out for solutions. I think the issue might lie in the db design. I have been playing around a bit and got to here.

I have tried everything I can think of to get the food item name. I either end up with 16 or 24 records or just the entries with feedings.

<?php
include "dbcore.php";

// select food db order by weight -- need another to figure out the food? hmm

$query = "SELECT
events_ref.ev_name,
events_ref.events_idx,
master_ev.ev_id,
master_ev.animal_id,
master_ev.events_idx,
master_ev.ev_date,
animals.animal_id,
animals.aname,
animals.species,
species.species,
species.species_idx
FROM
master_ev
INNER JOIN animals ON (master_ev.animal_id=animals.animal_id)
INNER JOIN species ON (animals.species=species.species_idx)
INNER JOIN events_ref ON (master_ev.events_idx=events_ref.events_idx)";

// error check

$result = mysql_query($query) or die(mysql_error());

// construct data

echo "<table border='1'>";
echo "<tr> <th>event</th> <th>aname</th> <th>date</th> </tr>";

// keeps getting the next row until there are no more to get

while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
// echo $row['events_idx'];
if ( $row['events_idx'] == 1 ) {
echo $row['type'];
} elseif ( $row['events_idx'] == 8 ) {
echo "NOTES HERE";
}
else {
echo $row['ev_name'];
}

echo "</td><td>";
echo $row['aname'];
echo "</td><td>";
echo $row['ev_date'];
echo "</td></tr>";
}

echo "</table>";
?>

[Old Pedant]

Your comment there// select food db order by weight -- need another to figure out the food? hmm
just doesn't match the query you show, at all.

There's no "order by" in there, at all. In fact, there's no link to the food_inv table, at all.

I have to ask: Why do you have *BOTH* a "foodinv_id" *AND* a "perm_id"???? And why do you specify the value of foodinv_id when you build the contents of that table, instead of just using the auto_increment value?

Same with events_id and events_idx. Seems pointless to duplicate those fields like that.

[fruitwerks]

Sorry bad commenting, I use similar code to display a more simple table, and I just copied and pasted.

perm_id is required so when we modify the foodstock, each item will have a static id, if the id changes, our feeding records would be useless. events_idx is for the event type. I have another table events_ref, so I know that event type 1 is a feed and event type 8 is a vet visit. I can get that working ok, but for events 1 and 2 I need to ref another table. I had one of these working in my original post, but if I had an event of a different type on the same day, the records would combine. This is a problem because we may enter about three events in one day sometimes.

I have looked at many relational db samples but most of them are simple relations like I already have. I am starting to think I'm doing it wrong.

[Old Pedant]

I doubt you are doing it wrong, in any absolute sense.

Clearly foodinv_id and event_id are just wasted fields. No real reason for them being there. But they don't hurt anything, so leave them for now.

Okay, given the sample data you showed in your first post, what is the FINAL RESULT that you want to see? That is what is the actual rows of data you want to see, when all is said and done?

[fruitwerks]

well, right now I can see everything I need except what they ate and the notes, if I can figure out the what they ate part, I can figure out the notes...

This query is the PIA one, I can get all theother queries woreking just fine, but what I need from this one is...

animal, action (event) - if notes or food, display notes or food item, date

lola sm bunny 07/28/2009
frost shed 08/04/2009
frost poo 08/04/2009
smiley note (blah...) 08/04/2009

What I was thinking was read the lookup tables, event_ref and foodinv and storing those in arrays and reference them somehow? that would unclutter the query.. *shrug*

[Old Pedant]

So if the ev_name is *NOT* either "feeding" or "note" then you just want to show ev_name in that second column, right?

And what happens if the DB is inconsistent? If the ev_name is "note" but there's no record in the notes table that matches the given animal and date??? I'm going to ignore that problem and hope your data is consistent.

Okay, I *think* I have it:

SELECT A.aname,
IF( E.ev_name='feeding', FD.type, IF( E.ev_name='note', N.note, E.ev_name ) ) AS eventOrFoodOrNote,
M.ev_date
FROM animals AS A
INNER JOIN master_ev AS M ON ( M.animal_id = A.animal_id )
INNER JOIN events_ref AS E ON ( E.events_idx = M.events_idx )
LEFT JOIN notes AS N ON ( N.animal_id = A.animal_id AND N.note_date = M.ev_date )
LEFT JOIN feedings AS F ON ( F.animal_id = A.animal_id AND F.feed_date = M.ev_date )
LEFT JOIN foodinv AS FD ON ( FD.perm_id = F.perm_id )

Obviously completely untested.

[fruitwerks]

Thanks for your time!

I had to modify the code a bit to work and I get null values for notes and feeding entries.

SELECT A.aname,
IF( E.ev_name='feeding', FD.food_type, IF( E.ev_name='notes', N.note, E.ev_name ) ) AS eventOrFoodOrNote,
M.ev_date
FROM animals AS A
INNER JOIN master_ev AS M ON ( M.animal_id = A.animal_id )
INNER JOIN events_ref AS E ON ( E.event_id = M.events_idx )
LEFT JOIN notes AS N ON ( N.events_idx = M.events_idx )
LEFT JOIN feedings AS F ON ( F.ev_id = M.events_idx )
LEFT JOIN foodinv AS FD ON ( FD.perm_id = F.perm_id )

I also made a few changes to the database, removed some stuff that would not be needed, that is why the above code looks a little thin.

--
-- Table structure for table `animals`
--

CREATE TABLE IF NOT EXISTS `animals` (
`idx` int(10) unsigned NOT NULL auto_increment,
`animal_id` mediumint(8) unsigned NOT NULL,
`bday` date NOT NULL,
`pdate` date default NULL,
`sdate` date default NULL,
`pprice` int(10) unsigned default NULL,
`sprice` int(10) unsigned default NULL,
`mom` smallint(6) default NULL,
`dad` smallint(6) default NULL,
`bin` varchar(10) NOT NULL,
`genetic` varchar(45) default NULL,
`aname` varchar(45) NOT NULL,
`foil` varchar(12) default NULL,
`feedfreq` tinyint(2) unsigned NOT NULL,
`species` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `animals`
--

INSERT INTO `animals` (`idx`, `animal_id`, `bday`, `pdate`, `sdate`, `pprice`, `sprice`, `mom`, `dad`, `bin`, `genetic`, `aname`, `foil`, `feedfreq`, `species`) VALUES
(2, 33, '2008-07-16', '2008-10-22', NULL, 500, NULL, NULL, NULL, 'm6104', 'kahl albino', 'frost', NULL, 7, 1),
(3, 28, '2007-07-16', NULL, NULL, 800, NULL, NULL, NULL, 'ap401', 'hypo het snow', 'lola', NULL, 14, 1);

-- --------------------------------------------------------

--
-- Table structure for table `events_ref`
--

CREATE TABLE IF NOT EXISTS `events_ref` (
`event_id` int(10) unsigned NOT NULL auto_increment,
`ev_name` varchar(45) NOT NULL,
PRIMARY KEY (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `events_ref`
--

INSERT INTO `events_ref` (`event_id`, `ev_name`) VALUES
(1, 'feeding'),
(2, 'poo'),
(3, 'pee'),
(4, 'shed'),
(5, 'weight'),
(6, 'length'),
(7, 'vet'),
(8, 'notes');

-- --------------------------------------------------------

--
-- Table structure for table `feedings`
--

CREATE TABLE IF NOT EXISTS `feedings` (
`feedings_id` int(11) NOT NULL auto_increment,
`ev_id` int(10) unsigned default NULL,
`animal_id` smallint(6) NOT NULL,
`perm_id` smallint(6) NOT NULL,
PRIMARY KEY (`feedings_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `feedings`
--

INSERT INTO `feedings` (`feedings_id`, `ev_id`, `animal_id`, `perm_id`) VALUES
(1, 5, 28, 18),
(3, 2, 28, 9);

-- --------------------------------------------------------

--
-- Table structure for table `foodinv`
--

CREATE TABLE IF NOT EXISTS `foodinv` (
`foodinv_id` int(11) NOT NULL auto_increment,
`food_type` varchar(45) NOT NULL,
`qty` smallint(6) NOT NULL,
`weight` smallint(5) unsigned NOT NULL,
`perm_id` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`foodinv_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

--
-- Dumping data for table `foodinv`
--

INSERT INTO `foodinv` (`foodinv_id`, `food_type`, `qty`, `weight`, `perm_id`) VALUES
(18, 'bunny 1-1.25lbs', 3, 510, 16),
(19, 'med rat', 9, 130, 9),
(20, 'rat pinky', 8, 35, 15);

-- --------------------------------------------------------

--
-- Table structure for table `master_ev`
--

CREATE TABLE IF NOT EXISTS `master_ev` (
`ev_id` int(11) NOT NULL auto_increment,
`animal_id` smallint(5) unsigned NOT NULL,
`events_idx` tinyint(3) unsigned NOT NULL,
`ev_date` date NOT NULL,
PRIMARY KEY (`ev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `master_ev`
--

INSERT INTO `master_ev` (`ev_id`, `animal_id`, `events_idx`, `ev_date`) VALUES
(2, 28, 1, '2009-08-03'),
(3, 33, 3, '2009-07-06'),
(4, 33, 8, '2009-06-16'),
(5, 28, 1, '2009-07-27'),
(6, 33, 4, '2009-08-05');

-- --------------------------------------------------------

--
-- Table structure for table `notes`
--

CREATE TABLE IF NOT EXISTS `notes` (
`notes_id` int(11) NOT NULL auto_increment,
`events_idx` mediumint(8) unsigned NOT NULL,
`note` text NOT NULL,
PRIMARY KEY (`notes_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `notes`
--

INSERT INTO `notes` (`notes_id`, `events_idx`, `note`) VALUES
(1, 33, 'this is a note');

-- --------------------------------------------------------

--
-- Table structure for table `species`
--

CREATE TABLE IF NOT EXISTS `species` (
`species_id` int(11) NOT NULL auto_increment,
`species_idx` tinyint(4) NOT NULL,
`species` varchar(45) NOT NULL,
`subspecies` varchar(45) NOT NULL,
PRIMARY KEY (`species_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `species`
--

INSERT INTO `species` (`species_id`, `species_idx`, `species`, `subspecies`) VALUES
(1, 1, 'Boa Constrictor', '');


I think my db changes will help streamline things.

[fruitwerks]

ok very close!

$query = "SELECT
events_ref.ev_name,
master_ev.ev_date,
animals.aname,
notes.note,
species.species,
IF( events_ref.ev_name='feeding', foodinv.food_type, IF( events_ref.ev_name='notes', notes.note, events_ref.ev_name ) ) AS eventOrFoodOrNote
FROM
master_ev
INNER JOIN animals ON (master_ev.animal_id=animals.animal_id)
INNER JOIN species ON (animals.species=species.species_idx)
INNER JOIN events_ref ON (master_ev.events_idx=events_ref.event_id),
feedings
INNER JOIN foodinv ON (feedings.perm_id=foodinv.perm_id),
notes";

Problems, I need to pull in the foodinv.food_type and notes.note that matches events_idx.

[Old Pedant]

Yes, that helps. Joining on the events_idx. Much better than the double join on animal_id and event_date.

But you wrote
I had to modify the code a bit to work and I get null values for notes and feeding entries.

??? You *still* get null values for those???

Then I think you have a data consistency problem.

[fruitwerks]

Think I posted the wrong code.. this is what I am down to now...

$query = "SELECT
events_ref.ev_name,
master_ev.ev_date,
animals.aname,
notes.note,
species.species,
IF( events_ref.ev_name='feeding', foodinv.food_type, IF( events_ref.ev_name='notes', notes.note , events_ref.ev_name ) ) AS eventOrFoodOrNote
FROM
master_ev
INNER JOIN animals ON (master_ev.animal_id=animals.animal_id)
INNER JOIN species ON (animals.species=species.species_idx)
INNER JOIN events_ref ON (master_ev.events_idx=events_ref.event_id),
feedings
INNER JOIN foodinv ON (feedings.perm_id=foodinv.perm_id),
notes";

The food and notes result throw me the first entry in the tables.

event aname species date
med rat lola Boa Constrictor 2009-08-03
pee frost Boa Constrictor 2009-07-06
this is a note frost Boa Constrictor 2009-06-16
med rat lola Boa Constrictor 2009-07-27
shed frost Boa Constrictor 2009-08-05
shed yibbles Ball Python 2009-08-04

well, medium rat is not the first entry, but has the lowest perm_id, med rat is the second entry in the table. One of those feedings is set to a bunny...

It's getting late. Gonna sleep on it. Thanks for all the help so far!

[Old Pedant]

That's why I joined on the dates, as well as the IDs. To make sure I got the note/foodinventory for the same date.

If your date fields are *actually* date/time fields then the problem might be that the times don't match. There are fixes for that.

[fruitwerks]

This has been quite a ride! I can get the proper records without joining the foodinv or note tables. I have changed the schema so that ev_id (from the master event table) matches the food or note entries. Dates and animal_id on those two tables are pointless.

I have tried many many things. I noticed a few glitches while testing the various routes. I am going to try adding a second inline query using some if-else stuff in php, probably not as elegant as it should be, but it should work.

Going to tackle that right now.

Thanks again!

[fruitwerks]

And the winner is...

SELECT
master_ev.ev_date,
animals.aname,
species.species,
IF(events_ref.ev_name = 'feeding', foodinv.food_type, IF(events_ref.ev_name = 'notes', notes.note, events_ref.ev_name)) AS event_fixed
FROM
master_ev
INNER JOIN animals ON (master_ev.animal_id=animals.animal_id)
INNER JOIN species ON (animals.species=species.species_idx)
INNER JOIN events_ref ON (master_ev.events_idx=events_ref.event_id)
LEFT OUTER JOIN notes ON (master_ev.ev_id=notes.ev_id)
LEFT OUTER JOIN feedings ON (master_ev.ev_id=feedings.ev_id)
LEFT OUTER JOIN foodinv ON (feedings.perm_id=foodinv.perm_id)

now I hope that my schema is proper and will actually work out right!

[fruitwerks]

Well.. this was great until I realized I had been ignoring the weight and length! exact same scenario as notes. But to my disappointment, the IF statement is only good for two outcomes...

*stumped*

[fruitwerks]

until I learned some php :)

<?php

include "dbcore.php";

$query = "SELECT
master_ev.ev_date,
animals.aname,
species.species,
species.subspecies,
events_ref.ev_name,
master_ev.events_idx,
notes.note,
weight.weight,
foodinv.food_type
FROM
master_ev
INNER JOIN animals ON (master_ev.animal_id=animals.animal_id)
INNER JOIN species ON (animals.species=species.species_idx)
INNER JOIN events_ref ON (master_ev.events_idx=events_ref.event_id)
LEFT OUTER JOIN notes ON (master_ev.ev_id=notes.ev_id)
LEFT OUTER JOIN weight ON (master_ev.ev_id=weight.ev_id)
LEFT OUTER JOIN length ON (master_ev.ev_id=length.ev_id)
LEFT OUTER JOIN feedings ON (master_ev.ev_id=feedings.ev_id)
LEFT OUTER JOIN foodinv ON (feedings.perm_id=foodinv.perm_id)";

$result = mysql_query($query) or die(mysql_error());

echo "<table border='1'>";
echo "<tr> <th>date</th> <th>animal</th> <th>species</th> <th>event</th> </tr>";

while($row = mysql_fetch_array( $result )) {
echo "<tr><td>";
echo $row['ev_date'];
echo "</td><td>";
echo $row['aname'];
echo "</td><td>";
echo $row['species'];
echo "</td><td>";
switch ( $row['events_idx'] ) {
case 8:
echo $row['note'];
break;
case 5:
echo "weight ",$row['weight'],"oz";
break;
case 6:
echo "length ",$row['length'],"in";
break;
case 1:
echo $row['food_type'];
break;
default:
echo $row['ev_name'];
}
echo "</td></tr>";
}

echo "</table>";
?>