Welcome to WebmasterWorld Guest from 54.167.82.170

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Nested PHP results page

looking for resources

     

ukgimp

9:58 am on Sep 25, 2002 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

10:10 am on Sep 25, 2002 (gmt 0)

WebmasterWorld Senior Member nick_w is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

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

Nick

andreasfriedrich

10:29 am on Sep 25, 2002 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

5:46 pm on Sep 25, 2002 (gmt 0)

WebmasterWorld Administrator ergophobe is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



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

6:19 pm on Sep 25, 2002 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

Andreas

ukgimp

7:36 am on Sep 26, 2002 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Thanks all, good starting point for me.

Cheers

ukgimp

3:45 pm on Sep 26, 2002 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

6:06 pm on Sep 26, 2002 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

 

Featured Threads

Hot Threads This Week

Hot Threads This Month