Welcome! Log In Create A New Profile

Advanced

Final Prac - delete.php & student_course table.

Posted by AlessioLaRuffa 
Announcements Last Post
Announcement SoC Curricula 09/30/2017 01:08PM
Announcement Demarcation or scoping of examinations and assessment 02/13/2017 07:59AM
Announcement School of Computing Short Learning Programmes 11/24/2014 08:37AM
Announcement Unisa contact information 07/28/2011 01:28PM
Forums Messages New
AlessioLaRuffa
Final Prac - delete.php & student_course table.
October 22, 2010 01:55PM
Hey, guys.

Final prac is moving along steadily, was a little overwhelming at first, but as with all things, you just have to take one step at a time, and it's become a lot clearer. I'm just having trouble with my delete.php file and the student_course table.
    1. student_course table - I'm struggling with how to actually implement this and to associate a student with a course.
    2. delete.php - Any tips on how I could delete a course, along with any association to a student or students?

Any help/tips would be greatly appreciated.

Thanks.
Reply Quote
MicheleC
Re: Final Prac - delete.php & student_course table.
October 22, 2010 03:12PM
At the risk of writing a lot of unnecessary stuff - you might need to be more specific.

I assume you are referring to :
1. inserting the course(s) the student selects into table course_student?
When the student registers - save the student record into table student - then select from the student table to get the student number (sno) - which will only be known once the student record is saved. Then select from the course table, matching on course name you are displaying on the input screen (cname) to get the course id (cid). Then insert into course_student the sno, cid and 2010 for the year.

2. Deleting from the course_student table when a course is deleted?
When the course is deleted - delete the record from the course table using the course id (cid). Then delete from the table course student matching the course ids (cid) of the 2 tables.

make a start and let us know how you go.smile
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
October 22, 2010 03:28PM
Hey, MicheleC

Great, I'm gonna get on it right away and keep everyone updated.

Thank you.
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
October 28, 2010 10:24PM
Hey, everyone.

Ran into some other problems with the rest of my code.

I'm still having a problem inserting the sno, cid and year into the course_student table. I've pretty much tried all the examples in previous years' forums, but to no avail. And I need this info to so that when deleting a course, I must delete the student from the course too.

I've got everything else working pretty smoothly though..
Reply Quote
rogerdurbs
Re: Final Prac - delete.php & student_course table.
October 29, 2010 06:36AM
Well, how have you tried to achieve this?

Basically when you run your query to insert the student you will need to get the sno for the added student after running the insert query and use whatever cid was chosen and run a second query to insert the course_student info.
Reply Quote
MicheleC
Re: Final Prac - delete.php & student_course table.
October 29, 2010 08:50AM
You are getting very close to the wire here - hand in is 2 weeks away

Lets get to the point - do you need code examples?
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
October 29, 2010 10:59AM
@rogerdurbs

Basically I get my student inserted into the student table, no problem, the problem comes in trying to get the sno and the cid and inserting them into the course_student table, I've tried this : 
Language: PHP
	case "Add" :
		switch ($_GET[';type';]) {
			case "course" :
				$sql = "INSERT INTO course (cname)
						VALUES (';" . $_POST[';cname';] . "';)" ;
				break ;
			case "student" :
				$sql = "INSERT INTO student
							(sname,
							 init,
							 fname,
							 title,
							 msname,
							 dob,
							 sex,
							 lang,
							 idno,
							 telh,
							 telw,
							 cel,
							 fax,
							 email,
							 address)
						 VALUES
						 	(';" . $_POST[';sname';] . "';,
							 ';" . $_POST[';init';] . "';,
							 ';" . $_POST[';fname';] . "';,
							 ';" . $_POST[';title';] . "';,
							 ';" . $_POST[';msname';] . "';,
							 ';" . $_POST[';dob';] . "';,
							 ';" . $_POST[';sex';] . "';,
							 ';" . $_POST[';lang';] . "';,
							 ';" . $_POST[';idno';] . "';,
							 ';" . $_POST[';telh';] . "';,
							 ';" . $_POST[';telw';] . "';,
							 ';" . $_POST[';cel';] . "';,
							 ';" . $_POST[';fax';] . "';,
							 ';" . $_POST[';email';] . "';,
							 ';" . $_POST[';address';] . "';)" ;
				break;
 
			}
			break;
}
 
if (isset($sql) && !empty($sql)) {
	echo "<!--" . $sql . "-->" ;
	$result = mysql_query($sql)
		or die ("Invalid Query: " .mysql_error()) ;
?>	
<p align="center" style="color:#00FF00">Done.<br />
<a href="indexreg.php" class="button">Return to the Index Page.</a></p>
<?php
	}
?>
<?php
$csquery = "INSERT INTO course_student
                (sno,
                cid,
                year)
            VALUES
                (';" . $_POST[';sno';] . "';, // also tried entering $sno, $cid here
                ';" . $_POST[';cid';] . "';,
                ';2010 ';)";
$result = mysql_query($csquery);
$rows = mysql_affected_rows();
?>
<?php include "footer.php"; ?>
but when I check the student_course table through phpmyadmin, sno and cid return values of zero, and year is 2010.

@MicheleC, if you could show me where I'm going wrong in the above code and help me out with some example code, that'd be great.
Reply Quote
MicheleC
Re: Final Prac - delete.php & student_course table.
October 29, 2010 01:20PM
OK - your code for the insert into course_student is working if you are populating field YEAR.

BUT - where are you getting the student number (sno) from and course id (cid) from?

Excluding the fact that I cannot see the rest of your development: - It appears to me that you have just inserted into the student and course table.

Now you need to query those two tables to get the sno and cid for the records you have just inserted!?
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
October 29, 2010 02:50PM
That's the problem I'm having, i.e. How do i query the two tables in order to get the sno and cid for the records to put into course_student?
Reply Quote
MicheleC
Re: Final Prac - delete.php & student_course table.
October 29, 2010 04:40PM
You are inserting the course name (cname) into the table - not so?

When the record is inserted - it will allocate a cid (if its not - then you are missing a setup on the field in the table) and save the cid and cname to a record in the table.

Now select from the table course where cname = the course name you just inserted, to get the cid.

It may be less confusing if you allocated the variables values into new variables in each page (you can wing it later when the concepts settle in) eg

$selected_course = $_POST['cname'];

then insert into table course (cid, cname) values (null,$selected_course) //pseudocode

then select cid from table course into local_variable where cname = $selected_course //pseudocode

when you get that working let us know.
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
October 29, 2010 05:06PM
Okay, from what I can understand, my code now looks like this: 
Language: PHP
<?php
$selected_course = $_POST[';cname';];
 
$sql = "SELECT cid FROM course WHERE cname = $selected_course"; 
 
$csquery = "INSERT INTO course_student
                (sno,
                cid,
                year)
            VALUES
                (';" . $_POST[';sno';] . "';,
                ';" . $_POST[';cid';] . "';,
                ';2010';)";
$result = mysql_query($csquery);
$rows = mysql_affected_rows();
?>
<?php include "footer.php"; ?>

or am I still missing it?
Reply Quote
MicheleC
Re: Final Prac - delete.php & student_course table.
October 29, 2010 05:52PM
I'm not going to give you the exact code - I'm trying to work on getting you to a point where you understand database concepts.

No - that code isn't going to work - go and look at an example in the prescribed book - that's where I got my answers from.

Your select from the course table needs to be executed before you insert into course_student. The value of the cid to be inserted into course_student is derived from that execution.

When you've got that right - you need to get the sno from the student table.
Reply Quote
rogerdurbs
Re: Final Prac - delete.php & student_course table.
October 30, 2010 09:22AM
Ya, going on what MicheleC said. You will need to get the cid and the sno before inserting into the course_student table. So your step 1 of getting cid should work, im not 100% sure what $selected_course is but i presume that will work!

Your problem lies in getting the sno of the student seeing as you have just added it and therefore dont know the sno yet. Try see if you can find a query to get the last inserted record in the students table!
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
October 30, 2010 01:17PM
@MicheleC - I'd rather *understand* it than copy and paste some code, because that's pretty pointless. I'm going through the good old prescribed book again, to help me get it.

@rogerdurbs - I'm following you, gonna try solve this sucker!

Thanks to both of you!
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
October 31, 2010 04:22PM
Okay, I obviously don't understand database theory too well, because I really can't get this right.
Reply Quote
rogerdurbs
Re: Final Prac - delete.php & student_course table.
October 31, 2010 07:53PM
Go and read up on mysql_affected_rows. This can be used to see how many records were inserted.

Or from an SQL point of view you could use MAX, so e.g. 
Language: SQL
SELECT MAX(sno) FROM students

will give you the max, i.e. last inserted record.
Reply Quote
MicheleC
Re: Final Prac - delete.php & student_course table.
November 01, 2010 10:17AM
Apologies for disappearing but my husband had a cycling accident this weekend and I need to attend to that somewhat more urgently.

OK - Apologies for the big variable names but $selected_course was for effect - I wasn't suggesting it be used.

Lets look at the code :

$selected_course = $_POST['cname']; / / this is ok

$sql = "SELECT cid FROM course WHERE cname = $selected_course";
this should be $sql = " Select cid from course where name = ". $selected_course . " ' ";

Now you need to execute $sql.

$result = mysql_query($sql);
$row=mysql_fetch_array($result);
$cid_of_course = $row['cid'];

Now you have the cid and the year.
Now insert into student_course as you were doing.

If you get this working - you can apply the same approach to getting the sno from student
Reply Quote
avatar
Mac
Re: Final Prac - delete.php & student_course table.
November 01, 2010 12:16PM
Write out in words the process as you see it, and what your code needs to do .
- a student registers // I need to show a registration form containing.... with the course information (cid and cname) drawn from the db and part of the form
- The student clicks submit // the student's personal details are inserted into the db. The sno is an autoincrement which is generated on the insert statement.
- The student_course table needs to be updated // I have the cid (it was passed by the form e.g. <option name=course_id value=$cid>$cname</option>. I need to get the sno. How do I do that?

Some suggestion were given above, another way here below 
Language: PHP
insert_author="INSERT INTO authors (name) VALUES (';$title';)";
mysql_query($insert_author) or die(mysql_error());
$authId = mysql_insert_id();
$query="INSERT INTO articles (body, date, category, auth_id) VALUES(';$body';, ';$date_now';, ';$category';, ';$authId';)";
//The build in function mysql_insert_id()  retrieves the ID generated for an AUTO_INCREMENT column by the previous query 
//Be sure to call mysql_insert_id() immediately after a query to get the correct value.
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
November 01, 2010 01:08PM
@MicheleC - No worries, I hope your husband's okay.

@Mac - I've followed MicheleC's example, still getting 0 values for sno and cid.

This is my code for my form to register the student: 
Language: PHP
<?php
include "headeradmin.php" ;
include "includes.php" ;
 
switch ($_GET[';action';]) {
	case "Edit" :
		...
	}
?>
 
<html>
<head>
<title><?php echo $_GET[';action';]; ?> Student</title>
<script> //...
</script>
<style type="text/css">
//{ CSS goes here } 
</style>
</head>
<body>
<form action="commit.php?action=<?php echo $_GET[';action';]; ?>&type=student&sno=<?php echo $_GET[';sno';]; ?>" method="post" id="frm1">
<br />
<center><b>Student Registration Form</b>
<br />
<br />
<b>Please provide the following information:</b></center>
<br />
<br />
<table width="600" height="auto" border="1" cellspacing="0" cellpadding="3" align="center" bgcolor="#EEE">
  <tr>
    <td>Course Name </td>
    <td><select name="cname" style="width:150px">
<?php
$query = "SELECT * FROM course" ;
$result = mysql_query($query) 
	or die("Query Error" . mysql_error());
while ($row = mysql_fetch_array($result)) {
	echo ';<option name ="cid" value="'; . $row[';cid';] . ';">'; . $row[';cname';] . ';</option>'; .  "\r\n" ;
}
?>
    </select></td>
 //etc..
  <td colspan="2" align="center">
<input name="SUBMIT" type="submit" value="<?php echo $_GET[';action';] ; ?>" />&nbsp;<input type="button" onClick="formReset()" value="Reset form" />
<br />
</table>
</form>
 
<?php include "footer.php"; ?>
</body>
</html>

and my commit.php file : 
Language: PHP
<?php
include "headeradmin.php" ;
include "includes.php" ;
$selected_course = $_POST[';cname';];
//COMMIT ADD AND EDITS
switch ($_GET[';action';]) {
	case "Edit" :
		switch ($_GET[';type';]) {
			case "student" :
				$sql = "UPDATE student SET
					....
	case "Add" :
		switch ($_GET[';type';]) {
			case "course" :
				$sql = "INSERT INTO course (cname)
						VALUES (';" . $_POST[';cname';] . "';)" ;
				break ;
			case "student" :
				$sql = "INSERT INTO student
							(sname,...)
						 VALUES
						 	(';" . $_POST[';sname';] . "';,
							...)" ;
				break;
 
			}
			break;
}
 
if (isset($sql) && !empty($sql)) {
	echo "<!--" . $sql . "-->" ;
	$result = mysql_query($sql)
		or die ("Invalid Query: " .mysql_error()) ;
?>	
<p align="center" style="color:#00FF00">Done.<br />
<a href="indexreg.php" class="button">Return to the Index Page.</a></p>
<?php
	}
?>
<?php
//$selected_course = $_POST[';cname';];
 
$sql1 = "SELECT cid FROM course WHERE cname = ';" . $selected_course . "';"; 
$result1 = mysql_query($sql1) ;
$row = mysql_fetch_array($result1) ;
$ccid = $row[';cid';] ;
 
 
$csquery = "INSERT INTO course_student
                (sno,
                cid,
                year)
            VALUES
                (';" . $_POST[';sno';] . "';,
                ';$ccid';,
                ';2010';)";
$result = mysql_query($csquery);
$rows = mysql_affected_rows();
?>
<?php include "footer.php"; ?>
</body>
</html>

Does this help put it in context a bit better?
Reply Quote
MicheleC
Re: Final Prac - delete.php & student_course table.
November 01, 2010 03:09PM
My approach to this bit was somewhat different so I decided to put your code into my laptop to see what was happening - you are passing the cid in the post and not the cname
ie $selected_course = $_POST['cname']; is returning the cid and not the cname - which would explain why you are not getting any results.

I'll have to look into this further - but it would be useful if someone else could step in and help.....
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
November 01, 2010 03:36PM
MicheleC :
I changed this 
Language: PHP
echo ';<option name ="cid" value="'; . $row[';cid';] . ';">'; . $row[';cname';] . ';</option>'; .  "\r\n" ;
to this 
Language: PHP
echo ';<option name ="cname" value="'; . $row[';cid';] . ';">'; . $row[';cname';] . ';</option>'; .  "\r\n" ;
and I'm getting values in my cid fields.

Thank you!
Reply Quote
MicheleC
Re: Final Prac - delete.php & student_course table.
November 01, 2010 03:36PM
OK - on your page for registering the student - make this change:

or die("Query Error" . mysql_error());
while ($row = mysql_fetch_array($result)) { ?> close the php code here
//echo '<option name ="cid" value="' . $row['cid'] . '">' . $row['cname'] . '</option>' . "\r\n" ; ?> comment this out and add this
<option name ="<?php echo $row['cid']; ?>" value="<?php echo $row['cname']; ?>" /><?php echo $row['cname']; ?> </option>


open the php code here
<?php
}
?>

You should now be passing the cname and not the cid in the post

OK - got to go - need to do some work
Reply Quote
MicheleC
Re: Final Prac - delete.php & student_course table.
November 01, 2010 03:38PM
Oops - looks like we are posting at the same time
Reply Quote
MicheleC
Re: Final Prac - delete.php & student_course table.
November 01, 2010 03:40PM
OK - good - you're on a role...
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
November 01, 2010 03:47PM
Finally!

Thank you for your help and all your patience! It's much appreciated! smile
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
November 01, 2010 03:54PM
course_student table is being populated!

Thanks again, everybody!
Reply Quote
Charl_Joubert
Re: Final Prac - delete.php & student_course table.
November 02, 2010 12:33PM
I also ran in to trouble with this, glad you guys figured it out, i took me like, i don't know how many hours..

Everything is in the prescribed book, not always straight forward, not for me anyway, but it's there. Sometimes you need to go down a different road to find what your looking for, but in the end it makes sense.. Like everything else, the more time you spend figuring it out, the better you will get. Rome wasn't built in a day..
Reply Quote
AlessioLaRuffa
Re: Final Prac - delete.php & student_course table.
November 03, 2010 01:05PM
Yeah, it felt impossible at the time. Everything went smoothly afterwards.

"Being stumped is part of developing mastery."
Reply Quote
Charl_Joubert
Re: Final Prac - delete.php & student_course table.
November 03, 2010 03:53PM
Most of the problems i ran into was using the wrong sql syntax..

Got errors like "check your sql manual for the correct syntax near WHERE on line 23......." or something like that, i knew what i wan't to do, just didn't quite know how to write it..

But the more i did it, the better i understood it. The prescribed book was a major help. Also found http://dev.mysql.com/ useful. And the old phorums, sometimes someone else' problems can help you straight out, or at least point you in the right direction, or make you think differently about a problem.. And you pick up other things as well along the way.

But the more you do it, the better you'll get, no doubt about it.
Reply Quote
Newer Topic Older Topic
Sorry, only registered users may post in this forum.

Click here to login