How to catch a wrong student number input here?

General PHP Help
#1

I’m trying to use mysql and php to show students their scores. The form page has 2 text input fields: class name, like 19BE1 and student number, a 10 digit number.

The code below does what I want, as long as the class and student number are correct. If there is a problem connecting with mysql, the catch (PDOException $e) will send an error message. I put a link in the error page back to the form.

For example, there is no class 19BE4, and therefore no mysql table 19BE4. If I write 19BE4, I get a catch (PDOException $e) error.

However, if I enter a wrong student number, I end up back at my formpage. Not quite sure how that happens! I would like an error message and a link to the formpage, or an error message in the formpage (form.html.php).

I’ve tried various things, but I haven’t succeeded yet. Any tips on how to get a nice error message if the student number is wrong please?

<?php
include $_SERVER['DOCUMENT_ROOT'] . '/includes/magicquotes.inc.php';

include $_SERVER['DOCUMENT_ROOT'] . '/includes/studentdbReadfrom.inc.php';

if (isset($_POST['getclass'])){
 
try
{
	$class = $_POST['getclass'];
	$studentnum = $_POST['studentnum'];
	
	$sql = 'SELECT number, name, score FROM ' . $class . 'scores WHERE number = ' . $studentnum . ';' ;
	$result = $pdo->query($sql);
	}

catch (PDOException $e)
{
$error = 'Error fetching student data: ' . $e->getMessage();
include 'error.html.php';
exit();
}
}

if(isset($_POST['studentnum'])) {
foreach ($result as $row)
{
$numname[] = array(
'number' => $row['number'],
'name' => $row['name'],
'score' => $row['score'],
);
echo '$row[\'number\'] is ' . $row['number'] ;
echo '<br>';
echo '$row[\'name\'] is ' . $row['name'] ;
echo '<br>';
echo '$row[\'score\'] is ' . $row['score'] ;
echo '<br>';
include 'output.html.php';
exit();
}
}

include 'form.html.php';
?>
#2

I’ll advise in the morning, but your scheme is poorly designed for one thing. More to follow later.

#3

Thanks! I should say, I don’t really know what I’m doing. I don’t really understand what is happening. All I can do is take bits that work in examples from my book and bend the code to suit my purposes. I’m a code bender, not a code writer!

But one things Kevin Yank says clearly in his book: prepare for errors, so the user is not left with a blank screen.

So, thanks for any tips!

#4

Your database scheme should be similar to this,

create table Student
(
    student_num varchar(10)not null primary key,
    first_name varchar(25) not null,
    last_name varchar(25) not null,
    email varchar(50)
)engine=innoDB;

create table Course
(
    id int auto_increment primary key,
    course_num varchar(10) not null -- add comma here if there is more
-- whatever other details you want recorded that are part of the course itself
-- like instructor if it isn't just you 
)engine=innoDB;

create table Student_Scores
(
    id int auto_increment primary key
    course_id int not null,
    student_num varchar(10) not null,
    score int(3),
    score_info varchar(200), -- this is where you would add things like the test name for instance
    date_added datetime default now(),
    FOREIGN KEY (course_id) REFERENCES Course(id),
    FOREIGN KEY (student_num) REFERENCES Student(student_num),
)engine=innoDB;

Now, when you insert data, it is in the correct table; all the course info should be in the course table, the student data in the student table; test data goes in the student_score table. The student_score table is your joining table. It takes the id’s from the other two to create a relationship that you query against; put the scores into and what they are for, you can also add when the score was taken.

You would query it with something like this:

SELECT 
    CONCAT(s.last_name, ', ', s.first_name) as name,
    c.name as course,
    ss.score
FROM
    Student s 
INNER JOIN Student_Score ss ON s.student_num = ss.student_num
INNER JOIN Course c ON ss.course_id = c.id

Also, since you are using PDO, you should be utilizing prepared statements as well. They are quite easy to implement in PDO too. Here is an example for reference:

$sql = "SELECT 
    CONCAT(s.last_name, ', ', s.first_name) as name,
    c.name as course,
    ss.score
FROM
    Student s 
INNER JOIN Student_Score ss ON s.student_num = ss.student_num
INNER JOIN Course c ON ss.course_id = c.id
WHERE
    s.student_num = ?
AND
    c.course_num = ?";
// prepare the statement
$stmt = $pdo->prepare($sql);
// use the passed in values. No need to filter or use other variables.
$stmt->execute([$_POST['studentnum'], $_POST['getclass']]);

// fetch the results and show what was returned
$result = $stmt->fetchAll();
print_r($result);
1 Like
#5

Thanks a lot! I have saved your suggestions. It will probably take me a few days to digest them!

The names will be easier, they are all Chinese. The longest is 4 characters!

I only use PDO because the book I have on PHP & MySQL uses PDO.

Do you think PDO is OK for this? Or should I use msqli? Or something else?

I did try to get mysql to work, but I failed. I’ll have to find another beginner’s book.

#6

PDO is the industry standard and should always be the preference.

What book do you have, I may be able to suggest something.

1 Like
#7

Hi and thanks!

The book is: PHP & MySQL: Novice to Ninja by Kevin Yank

I think it is good for beginners. It has taken me all day today to get my head around INNER JOIN, but I can make it do what I want now!

1 Like
#8

I’ve read that book. Good examples. I was going to tell you to do exactly what you’ve already been told…I’m glad I didn’t have to write it all out but it’s exactly what I would have suggested.

Sponsor our Newsletter | Privacy Policy | Terms of Service