PHP RSS PHP RSS

update student record

Thread Solved

•
•
Join Date: Dec 2008
Posts: 10
Reputation: trtcom1 is an unknown quantity at this point 
Solved Threads: 0
trtcom1 trtcom1 is offline Offline
Newbie Poster

update student record

 
0
  #1
Jul 22nd, 2009
Hi there,
I am having problems with editing/updating a record.
I have 3 tables:
Student (studentNum,firstName,lastName,ClassRoom),
course (courseID,courseTotalCapacity),
course_allocation (studentNum,courseID).
I have 3 courses, MATHS=101,BIOLOGY=102,CHEMISTRY=103.
The total number of students allowed for each of the 3 courses is courseTotalCapacity = 20.
Meaning that only 20 students can register for MATHS, 20 for BIOLOGY and so on.
The classes are taught in 5 class rooms (Maths=2, Bio=1,Chemistry=2).
Currently my code allows students to register for a course as long as the course is not full.
And registered student from one course can change to another course as long as the course is not full.
My problem:
For example for the Maths course,if the course is not full, my code allows me to edit records and move students from one class room to another class room, within the Maths course.
However, there are cases when the course has reached the courseTotalCapacity = 20,and lets say we have 7 students on classroom1 and 13 students for classroom2 for the maths course.I want to be able to move students from classroom1 to classroom2 and vice-versa.But as it is the code does not allow me, stating that the course is full,even though I just want to move from one class to another of the same course.
Please have a look at my code and advise.
Thank you

php Syntax (Toggle Plain Text) 
  1. <html>
  2.    <body>
  3.     <center>
  4.  
  5.     <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
  6.     <table>
  7.     <tr><td>Student ID:</td><td><input type="text" name="studentNum"/></td></tr>
  8.  
  9.     <tr><td>FirstName:</td><td><input type="text" name="firstName"/></td></tr>
  10.    <tr><td>LastName:</td><td><input type="text" name="lastName"/></td></tr>
  11.    <tr><td>Class Room number:</td>
  12.       <td>
  13.       <SELECT NAME="ClassRoom">
  14.         <OPTION VALUE="1">1</OPTION>
  15.         <OPTION VALUE="2">2</OPTION>
  16.         <OPTION VALUE="3">3</OPTION>
  17.         <OPTION VALUE="4">4</OPTION>
  18.         <OPTION VALUE="5">5</OPTION>
  19.       </SELECT>
  20.       </td></tr>
  21.  
  22.    <tr><td>Course ID:</td><td><input type="text" name="courseID"/></td></tr>
  23.  
  24.    <tr><td colspan="2" align="centre"><input type="submit" value="SUBMIT"/></td></tr>
  25.    </table>
  26.    </form>
  27.  
  28.    <?php
  29.  
  30.    // Connect to the database server
  31.     $dbcnx = @mysql_connect('xxxx', 'xxxx', 'xxxx');
  32.     if (!$dbcnx) {
  33.       exit('<p>Could not connect to the ' .
  34.           'database server.</p>');
  35.     }//end of if-statement
  36.  
  37.     // Select the database
  38.     if (!@mysql_select_db('database_Name')) {
  39.       exit('<p>Cannot locate the database_Name ' .
  40.           'database.</p>');
  41.     }//end of if-statement
  42.  
  43.  
  44.    //Traverse through the COURSE_ALLOCATION table and count the number of entries for
  45.    //each user id, aka: how many students per course.
  46.  
  47.    $result = mysql_query('SELECT courseID, courseTotalCapacity FROM course WHERE courseID IN (101, 102,103)') or exit(mysql_error());
  48.    while ($row = mysql_fetch_assoc($result))
  49.    {
  50.        $course_array[$row['courseID']]['courseTotalCapacity']] = $row['courseTotalCapacity'];
  51.    }//end of while-loop
  52.  
  53.    $result = mysql_query('SELECT courseID, COUNT(*) AS count FROM course_allocation WHERE courseID IN (101, 102,103) GROUP BY courseID') or exit(mysql_error());
  54.    while ($row = mysql_fetch_assoc($result))
  55.    {
  56.        $course_array[$row['courseID']]['count']] = $row['count'];
  57.    }//end of while-loop
  58.  
  59.    echo "<br />";
  60.  
  61.       $studentNum = $_POST['studentNum'];
  62.       $firtName = $_POST['firtName'];
  63.       $lastName = $_POST['lastName'];
  64.       $ClassRoom = $_POST['ClassRoom'];
  65.       $courseID = $_POST['courseID'];
  66.  
  67.  
  68.  
  69.            if($course_array[$courseID]['count']<$course_array[$courseID]['courseTotalCapacity'])
  70.          {
  71.            $sql = "UPDATE student SET
  72.               studentNum='$studentNum',
  73.               firstName='$firstName',
  74.               lastName='$lastName',
  75.               ClassRoom='$ClassRoom'";
  76.  
  77.  
  78.            if (@mysql_query($sql)) {
  79.                 echo '<p>Submitted student has been added.</p>';
  80.            } else {
  81.                 echo '<p>Error adding submitted student: ' .
  82.                     mysql_error() . '</p>';
  83.            }
  84.  
  85.  
  86.            $sql1 = "UPDATE course_allocation SET
  87.           studentNum='$studentNum',
  88.  
  89.           courseID='$courseID'";
  90.  
  91.           if (@mysql_query($sql1)) {
  92.                echo '<p>Submitted student has been allocated.</p>';
  93.            } else {
  94.                 echo '<p>Error allocating submitted student: ' .
  95.                 mysql_error() . '</p>';
  96.            }
  97.        }//end of if-statement
  98.        else
  99.        {
  100.            echo 'sorry. you have reached the limit for course #' . $key;
  101.            echo "<br />";
  102.        }//end of else
  103.  
  104.  
  105.  
  106.  
  107.    ?>
  108.  
  109.  
  110.  </center>
  111.  </body>
  112.  </html>
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2009
Posts: 44
Reputation: guru12 is an unknown quantity at this point 
Solved Threads: 6
guru12's Avatar
guru12 guru12 is offline Offline
Light Poster

Re: update student record

 
0
  #2
Jul 22nd, 2009
Dear Friend

The update query should have the "WHERE" condition ,
use the below syntax to update the dates

sql Syntax (Toggle Plain Text) 
  1. UPDATE table_name
  2. SET column1=value, column2=value2,...
  3. WHERE some_column=some_value

THanks
Last edited by peter_budo; Jul 22nd, 2009 at 3:03 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
Joomla Web Design
Joomla Web Development
Reply With Quote Quick reply to this message  
•
•
Join Date: Oct 2008
Posts: 61
Reputation: Manuz is an unknown quantity at this point 
Solved Threads: 8
Manuz's Avatar
Manuz Manuz is offline Offline
Junior Poster in Training

Re: update student record

 
0
  #3
Jul 22nd, 2009
Hey i didnt fully gone thru ur code... its only a suggestn okeyyy!!!!!

You r checkg the count of course so defnty it fails.... if possible take the count of course inside each class and use this inside condition.
Sharing Knowledge Is Better Than Any Other Thingz
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 10
Reputation: trtcom1 is an unknown quantity at this point 
Solved Threads: 0
trtcom1 trtcom1 is offline Offline
Newbie Poster

Re: update student record

 
0
  #4
Jul 22nd, 2009
•
•
•
•
Originally Posted by guru12 View Post
Dear Friend

The update query should have the "WHERE" condition ,
use the below syntax to update the dates

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

THanks
My code has the WHERE clause"
php Syntax (Toggle Plain Text) 
  1. $sql = "UPDATE student SET
  2. firstName='$firstName',
  3. lastName='$lastName',
  4. ClassRoom='$ClassRoom',
  5. WHERE  studentNum='$studentNum'";

and

php Syntax (Toggle Plain Text) 
  1. $sql1 = "UPDATE course_allocation SET
  2. courseID='$courseID',
  3. WHERE studentNum='$studentNum' ";

And this is the problem.
The problem is the, I tried the following but did not work also:

php Syntax (Toggle Plain Text) 
  1.  if($course_array[$courseID]['count'] < $course_array[$courseID]['courseTotalCapacity'])
  2.     {
  3.           //move student from one course to another
  4.     }//end of if
  5.    elseif ($course_array[$courseID]['count']==$course_array[$courseID]['courseTotalCapacity'])
  6.     {
  7.          //Student stay in the same course, but move him to another class
  8.     }
  9.    else
  10.       //State that the course is full, not allowed to add more students
Any idea?
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 21
Reputation: spthorn is an unknown quantity at this point 
Solved Threads: 5
spthorn's Avatar
spthorn spthorn is offline Offline
Newbie Poster

Re: update student record

 
0
  #5
Jul 22nd, 2009
There's some logic missing in all this code. I'd suggest sitting down and writing out pseudocode to cover all the options and THEN coding. Something like this:

PHP Syntax (Toggle Plain Text) 
  1. if (studentid doesn't exist)
  2. 	add new record to student table
  3. else
  4. 	update student table with new firstname and lastname
  5.  
  6. if (studentid and courseid exist in course_allocation)
  7. 	// just changing class room
  8. 	update student table with new classroom
  9. else
  10. 	if (studentid exists in course_allocation)
  11. 		if (new courseid course is filled to capacity)
  12. 			ERROR: New course is filled
  13. 		else
  14. 			update student's record in course_allocation to new courseid
  15. 			update student table with new classroom
  16. 	else
  17. 		if (new courseid course is filled to capacity)
  18. 			ERROR: New course is filled
  19. 		else
  20. 			insert new course_allocation record
  21. 			update student table with new classroom
Last edited by spthorn; Jul 22nd, 2009 at 1:23 pm.
All opinions 100% correct. Or your money back.
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 10
Reputation: trtcom1 is an unknown quantity at this point 
Solved Threads: 0
trtcom1 trtcom1 is offline Offline
Newbie Poster

Re: update student record

 
0
  #6
Jul 23rd, 2009
Here is the solution,
thank you everyone:

php Syntax (Toggle Plain Text) 
  1. <html>
  2. <head>
  3. <title>Trainee CMS: Edit Student</title>
  4. <meta http-equiv="content-type"
  5. 	content="text/html; charset=iso-8859-1" />
  6. </head>
  7.  
  8. <?php
  9.   require('header.inc');
  10. ?>
  11.  
  12. <body>
  13.  
  14. <?php
  15.  
  16. // Connect to the database server
  17.  require('connectdatabase.inc.php');
  18.  
  19. //If you want to edit a student
  20.  
  21.  if (isset($_POST['studentNum']));
  22.  
  23. //Traverse through the ALLOCATION table and count the number of entries for
  24. 	//each user id, aka: how many trainees on each vessel.
  25.  
  26. $result = mysql_query("SELECT courseID, courseTotalCapacity FROM course WHERE 
  27.  
  28. courseID IN ('101','102')") or exit(mysql_error());
  29.  
  30. while ($row = mysql_fetch_assoc($result)) 
  31. {
  32. 		$vessel_array[$row['courseID']]['courseTotalCapacity'] = 
  33.  
  34. $row['courseTotalCapacity'];
  35.  
  36. }//end of while-loop
  37. // collect posted information.
  38. $studentNum = $_POST['studentNum'];
  39. $firtName = $_POST['firtName'];
  40. $lastName = $_POST['lastName'];
  41. $ClassRoom = $_POST['ClassRoom'];
  42. $courseID = $_POST['courseID'];
  43.  
  44. // removing matches for the current student - would also limit to current course but seems unnecessary.
  45.  
  46. $query  = 'SELECT courseID, COUNT(*) AS count ';
  47. $query .= 'FROM course_allocation ';
  48. $query .= 'WHERE courseID IN (101, 102) ';
  49. // adding next line:
  50. $query .= 'AND studentNum != '.intval( $studentNum ).' ';
  51. $query .= 'GROUP BY courseID';
  52.  
  53. $result = mysql_query( $query ) or die( mysql_error() );
  54.  
  55. while ($row = mysql_fetch_assoc($result)) 
  56. {
  57. 		$vessel_array[$row['courseID']]['count'] = $row['count'];
  58.  
  59. }//end of while-loop
  60. echo "<br />";
  61.   $studentNum = $_POST['studentNum'];
  62.   $firtName = $_POST['firtName'];  
  63.   $lastName = $_POST['lastName'];
  64.   $courseName = $_POST['courseName'];
  65.   $classRoom = $_POST['classRoom'];
  66.   $courseID = $_POST['courseID'];
  67.  
  68.   if ($vessel_array[$courseID]['count'] < $vessel_array[$courseID]['courseTotalCapacity'])
  69. 	{
  70. 	  	$sql = "UPDATE student SET
  71. 		   	firtName = '$firtName',
  72. 		   	lastName = '$lastName',   	   				   	
  73. 		   	courseName = '$courseName',
  74. 			classRoom = '$classRoom'
  75.  
  76. 		   	WHERE studentNum = '$studentNum'";
  77.  
  78. 	  	if (@mysql_query($sql)) {
  79.     			echo '<p>student details updated.</p>';
  80. 	  	} else {
  81. 		    	echo '<p>Error updating student details: ' .
  82. 			mysql_error() . '</p>';
  83. 	  	}
  84.  
  85.   		$sql1 = "UPDATE course_allocation SET
  86. 	  	   	courseID = '$courseID'
  87.  
  88. 		   	WHERE studentNum = '$studentNum'";
  89.  
  90. 	  	if (@mysql_query($sql1)) {
  91.     			echo '<p>student details were allocated.</p>';
  92. 	  	} else {
  93. 		    	echo '<p>Error allocating student details: ' .
  94. 			mysql_error() . '</p>';
  95.   		}
  96. 	}//end of if
  97. else
  98. 	{
  99. 		echo "Complain that the class is full.";
  100. 	}	
  101. ?>
  102. <p><a href="allocatestudent.php">Return to allocate student</a></p>
  103. <?php
  104. //else:
  105.   $studentNum = $_GET['studentNum'];
  106.   $student = @mysql_query(
  107. 	"SELECT studentNum, firtName, lastName, courseName,classRoom FROM student WHERE 
  108.  
  109. studentNum ='$studentNum'");
  110.   if(!$student){
  111. 	exit('<p>Error fetching student details: ' .
  112. 		mysql_error() . '</p>');
  113.   }
  114.   $student = mysql_fetch_array($student);
  115.   $studentNum = $student['studentNum'];
  116.   $firtName = $student['firtName'];
  117.   $lastName = $student['lastName'];
  118.   $courseName = $student['courseName'];
  119.   $classRoom = $student['classRoom'];
  120.   $course_allocation = @mysql_query(
  121. 	"SELECT studentNum, courseID FROM course_allocation WHERE studentNum 
  122.  
  123. ='$studentNum'");
  124.   if(!$course_allocation){
  125. 	exit('<p>Error fetching student details from course allocation: ' .
  126. 		mysql_error() . '</p>');
  127.   }
  128.   $course_allocation = mysql_fetch_array($course_allocation);
  129.   $studentNum = $course_allocation['studentNum'];
  130.   $courseID = $course_allocation['courseID'];
  131. ?>
  132.  
  133. <p>Edit student:</p>
  134. <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  135. <table>
  136. <tr><td>first Name:</td><td><input type="text" name="firtName"
  137. 	value="<?php echo $firtName; ?>" /></td></tr><br />
  138. <tr><td>Last Name:</td><td><input type="text" name="lastName"
  139. 	value="<?php echo $lastName; ?>" /></td></tr><br />
  140. <tr><td>course Name:</td><td><input type="text" name="courseName"
  141. 	value="<?php echo $courseName; ?>" /></td></tr><br />
  142. <tr><td>Class Room:</td>
  143. <td><input type="text" name="classRoom"
  144. 	value="<?php echo $classRoom; ?>" />
  145. <SELECT NAME="classRoom">
  146.   <OPTION VALUE="11">11</OPTION>
  147.   <OPTION VALUE="22">22</OPTION>
  148.   <OPTION VALUE="33">33</OPTION>
  149.   <OPTION VALUE="44">44</OPTION>
  150. </SELECT></td></tr>
  151. <br />
  152. <tr><td>Course ID:</td>
  153. <td><input type="text" name="courseID"
  154. 	value="<?php echo $courseID; ?>" />
  155. <SELECT NAME="courseID">
  156.   <OPTION VALUE="101">101</OPTION>
  157.   <OPTION VALUE="102">102</OPTION>
  158.   <OPTION VALUE="103">103</OPTION>
  159.   <OPTION VALUE="104">104</OPTION>  
  160. </SELECT>
  161. </td></tr>
  162. <br />
  163. <input type="hidden" name="studentNum" value="<?php echo $studentNum; ?>" />
  164. <tr><td colspan="2" align="centre"><input type="submit" value="SUBMIT" /></td></tr>
  165. </table>
  166. </form>
  167. </body>
  168. <?php
  169.   require('footer.inc');
  170. ?>
  171. </html>
Last edited by trtcom1; Jul 23rd, 2009 at 10:44 am.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the PHP Forum
Thread Tools Search this Thread



.htaccess ajax apache api array beginner binary broken buttons cakephp checkbox class cms code cron curl database date directory display dynamic ebooks echo email error file files folder form forms function functions google href htaccess html image include insert integration ip java javascript joomla limit link login loop mail mediawiki menu mlm mod_rewrite multiple mysql number oop paypal pdf php phpincludeissue phpmyadmin problem query radio random recursion regex remote script search server sessions sms soap source sp space speed sql subdomain syntax system table tag tutorial update upload url validation validator variable vbulletin video web webdesign websphere white xml youtube
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC