Collecting Variables in a table

[Richzilla]

I'm trying to collect variables that share the same traits and display them in my table. Here's an excample of the table output i want -

 

 

 

Event                          DJs                        Date                          Download

                                  A DJ                    January 2007                http://download.com

[image of event]          B DJ                    January 2007                http://download.com

An Event                      C DJ                    January 2007                http://download.com

                                  D DJ                    January 2007                http://download.com

                                  E DJ                    January 2007                http://download.com

 

 

I hope the above makes sense. Esentially all the values in my table have many differnt types of events, so i want to dynamically organise them into tables as above. The 'An Event' field will have a lot of the same values in mysql database.

 

Here's the code i've come up with so far, but it doesn't gather the variables together.

 

 

$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"id");
$dj=mysql_result($result,$i,"dj");
$rave=mysql_result($result,$i,"event");
$date=mysql_result($result,$i,"date");
$download1=mysql_result($result,$i,"download1");
$download2=mysql_result($result,$i,"download2");
$download3=mysql_result($result,$i,"download3");
$download4=mysql_result($result,$i,"download4");
$tracklist=mysql_result($result,$i,"tracklist");
$flyer_sm=mysql_result($result,$i,"flyer_sm");
$flyer_lg=mysql_result($result,$i,"flyer_lg");
$tlist="tracklist.php?id=";

?>


          <tr> 
	  	<td class="trk_list_main" width="29%">
		<p align="center">
		<? if ($flyer_sm != "")
             	echo "<a href=\"$flyer_lg\" target=blank >
			 <img src= $flyer_sm alt= '$event' border=0 > </a>";
	  	
		if ($event = "$event");
   			    echo $event 
		?> </td>

            <td class="trk_list_main" width="21%">
		<? if ($event = "$event+");
   			    echo $dj ?> </td>
            <td class="trk_list_main" width="16%"><? echo $date ?></td>
            <td class="trk_list_main" width="19%">

<? 
if ($download1 != "")
   echo "<a href=\"$download1\">part 1 <br>";

if ($download2 != "")
   echo "<a href=\"$download2\">part 2 <br>";
   
if ($download3 != "")
   echo "<a href=\"$download3\">part 3 <br>";
if ($download4 != "")
   echo "<a href=\"$download4\">part 4 <br>";
   ?>

	 <td class="trk_list_main" width="15%">
	 <? if ($tracklist != "")
         echo "<a href=javascript:launch('$tlist$id')>Tracklist </a><br> </td> </tr>";


	  ?>

	<? $i++;
}
?>

 

[nloding]

Dunno why you wrote your code that way, but here's what I would do:

 

<?php

$query = "SELECT event, dj, date, download FROM event_table";
$result = mysql_query($query);

echo "<table>";

while($row = mysql_fetch_assoc($result)) {
  echo "<tr><td>" . $row['event'] . "</td><td>" . $row['dj'] . "</td><td>" . $row['date'] . "</td><td>" . $row['download'] . "</td></tr>";
}

echo "</table>";

?>

 

That is very simplified, and you can change the table layout easier, but that's how to do it.  Use the while loop, fetching each individual row till there aren't any more.

[Richzilla]

Thnaks for the reply. Doing it this way doesn't fetch just one event header for a collection of djs playing at an event. The way we have both done this is to organise the data by the event, which creates an individual line for each mix i have.

 

The reason for doing this page the way i have is because i want to add a lot more functionality than just displaying the data in rows.

 

All i want to do is gather like for like event names and display the djs taht played at the event in the same cell (as per the basic diagram above). I want the grouping to be duplicated for all the differnt types of event names i have.

 

I hope this makes sense.

[nloding]

If I get what you're saying ... you want to display only information pertaining to a single event.  Or a single DJ.

 

<?php

$event = "An Event";
$query = "SELECT event, dj, date, download FROM event_table WHERE event=" . $event;

?>

[Richzilla]

sorry, that's no what i'm trying to do. I'm obviously not explaining what i want to do. Here's the page that i have created in html that i want to replicate in php. This needs to be done dynamically according to what is in teh database -

 

html page -

 

www.jungletekno.co.uk/mixes2.htm

 

php page -

 

http://www.jungletekno.co.uk/mixes4.php

 

As you can see I need to organise the events as they are in teh above page.

 

How do i do this??

[nloding]

Ah, I get it, you normalized the tables so there's only one DJ per table row.  Personally, I wouldn't normalize the tables and put all the DJs together, so you each row represents the mix/event, not the DJ.

 

Based off your HTML, your MySQL table would look like this (representing one row):

 

The Eclipse - Blast from the past volume 1

Carl Cox,Easygroove,Ellis Dee,Sasha

1990-92,various dates

http://www.site.com/download1,http://www.site.com/download2

 

Then use explode() on the columns and separate by "," for easy formatting.  That's the quickest way.

 

Otherwise ... I dunno what the best way is ... put everything into an array in the while loop, and then use a for statement afterwards and cycle through the array?

[Richzilla]

thanks for the reply. it's hard isn't it!!! I can't lay the tables out as you suggested as this is just the start of the database. I want to add to it in future by form pages for users to upload their own info. This would cause issues as you can imagine.

 

anyone got a way of doing this?

[nloding]

Did some thinking in the shower -- what better place? -- and realized what I should have earlier.  My excuse is sleepiness ... I had just woken up!

 

You need more than one table for this.  You should have at least three that I see immediately (I'm not gonna write the SQL):

 

TABLE: event_table (normalized)

event_id

event_name

event_date

event_download

 

TABLE: dj_table (normalized)

dj_id

dj_name

 

TABLE: dj_to_events (not normalized, many-to-many)

event_id (foreign key)

dj_id (foreign key)

 

I'd probably make a separate table for the track listings as well.  Something like this, I guess:

 

TABLE: track_listing

track_id

track_name

track_length

track_number

event_id (to link it to the mix)

 

The "dj_to_events" table is kinda messy, but I can't think of a better way to do that ... then you use the SQL JOIN command to link them all together and output the data.

 

See where I'm headed with it?  If you're having trouble with the database design, head over the MySQL forums here and post the question about design.

[Richzilla]

This looks messy and hard for me to do!! I'm kinda new to all this. There must be an easier way of doing what I want??

[nloding]

Those two ways are the only ways I can think of.  Databases are not easy, especially relational ones.  For what you want to do, you need separate tables for multiple reasons.  You can do more custom searches and sorting more easily.  It's easier to find the data you're looking for.  It takes less CPU cycles to find it.

 

You'll want to go grab a good book on MySQL and check it out.  The second method with multiple tables is the best way to go (except possibly that djs_to_events table, but I haven't discovered a better way to set something like that up).