Solved

employee schedule php/mysql within a dynamic table

Posted on 2013-02-01
10
991 Views
Last Modified: 2013-02-21
I am trying to create a dynamic table where an employees hours are posted...
The employees names are in another table so all I would need is the hours to the right of them...
I have the hours in a table in my mysql database setup as so..

emp_id | s_id | work | timestart | timeend | medleave | request | scrubflrs | temps | train | earlylater

emp_id = the employee id linking to the employees id of said employee in the users table
s_id = the store number of said employee
work = if they work that day or not. true of false 1 being true, 0 being false
timestart = in datetime format of the time they start working
timeend = see above just ending time
medleave = to show if they are on medical leave or not
request = to show if they requested the day off
scrubflrs = if they need to scrub the floors that night
temps = if they need to do temps or not
train = if they are training
earlylater = if they can leave early or later than when they are scheduled.

I'm trying to make it so that that data will display in a row and will create other rows for however many employees there are. One column for each day of the week and will display if they are off work too, all sorted by the emp_id.

I have been looking all over for solutions and I've seen some stuff, but nothing really that would help me in the way I'm looking for it to be...

here is essentially what I have thus far...
http://schedule.test.spydie.net/?store=3

I also added an image of what the layout of this table would be...

I know how I would do all the information on the backend of it. Just no idea how to display it on the frontend of the site. Any help would be greatly appreciated!
database-layout.png
0
Comment
Question by:Spydie
  • 5
  • 5
10 Comments
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38846218
So do you want to display timeend - timestart for each employee? Can you post the code you have right now? I really don't understand what exactly is your problem...
0
 

Author Comment

by:Spydie
ID: 38846460
yes sir and if they are on med leave or whichever.
no code as of yet. I'm clueless on what I'm suppose to do.  :(
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38846582
no code as of yet ?

How create you the ouptu seen here http://schedule.test.spydie.net/?store=3?
0
 

Author Comment

by:Spydie
ID: 38847360
I was meaning for the hours/dates sections...
Here is my code thus far for the schedule area:
<?php
	define('MAIN_DIR',dirname(__FILE__) . '/');
	define('INC_DIR',MAIN_DIR . 'includes/');
	define('REQ_DIR',MAIN_DIR . 'requires/');
	define('FUNC_DIR',MAIN_DIR . 'includes/functions/');
	define('LANG_DIR',MAIN_DIR . 'includes/lang/');
	include (FUNC_DIR . 'db.conn.php');
	include (LANG_DIR . 'en.php');
	require (FUNC_DIR . 'name_get.php');
	require (FUNC_DIR . 's_num_get.php');
?>

<head>
<title><?php echo lang('TITLE').$title; ?></title>
<LINK REL=stylesheet HREF="includes/css/main.css" TYPE="text/css">
</head>
<?php require (REQ_DIR . 'header.php'); ?></br>
	<table width="70%" align="center" border="0" cellspacing="0" cellpadding="0">
		<tr>
			<td>
				<table width="100%" height="60" border="0" class="round" cellpadding="0" cellspacing="0">
					<tr class="dates">
						<td height="5" width="7.5%" class="toplefttop">&nbsp;</td>
						<td height="5" width="6.5%" class="datestop" colspan="2" >01/23/2013</td>
						<td height="5" width="6.5%" class="datestop" colspan="2" >01/24/2013</td>
						<td height="5" width="6.5%" class="datestop" colspan="2" >01/25/2013</td>
						<td height="5" width="6.5%" class="datestop" colspan="2" >01/26/2013</td>
						<td height="5" width="6.5%" class="datestop" colspan="2" >01/27/2013</td>
						<td height="5" width="6.5%" class="datestop" colspan="2" >01/28/2013</td>
						<td height="5" width="6.5%" class="datestoptop" colspan="2" >01/29/2013</td>
					</tr>
					<tr class="days">
						<td height="15" width="7.5%" class="topleftbottom">&nbsp;</td>
						<td height="15" width="6.5%" class="datesbottom" colspan="2" ><div>Wednesday</div></td>
						<td height="15" width="6.5%" class="datesbottom" colspan="2" ><div>Thursday</div></td>
						<td height="15" width="6.5%" class="datesbottom" colspan="2" ><div>Friday</div></td>
						<td height="15" width="6.5%" class="datesbottom" colspan="2" ><div>Saturday</div></td>
						<td height="15" width="6.5%" class="datesbottom" colspan="2" ><div>Sunday</div></td>
						<td height="15" width="6.5%" class="datesbottom" colspan="2" ><div>Monday</div></td>
						<td height="15" width="6.5%" class="datesbottombottom" colspan="2" ><p>Tuesday</p></td>
					</tr>
					<tr>
						<td width="6%">
							<?php
							$emp = mysql_query("SELECT * FROM users
							WHERE s_id = '" .$s_num. "' OR s_vist = '" .$s_num. "' ORDER BY emp_id ASC");
							while($names = mysql_fetch_array($emp)) { 
							if ($names['show'] == '1'){?>
								<table width="100%" class="round1" border="1" cellspacing="0" cellpadding="0">
									<tr>
										<td height="7.5%" class="name"
											<div>
											<?php
											if ($names['showlast'] == 0)
											{echo $names['first'];}
											else
											{echo $names['first']. ' ' .$names['last'];} ?>
											</div>
										</td>
									</tr>
									<tr>
										<td class="phone">
											<div>
											<?php if ($names['showphone'] == '1')
											{echo $names['phone_num'];}
											else
											{echo '&nbsp;';} ?>
											</div>
										</td>
									</tr>
								</table>
							<?php }} ?>
							</td>
						</td>
						<td height="25" width="6.5%" colspan="2" class="hours">hours<p></td>
						<td height="25" width="6.5%" colspan="2" class="hours">hours<p></td>
						<td height="25" width="6.5%" colspan="2" class="hours">hours<p></td>
						<td height="25" width="6.5%" colspan="2" class="hours">hours<p></td>
						<td height="25" width="6.5%" colspan="2" class="hours">hours<p></td>
						<td height="25" width="6.5%" colspan="2" class="hours">hours<p></td>
						<td height="25" width="6.5%" colspan="2" class="hoursboth">hours<p></td>
					</tr>  
				</tr>
			</table>
		</td>
	</tr>
</td>
</tr>
</table>
</br>
<?php require (REQ_DIR . 'footer.php'); ?></br>

Open in new window

0
 

Author Comment

by:Spydie
ID: 38857952
no help for this, eh?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38858264
I apologize, but I had no time. Seeing your code, I think you need to do a single query to extract all data from both tables at once. First, you need to have an emplyee id in both table: do you have it? The best thing would be to use phpMyAdmin and export table structures (no data, of course, only the create statement) and post here the two create instructions so we can see the full table structure. Then, but I'll can't be fast, I can build a single query to build the table dinamically. The best query type is probably a LEFT JOIN, but I'm not so able with mysql.

You could also post a new question entitling it as LEFT JOIN PROBLEM, so all mysql experts will run to help you, but you'll need anyway to post both CREATE queries built with phpMyAdmin or similar program to allow them to see exactly how your tales are built.

Cheers
0
 

Author Comment

by:Spydie
ID: 38858531
Here are the table structures...

--
-- Table structure for table `hours`
--

CREATE TABLE IF NOT EXISTS `hours` (
  `u_id` int(10) NOT NULL,
  `s_id` int(15) NOT NULL,
  `s_vist` int(10) NOT NULL DEFAULT '1',
  `date` date NOT NULL,
  `work` int(1) NOT NULL DEFAULT '0',
  `timestart` time DEFAULT NULL,
  `timeend` time DEFAULT NULL,
  `medleave` int(1) NOT NULL DEFAULT '1',
  `request` int(1) NOT NULL DEFAULT '1',
  `scrubflrs` int(1) NOT NULL DEFAULT '1',
  `temps` int(1) NOT NULL DEFAULT '1',
  `train` int(1) NOT NULL DEFAULT '1',
  `earlylater` int(1) NOT NULL DEFAULT '1',
  KEY `u_id` (`u_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `u_id` int(3) NOT NULL AUTO_INCREMENT,
  `emp_id` int(2) NOT NULL,
  `first` text,
  `last` text,
  `showlast` int(1) NOT NULL DEFAULT '0',
  `area_code` char(3) NOT NULL DEFAULT '309',
  `phone_3` char(3) DEFAULT NULL,
  `phone_4` char(4) DEFAULT NULL,
  `showphone` int(1) NOT NULL DEFAULT '1',
  `s_id` varchar(255) NOT NULL DEFAULT '1',
  `s_vist` int(10) DEFAULT NULL,
  `show` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`u_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=42 ;

Open in new window

Thanks again, marqusG
0
 
LVL 30

Accepted Solution

by:
Marco Gasi earned 500 total points
ID: 38860324
Sorry, Spydie, but I really have no time to do it. So far I set up this query which should grab all needed values form the database (even if a LEFT JOIN would be better). The problem should be now to order data by day of week. For this you can refer to the MySql man page http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html and to this http://w3resource.com/mysql/date-and-time-functions/mysql-dayofweek-function.php.

You can also delete this question or close without awarding points and reopen it with a new name such as 'LEFT JOIN problem' or so.

            $query = <<<QRY
  SELECT u.u_id, u.first, u.last, u.showlast, u.showphone, u.phone_3, 
  h.u_id, h.s_id, h.date, h.timestart, h.timeend 
  FROM users AS u, hours AS h 
  WHERE 
    u.u_id = h.u_id 
  AND 
    (h.s_id = '$s_num' OR h.s_vist = '$s_num') 
  ORDER BY emp_id ASC
QRY;
            $emp = mysql_query($query);

Open in new window


Hope this can give a little help.

Cheers
Marco
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 38912727
Thanks for points, Spydie. I hope you have solved your problem and I'm sorry I coudn't help you better :(

Cheers
0
 

Author Comment

by:Spydie
ID: 38913985
You're welcome, thanks for the lil help. No I have not gotten anywhere with it yet. I've been busy at work to even attempt it.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now