Nested PHP results page

looking for resources

[ukgimp]

Hello all

I have a mySQL db with "stuff" in and I would like to create a repeat region where the results are grouped.

eg

2002
--Item 1
--Item 2
--Item 3
2003
--Item X
--Item Y
--Item Z

Is this easy to do in php. I have come across articles to accomplish this with ASP and Dreamweaver UD but nothing with php

Anybody know of a resource that covers what I am trying to achieve.

Cheers

[Nick_W]

I think you could do that directly from the mysql client.

Try mysql.com and check out copying tables etc...?

Nick

[andreasfriedrich]

Depends on how the stuff in your db is organized. If your SELECT query returns a bunch of rows each with the year in it, do someting like this.

foreach(row in result) { 
 if ($last_year <> $row[year]) { 
  print "your html, e.g. <tr><td colspan='2'>$row[year]</td></tr>"; 
  $last_year = $row[year]; 
 } 
 print "the rest of your data as you like"; 
}

Andreas

[ergophobe]

To back up a bit from Andreas script, you will like want to add an ORDER BY statement to yur SQL query as in

ORDER BY year DESC

or something like that. Then when you loop through, you already have the results sorted by year. Also look at the MySQL manual on GROUP BY (probably in chapter 7 unless the manual has changed a lot).

Tom

[andreasfriedrich]

Good thinking Tom, I silently expected the results to be ordered by year. Probably good to say so explicitly ;)

Andreas

[ukgimp]

Thanks all, good starting point for me.

Cheers

[ukgimp]

A little bit more guidance if I can?

Trying to get my head round your post Andreas and I am getting stuck with the logic I think.

I can get the two separate queries and results on to the page. The inner one (year) i have grouped by so I only get one occurance.

Do you put a loop inside a loop and what if the inner loop depends on a value of the outer loop. For example I can do the inner loop with a WHERE column = 2000 and I get the right results but I am unceratain how to use the value of the outer (year) for each of the inner set of loops.

EG, is this the right sort of thing.

//loop for year
$i=0;
while ($i < $qnumber) {
$year=mysql_result($qResult,$i,"YEAR(EventStartDate)");
echo "<b>$year</b><br>{insert second loop here?}";
++$i;
}

I hope this makes sense. :)

[andreasfriedrich]

You can always do it your way with two loops ukgimp.

My code will only make sense if you forget about the two loops and have your SELECT query return a bunch of rows each with the year in it ordered by year.

Suppose you have a table like this:

name ¦ birthday 
t.h. ¦ 03-14-1983 
i.h. ¦ 11-17-1980 
z.h. ¦ 10-22-1985 
l.d. ¦ 11-11-1974  
a.c. ¦ 07-12-1987 
j.b. ¦ 03-17-1986

You could loop through each year and do the query for each year looping through all the results returned for each year.

foreach $year (1974 .. 1987) { 
 @rows = execute_query("SELECT name FROM table WHERE birthday=$year"); 
 print "people born in $year\n"; 
 foreach $row (@rows) { 
  print "--$row{name}\n"; 
 } 
}

would give you:

people born in 1974 
--l.d. 
people born in 1975 
people born in 1976 (me) 
people born in 1977 
... 
people born in 1983 
--t.h. 
people born in 1984 
people born in 1985 
--z.h. 
people born in 1986 
--a.c. 
people born in 1987 
--j.b.

You could, however, and this is the method I first suggested do the following:

@rows = execute_query("SELECT name FROM table ORDERED BY birthday"); 
foreach $row (@rows) { 
 if ($last_year <> $row{year}) { 
  print "people born in $row{year}\n"; 
  $last_year = $row{year}; 
 } 
 print "--$row{year}"; 
}

Btw the code is just pseudo code.

Andreas