Best way to simply check if an entry exists in a table

Beginners - Learning PHP
#1

In the table allstudentsAnswers19BE I want to check if a number exists in the column studentnr.

Maybe I have:

$studentnr = ‘1925010101’

At the moment I wrote this:

//get the user with his or her studentnr
		$stmt = $pdo->prepare('SELECT * FROM allstudentsAnswers19BE WHERE studentnr = :studentnr');
		try{
			$stmt->execute(['studentnr' => $studentnr]);
			//check if the studentnr exists
			if($stmt->rowCount() > 0){
			// if the student number exists, insert a new row with the name, student number, week number and the answers

I suppose this will work, but seems a bit excessive.

Is there a simpler way just to check if the student number is in the table before I continue, or exit if the number is not in the table?

#2

MySQL EXISTS is your friend here:

$stmt = $pdo->prepare(
    'SELECT EXISTS(SELECT * FROM allstudentsAnswers19BE WHERE studentnr = :studentnr) AS found'
);
$stmt->execute(['studentnr' => $studentnr]);

if($stmt->fetchObject()->found == 1){
    // you found a result...
}

This doesn’t look simpler than your code, but the underlying SQL will be much more efficient as the EXISTS function will stop looking as soon as it finds a single result.

#3 
$query = "SELECT 1 FROM allstudentsAnswers19BE WHERE studentnr = :studentnr";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(':studentnr', $studentnr);
    $stmt->execute();
    $row = $stmt->fetch();
    if ($row) {
        return true; // studentnr is in database table
    }

I personally do the above as it really isn’t all that much slower unless you have a really really large database table.

1 Like
#4

If you are checking and inserting data into the same table, this method has an initial condition problem, in that it will fail for the first ever row of data for any student number. Is that the desired operation? I suspect you are doing this to validate the student number. If so, you would need to validate against the primary table where the student numbers are stored.

Who is the user on your site that is causing this code to be executed? Is it a student or you, an administrator?

If name is the student name, don’t include it in the data in the answers table. The student number uniquely identifies who the data belongs to. Don’t insert redundant information.

1 Like
#5

@Strider64: Thanks, that works perfectly!

@phdr You are of course right! I already changed it to check the course list of students, see if the number is in there. Last week someone put a strange number in and it caused me a lot of grief! Also, I had some entries already, which I put in from the command line, to try the mysql.

This code runs when a student clicks “Submit homework”.

At the moment, PHP just writes each answer to a text file, saved with the student number. Then I use rsync to download the files and Python to mark and insert the results in the timetable. (My MySQL skills are very limited, as you know,)

Last week I thought,

"If I save each student’s answers each week to a table, I can easily check them against the correct answers, assign a score and using JOIN make an output.php which shows each student the right answers, their answers and their score. "

Thanks to astoncipher here, I have assimilated some knowledge of Foreign Keys, and I use the student number from the primary course list as a Foreign Key in this table. That way, each student can have multiple entries. The column weeknr identifies which week.

So that’s what I am attempting now. I can now save the answers, that’s step 1.

Sponsor our Newsletter | Privacy Policy | Terms of Service