Re: php-db Digest 5 Feb 2002 02:30:20 -0000 Issue 1034

Hans, On 2/4/02 6:30 PM, "php-db-digest-help@lists.php.net" <php-db-digest-help@lists.php.net> wrote: > From: hz11 <hz11@nyu.edu> > Date: Mon, 04 Feb 2002 21:17:15 -0500 > To: php-db@lists.php.net > Subject: Dealing with One-to-Many SELECTS (MySQL) > > >.... > 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? According to the DBMS there is no redundant data in the above results set. Yes we see the first 4 columns are identical but because the last two are not the same the set contains no duplicate rows (commands like "DISTINCT and GROUOP BY will not do what you wish). > 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 This cannot be done with a single select statement - SQL results are very square (think excel spread sheet) every row will have every column and you cannot have a single row / column with distinct values from multiple rows. Even if you're talking about aggregate functions (average, count, min, max) these aren't distinct values. > I could do this in multiple queries, That's the way I'd do it. > 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. Try opening two DB connections - you've got one working already $res1 has the result of SELECT event.eid, event.tid, event.itimestamp, event.etimestamp FROM event WHERE event.eid='45' (I've left out the join and date table) /* I'll assume ODBC but it works for any type */ While(odbc_fetch_row($res1)) { $eid = odbc_result($res1,1); /* Print or whatever you want to do with */ /* event.eid, event.tid, event.itimestamp, event.etimestam */ $sql2 = "SELECT 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=/"$my_eid/"; $res2 = odbc_exec($con2,$sql2); While(odbc_fetch_row($res2)) { /* now fetch and do what you want with the date.did and data */ $did = odbc_result($res2,1); $data = odbc_result($res2,2); } } Not so messy, No? Good Luck, Frank