0

I can't figure out while the table rows and columns are not display as intended. I have tried everything I could but no solution yet. I'm new to PHP/Mysql.

What I actually wanted to see as the output is something like this:

[B]$days_of_week_rows['week_day'][/B]
$period_rows['period']   $class_rows['class']  $time_rows['time']
$period_rows['period']   $class_rows['class']  $time_rows['time']
$period_rows['period']   $class_rows['class']  $time_rows['time']
$period_rows['period']   $class_rows['class']  $time_rows['time']

But instead I see something like this:

[B]$days_of_week_rows['week_day'[/B]
$period_rows['period']
$period_rows['period']
$period_rows['period']
$period_rows['period'] 
$class_rows['class']  
$class_rows['class']
$class_rows['class']
$class_rows['class']     
$time_rows['time']
$time_rows['time']
$time_rows['time']
$time_rows['time']

Here is the PHP Code

?>
<table width="100%" height="100%" cellspacing="0" cellpadding="0" border="1px">
<?php
$days_of_week_count = 1;
            while($days_of_week_count < 6)
            {
                $select_days_of_week = "SELECT week_dayID, week_day FROM week_day
                                        WHERE week_dayID = '".$days_of_week_count."'";
                $result_days_of_week = mysql_query($select_days_of_week) or die('Couldn\'t select from the week_day table' . mysql_error());
                while($days_of_week_rows = mysql_fetch_array($result_days_of_week))
                {
                    $week_dayID = $days_of_week_rows['week_dayID'];
                    echo "<tr><td colspan='3'>" . $days_of_week_rows['week_day'] . "</td></tr>";
                       //Select information from period
                        $period_select = "SELECT period FROM table_period
                                           INNER JOIN table_relate
                                             ON table_period.periodID = table_relate.periodID
                                          WHERE teacherID = '".$teacherID."' AND week_dayID = '".$week_dayID."'";
                        $period_result = mysql_query($period_select) or die('Couldn\'t select from table_period ' . mysql_error());
                        
                        while($period_rows = mysql_fetch_array($period_result))
                        {    
                            echo "<tr><td>" .$period_rows['period']. "</td></tr>";
                            
                        }
                        
                        //Select information from class
                        $class_select = "SELECT class FROM table_class
                                           INNER JOIN table_relate
                                             ON table_class.classID = table_relate.classID
                                          WHERE teacherID = '".$teacherID."' AND week_dayID = '".$week_dayID."'";
                        $class_result = mysql_query($class_select) or die('Couldn\'t select from table_class ' . mysql_error());
                        while($class_rows = mysql_fetch_array($class_result))
                        {
                            echo "<tr><td>" .$class_rows['class']. "</td></tr>";
                        }
                        
                        //Select information from time
                        $time_select = "SELECT time FROM table_time
                                           INNER JOIN table_relate
                                             ON table_time.timeID = table_relate.timeID
                                          WHERE teacherID = '".$teacherID."' AND week_dayID = '".$week_dayID."'";
                        $time_result = mysql_query($time_select) or die('Couldn\'t select from table_time ' . mysql_error());
                        while($time_rows = mysql_fetch_array($time_result))
                        {
                            echo "<tr><td>" .$time_rows['time']. "</td></tr>";
                        }
                        
                }
                    
                $days_of_week_count = $days_of_week_count  + 1;
            }
            echo "</table>";

and the html output code when I view source from the browser

<!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" xml:lang="en" lang="en">
<head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8" />
  <title>Welcome to UPSS | Add a Teacher's Time Table</title>
  <link rel="stylesheet" href="../../css/add_teacher_complete.css" type="text/css" />

  <body>
    <div id="header">
      <div id="left_header_content">

        <p class="school_name">University Preparatory Secondary School (UPSS)</p>
        <p class="school_slogan">Knowledge & Virtue.</p>
      </div>
    </div>
      <div id="wrapper">
        <p class="heading">Preview Newly Added Teacher</p>

        <div id="main_content">
          <table width="100%" height="100%" cellspacing="0" cellpadding="0" border="2px">
                       <tr><td colspan='3'>Monday</td></tr><tr><td>1</td></tr><tr><td>3</td></tr><tr><td>5</td></tr><tr><td>8</td></tr><tr><td>JS 1 Diamond</td></tr><tr><td>JS 1 Silver</td></tr><tr><td>JS 1 Silicon</td></tr><tr><td>JS 2 Mercury</td></tr><tr><td>8:10am - 8:55am</td></tr><tr><td>9:40am - 10:25am</td></tr><tr><td>11:10am - 11:55am</td></tr><tr><td>1:50pm - 2:30pm</td></tr><tr><td colspan='3>
           </table>

        </div>
      </div>
    </div>
  </body>
</html>

Please help me point out where I'm doing it wrong

3
Contributors
10
Replies
11
Views
5 Years
Discussion Span
Last Post by sality63
0

Every output you have, uses <tr></tr>, meaning a new row. You are trying to output on a per column basis, and that's not possible. You will need to change your query, so you will get your three values in each record, and then display them on a per row basis.

Edited by pritaeas: n/a

0

@pritaeas
I have been trying to figure that out. I'm new to this and I can't quite get my head around it. Any chance you could help?

0

Can you make a single query that returns all three values (period, class and time) ? If not, specify your tables and how they are linked.

Edited by pritaeas: n/a

1

Heres how i would do it, i don't know your mysql table structure enough to pull the time though. Whats `table_time` and `table_relate` and how are they linked to table_period?

<table width="100%" height="100%" cellspacing="0" cellpadding="0" border="1px">
<?php
function getTimetable($teacherID,$con = false){
	if(is_int($teacherID) || ctype_digit($teacherID)){
		$Q = 	"SELECT week_dayID, period FROM table_period"
				." INNER JOIN table_relate"
				." ON table_period.periodID = table_relate.periodID"
				." WHERE teacherID = '{$teacherID}'"
				//."AND week_dayID IN(0,1,2,3,4,5,6)"
				." GROUP BY week_dayID";
		
		if(!$con){
			$R = mysql_query($Q);
		}else{
			$R = mysql_query($Q,$con);
		}
		$periodData = array();
		while($row = mysql_fetch_assoc($R)){
			$periodData[$row['week_dayID']][] = $row;
		}
	}else{
		$periodData = false;
	}
	return $periodData;
}
$periodData = getTimetable($teacherID);
foreach($periodData as $k=>$v){
	echo "<tr>\r\n";
	echo "<td>{$k}</td>\r\n";
	echo "<td>{$v['period']}</td>\r\n";
	echo "<td>{}</td>\r\n";
	echo "</tr>\r\n";
}
?>
</table>
0

Please I have attached the tables in my database in this reply. All the table are related to table_relate.

Edited by sality63: n/a

Attachments db.jpg 42.92 KB
0

Please I have attached the tables in my database in this reply. All the table are related to table_relate.

How does this work?

<table width="100%" height="100%" cellspacing="0" cellpadding="0" border="1px">
<?php
function getTimetable($teacherID,$con = false){
	if(is_int($teacherID) || ctype_digit($teacherID)){
		
		$Q = 	"SELECT `tbl_wd`.`week_dayID`, `tbl_rel`.`periodID`, `tbl_time`.`time` FROM `table_weekday` `tbl_wd`"
				." LEFT JOIN `table_relate` `tbl_rel`"
				." ON `tbl_wd`.`week_dayID` = `tbl_rel`.`week_dayID`"
				." LEFT JOIN `table_time` `tbl_time`"
				." ON `tbl_rel`.`timeID` = `tbl_time`.`timeID`"
				." LEFT JOIN `table_period` `tbl_p`"
				." ON `tbl_rel`.`periodID` = `tbl_p`.`periodID`"
				." WHERE `tbl_rel`.`teacherID` = '{$teacherID}'"
				//." AND `tbl_wd`.`week_dayID` IN(0,1,2,3,4,5,6)"
				." GROUP BY `tbl_wd`.`week_dayID`,`tbl_rel`.`periodID`";
 
		if(!$con){
			$R = mysql_query($Q);
		}else{
			$R = mysql_query($Q,$con);
		}
		$periodData = array();
		while($row = mysql_fetch_assoc($R)){
			$periodData[$row['week_dayID']][] = $row;
		}
	}else{
		$periodData = false;
	}
	return $periodData;
}
$periodData = getTimetable($teacherID);
foreach($periodData as $k=>$v){
	echo "<tr>\r\n";
	echo "<td>{$k}</td>\r\n";
	echo "<td>{$v['period']}</td>\r\n";
	echo "<td>{$v['time']}</td>\r\n";
	echo "</tr>\r\n";
}
?>
</table>

I think it would be better to store the time in time format eg.

timeID, start, finish
1,08:10:00,08:50:00
2,09:00:00,09:50:00

Then you'll be able to make use of mysql's functions on dates and times.

also can't table_time just be merged into table_period?

periodID,start,finish
1,08:10:00,08:50:00
2,09:00:00,09:50:00

0

Heres how i would do it, i don't know your mysql table structure enough to pull the time though. Whats `table_time` and `table_relate` and how are they linked to table_period?

<table width="100%" height="100%" cellspacing="0" cellpadding="0" border="1px">
<?php
function getTimetable($teacherID,$con = false){
	if(is_int($teacherID) || ctype_digit($teacherID)){
		$Q = 	"SELECT week_dayID, period FROM table_period"
				." INNER JOIN table_relate"
				." ON table_period.periodID = table_relate.periodID"
				." WHERE teacherID = '{$teacherID}'"
				//."AND week_dayID IN(0,1,2,3,4,5,6)"
				." GROUP BY week_dayID";
		
		if(!$con){
			$R = mysql_query($Q);
		}else{
			$R = mysql_query($Q,$con);
		}
		$periodData = array();
		while($row = mysql_fetch_assoc($R)){
			$periodData[$row['week_dayID']][] = $row;
		}
	}else{
		$periodData = false;
	}
	return $periodData;
}
$periodData = getTimetable($teacherID);
foreach($periodData as $k=>$v){
	echo "<tr>\r\n";
	echo "<td>{$k}</td>\r\n";
	echo "<td>{$v['period']}</td>\r\n";
	echo "<td>{}</td>\r\n";
	echo "</tr>\r\n";
}
?>
</table>

I can't read your code very well. I'm new to php and function is there anyway you can edit my code for correction?

0

How does this work?

<table width="100%" height="100%" cellspacing="0" cellpadding="0" border="1px">
<?php
function getTimetable($teacherID,$con = false){
	if(is_int($teacherID) || ctype_digit($teacherID)){
		
		$Q = 	"SELECT `tbl_wd`.`week_dayID`, `tbl_rel`.`periodID`, `tbl_time`.`time` FROM `table_weekday` `tbl_wd`"
				." LEFT JOIN `table_relate` `tbl_rel`"
				." ON `tbl_wd`.`week_dayID` = `tbl_rel`.`week_dayID`"
				." LEFT JOIN `table_time` `tbl_time`"
				." ON `tbl_rel`.`timeID` = `tbl_time`.`timeID`"
				." LEFT JOIN `table_period` `tbl_p`"
				." ON `tbl_rel`.`periodID` = `tbl_p`.`periodID`"
				." WHERE `tbl_rel`.`teacherID` = '{$teacherID}'"
				//." AND `tbl_wd`.`week_dayID` IN(0,1,2,3,4,5,6)"
				." GROUP BY `tbl_wd`.`week_dayID`,`tbl_rel`.`periodID`";
 
		if(!$con){
			$R = mysql_query($Q);
		}else{
			$R = mysql_query($Q,$con);
		}
		$periodData = array();
		while($row = mysql_fetch_assoc($R)){
			$periodData[$row['week_dayID']][] = $row;
		}
	}else{
		$periodData = false;
	}
	return $periodData;
}
$periodData = getTimetable($teacherID);
foreach($periodData as $k=>$v){
	echo "<tr>\r\n";
	echo "<td>{$k}</td>\r\n";
	echo "<td>{$v['period']}</td>\r\n";
	echo "<td>{$v['time']}</td>\r\n";
	echo "</tr>\r\n";
}
?>
</table>

I think it would be better to store the time in time format eg.

timeID, start, finish
1,08:10:00,08:50:00
2,09:00:00,09:50:00

Then you'll be able to make use of mysql's functions on dates and times.

also can't table_time just be merged into table_period?

periodID,start,finish
1,08:10:00,08:50:00
2,09:00:00,09:50:00

I will try and merge that. What will be the benefit of storing the time in a time format? Considering the application did not rely on actual time. Thanks for your help

0

How does this work?

<table width="100%" height="100%" cellspacing="0" cellpadding="0" border="1px">
<?php
function getTimetable($teacherID,$con = false){
	if(is_int($teacherID) || ctype_digit($teacherID)){
		
		$Q = 	"SELECT `tbl_wd`.`week_dayID`, `tbl_rel`.`periodID`, `tbl_time`.`time` FROM `table_weekday` `tbl_wd`"
				." LEFT JOIN `table_relate` `tbl_rel`"
				." ON `tbl_wd`.`week_dayID` = `tbl_rel`.`week_dayID`"
				." LEFT JOIN `table_time` `tbl_time`"
				." ON `tbl_rel`.`timeID` = `tbl_time`.`timeID`"
				." LEFT JOIN `table_period` `tbl_p`"
				." ON `tbl_rel`.`periodID` = `tbl_p`.`periodID`"
				." WHERE `tbl_rel`.`teacherID` = '{$teacherID}'"
				//." AND `tbl_wd`.`week_dayID` IN(0,1,2,3,4,5,6)"
				." GROUP BY `tbl_wd`.`week_dayID`,`tbl_rel`.`periodID`";
 
		if(!$con){
			$R = mysql_query($Q);
		}else{
			$R = mysql_query($Q,$con);
		}
		$periodData = array();
		while($row = mysql_fetch_assoc($R)){
			$periodData[$row['week_dayID']][] = $row;
		}
	}else{
		$periodData = false;
	}
	return $periodData;
}
$periodData = getTimetable($teacherID);
foreach($periodData as $k=>$v){
	echo "<tr>\r\n";
	echo "<td>{$k}</td>\r\n";
	echo "<td>{$v['period']}</td>\r\n";
	echo "<td>{$v['time']}</td>\r\n";
	echo "</tr>\r\n";
}
?>
</table>

I think it would be better to store the time in time format eg.

timeID, start, finish
1,08:10:00,08:50:00
2,09:00:00,09:50:00

Then you'll be able to make use of mysql's functions on dates and times.

also can't table_time just be merged into table_period?

periodID,start,finish
1,08:10:00,08:50:00
2,09:00:00,09:50:00

Pls can you re-write this withouth using function and use a more descriptive variable name. Ths will enable me read the code better. I came to php from html/css background so i don't know much about coding. thanks

0

How does this work?

<table width="100%" height="100%" cellspacing="0" cellpadding="0" border="1px">
<?php
function getTimetable($teacherID,$con = false){
	if(is_int($teacherID) || ctype_digit($teacherID)){
		
		$Q = 	"SELECT `tbl_wd`.`week_dayID`, `tbl_rel`.`periodID`, `tbl_time`.`time` FROM `table_weekday` `tbl_wd`"
				." LEFT JOIN `table_relate` `tbl_rel`"
				." ON `tbl_wd`.`week_dayID` = `tbl_rel`.`week_dayID`"
				." LEFT JOIN `table_time` `tbl_time`"
				." ON `tbl_rel`.`timeID` = `tbl_time`.`timeID`"
				." LEFT JOIN `table_period` `tbl_p`"
				." ON `tbl_rel`.`periodID` = `tbl_p`.`periodID`"
				." WHERE `tbl_rel`.`teacherID` = '{$teacherID}'"
				//." AND `tbl_wd`.`week_dayID` IN(0,1,2,3,4,5,6)"
				." GROUP BY `tbl_wd`.`week_dayID`,`tbl_rel`.`periodID`";
 
		if(!$con){
			$R = mysql_query($Q);
		}else{
			$R = mysql_query($Q,$con);
		}
		$periodData = array();
		while($row = mysql_fetch_assoc($R)){
			$periodData[$row['week_dayID']][] = $row;
		}
	}else{
		$periodData = false;
	}
	return $periodData;
}
$periodData = getTimetable($teacherID);
foreach($periodData as $k=>$v){
	echo "<tr>\r\n";
	echo "<td>{$k}</td>\r\n";
	echo "<td>{$v['period']}</td>\r\n";
	echo "<td>{$v['time']}</td>\r\n";
	echo "</tr>\r\n";
}
?>
</table>

I think it would be better to store the time in time format eg.

timeID, start, finish
1,08:10:00,08:50:00
2,09:00:00,09:50:00

Then you'll be able to make use of mysql's functions on dates and times.

also can't table_time just be merged into table_period?

periodID,start,finish
1,08:10:00,08:50:00
2,09:00:00,09:50:00

The table_relate is the table that stores the id of the other tables.

Edited by sality63: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.