Dynamic Event Table Help

[foufvious]

kind of a beginner php coder here

here is my problem:

i have a table in mysql that stores events (id, name, start_time, duration (hours), gamemaster, table)

 

sample data:

001, "Clay Wars", 2012-10-10 09:00, 3, "John Smith", "MIN1"

002, "Fistful of Minis", 2012-10-10 13:00, 2, "Abe Llincoln", "MIN1"

003, "Hero Clix", 2012-10-10 11:00, 3, "Tony Stark", "MIN2"

004, "Settlers of Catan", 2012-10-10 09:00, 1, "Jane Doe", "BG1"

005, "Ad Astra", 2012-10-10 10:00, 3, "Abe Lincoln", "BG1"

006, "Innovation", 2012-10-10 09:00, 2, "Tony Stark", "BG2"

007, "Mech Warrior", 2012-10-10 10:00, 2, "Jane Doe", "BG3"

008, "Manhattan Project", 2012-10-10 12:00, 2, "Al Einstein" "BG3"

 

 

i need to write something that will read through the table and dynamically build an events page for convention attendees.

 

 

last year i dynamically built the table with php reading from the db, however it was a simple table that listed the name of each event in the hour slot it started.

9am Clay Wars

Settlers of Catan

Innovation 10am Ad Astra

Mech Warriors 11am Hero Clix 12pm Manhattan Project 1pm Fistfull of Minis

 

this year i want to graphically represent how long the event lasts, so attendees can better plan their days.

 

ideally like:

Table 9am 10am 11am 12pm 1pm 2pm MIN1 ClayWars Fistfull of Minis MIN2 Hero Clix BG1 Settlers of Catan Ad AStra BG2 Innovation BG3 Mech Warrior ManHattan Project

 

i'm having trouble figuring out how to accomplish this. i was thinking that i could adjust the colspan for each event based on the duration, but how to put everything together in one table row?

[foufvious]

the tables i posted got screwy here's a better reprint:

last year:

------------------------

 

| 9am|Clay Wars |

| |Settlers of Catan|

| |Innovation |

------------------------

|10am|Ad Astra |

| |Mech Warriors |

------------------------

|11am|Hero Clix |

------------------------

|12pm|Manhattan Project|

------------------------

| 1pm|Fistfull of Minis|

------------------------

 

this year:

Table | 9am |10am |11am |12pm |1pm |2pm

------------------------------------------------------------------

MIN1 | ClayWars |Fistfull of Minis |

MIN2 | | |Hero Clix |

BG1 | Settlers |Ad AStra | |

BG2 | Innovation | | | |

BG3 | |Mech Warrior |ManHattan Project |

[Barand]

I usually resort to GD images for tasks like this

 

main code

<?php
include("testDBconnect.php");

$sql = "SELECT table_code,event_name, event_date, duration
 FROM event
 ORDER BY table_code, event_date";
$res = mysql_query($sql) or die(mysql_error());

echo "<table>\n";
$prev='';
while (list($table,$name, $time, $dur) = mysql_fetch_row($res)) {
$tname = $table==$prev ? '' : $table;
$prev = $table;
echo "<tr><td>$tname</td><td>$name</td><td><img src='timechart.php?start=$time&duration=$dur' /></td></tr>\n";
}
echo "</table>\n";

?>

 

timechart.php

<?php
if ( isset($_GET['start']) && isset($_GET['duration']) ) {
$start = date('H', strtotime($_GET['start']));
$duration = $_GET['duration'];
}
else {
$start = $duration = 0;
}
$mintime = 9;
$maxtime = 17;
$pixperhour = 50;
$width = ($maxtime - $mintime)*$pixperhour;
$height = 40;
$im = imagecreate($width,$height);
$bg = imagecolorallocate($im, 0xFF, 0xFF, 0xFF);
$linecol = imagecolorallocate($im, 0xCC, 0xCC, 0xCC);
$barcol = imagecolorallocate($im, 0x00, 0xCC, 0x00);
$txtcol = imagecolorallocate($im, 0x00, 0x00, 0x00);

$x0 = ($start - $mintime) * $pixperhour;
$x1 = ($start + $duration - $mintime) * $pixperhour;
imagefilledrectangle($im, $x0,0,$x1,$height, $barcol);

for ($h=0, $t=$mintime; $h <= ($maxtime - $mintime)*$pixperhour; $h+=$pixperhour, $t++) {
imageline($im, $h,0,$h,$height,$linecol);
imagestring($im,1,$h+1,1,$t,$txtcol);
}

header("content-type: image/png");
imagepng($im);
imagedestroy($im);
?>

 

Results attached

Edited October 10, 2012 by Barand

[foufvious]

is there a way to keep all events for a single table on the same line? would could have 8-12 events over the course of a day on a single table.

[Barand]

Yes, you just need to adapt it a little to meet your exact requirements.

[Barand]

OK, MkII version

 

main code

<?php
include("testDBconnect.php");

$sql = "SELECT table_code,
    GROUP_CONCAT(CONCAT_WS('~',event_name, event_date, duration) SEPARATOR '|') as data
    FROM event
    GROUP BY table_code";
$res = mysql_query($sql) or die(mysql_error());

echo "<table>\n";

while (list($table,$data) = mysql_fetch_row($res)) {
   $tname = $table==$prev ? '' : $table;
   $prev = $table;
   echo "<tr><td>$tname</td><td><img src='timechart.php?events=$data' /></td></tr>\n";
}
echo "</table>\n";

?>

 

timechart.php

<?php
if ( isset($_GET['events']) ) {
   $events = explode('|', $_GET['events']);
}
else {
   $events=array();
}
$mintime = 9;
$maxtime = 17;
$pixperhour = 50;
$width = ($maxtime - $mintime)*$pixperhour;
$height = 40;
$im = imagecreate($width,$height);
$bg = imagecolorallocate($im, 0xFF, 0xFF, 0xFF);
$linecol = imagecolorallocate($im, 0xCC, 0xCC, 0xCC);
$barcol1 = imagecolorallocate($im, 0x00, 0xCC, 0x00);
$barcol2 = imagecolorallocate($im, 0xFF, 0xAA, 0x00);
$txtcol = imagecolorallocate($im, 0x00, 0x00, 0x00);

$e = 0;
foreach ($events as $data) {
   list($name,$time,$duration) = explode('~', $data);
   $start = date('H', strtotime($time));
   $x0 = ($start - $mintime) * $pixperhour;
   $x1 = ($start + $duration - $mintime) * $pixperhour;
   $barcol = $e%2 ? $barcol1:$barcol2;
   $ty = $e%2 ? 10 : 20;
   imagefilledrectangle($im, $x0,0,$x1,$height, $barcol);
   imagestring($im,3,$x0+1,$ty,$name,$txtcol);
   $e++;
}
for ($h=0, $t=$mintime; $h <= ($maxtime - $mintime)*$pixperhour; $h+=$pixperhour, $t++) {
   imageline($im, $h,0,$h,$height,$linecol);
   imagestring($im,1,$h+1,1,$t,$txtcol);
}

header("content-type: image/png");
imagepng($im);
imagedestroy($im);
?>

 

results attached

[foufvious]

that is great, thanks.

[foufvious]

ok, one more question. the timechart.php code is accepting in a list of events for the whole day, for a table, correct?

if i want each event name to be a link to show details about that event, i would need to modify the code to accept a single event and pass back the image for one event, so an href tag can be wrapped around it, correct?

or is there another way when generating each event image to embed the link?

[Barand]

I don't do "function creep" for free

[foufvious]

i was just asking if i was on the right track.

[Barand]

I've changed it so it builds imagemaps for use with the current timechart.php images. It currently assumes you want to link to

 

mech_warrior.html

manhattan_project.html

etc

 

<?php
include("testDBconnect.php");

$sql = "SELECT table_code,
		    GROUP_CONCAT(CONCAT_WS('~',event_name, event_date, duration) SEPARATOR '|') as data
		    FROM event
		    GROUP BY table_code";
$res = mysql_query($sql) or die(mysql_error());

echo "<table cellspacing=0>\n";

$mapStr = '';

while (list($table,$data) = mysql_fetch_row($res)) {
    makeMap($table, $data, $mapStr);
    echo "<tr><td>$table</td><td><img border=0 src='timechart.php?events=$data' usemap='#{$table}_map' /></td></tr>\n";
}
echo "</table>\n";

echo $mapStr;


//
//  build imagemaps
//
function makeMap ($table, $data, &$mapStr)
{
   $mintime = 9;							  // these must match
   $pixperhour = 50;						  // values used in
   $height = 40;							  // timechart.php

   $mapStr .= "<map name='{$table}_map'>\n";
   $events = explode('|', $data);
   foreach ($events as $e) {
    list($name,$time,$duration) = explode('~', $e);
    $start = date('H', strtotime($time));
    $x0 = ($start - $mintime) * $pixperhour;
    $x1 = ($start + $duration - $mintime) * $pixperhour;
    $href = str_replace(' ', '_', $name) . '.html';
    $mapStr .= "<area shape='rect' coords='$x0,0,$x1,$height' href='$href' alt='$name'>\n";
   }
   $mapStr .= "</map>\n";   
}

?>

 

So you can now keep it as multiple events for each table code.