Back To Programming / Scripting / Coding Forum

Complex Team Scheduler / Calendar (PHP/MySql)

  • Ifrit
  • Graduate
  • Graduate
  • User avatar
  • Joined: Jun 07, 2004
  • Posts: 196
  • Loc: Portugal
  • Status: Offline

Post July 21st, 2008, 5:49 am

I've been trying for the past few days to accomplish something that I finally deemed out of my league when it comes to programming.

I need to create/generate a graphical display that shows when and where a team needs to be at a certain time in a day.

My MYSQL Table returns the following information:

TEAM_ID | CLIENT / LOCATION | BEGIN HOUR | END HOUR | DAY |
1 | FOO | 8 | 11 | 2008-06-07 |
1 | BAR | 9 | 12 | 2008-06-07 |
2 | FOO | 10 | 13 | 2008-06-07 |
2 | JOE | 15 | 18 | 2008-06-07 |
3 | FOO | 18 | 20 | 2008-06-07 |
3 | JOE | 10 | 19 | 2008-06-07 |
4 | BAR | 8 | 14 | 2008-06-07 |


I can display this information easily, but my client needs it to be displayed graphically.

I've ran into some problems trying to do it by myself, mainly due to overlapping events (which are allowed to occur), therefore I wanted to ask anyone out there to see if they had a suggestion I could follow, a tutorial or some other type of help, anything would be greatly appreciated.

ps, Because there will be recurring events, the check for overlapped events needs to be done at "runtime", I have no idea how to achieve this...

I've added a picture to illustrate the required output.

Image
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post July 21st, 2008, 5:49 am

  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Joined: May 22, 2004
  • Posts: 3415
  • Loc: Richland, WA
  • Status: Offline

Post July 21st, 2008, 6:24 am

I have to head to work but if I have any free time I will tackle this problem for ya. Just wanted to let you know someone is looking for an answer for you. :)
#define NULL (::rand() % 2)
  • Ifrit
  • Graduate
  • Graduate
  • User avatar
  • Joined: Jun 07, 2004
  • Posts: 196
  • Loc: Portugal
  • Status: Offline

Post July 21st, 2008, 6:28 am

I forgot to mention this is PHP/Mysql

I am very grateful, SpooF.
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post July 21st, 2008, 7:21 am

maybe something like this could work?
Code: [ Select ]
<?php
$sql= mysql_query('SELECT * FROM table ORDER BY day,team, begin_hour');
$date1 = '';
$date2 = '';
$datecount = 0;
$team1 = '';
$team2 = '';
$teamcount = 0;
while ($row = mysql_fetch_array($sql))
{
$date1 = $row['day'];
$team1 = $row['team'];
if ($date1 == $date2)
{
//everything happens in here
if ($team1 == $team2)
{
echo "<span style='margin-left:" . $row['start_time'] . "opx width:" . ($row['end-time'] - $row['start-time']) . "0px;'>" . $row['client_location'] . "</span>";
}
else
{
if ($teamcount == 0)
{
echo "<div class='team'>";
echo "<p>" . $row['team'] . "</p>";
echo "<span style='margin-left:" . $row['start_time'] . "opx width:" . ($row['end-time'] - $row['start-time']) . "0px;'>" . $row['client_location'] . "</span>";
}
else
{
echo "</div><div class='team'>";
echo "<p>" . $row['team'] . "</p>";
echo "<span style='margin-left:" . $row['start_time'] . "opx width:" . ($row['end-time'] - $row['start-time']) . "0px;'>" . $row['client_location'] . "</span>";
}
}
//end
}
else
{
if ($datecount == 0)
{
echo "<div class='date'>";
$datecount ++;
}
else
{
echo "</div><div class='date'>";
}
}

$team2 = $row['team'];
$date2 = $row['day'];
}
echo "</div></div>";
?>
  1. <?php
  2. $sql= mysql_query('SELECT * FROM table ORDER BY day,team, begin_hour');
  3. $date1 = '';
  4. $date2 = '';
  5. $datecount = 0;
  6. $team1 = '';
  7. $team2 = '';
  8. $teamcount = 0;
  9. while ($row = mysql_fetch_array($sql))
  10. {
  11. $date1 = $row['day'];
  12. $team1 = $row['team'];
  13. if ($date1 == $date2)
  14. {
  15. //everything happens in here
  16. if ($team1 == $team2)
  17. {
  18. echo "<span style='margin-left:" . $row['start_time'] . "opx width:" . ($row['end-time'] - $row['start-time']) . "0px;'>" . $row['client_location'] . "</span>";
  19. }
  20. else
  21. {
  22. if ($teamcount == 0)
  23. {
  24. echo "<div class='team'>";
  25. echo "<p>" . $row['team'] . "</p>";
  26. echo "<span style='margin-left:" . $row['start_time'] . "opx width:" . ($row['end-time'] - $row['start-time']) . "0px;'>" . $row['client_location'] . "</span>";
  27. }
  28. else
  29. {
  30. echo "</div><div class='team'>";
  31. echo "<p>" . $row['team'] . "</p>";
  32. echo "<span style='margin-left:" . $row['start_time'] . "opx width:" . ($row['end-time'] - $row['start-time']) . "0px;'>" . $row['client_location'] . "</span>";
  33. }
  34. }
  35. //end
  36. }
  37. else
  38. {
  39. if ($datecount == 0)
  40. {
  41. echo "<div class='date'>";
  42. $datecount ++;
  43. }
  44. else
  45. {
  46. echo "</div><div class='date'>";
  47. }
  48. }
  50. $team2 = $row['team'];
  51. $date2 = $row['day'];
  52. }
  53. echo "</div></div>";
  54. ?>

you'll just need to add some CSS to that ...
Let's leave all our *plum* where it is and go live in the jungle ...
  • Ifrit
  • Graduate
  • Graduate
  • User avatar
  • Joined: Jun 07, 2004
  • Posts: 196
  • Loc: Portugal
  • Status: Offline

Post July 21st, 2008, 10:21 am

righteous_trespasser, thank you a ton for the help

i've tried applying it but im not 100% sure what its doing, could you maybe let me know what your line of thought was, doing that code?
  • alex89
  • Bronze Member
  • Bronze Member
  • User avatar
  • Joined: Jul 18, 2008
  • Posts: 239
  • Loc: Western Australia
  • Status: Offline

Post July 21st, 2008, 11:39 am

Very impressive. I'll go through line-by-line for you. (Turn line numbers on)

2: sets a variable called $sql (see 9)
3-8: sets variables to ""
9: starts a loop (runs a mysql query for all data in table organised by day,team,begin hour) and runs until out of data
11-12: gets data from database for variables date1 & team1
13: if team is same as previous (see 50)
16: if date is same as previous (see 51)
18: echos some html (a span with left margin at start_time pixels, with width of start_time - finish_time - saying client location)
20: else, if team is different from previous entry in db
22: if teamcount is zero (no team)
24-26: div of class 'team' (use css to make pretty) which says the team, and then another span (see 18)
28: else, if teamcount is different
30-32: close the div (24), say team, another span
37: else, if date is different from previous entry in db
39: and if teamcount is zero (team exists)
41-42: div class = 'date', increase datecount
44: else, if datecount isn't zero
46: end div, start new one (for new date)
50-51: set team2 to be current team1, and same for date2
53: close both divs

I hope I've got this right. So basically, it generates a picture using spans & divs (divs need a css sheet with styles for .date & .team) from a table called 'table' in a mysql database with columns of 'start_time', 'end_time', 'client_location', 'team', and 'day'.

If you have a server running apache, php & mysql, give it a shot (use phpadmin to create the table & db).

To do: Connect to the db (at the top), disconnect (at bottom), write a css sheet & make the table.

If you get stuck, I can post more detailed info :)
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post July 21st, 2008, 12:15 pm

Thanks alex, I was busy playing oblivion, this last mission is damn impossible ...
Let's leave all our *plum* where it is and go live in the jungle ...
  • alex89
  • Bronze Member
  • Bronze Member
  • User avatar
  • Joined: Jul 18, 2008
  • Posts: 239
  • Loc: Western Australia
  • Status: Offline

Post July 21st, 2008, 12:17 pm

righteous_trespasser wrote:
Thanks alex, I was busy playing oblivion, this last mission is damn impossible ...

Played the expansion? I think I've completed all the quests - which one are you stuck on?
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post July 21st, 2008, 12:37 pm

//The last mission of the main quest ... in "paradise" ... it's freakin tough ... but that's because my sword sucks ... a few minutes ago I picked up a new one and now I'm just chopping everything to pieces ...
Let's leave all our *plum* where it is and go live in the jungle ...
  • Ifrit
  • Graduate
  • Graduate
  • User avatar
  • Joined: Jun 07, 2004
  • Posts: 196
  • Loc: Portugal
  • Status: Offline

Post July 21st, 2008, 3:14 pm

I have finished this part of the project successfully, however I couldn't get your code to work, righteous_trespasser, thank you very much, however.
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post July 21st, 2008, 7:01 pm

:lol: Well, it works but not exactly how you want it. I got it to work on my test demo and it shows the thing almost all good... there are a few things that needs to be done... I'm doing it right now :)

Here is how the source (after some editing to the PHP) looks like
HTML Code: [ Select ]
<div class='date'>
</div>
<div style='margin-left:90px; width:30px; background-color: #CECECE;'>
BAR
</div>
<div style='margin-left:100px; width:30px; background-color: #CECECE;'>
FOO
</div>
<div style='margin-left:150px; width:30px; background-color: #CECECE;'>
JOE
</div>
<div style='margin-left:180px; width:20px; background-color: #CECECE;'>
FOO
</div>
<div style='margin-left:100px; width:90px; background-color: #CECECE;'>
JOE
</div>
<div style='margin-left:80px; width:60px; background-color: #CECECE;'>
BAR
</div>
  1. <div class='date'>
  2. </div>
  3. <div style='margin-left:90px; width:30px; background-color: #CECECE;'>
  4. BAR
  5. </div>
  6. <div style='margin-left:100px; width:30px; background-color: #CECECE;'>
  7. FOO
  8. </div>
  9. <div style='margin-left:150px; width:30px; background-color: #CECECE;'>
  10. JOE
  11. </div>
  12. <div style='margin-left:180px; width:20px; background-color: #CECECE;'>
  13. FOO
  14. </div>
  15. <div style='margin-left:100px; width:90px; background-color: #CECECE;'>
  16. JOE
  17. </div>
  18. <div style='margin-left:80px; width:60px; background-color: #CECECE;'>
  19. BAR
  20. </div>
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post July 21st, 2008, 8:22 pm

Alright... I got it to work... somewhat :lol:

Code: [ Select ]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<title>Team Schedular</title>
<style type="text/css">
<!--
.container {
margin-left: -157px;
}

.team {
background-color: #dddddd;
}

ul.daten {
list-style-type: none;
margin-left: 157px;
}

ul.daten li {
float: left;
background-color: #ffffff;
color: #000000;
width: 25px;
}
//-->
</style>
</head>
<body>
<div class="container">
<?php
mysql_connect('host','user','pass');
mysql_select_db("database");

$sql= mysql_query('SELECT * FROM table ORDER BY day,team_id,begin_hour');

while ($row = mysql_fetch_assoc($sql))
{

    $date1 = $row['day'];
    $team1 = $row['team'];
    if ($date1 == $date2)
    {

        $team = $row['client'];
        $margin = $row['begin_hour'] * 25;
        $width = ($row['end_hour'] - $row['begin_hour']) * 25;
        $client = $row['client'];
        $location = $row['location'];
    
        echo " <div class=\"team_cont\">\n";

        echo <<< EOT
<div style='margin-left: {$margin}px; width: {$width}px; background-color: #CECECE;'>
$team - $location
</div>

EOT;
        
        echo " </div>\n";
    }
    else
    {

        echo <<<EOT
<ul class="daten">
<li>08</li>
<li>09</li>
<li>10</li>
<li>11</li>
<li>12</li>
<li>13</li>
<li>14</li>
<li>15</li>
<li>16</li>
<li>17</li>
<li>18</li>
<li>19</li>
<li>20</li>
<li>21</li>
<li>22</li>
<li>23</li>
</ul>

EOT;
    }
    $date2 = $row['day'];
}
?>
</div>
</body>
</html>
  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  2. "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
  3. <html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
  4. <head>
  5. <title>Team Schedular</title>
  6. <style type="text/css">
  7. <!--
  8. .container {
  9. margin-left: -157px;
  10. }
  12. .team {
  13. background-color: #dddddd;
  14. }
  16. ul.daten {
  17. list-style-type: none;
  18. margin-left: 157px;
  19. }
  21. ul.daten li {
  22. float: left;
  23. background-color: #ffffff;
  24. color: #000000;
  25. width: 25px;
  26. }
  27. //-->
  28. </style>
  29. </head>
  30. <body>
  31. <div class="container">
  32. <?php
  33. mysql_connect('host','user','pass');
  34. mysql_select_db("database");
  36. $sql= mysql_query('SELECT * FROM table ORDER BY day,team_id,begin_hour');
  38. while ($row = mysql_fetch_assoc($sql))
  39. {
  41.     $date1 = $row['day'];
  42.     $team1 = $row['team'];
  43.     if ($date1 == $date2)
  44.     {
  46.         $team = $row['client'];
  47.         $margin = $row['begin_hour'] * 25;
  48.         $width = ($row['end_hour'] - $row['begin_hour']) * 25;
  49.         $client = $row['client'];
  50.         $location = $row['location'];
  51.     
  52.         echo " <div class=\"team_cont\">\n";
  54.         echo <<< EOT
  55. <div style='margin-left: {$margin}px; width: {$width}px; background-color: #CECECE;'>
  56. $team - $location
  57. </div>
  59. EOT;
  60.         
  61.         echo " </div>\n";
  62.     }
  63.     else
  64.     {
  66.         echo <<<EOT
  67. <ul class="daten">
  68. <li>08</li>
  69. <li>09</li>
  70. <li>10</li>
  71. <li>11</li>
  72. <li>12</li>
  73. <li>13</li>
  74. <li>14</li>
  75. <li>15</li>
  76. <li>16</li>
  77. <li>17</li>
  78. <li>18</li>
  79. <li>19</li>
  80. <li>20</li>
  81. <li>21</li>
  82. <li>22</li>
  83. <li>23</li>
  84. </ul>
  86. EOT;
  87.     }
  88.     $date2 = $row['day'];
  89. }
  90. ?>
  91. </div>
  92. </body>
  93. </html>

The problems with it are...
  • Doesn't show the first result
  • You didn't specify what you want to be for groups so I messed the database up... I use clients for groups and the clients for location. The team_id is a team_id I made in the database. A primary key
Here is how my database looks like for the test
SQL Code: [ Select ]
CREATE TABLE `test` (
 `team_id` tinyint(4) NOT NULL AUTO_INCREMENT,
 `client` varchar(255) NOT NULL,
 `location` varchar(25) NOT NULL,
 `begin_hour` varchar(2) NOT NULL,
 `end_hour` varchar(2) NOT NULL,
 `day` varchar(10) NOT NULL,
 PRIMARY KEY (`team_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
 
INSERT INTO `test` (`team_id`, `client`, `location`, `begin_hour`, `end_hour`, `day`) VALUES
(1, '1', 'FOO', '8', '11', '2008-06-07'),
(2, '1', 'BAR', '9', '12', '2008-06-07'),
(3, '2', 'FOO', '10', '13', '2008-06-07'),
(4, '2', 'JOE', '15', '18', '2008-06-07'),
(5, '3', 'FOO', '18', '20', '2008-06-07'),
(6, '3', 'JOE', '10', '19', '2008-06-07'),
(7, '4', 'BAR', '8', '14', '2008-06-07');
  1. CREATE TABLE `test` (
  2.  `team_id` tinyint(4) NOT NULL AUTO_INCREMENT,
  3.  `client` varchar(255) NOT NULL,
  4.  `location` varchar(25) NOT NULL,
  5.  `begin_hour` varchar(2) NOT NULL,
  6.  `end_hour` varchar(2) NOT NULL,
  7.  `day` varchar(10) NOT NULL,
  8.  PRIMARY KEY (`team_id`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
  10.  
  11. INSERT INTO `test` (`team_id`, `client`, `location`, `begin_hour`, `end_hour`, `day`) VALUES
  12. (1, '1', 'FOO', '8', '11', '2008-06-07'),
  13. (2, '1', 'BAR', '9', '12', '2008-06-07'),
  14. (3, '2', 'FOO', '10', '13', '2008-06-07'),
  15. (4, '2', 'JOE', '15', '18', '2008-06-07'),
  16. (5, '3', 'FOO', '18', '20', '2008-06-07'),
  17. (6, '3', 'JOE', '10', '19', '2008-06-07'),
  18. (7, '4', 'BAR', '8', '14', '2008-06-07');

Hope this helps you and helps someone to fix the problems listed :lol:

(P.S: Credit is due here... I used R_T's function and edited it a bit...
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • alex89
  • Bronze Member
  • Bronze Member
  • User avatar
  • Joined: Jul 18, 2008
  • Posts: 239
  • Loc: Western Australia
  • Status: Offline

Post July 21st, 2008, 9:17 pm

Nice work! I wish I had as much spare time as you :)
  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post July 21st, 2008, 9:34 pm

Must've taken me about an hour 30 minutes to do that. Man I need to get a life :lol:
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • alex89
  • Bronze Member
  • Bronze Member
  • User avatar
  • Joined: Jul 18, 2008
  • Posts: 239
  • Loc: Western Australia
  • Status: Offline

Post July 21st, 2008, 9:37 pm

Whoops, I'm half an hour late for a date - I've got a life but it interferes with my coding life :(
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post July 21st, 2008, 9:37 pm

1, 2

Post Information

  • Total Posts in this topic: 27 posts
  • Users browsing this forum: No registered users and 137 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.