Welcome! Log In Create A New Profile

Advanced

Joining Tables

Posted by louisesanders 
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
Joining Tables
May 17, 2008 01:19PM
Hi Guys

Please can anyone help wth this. I want to be able to join the following queries so there is just one query to delete the course and associations with it. It works fine in two seperate queries, just wanted to know if I could do it in one?

$query1 = mysql_query("DELETE FROM course WHERE cid = '$cid' "winking smiley;
$query2 = mysql_query("DELETE FROM course_student WHERE cid = '$cid' "winking smiley;


Tried writing it a couple of different ways but alas.....nothing works.

Thanks
Louise
avatar
Mac
Re: Joining Tables
May 18, 2008 04:54PM
Why???? The above executes quickly enough....

But write everything out, as in example here below.

DELETE category, product FROM category, product WHERE category.id_category = '$id' AND product.id_category = '$id'
Re: Joining Tables
May 19, 2008 07:32AM
Cool, thanks Mac. It is weird because writing it the longer way works fine - the course gets deleted and I get a happy message saying "Course deleted successfully".

Writing it the way below doesn't delete the course. It does give me a happy message though saying "the course has been deleted successfully" but when I actually check the list of courses - the course hasn't been deleted.

$query1 = mysql_query("DELETE course, course_student FROM course, course_student WHERE course.cid = '$cid' AND course_student.cid = '$cid' "winking smiley;

Any Ideas, otherwise I'll just keep it the simple way! smile
Re: Joining Tables
May 19, 2008 08:54AM
Hi..

I understand what you mean by saying you want to use single query but if it works fine the longer yet simpler method use that. Try refreshing your page or Ctrl + Refresh. Your browser might be caching(Cache) so therefore the updated databse isn't showing.

Oh and i was wondering something. Deleting the course from the course and course student tables. The course_student associates the student with a course. If you delete a course from there the student or students already registered for that course will not be associated with any as the course itself will no longer be visible. So the student will be registered but not have a course.

smile
JwV
Re: Joining Tables
May 19, 2008 11:08AM
Hi All

i made it that you cannot remove a course if there are still student registered for the course. An error message is displayed when you try to delete the couse. This way I ensure that there will be no students without a course.
avatar
Mac
Re: Joining Tables
May 20, 2008 07:06AM
Now you're starting to think..... good. We're starting with small steps here since we're focusing on the coding part.

Have a look here http://www.geekgirls.com/databases_from_scratch_3.htm
How would you have changed the current db structure??? Or would you have not? Does it depend on what you want, i.e. if you want to keep a history of student registration, how can the db be designed better?
Re: Joining Tables
May 20, 2008 08:26AM
Hi.. how u all?

I added that as well where the admin cannot delete a course if the course has been registered for. Good work JwV smile

I actually enjoyed working with this database structure. 3 tables linked by a common table (Course_Student). It was a bit challenging and fun. To make it much more simpler would have been to add the course the student registered for into the student table as well.

To keep a histroy of student registrations, another table called student_history for example could be used. Every student that registeres for a course would be added here as well as the course. This table will not be dependent on any other so data will not be lost if deleted elsewhere. OR say from some reason the admin has to delete a course, the student details are added to another table which keeps a record that he/she actually did register.

smile What i would like to have had with the student table is student numbers that are generated instead of using single digits from auto_increment as the student number. The id field(auto_increment) could have been used to generate a user id in the table but student number could be generated as below.

<?php
//returns 8 digit random number.
$random_digit=rand(00000000,99999999);

echo $random_digit;
?>

Just some ideas floating in my head.

smile hope you'll enjoying your day thus far.
avatar
Mac
Re: Joining Tables
May 21, 2008 09:30AM
> To make it much more simpler would have been to add the course the student registered for into the student table as well.

Student table must just have student info. course table just course info. The a table to join information. That's good db design (for starters). How you setup and use student_course db can change (e.g. use course name instead of course id)
Re: Joining Tables
May 21, 2008 09:48AM
I have my tables joined at the moment. I never liked it before(Joining tables) but when doing it here was fun and interesting to see the results. I prefer using an id or code wen parsing throught url(.php?id=). I like it in that way because i can manually input it incase i want to access something quickly. If i use example the course name i would have to each time enter the full name instead of a code if i wanted to enter it manually.

O and mac, could we make it that the student can edit his/her details as well. Maybe they made a mistake in their name etc or is it up to us.

smile
Re: Joining Tables
May 21, 2008 11:27AM
thanks for the cool link Mac, looks like a good article.

But what if we have to delete a course that students are already registered for? Perhaps there are no lecturers or perhaps it was a new course and not enough students regsitered for the course? Now we have to delete the course and let students know that they will be refunded etc etc.

Perhaps the error message should give you a choice to delete the course if necessary despite any current registrations.

Once I get the basics down I might try doing this as the project has taken me forever thus far! But it is still exciting though when something finally works!!! smile
Re: Joining Tables
May 21, 2008 11:51AM
Hi there.

That is quite a good idea though. In case where the course has to be deleted for what ever purpose. smile

Yeah true it is a great feeling after trying and trying for long then finally it works.

Enjoy.
avatar
Mac
Re: Joining Tables
May 21, 2008 12:45PM
You can do what you want within the database structures we provide.

Read this just for interest - attackers highjack what you sent with your URL, especially if they can see what you are sending...
Re: Joining Tables
May 21, 2008 01:02PM
Yeah espacially if you use GET through url with passwords. Ok but if for example you have a database displayed on screen. which other method would you use without using the url. Example

echo "<td><a href='delete.php?id=$row[0]'>$row[1]</td>";

So when submitting this and requesting it on the next page as follows it would delete the file

$id = $_REQUEST['id'];
DELETE from Whatever WHERE id='$id'

Is there a better way to do this without using url.
avatar
Mac
Re: Joining Tables
May 22, 2008 07:36AM
If you use GET, anyone can see what you are passing by looking at the URL. So use POST.

http://www.tizag.com/phpT/postget.php
Re: Joining Tables
May 22, 2008 07:54AM
Yeah thanks but to use post or get everytime ill need to use a form each time to state post or get. Like this i'm sending information without a form. But ill do some research about other methods and see what happens.

Thanks mac.
JwV
Re: Joining Tables
May 22, 2008 05:30PM
Hi All

With passing values from page to page one can use a session or a cookie.

When deleting a course you could link any student registered for that course to a "special course" called Deleted-couse and link that cid to the student. the cid for this special course could be 999999.
Re: Joining Tables
May 22, 2008 09:28PM
Hi..Hope the assignment is going well.. smile

I have never used a cookie or session to pass from one page to another. Would you care to give more info on that.

When passing through url it is dangerous when sending personal info but i have seen many places where it is used. Facebook for example uses:

http://www.facebook.com/profile.php?id=231333333

If I have a database table and i want to delete a user.

Page 1:

<a href = 'delete.php?id=1'>Delete user</a>

Page 2:

$id = $_REQUEST['id'];

DELETE from users WHERE uid='$id'

I find this method simple and easy, but in programming many ways can be used to achive the same outcome. Thats what makes it so great. Can always learn new methods.

JwV >> How far with the prac. Coming along well. smile

Enjoy all
Re: Joining Tables
May 22, 2008 09:30PM
Sorry my example became a link.

Here it is again.

"profile.php?id=233333333"

smile
JwV
Re: Joining Tables
May 23, 2008 04:42PM
Hi there MuhammadA

Prac is nearly there. Busy writing the update student and delete student pages and then I need to finish the report, then I am done.Then starts the testing, and adding the bells and whistles.


regarding sessions. I am using sessions thru out my prac. Each page starts with session_start(); then to load data into the session I use $_SESSION['init'] = $init; and to extract it I use $frm_init = $_SESSION['init'];. See chapter 5 (I think) in the masterskill manual for more info. All info I need is stored there. No hidden fields in forms or passing info from page to page. For some info I do use forms (POST and GET). Just remember to pass the session id from page to page. Hope this helps.
JwV
Re: Joining Tables
May 24, 2008 09:31PM
Mac

Must one pass the session id from page to page [ eg student_admin.php?' . session_id() ], or can one only have the statement session_start(); at the top of each page.
JwV
Re: Joining Tables
May 24, 2008 09:41PM
Hi MuhammadA

Sorry for my short previous answer, was a bit of a rush.

Here is some more session info.

start each page with the statement session_start();

to move a value into the session "file" you do : $_SESSION['keep-sname'] = $sname;

to get a value from the session "file" you use $frm_init = $_SESSION['keep_sname'];

to call the next page use something like <a href="student_admin.php?' . session_id() . '">

Hope this is of some help.
Re: Joining Tables
May 24, 2008 11:04PM
Hi..

Thanx for that explaination. I didn use that in all my pages but did use it now in few sections. Works quite nice. smile

Thanks so much.

Hope the assignment coming along well.
avatar
Mac
Re: Joining Tables
May 26, 2008 06:51AM
session_start is fine
JwV
Re: Joining Tables
May 26, 2008 02:24PM
Hi all

Mac >> Thanks for the info. I am passing the session id through out the prac. I am leaving it in, since everything is working. Will remove it later and see how it works. I hope that leaving it in will no count against me??? eye rolling smiley

MuhammadA >> Hi there. Hope you are well. I finished my prac late last night. hot smiley
Now just need to do some testing and see if everything works. Oh Yeah!!! Finished at last.

To all still busy with development - good luck to you all.
Re: Joining Tables
May 26, 2008 02:39PM
Hi there. Thats good smile I sat the entire saturday finishing mine. Its done but testing every possible way that could cause an error. Everything is working so far. Thankfully.

Good luck with the testing and to everyone else good luck and enjoy. smile
JwV
Re: Joining Tables
May 28, 2008 09:03AM
Hi all

Started a new thread call Prac problems. Got a problem there which I hope somebody can solve for me.
Sorry, only registered users may post in this forum.

Click here to login