Work with data from two tables

concrete · January 17, 2012

Hi all,

I am lost again in this PHP world. I am trying to set up a grade book for my reporting system. I have got totally lost in the code and I am need of some help.

Many Thanks

F

 <?php 
function doquery($query) {
	$db = "prs";
  	$link = mysql_connect("localhost","gandalf","A3tti2ca")
    	or die("Could not connect to server! Error: ".mysql_error());
  	mysql_select_db($db,$link)
    	or die("Could not select $db! Error: ".mysql_error());
  	$result = mysql_query($query,$link)
    	or $result = "Query Error!<p>Query: $query<p>Error: ".mysql_error();
  	mysql_close($link);
  	return($result);
}

function parseresults($result) {
	 if ($line = mysql_fetch_array('$result', MYSQL_ASSOC)) {
        displayform($line);
	 } 
        else {
   	print "A database problem has occurred. Contact the database administrator<p>";
  }
}


function displayform($line) {
<<<HTML
	 <form action="gradebook2.php" method="post">
  		<H4>Thank You, <p>
HTML;

	 print $line['firstname']." ".$line['surname']." (#".$line['upn'].") in set ".$line['class_set'];
	 print ' has been sucessfully added to the system.<p>';
         echo"<form id=\"Gradebook\" name=\"gradebook\" method=\"post\" action=\"gradebook2.php\">
	     	<table width=\"77%\" border=\"1\" align=\"center\" cellpadding=\"5\" cellspacing=\"0\" bordercolor=\"#de6057\">
       			 <tr>
         			 <td width=\"45%\" class=\"footer\">
				 <label class=\"form_labels\">Coursework Mark</label>
                                 </td>
         			 
			 <td width=\"55%\">
			 	<input name=\"cw_mark\" type=\"text\" id=\"cw_mark\" value=".$line['english_cw_mark']." tabindex=\"1\" size=\"6\" maxlength=\"4\" />
				<input type=\"hidden\" name=\"upn\" value=".$line['upn'].">
			</td>
        		</tr>
        		
		<tr>
          			<td class=\"footer\">Coursework Comment </td>
         			 <td>
			 	<label>
				 	<textarea name=\"cw_comment\" id=\"cw_comment\" cols=\"35\" rows=\"7\" value=".$line['english_cw_commnents']."></textarea>
       					</label>
			</td>
       			</tr>
        
		<tr>
         			 <td class=\"footer\"><label class=\"form_labels\">Exam Mark</label></td>
         			 <td>
				<input name=\"exam_mark\" type=\"text\" id=\"exam_mark\" value=".$line['english_exam_mark']." tabindex=\"3\" size=\"6\" maxlength=\"4\">
			</td>
        		</tr>
       
   		<tr>
          			<td class=\"form_labels\">Exam Comment </td>
          		        <td>	
			     <label>
            				<textarea name=\"exam_comment\" id=\"exam_comment\" cols=\"35\" rows=\"7\" value=".$line['english_exam_comments']."></textarea>
          			     </label>
		         </td>
        		</tr>
       
   		<tr>
          			<td class=\"form_labels\"><label>Commendation in Poetry</label></td>
         			<td>
			 	<select name=\"com_poetry\" id=\"com_poetry\" value=".$line['com_poetry'].">
              				          <option value=\"No Commendation\" selected=\"selected\">No Commendation</option>
		             	          <option value=\"Commendation Awarded\">Commendation Awarded</option>
				</select>
			</td>
	       </tr>
        
	       <tr>
	            <td class=\"form_labels\"><label>Commendation in Creative Writing</label></td>
        		    <td>
			<select name=\"com_creative\" id=\"com_creative\" value=".$line['com_creative'].">
              			     <option selected=\"selected\">No Commendation</option>
             			     <option value=\"Commendation Awarded\">Commendation Awarded</option>
			</select>
         		    </td>
        	        </tr>
        
		<tr>
          			<td class=\"form_labels\">Commendation in Literary Interpretation</td>
         			 <td>
				 	<select name=\"com_lit_int\" id=\"com_lit_int\" value=".$line['com_lit_int'].">
            				<option value=\"No Commendation\" selected=\"selected\">No Commendation</option>
           					<option value=\"Commendation Awarded\">Commendation Awarded</option>
					</select>
				</td>
        		</tr>
        
		<tr>
	         	<td class=\"form_labels\"><label>Overall Comments</label></td>
        		  	<td>
			    <textarea name=\"overall_comments\" id=\"overall_comments\" cols=\"35\" rows=\"7\" value=".$line['english_overall_commnents'].'"></textarea>
			 </td>
        		</tr>
       
   		<tr>
          			<td> </td>
          			<td>
				<input type=\"reset\" name=\"Reset\" id=\"Reset\" value=\"Reset Form\" tabindex=\"12\" />   
		  		<input type=\"submit\" name=\"submit\" id=\"submit\" value=\"Submit Form\" tabindex=\"11\" />
			</td>
        		</tr>
      		</table>
  </form>";
}


$student_upn = (integer) $_POST["stu_upn"];
$query = "select count(*) as numberRecords from english where upn = ".$student_upn."";
$result = doquery($query);
$row = mysql_fetch_array($result);

if ($row['numberRecords'] == 0)  
{
	$query = "select students.upn from students where students.upn = '".$student_upn."'";
	$result = doquery($query);
	$row = mysql_fetch_array($result);
	$cw_mark = $row['english_cw_mark'];
	$cw_comment = $row['english_cw_comments'];
	$exam_mark = $row['english_exam_mark'];
	$exam_comment = $row['english_exam_comments'];
	$com_poetry = $row['com_poetry'];
	$com_creative = $row['com_creative'];
	$com_lit_int = $row['com_lit_int'];
	$overall_comment = $row['english_overall_comments'];
	$insert = "insert into english (upn, english_cw_mark, english_cw_comments, english_exam_mark, english_exam_comments, com_poetry, com_creative, com_lit_int, english_overall_ comments) values (".$student_upn.",'".$cw_mark."','".$cw_comment."', '".$exam_mark.",'".$exam_comment."','".$com_poetry."','".$com_creative."','".$com_lit_int."','".$overall_comment."',0);
	print $insert;
	doquery($insert);
}

	$query =  select students.upn as upn, firstname, surname, class_set, from customers left join english on students.upn = english.upn where students.upn = ".$student_upn.";	
	$result = doquery($query);

	if (is_resource($result)) {
			parseresults($result);
	} else {
  			print "$result";
	}
	print "<a href=\"gradebook.php\"><h4>Grade Another Student</h4></a>";
?>

SergeiSS · January 17, 2012

Do you see that your code is highlighted? I think you see. Check it - in the beginning it highlighted correctly, but at the end it's highlighted incorrectly. It means that you lose something, somewhere in the middle there is an error.

Check colors - I hope that you are able to find the place with error It's easy to do.

PS. If you fail to find I can help of course.

concrete · January 17, 2012

Hi SergeiSS,

I have been looking at this for ages and I can't figure out what I have missed out or put in the wrong place. I know it is going to be something silly. I have looked at the for so long I just see red blobs.

Muddy_Funster · January 17, 2012

youre not closing your <<<HTML block at the end of the function

concrete · January 17, 2012

Hi Muddy_Funster,

Thanks again. I have closed the HTML block now. Really out of my depth on this one. I have spent all day banging my head against a php wall.

I think my problem is with the echo command but i'm not sure how to fix it.

Muddy_Funster · January 17, 2012

ok, I don't know what this line means...

$student_upn = (integer) $_POST["stu_upn"];

But I also don't know what's going wrong...repost your updated code and let me know what / where it's going wrong.

scootstah · January 17, 2012

On line 103 you have a stray single quote.

value=".$line['english_overall_commnents'].'"

Also, you need to close HEREDOC blocks like this, with no whitespace:

<<<HTML
	 <form action="gradebook2.php" method="post">
  		<H4>Thank You, <p>
HTML;

Proletarian · January 17, 2012

Find this line:

<textarea name=\"overall_comments\" id=\"overall_comments\" cols=\"35\" rows=\"7\" value=".$line['english_overall_commnents'].'"></textarea>

Do you see the extra apostrophe?

value=".$line['english_overall_commnents'].'"></textarea>

Do you see it now? It's highlighted red.

commnents'].'"></textarea>

Find this apostrophe and delete it and I'll bet it will work.

concrete · January 17, 2012

Hi guys, can't thank you all enough for all your help. I have made all the suggested changes so far.

muddy_funster - my tutor gave me this line (i think)

$student_upn = (integer) $_POST["stu_upn"];

His comments on the line

The syntax of 
//       $student_upn = (integer) $_POST["stu_upn"];
// may seem a bit odd. It means the following:
// Assign into the variable $student_upn the value of the string
// $_POST["stu_upn"], but before the assignment is made, ensure that
// the contents of $_POST["stu_upn"] are translated into an integer.
// This translating (or 'casting') is necessary so that the database 
// query contains the primary key in the required format.

I am not getting the following error message

( ! ) Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in ..\gradebook1.php on line 82

which relates to this line

print $line['firstname']." ".$line['surname']." (#".$line['upn'].") in set ".$line['class_set'];

here is the code in full with amendments

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Pupil Record System</title>
<link href="css/main.css" rel="stylesheet" type="text/css" />

<style type="text/css">
<!--
.oneColElsCtrHdr #container #mainContent #form1 p label {
text-align: left;
}
td {
text-align: left;
}
.oneColElsCtrHdr #container #mainContent #Add_Student table {
text-align: left;
}
-->
</style>
</head>

<body class="oneColElsCtrHdr">

<div id="container">
  <div id="header">
       <img src="images/crs.png" width="276" height="100" alt="CRS" />

  
    <!-- Date & Time -->
    <div class="time">
      <?php
        $mydate = date("D j F, Y - H:i:s");
	print "<br><h4>".$mydate."</h4>";        
      ?>
    </div><!-- end Date & Time .time -->

  <!-- end #header --></div>
  <div id="mainContent">
  
  <!-- Top Menu -->

	<ul>
    	<a href="index.php" class="myButton">Home</a>
		<a href="add_student.php" class="myButton">Add Student</a>
    	<a href="del_student.php" class="myButton">Delete Student</a>
    	<a href="gradebook.php" class="myButton">Gradebook</a>
    <a href="update_record.php" class="myButton">Update Record</a>
    	<a href="student_report.php" class="myButton">Student Report</a>
    </ul>
    

  <!-- End Top Menu -->
    <h2>Gradebook</h2>   
<?php 
function doquery($query) {
	$db = "prs";
  	$link = mysql_connect("localhost","gandalf","bella")
    	or die("Could not connect to server! Error: ".mysql_error());
  	mysql_select_db($db,$link)
    	or die("Could not select $db! Error: ".mysql_error());
  	$result = mysql_query($query,$link)
    	or $result = "Query Error!<p>Query: $query<p>Error: ".mysql_error();
  	mysql_close($link);
  	return($result);
}

function parseresults($result) {
	 if ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
     displayform($line);
	 } else {
   	 print "A database problem has occurred. Contact the database administrator<p>";
  }
}

function displayform($line) {
print <<<HTML
	 <form action="gradebook2.php" method="post">
  		<H4>Thank You,

print $line['firstname']." ".$line['surname']." (#".$line['upn'].") in set ".$line['class_set'];
print ' has been sucessfully added to the system.</h4>';
}
HTML;	

echo " <form id="Gradebook" name="gradebook" method="post" action="gradebook2.php">
		<table width="77%" border="1" align="center" cellpadding="5" cellspacing="0" bordercolor="#de6057">
       			 <tr>
         			 <td width="45%" class="footer">
				 	<label class="form_labels">Coursework Mark</label></td>
         			 
				 <td width="55%">
				 	<input name=\"cw_mark\" type=\"text\" id=\"cw_mark\" value=".$line['english_cw_mark']." size=\"6\" maxlength=\"4\" />
					<input type=\"hidden\" name=\"upn\" value=".$line['upn'].">
				 </td>
        		</tr>
        		
			<tr>
          			<td class=\"footer\">Coursework Comment </td>
         			 <td>
				 	<label>
				 		<textarea name=\"cw_comment\" id=\"cw_comment\" cols=\"35\" rows=\"7\" value=".$line['english_cw_commnents']."></textarea>
       					</label>
				</td>
       			</tr>
        
			<tr>
         			 <td class=\"footer\"><label class=\"form_labels\">Exam Mark</label></td>
         			 <td>
				 	<input name=\"exam_mark\" type=\"text\" id=\"exam_mark\" value=".$line['english_exam_mark']." size=\"6\" maxlength=\"4\">
				</td>
        		</tr>
       
   			<tr>
          			<td class=\"form_labels\">Exam Comment </td>
          			<td>	
					<label>
            				<textarea name=\"exam_comment\" id=\"exam_comment\" cols=\"35\" rows=\"7\" value=".$line['english_exam_comments']."></textarea>
          				</label>
				</td>
        		</tr>
       
   			<tr>
          			<td class=\"form_labels\"><label>Commendation in Poetry</label></td>
         			 <td>
				 	<select name=\"com_poetry\" id=\"com_poetry\" value=".$line['com_poetry'].">
              				<option value=\"No Commendation\" selected=\"selected\">No Commendation</option>
		             	<option value=\"Commendation Awarded\">Commendation Awarded</option>
					</select>
				</td>
	       </tr>
        
		   <tr>
	          <td class=\"form_labels\"><label>Commendation in Creative Writing</label></td>
        		  <td>
			  	<select name=\"com_creative\" id=\"com_creative\" value=".$line['com_creative'].">
              			<option selected=\"selected\">No Commendation</option>
             			<option value=\"Commendation Awarded\">Commendation Awarded</option>
				</select>
         		  </td>
        		</tr>
        
			<tr>
          			<td class=\"form_labels\">Commendation in Literary Interpretation</td>
         			 <td>
				 	<select name=\"com_lit_int\" id=\"com_lit_int\" value=".$line['com_lit_int'].">
            				<option value=\"No Commendation\" selected=\"selected\">No Commendation</option>
           					<option value=\"Commendation Awarded\">Commendation Awarded</option>
					</select>
				</td>
        		</tr>
        
			<tr>
	         	 <td class=\"form_labels\"><label>Overall Comments</label></td>
        		  	 <td>
			  		<textarea name=\"overall_comments\" id=\"overall_comments\" cols=\"35\" rows=\"7\" value=".$line['english_overall_commnents']."></textarea>
			 	 </td>
        		</tr>
       
   			<tr>
          			<td> </td>
          			<td>
					<input type=\"reset\" name=\"Reset\" id=\"Reset\" value=\"Reset Form\" />   
		  			<input type=\"submit\" name=\"submit\" id=\"submit\" value=\"Submit Form\" />
				</td>
        		</tr>
      		</table>
  </form>";
}

$student_upn = (integer) $_POST["stu_upn"];
$query = "select count(*) as numberRecords from english where upn = ".$student_upn."";
$result = doquery($query);
$row = mysql_fetch_array($result);

if ($row['numberRecords'] == 0)  
{
	$query = "select students.upn from students where students.upn = '".$student_upn."'";
	$result = doquery($query);
	$row = mysql_fetch_array($result);
	$cw_mark = $row['english_cw_mark'];
	$cw_comment = $row['english_cw_comments'];
	$exam_mark = $row['english_exam_mark'];
	$exam_comment = $row['english_exam_comments'];
	$com_poetry = $row['com_poetry'];
	$com_creative = $row['com_creative'];
	$com_lit_int = $row['com_lit_int'];
	$overall_comment = $row['english_overall_comments'];
	$insert = "insert into english (upn, english_cw_mark, english_cw_comments, english_exam_mark, english_exam_comments, com_poetry, com_creative, com_lit_int, english_overall_ comments) values (".$student_upn.",'".$cw_mark."','".$cw_comment."', '".$exam_mark.",'".$exam_comment."','".$com_poetry."','".$com_creative."','".$com_lit_int."','".$overall_comment."',0);
	print $insert;
	doquery($insert);
}

	$query =  SELECT students.upn as upn, firstname, surname, class_set, from students left join english on students.upn = english.upn where students.upn ='".$student_upn."'";	
	$result = doquery($query);

	if (is_resource($result)) {
			parseresults($result);
	} else {
  			print "$result";
	}
	print "<a href=\"gradebook.php\"><h4>Grade Another Student</h4></a>";
?>
    <!-- end #mainContent -->
  </div>
   <!-- Footer -->
  <div id="footer">
    <p class="footer">
	 	<a href="index.php" class="footer">Home</a>    |   
    	<a href="add_student.php" class="footer">Add Student</a>    |   
    <a href="del_student.php" class="footer">Delete Student</a>    |   
    	<a href="gradebook.php" class="footer">Gradebook</a>    |   
    <a href="update_record.php" class="footer">Update Record</a>    |   
    	<a href="student_report.php" class="footer">Student Report</a>
    </p>
  <!-- end Footer #footer --></div></div>
<!-- end #container --></div>
</body>
</html>

Muddy_Funster · January 18, 2012

you have the print command within your <<<HTML, that's why it's getting upset with that one. also, when including array values within parsable strings wrap them within curly braces: {array['key']} Also, your tutors comment on that line is antiquated logic, both PHP and MySQL are robust enough to work out whither you want to use a string or integer value. e.g.

 <?php $a="3";
$b= 7;
$c = $a+$b;
echo $c; //shows 10, even though $a is a string

concrete · January 18, 2012

That matches my tutor then, ancient and not much help!!

It going to be a long long day!

Thanks

Fergal

concrete · January 18, 2012

OK, I have given up on the old code and I have started again! I have duplicated a piece of code that I have used in another page to call records from the database and display them in the fields. I think some of you gave me a hand to get that work as well.

I have got this partially working as it calls some of the records from the database but not all the records. It displays the exam and coursework marks but it doesn't show the comments. :confused:

Also how do I join the two table so the records are connected through the primary key 'upn'?

Here is the code.

    <?php
     
    function doquery($query) {
    $db = "prs";
    $link = mysql_connect("localhost","gandalf","bella")
    or die("Could not connect to server! Error: ".mysql_error());
    mysql_select_db($db,$link)
    or die("Could not select $db! Error: ".mysql_error());
    $result = mysql_query($query,$link)
    or $result = "Query Error!<p>Query: $query<p>Error: ".mysql_error();
    return($result);
    mysql_close($link);
    }
     
    if (isset($_POST['submit'])) { 
    $success=0;
     
    $student_upn = mysql_real_escape_string($_POST["stu_upn"]);
    $query = "select count(*) as numberRecords from english where upn = '".$student_upn."'";
    $result = doquery($query);
    $row = mysql_fetch_array($result);
     
    if ($row['numberRecords'] == 0)
    {
    $query = "select upn from students where upn = '".$student_upn."'";
    $result = doquery($query);
    $row = mysql_fetch_array($result);
    $cw_mark = $row['english_cw_mark'];
    $cw_comment = $row['english_cw_comments'];
    $exam_mark = $row['english_exam_mark'];
    $exam_comment = $row['english_exam_comments'];
    $com_poetry = $row['com_poetry'];
    $com_creative = $row['com_creative'];
    $com_lit_int = $row['com_lit_int'];
    $overall_comment = $row['english_overall_comments'];
    $insert = "insert into english (upn, english_cw_mark, english_cw_comments, english_exam_mark, english_exam_comments, com_poetry, com_creative, com_lit_int, english_overall_ comments) values ('".$student_upn."','".$cw_mark."','".$cw_comment."', '".$exam_mark.",'".$exam_comment."','".$com_poetry."','".$com_creative."','".$com_lit_int."','".$overall_comment."')";
    doquery($insert);
    }
     
    $query = "select * from students where upn = '".$student_upn."'";
    $result = doquery($query);
    if ($result) {
    $success=1;
    }
    }
    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Pupil Record System</title>
    <link href="css/main.css" rel="stylesheet" type="text/css" />
     
    <style type="text/css">
    <!--
    .oneColElsCtrHdr #container #mainContent #form1 p label {
    text-align: left;
    }
    td {
    text-align: left;
    }
    .oneColElsCtrHdr #container #mainContent #Add_Student table {
    text-align: left;
    }
    -->
    </style>
    </head>
     
    <body class="oneColElsCtrHdr">
     
    <div id="container">
    <div id="header">
    <img src="images/crs.png" width="276" height="100" alt="CRS" />
     
     
    <!-- Date & Time -->
    <div class="time">
    <?php
    $mydate = date("D j F, Y - H:i:s");
    print "<br><h4>".$mydate."</h4>";
    ?>
    </div><!-- end Date & Time .time -->
     
    <!-- end #header --></div>
    <div id="mainContent">
     
    <!-- Top Menu -->
     
    <ul>
    <a href="index.php" class="myButton">Home</a>
    <a href="add_student.php" class="myButton">Add Student</a>
    <a href="del_student.php" class="myButton">Delete Student</a>
    <a href="gradebook.php" class="myButton">Gradebook</a>
    <a href="update_record.php" class="myButton">Update Record</a>
    <a href="student_report.php" class="myButton">Student Report</a>
    </ul>
     
     
    <!-- End Top Menu -->
    <h2>Gradebook</h2>
    
    <?php 
 $id=$_REQUEST['id']; 
 $firstname=$_REQUEST['firstname'];
 $surname=$_REQUEST['surname'];
 $db='prs';

 $link=mysql_connect("localhost",".........","......") or die("Cannot Connect to the database!");

 mysql_select_db($db,$link) or die ("Cannot select the database!");
 $query="SELECT * FROM english WHERE english.upn='".$id."'";

	 $resource=mysql_query($query,$link) or die ("An unexpected error occured while trying to <b>modify</b> the record, Please try again!");
	  $result=mysql_fetch_array($resource);
	  
	echo "<h4>You have accessed (#".$id.") ".$firstname." ".$surname."'s record!</h4>";
	  
 ?>
     
      <form action="gradebook2.php" method="post">
     
    <form id="Gradebook" name="gradebook" method="post" action="gradebook2.php">
    <table width="77%" border="1" align="center" cellpadding="5" cellspacing="0" bordercolor="#de6057">
    <tr>
    <td width="45%" class="footer">
        <input type="hidden" name="upn" value="<?php echo $result[0] ?>">
    <label class="form_labels">Coursework Mark</label></td>

     
    <td width="55%">
    <input name="cw_mark" type="text" id="cw_mark" value="<?php echo $result[1] ?>" size="6" maxlength="4" />
    </td>
    </tr>
     
    <tr>
    <td class="footer">Coursework Comment </td>
    <td>
    <label>
    <textarea name="cw_comment" id="cw_comment" cols="35" rows="7" value="<?php echo $result[2] ?>"></textarea>
    </label>
    </td>
    </tr>
     
    <tr>
    <td class="footer"><label class="form_labels">Exam Mark</label></td>
    <td>
    <input name="exam_mark" type="text" id="exam_mark" value="<?php echo $result[3] ?>" size="6" maxlength="4">
    </td>
    </tr>
     
    <tr>
    <td class="form_labels">Exam Comment </td>
    <td>
    <label>
    <textarea name="exam_comment" id="exam_comment" cols="35" rows="7" value="<?php echo $result[4] ?>"></textarea>
    </label>
    </td>
    </tr>
     
    <tr>
    <td class="form_labels"><label>Commendation in Poetry</label></td>
    <td>
    <select name="com_poetry" id="com_poetry" value="<?php echo $result[5] ?>">
    <option value="No Commendation" selected="selected">No Commendation</option>
    <option value="Commendation Awarded">Commendation Awarded</option>
    </select>
    </td>
    </tr>
     
    <tr>
    <td class="form_labels"><label>Commendation in Creative Writing</label></td>
    <td>
    <select name="com_creative" id="com_creative" value="<?php echo $result[6] ?>">
    <option selected="selected">No Commendation</option>
    <option value="Commendation Awarded">Commendation Awarded</option>
    </select>
    </td>
    </tr>
     
    <tr>
    <td class="form_labels">Commendation in Literary Interpretation</td>
    <td>
    <select name="com_lit_int" id="com_lit_int" value="<?php echo $result[7] ?>">
    <option value="No Commendation" selected="selected">No Commendation</option>
    <option value="Commendation Awarded">Commendation Awarded</option>
    </select>
    </td>
    </tr>
     
    <tr>
    <td class="form_labels"><label>Overall Comments</label></td>
    <td>
    <textarea name="overall_comments" id="overall_comments" cols="35" rows="7" value="<?php echo $result[8] ?>"></textarea>
    </td>
    </tr>
     
    <tr>
    <td> </td>
    <td>
    <input type="reset" name="Reset" id="Reset" value="Reset Form" />   
    <input type="submit" name="submit" id="submit" value="Submit Form" />
    </td>
    </tr>
    </table>
    </form>
     
    <h4><a href="gradebook.php">Grade Another Student</a></h4>
     
    <!-- end #mainContent -->
    </div>
    <!-- Footer -->
    <div id="footer">
    <p class="footer">
    <a href="index.php" class="footer">Home</a>    |   
    <a href="add_student.php" class="footer">Add Student</a>    |   
    <a href="del_student.php" class="footer">Delete Student</a>    |   
    <a href="gradebook.php" class="footer">Gradebook</a>    |   
    <a href="update_record.php" class="footer">Update Record</a>    |   
    <a href="student_report.php" class="footer">Student Report</a>
    </p>
    <!-- end Footer #footer --></div></div>
    <!-- end #container --></div>
    </body>
    </html>

Muddy_Funster · January 18, 2012

I don't see any reason for the comments not to show, are there comments in both tables?

to join the tables together you would use JOIN in your sql FROM clause e.g.

SELECT first_name, last_name, job_title, job_description FROM employee INNER JOIN job_list ON employee.employee_number = job_list.employee_number

This is joining the tables employee, which holds the name information, with the job_list table, which holds information about the jobs the employees have. employee_number is the primary key field in the employee table, and is included as a forign key field in the job_list table. the forign key is irrelevent for this discussion, it could be that there is no pre-established relationship at all. however the field must exist so that each table can refference each other. using INNER JOIN generates a one-to-one relationship so you will get back results for each entry of employee_number that is present as the same value in both tables. Any values that do not match, for example a job is available and has no one working in that possition, and thus no employee_number associated with it, then it will not be included. The other main JOIN is LEFT JOIN. This is used to create a one-to-many relationship in the same manner.

concrete · January 18, 2012

Hi Muddy_Funster

No the comments are only in the english table!!!

I have attached an image of both table structures.

Thanks

Muddy_Funster · January 18, 2012

ok, so how are you populating your comments? I thought is was from the INSERT query whuch is looking for them in the students table...

    $query = "select upn from students where upn = '".$student_upn."'";
    $result = doquery($query);
    $row = mysql_fetch_array($result);
   ...
    $overall_comment = $row['english_overall_comments'];

also, could you list the fields that you need to return from each table.

concrete · January 18, 2012

What I had hope to do was the following:

when I access the gradebook.php page I am presented with a list of student data. (see fig1)
When I click on the grade icon in the far right hand column I am then directed to gradebook1.php (see fig 2)
when I access gradebook1, the idea was that if there was already information in there then I could update the current information and if there was no information then I could add the information.

I hope this makes sense.

ok, so how are you populating your comments?

Honestly, I really don't know anymore.

Am I really badly off track or is it just a matter of tidying up my poor coding?

Thanks again

Muddy_Funster · January 18, 2012

looks like the only place the comment is going to come from is the texbox on the form. so there is no point in trying to select it from the student table. It may be time to take a step back and have a deap breath, then come at it again from the ground up.

concrete · January 18, 2012

Hi Muddy_Funster,

Unfortunately I don't have that luxury as I have to submit this on Tuesday! Thanks for all your help, it was much appreciated.

Sign In

Work with data from two tables

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information