Practical

AlessioLaRuffa Wrote:
-------------------------------------------------------
> @Mac:
>
> I get the following error when pasting the code
> into the SQL tab:

mmm. try this

Language: SQL
CREATE TABLE IF NOT EXISTS `course` (
  `cname` VARCHAR(50) NOT NULL DEFAULT ';';,
  `cid` INT(6) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`cid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `course_student`
--
 
CREATE TABLE IF NOT EXISTS `course_student` (
  `sno` INT(6) NOT NULL DEFAULT ';0';,
  `cid` INT(6) NOT NULL DEFAULT ';0';,
  `year` INT(4) NOT NULL DEFAULT ';0';
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `student`
--
 
CREATE TABLE IF NOT EXISTS `student` (
  `sno` INT(6) NOT NULL AUTO_INCREMENT,
  `sname` VARCHAR(40) NOT NULL DEFAULT ';';,
  `init` VARCHAR(5) NOT NULL DEFAULT ';';,
  `fname` VARCHAR(40) NOT NULL DEFAULT ';';,
  `title` VARCHAR(4) NOT NULL DEFAULT ';';,
  `msname` VARCHAR(40) NOT NULL DEFAULT ';';,
  `dob` VARCHAR(8) NOT NULL DEFAULT ';';,
  `sex` CHAR(2) NOT NULL DEFAULT ';';,
  `lang` VARCHAR(10) NOT NULL DEFAULT ';';,
  `idno` VARCHAR(13) NOT NULL DEFAULT ';';,
  `telh` VARCHAR(12) NOT NULL DEFAULT ';';,
  `telw` VARCHAR(12) NOT NULL DEFAULT ';';,
  `cel` VARCHAR(12) NOT NULL DEFAULT ';';,
  `fax` VARCHAR(12) NOT NULL DEFAULT ';';,
  `email` VARCHAR(40) NOT NULL DEFAULT ';';,
  `address` tinytext NOT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1  ;

theSinthesizer Wrote:
-------------------------------------------------------
> @mac or any other brainiac
>
> i found the following generic code on the Web for
> adding users to a database:

When you add users make use of phpmyadmin the privileges tab - do not use root with no password and do not givce all priviliges! If you code is not secure a hacker can delete all you dbs etc etc..

rogerdurbs Wrote:
-------------------------------------------------------
> Just to be 100% sure on this.
> course_students links a student to a course.
> What should i store in the year field? Just
> "2010"?

No, just the course-student info... yes, year is just 2010.

Please start a new prac topic - this is getting too long.

Dear fellow PHP code monkeyz,

please can you tell me why my program won't input records into my database.

Here's my MySQL code for granting myself access to the database "registration"

Language: SQL
GRANT ALL   
ON registration.*
TO lizwe@localhost
IDENTIFIED BY ';';;

Here's my PHP code for adding a record:

Language: PHP
<?php
$title = "Course Manager";
//getting the value of "course_name" variable from the add_course.html page
$course_name = $_GET[';course_name';];
//connecting to the database
$link_id = mysql_connect("localhost", "lizwe", "");
//testing connection
if ($link_id) echo "Success in connecting!";
else echo "Failed to connect.";
//inserting value of "course_name" variable into the table "course"
mysql_select_db("registration");
$query = "INSERT INTO course VALUES (';$course_name';)";
$result = mysql_query($query);
//to test if the record was added to the database
$rows = mysql_affected_rows();
if ($rows < 1) echo "Error. The record has not been added.";
else "$rows record has been added";
 
?>

>>>
The One that owns The Technology rulez the world!

The insert statement... how will mysql know into which column it must insert $course_name since there is a course id? If there was just one column, then it would have been correct.

And just a warning to all - the connection details must be in a single config file and included in all your pages so that I can change the db and connection details in one single file. I am not going to change it in every file!

@mac,

thanks for your response mac - i appreciate it.

There's something i don't get....

since i'll be stating the columns, how then do i make course_id automatically increment?

Also, which "config.inc" file do I include since there seem to be many in my Wamp folder?

>>>
The One that owns The Technology rulez the world!

course_id must be set in mysql as automatic increment, meaning when you do

Language: PHP
INSERT INTO course VALUES (';';, ';$course_name';)";

the first empty ' ' will tell mysql to add the record ($course_name) and give it the next course_id

Did you download the prac tut letter? config.inc is a file you create that contains connection details.

thanks mac!

>>>
The One that owns The Technology rulez the world!

please can you tell me why the switch/case statement won't execute?

My HTML form for adding a course name has the following line (there's other code, but I decided to only post the relevant code):

Language: HTML
<input name=';option'; TYPE=';hidden'; value=';add';>

I then have the following code in my course_man.php page:

Language: PHP
//Checking to see if the request came from adding, deleting or editing the course_name
$option = $_GET[';option';];
//selecting appropriate action to take based on the value of "option" variable
switch ($option) {
 case "add" :
 //getting the value of "course_name" variable from the add_course.html page
 $course_name = $_GET[';course_name';];
 //inserting value of "course_name" variable into the table "course"
 mysql_select_db("registration");
 $query = "INSERT INTO course VALUES (';$course_name';, ';';)";
 $result = mysql_query($query);
 //to test if the record was added to the database
 $rows = mysql_affected_rows();
 if ($rows < 1) echo "Error. The record has not been added.";
 else echo "$rows record has been added";
 break;
 }

The program should be entering the case "add" statement because I'm only accessing this page from the page with the above HTML.

Please help.

>>>
The One that owns The Technology rulez the world!

The problem may just lie with the other code you did not include! Is you form method GET? What happens when you echo $option on the next page - is it echoing "add"?
e.g.

Language: PHP
$option = $_GET[';option';];
echo "option = $option";

Hi mac,

i was using GET in the form.

it worked only after i saved the form as a .php file. Very queer indeed....
Wonder y?

>>>
The One that owns The Technology rulez the world!

XAMPP is not set up to interpret php code in an .htm or .html file.

You could specify that though, e.g.

Create a file called .htaccess, save it in the root (htdocs) . Type this into that file:

RemoveHandler .html .htm
AddType application/x-httpd-php .php .htm .html

This tells teh server that files with the extensions .php .htm and .html must be interpreted by php.

But generally it is better to just leave it .php, for various reasons (such as speed).

I need to clear something up - Can a student register for more than one course?

If the answer is yes, then why would we not separate the student registration from the student course registration? - why would a student need to type in his details more than once if he is going to register for several courses? I was working on this assumption until I realised I may be on the wrong track.

I was proposing to register the student - give him his student number and then allow him to register for whatever courses he wants to - then - in the greater scheme of things - he can always search for his registration details using a unique identifier - his student number - (I do understand that this is out of scope)

As you say, it is out of scope.... This is certainly not a functional or smart registration system - there are 100's of ways in which it can be bettered. But I have to limit what is required otherwise each new functionality will lead to another shortcoming! How the system is working - or is supposed to work - is less important here, since I am interested in you being bale to get the basics right. E.g. if you can pull and update info from the db, it means that you can write more complex systems. I am not testing your logic in writing the program, I am testing your coding skills. Having said that, it is good that you are questioning the logic, and nothing stops you from allowing a student to register fro more than one course. He would not need to type his details in mor et5han once - he will type it in once, and select his courses from an array of courses.

@mac,

what do you mean by this?

"Note the student registration page is the very same page a student will see when he/she registers for a course -
the link will just be named "Register for a course". "

>>>
The One that owns The Technology rulez the world!

There are two sides to a registration system - and admin side and a student side. In this case, admin and students use the same page. Of course, in "real" life, you would have many differences, but we are testing your coding skills here - not your logic.

Hi Mac,

Can I create logins to distinguish between admin users and normal users by setting different access levels?
Can I for example link a logged in student with the edit / delete facility for records linked only to their student ID, and pure simple search functionality for non logged in users as well as a separate admin section if access level tests > '2' I would achieve this through sessions.
I am feeling the need to lay this out for a real world scenario.
Please let me know if this is acceptable.

If you want to - can't sit still can you? But I mark only what is required, i.e. you will not necessarily get more marks .

Afternoon Mac

Sorry to bug you, but I just want to find out in the Database under "course_student" there is a "fmark" field. Is there something specific that must go in there or does it stay blank?

Armand

Blank.....