Hello,

I have three tables:

CREATE TABLE event (
  eid int(10) unsigned NOT NULL auto_increment,
  tid smallint(5) unsigned NOT NULL default '0',
  itimestamp int(10) unsigned NOT NULL default '0',
  etimestamp int(10) unsigned NOT NULL default '0',
  somethingelse tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (eid)
);

CREATE TABLE line (
  eid int(10) unsigned NOT NULL default '0',
  position tinyint(3) unsigned NOT NULL auto_increment,
  did int(11) NOT NULL default '0',
  PRIMARY KEY  (eid,position)
);

CREATE TABLE data (
  did int(10) unsigned NOT NULL auto_increment,
  data varchar(255) binary NOT NULL default '0',
  PRIMARY KEY  (did),
  UNIQUE KEY data (data)
);



Basically I insert data into event and record the resulting eid from the
auto increment.  Then I insert my raw data into the data table,
recording each did from the auto increment.  Lastly, I insert multiple
records into the line table, one for each of my raw data elements.  I
then am able to do:

SELECT
event.eid,event.tid,event.itimestamp,event.etimestamp,data.did,data.data

FROM event LEFT JOIN line ON event.eid=line.eid LEFT JOIN data ON
line.did=data.did
WHERE event.eid='45'

to get a single event.  This all works fine, however this is the result
set I get back:

45 48 1012359206 1012134660 1 Jan
45 48 1012359206 1012134660 2 27
45 48 1012359206 1012134660 61 07
45 48 1012359206 1012134660 39 31
45 48 1012359206 1012134660 5 00
45 48 1012359206 1012134660 6 ahost
45 48 1012359206 1012134660 69 14823
45 48 1012359206 1012134660 10 my.host.com

My question is there any way to consolidate the rows, so that I avoid
the redundant data?  In other words:

45 48 1012359206 1012134660 1 Jan 2 27 61 07 39 31 5 00 6 ahost 69 14823
10 my.host.com


I could do this in multiple queries, which works fine when there is only
one event returned, but if I query on a different field (event.tid for
example, which is a type of line) things get very messy.

Currently I am handling this consolidation in PHP, but it's CPU and
memory intensive and it just seems a waste to have so much of the same
data returned.  If anyone could give me some pointers on doing this, or,
let me know if I'm missing something obvious or that there is a better
way to possibly design my schema.  Please feel free to ask me anything
for clarification etc.

Thank you

Hans


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to