Complex Team Scheduler / Calendar (PHP/MySql)

  • Ifrit
  • Graduate
  • Graduate
  • User avatar
  • Posts: 196
  • Loc: Portugal

Post 3+ Months Ago

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
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

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. :)
  • Ifrit
  • Graduate
  • Graduate
  • User avatar
  • Posts: 196
  • Loc: Portugal

Post 3+ Months Ago

I forgot to mention this is PHP/Mysql

I am very grateful, SpooF.
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

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. }
  49. $team2 = $row['team'];
  50. $date2 = $row['day'];
  51. }
  52. echo "</div></div>";
  53. ?>

you'll just need to add some CSS to that ...
  • Ifrit
  • Graduate
  • Graduate
  • User avatar
  • Posts: 196
  • Loc: Portugal

Post 3+ Months Ago

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
  • Posts: 239
  • Loc: Western Australia

Post 3+ Months Ago

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
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

Thanks alex, I was busy playing oblivion, this last mission is damn impossible ...
  • alex89
  • Bronze Member
  • Bronze Member
  • User avatar
  • Posts: 239
  • Loc: Western Australia

Post 3+ Months Ago

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
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

//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 ...
  • Ifrit
  • Graduate
  • Graduate
  • User avatar
  • Posts: 196
  • Loc: Portugal

Post 3+ Months Ago

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
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

: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>
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

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. }
  11. .team {
  12. background-color: #dddddd;
  13. }
  14. ul.daten {
  15. list-style-type: none;
  16. margin-left: 157px;
  17. }
  18. ul.daten li {
  19. float: left;
  20. background-color: #ffffff;
  21. color: #000000;
  22. width: 25px;
  23. }
  24. //-->
  25. </style>
  26. </head>
  27. <body>
  28. <div class="container">
  29. <?php
  30. mysql_connect('host','user','pass');
  31. mysql_select_db("database");
  32. $sql= mysql_query('SELECT * FROM table ORDER BY day,team_id,begin_hour');
  33. while ($row = mysql_fetch_assoc($sql))
  34. {
  35.     $date1 = $row['day'];
  36.     $team1 = $row['team'];
  37.     if ($date1 == $date2)
  38.     {
  39.         $team = $row['client'];
  40.         $margin = $row['begin_hour'] * 25;
  41.         $width = ($row['end_hour'] - $row['begin_hour']) * 25;
  42.         $client = $row['client'];
  43.         $location = $row['location'];
  44.     
  45.         echo " <div class=\"team_cont\">\n";
  46.         echo <<< EOT
  47. <div style='margin-left: {$margin}px; width: {$width}px; background-color: #CECECE;'>
  48. $team - $location
  49. </div>
  50. EOT;
  51.         
  52.         echo " </div>\n";
  53.     }
  54.     else
  55.     {
  56.         echo <<<EOT
  57. <ul class="daten">
  58. <li>08</li>
  59. <li>09</li>
  60. <li>10</li>
  61. <li>11</li>
  62. <li>12</li>
  63. <li>13</li>
  64. <li>14</li>
  65. <li>15</li>
  66. <li>16</li>
  67. <li>17</li>
  68. <li>18</li>
  69. <li>19</li>
  70. <li>20</li>
  71. <li>21</li>
  72. <li>22</li>
  73. <li>23</li>
  74. </ul>
  75. EOT;
  76.     }
  77.     $date2 = $row['day'];
  78. }
  79. ?>
  80. </div>
  81. </body>
  82. </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...
  • alex89
  • Bronze Member
  • Bronze Member
  • User avatar
  • Posts: 239
  • Loc: Western Australia

Post 3+ Months Ago

Nice work! I wish I had as much spare time as you :)
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

Must've taken me about an hour 30 minutes to do that. Man I need to get a life :lol:
  • alex89
  • Bronze Member
  • Bronze Member
  • User avatar
  • Posts: 239
  • Loc: Western Australia

Post 3+ Months Ago

Whoops, I'm half an hour late for a date - I've got a life but it interferes with my coding life :(
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

liar. lol

I got facebook... does that count?
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

No, it doesn't ...
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

righteous_trespasser wrote:
No, it doesn't ...

darn...


:lol:
  • Ifrit
  • Graduate
  • Graduate
  • User avatar
  • Posts: 196
  • Loc: Portugal

Post 3+ Months Ago

Guys guys! The problem has been solved, but thanks SO MUCH for everyone's trouble and help!

Here's a screenshot:

Image

I will release the program when its fully finished, even if all of it done very amateurishly, who knows if people can improve it!

ps. A good friend of mine helped me with this, but thanks so much to all of you for finding the time to help me!
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

Excellent, I'm glad you got it sorted out ... looks cool ...
  • alex89
  • Bronze Member
  • Bronze Member
  • User avatar
  • Posts: 239
  • Loc: Western Australia

Post 3+ Months Ago

It looks very impressive - I'd love to have a play with it when you release :)
  • Ifrit
  • Graduate
  • Graduate
  • User avatar
  • Posts: 196
  • Loc: Portugal

Post 3+ Months Ago

Thanks!

The biggest problem I will face... coming up with a name for it! Heh!
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

FXP Scheduler :) Put your site in it

I wouldn't mind getting my hands on that application :D
  • Ifrit
  • Graduate
  • Graduate
  • User avatar
  • Posts: 196
  • Loc: Portugal

Post 3+ Months Ago

That website is long gone :p

Things I'm planning for this application:

* Billable Hours - Specify Price before creating an Event/Intervention
* Contact Manager - Sort of an Address Book
* Client Manager - With Events/Intervention reports for each client, reports for payments, etc
* Monthly Calendar for Events/Interventions (maybe also Weekly calendar, though that will probably be very complicated for me, but I've got my hopes up)

Amongst other more trivial functions
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

or Ifrit Scheduler

if you need any coding help you know where to go :thumbsup:
  • alex89
  • Bronze Member
  • Bronze Member
  • User avatar
  • Posts: 239
  • Loc: Western Australia

Post 3+ Months Ago

I'm looking to create a site that manages several people and design jobs, any chance I could use your Team Scheduler? I'm willing to pay if you'll sell it to me :)
  • Sunil
  • Born
  • Born
  • Sunil
  • Posts: 1

Post 3+ Months Ago

Hi Ifrit,
I am new to php. I am going to develop page looks like complex team scheduler. I am unable to do the coding. could you please paste the code for team scheduler.

Post Information

  • Total Posts in this topic: 27 posts
  • Users browsing this forum: No registered users and 122 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
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.