PHP PDO Switch Database Record to Another Table

jmyrtle · November 24, 2020, 8:20pm

I have one more piece I need to add to my code for the next version to be ready.

At the moment, you can mark attendance and absence records using this code:

Attendance:

<?php

include('nav/head.php');

$msg = "";

// Prepare SQL Statement
$stmt = $pdo->prepare("INSERT INTO attendance (member_id, member_email, 
member_phone, present, attend_state) VALUES (:member_id, :member_email, 
:member_phone, :present, :attend_state)");
$stmt->bindParam(':member_id', $member_id);
$stmt->bindParam(':member_email', $member_email);
$stmt->bindParam(':member_phone', $member_phone);
$stmt->bindParam(':present', $present);
$stmt->bindParam(':attend_state', $attend_state);

// insert a row and add new record
if(isset($_POST['btn_save'])) {
    try {
      $id = $_POST["member_id"];
      $ary = explode("-", $id);
      $member_id = $ary[0];
      $member_email = $_POST["member_email"];
      $member_phone = $_POST["member_phone"];
      $present = $_POST["present"];
      $attend_state = $_POST["attend_state"];
      $stmt->execute();
      header('Location: record_attn.php');
      exit();
    } catch(PDOException $e) {
      $msg = "This delegate has already been marked present";
    }
}

// insert a row and exit attendance screen
if(isset($_POST['btn_close'])) {
    try {
      $id = $_POST["member_id"];
      $ary = explode("-", $id);
      $member_id = $ary[0];
      $member_email = $_POST["member_email"];
      $member_phone = $_POST["member_phone"];
      $present = $_POST["present"];
      $attend_state = $_POST['attend_state'];
      $stmt->execute();
      header('Location: index.php');
      exit();
    } catch (PDOException $e) {
      $msg = "This delegate has already been marked present";
    }
}

// return to home screen
if(isset($_POST['btn_cancel'])) {
  header('Location: index.php');
}

// Populate Address Field


?>

Absence:

<?php

include('nav/head.php');

if($role_id != '1') {
	header('Location: error.php');
	exit();
}

$msg = "";

// Prepare SQL Statement
$stmt = $pdo->prepare("INSERT INTO absence (member_id, member_email, 
member_phone, absence_desc) VALUES (:member_id, :member_email, 
:member_phone, :absence_desc)");
$stmt->bindParam(':member_id', $member_id);
$stmt->bindParam(':member_email', $member_email);
$stmt->bindParam(':member_phone', $member_phone);
$stmt->bindParam(':absence_desc', $absence_desc);

// insert a row and add new record
if(isset($_POST['btn_save'])) {
	try {
		$id = $_POST["member_id"];
  	$ary = explode("-", $id);
  	$member_id = $ary[0];
  	$member_email = $_POST['member_email'];
  	$member_phone = $_POST['member_phone'];
  	$absence_desc = $_POST["absence_desc"];
  	$stmt->execute();
  	header('Location: record_abs.php');
		exit();
	} catch (PDOException $e) {
		$msg = "This delegate has already filed an excused absence";
	}
}

// insert a row and exit attendance screen
if(isset($_POST['btn_close'])) {
		try {
  	$id = $_POST["member_id"];
  	$ary = explode("-", $id);
  	$member_id = $ary[0];
  	$member_email = $_POST['member_email'];
  	$member_phone = $_POST['member_phone'];
  	$absence_desc = $_POST["absence_desc"];
  	$stmt->execute();
  	header('Location: index.php');
		exit();
	} catch (PDOException $e) {
		$msg = "This delegate has already filed an excused absence";
	}
}

?>

I need to create a feature in this code where if I put someone in as absent and then they show up, that person should be converted to the attendance list. The absent record should be deleted and a new attendance record should be made.

If the person is marked present and then I try to enter an absent record for them, a warning should appear indicating the person is already in the attendance list.

The in-person takes precedence over the absentee request. What’s the best way to do it?

m_hutley · November 25, 2020, 2:22am

I’m trying not to sound insulting, but ‘redesigning the database’ is the first thing that comes to mind.

Why is attendance/absence two different tables? Why do those tables contain email, phone numbers (fields that, unless I’m mistaken, belong to the members, and would already be referencable via the member_id?

droopsnoot · November 25, 2020, 8:34am

My thought on that last time it appeared was that perhaps there was a need to record those details at the time of the absence or attendance, in case they later change. I’m not sure what that need would be, but it was the only thing I could come up with to explain the duplication of data.

Unless the data tables support more than one event, then surely it’s just a case of deleting the rows for that member_id from the “absent” table using their member id, and inserting them into the “attendance” table? But as above, it does seem very strange to have separate tables for this. I’m also not sure what would be in the “present” column in your attendance table, but if that’s just a flag to say that the delegate is present, surely just the fact that they’re in the “attended” table would indicate that?

jmyrtle · November 25, 2020, 3:43pm

Redesigning the database means rewriting the code and that would take a long time for me to get back to where I am now.

You would be correct on this. However, the email field is not in the members table therefore it cannot be referenced via member ID. The phone number can, but since it comes from another source, it is ideal to add the phone number here so that when new data is generated the following year, then the data would be up to date.

@droopsnoot is right when he says:

When the new data is imported the next year, the new data would be shown.

Yes, but there needs to be a place for the attendance and absence records to be shown. It’s ideal to include different tables that way the query would be less complicated.

At least this is how I look at it…

benanamen · November 25, 2020, 4:35pm

This is exactly why I am always telling people to get the database right FIRST. It is the foundation of all the code you will ever write against it. If the DB is wrong, then all the code you ever write against it will be wrong as well.

You are falling for the Sunk Cost Fallacy.

Individuals commit the sunk cost fallacy when they continue a behavior or endeavor as a result of previously invested resources (time, money or effort) (Arkes & Blumer, 1985). This fallacy, which is related to loss aversion and status quo bias, can also be viewed as bias resulting from an ongoing commitment.

If you continue on your path without fixing the database you will continue to compound your problems ending up with increasing Technical Debt that will one day have to be paid at great expense of time, money and effort.

jmyrtle · November 25, 2020, 5:51pm

So how then should I restructure the database?

benanamen · November 25, 2020, 5:59pm

First step is to learn about “Database Normalization” then apply what you learned. It is not that complicated so you it wont take long to learn it. After you have applied what you learned, post the SQL dump of your new design and we will review it.

jmyrtle · November 25, 2020, 6:17pm

So here is my current database:

Absence and attendance hold the records when someone is marked present or absent accordingly:

Absence:

Attendance:

Members holds the entire list of people regardless if they are present or absent.

Members:

A roles table is used to define the user type and the pages they can access:

Roles:

And finally, a users table holds the list of users who have access to run the program:

Users:

There are two SQL views I had to make since the member ID links back to the person’s name. One for attendance and the other absence:

Attendance View:

Absence View:

Just by looking at this, what needs to be normalized?

benanamen · November 25, 2020, 6:20pm

I am more than happy to help, but it seems you have not taken any time to do what I already advised. When you learn Normalization you wont be asking this question.

jmyrtle · November 25, 2020, 6:22pm

I’m just confused as to what form it is in. When I read this:

it appears that I am in 1NF because it follows those 4 rules. Am I on the right track or do I have the wrong understanding?

benanamen · November 25, 2020, 6:26pm

See if this helps https://www.guru99.com/database-normalization.html

The first clue to a normalization problem is duplicate data. It is quite clear that you do have duplicate data. You should not. Start there.

m_hutley · November 25, 2020, 7:34pm

Let’s start with something simple by way of example. One that has already been brought up in this thread.

Why?

If I attend a class (i assume it’s a class system), and my email is m_hultey@example.com (it’s not.). You write into your table, I attended the class on this date, and my email is m_hultey@example.com.

The next day, I attend class again.
What are the chances that my email has changed in the last 24 hours? What’re the chances that I have multiple email accounts for your recordkeeping that i’m using at the same time? Why do you need to write the email down again on day 2?

jmyrtle · November 25, 2020, 8:02pm

It isn’t. It is a convention attendance program that takes place once a year.

Because the data could possibly change within the next year.

Because the email address of the person is not contained in this data. The members table is only what is imported, not what is recorded.

So, even though an email is recorded (whether it is a mark absence or attendance), it is used later to send a mass email thanking those who attended and send out other information to those who were absent.

droopsnoot · November 26, 2020, 8:57am

If the information has changed, is there any need to have the old email address from the previous year? If not, then it makes more sense for the email address to be in the members table, just like their postal address or telephone number.

Then you would update the members table to reflect the new email address, surely?

There’s no reason you couldn’t have a column in the members table ready for the email address, though. It is still more logical and therefore more easy to understand if you store the members information in the members table, and their attendance information in the attendance table.

If you have to retrieve the persons email address so you can mail them next year about another conference, how will you do it? It’s easy if they’ve only been to one conference (except that you have to trawl through two tables to get everyone that was invited, whether they attended or not), but what if they’ve been to two conferences a year apart, and the email addresses are different? Which one do you use? I don’t see anything in the attendance table dealing with dates, so you can’t just use the newest, but you could if you just overwrote the members table with a new email address. Or do you blank the database every time there’s a new conference? That seems unusual, people who organise conferences love to build up databases of previous attendees.

Just like in an accounting system, for example a bank account, you’d record the header information (the personal details of the person who owns the account, their usual branch, that kind of thing) in one table and the transaction information (date, reference, amount, dr/cr) in a different table. In that example, if the counter staff were asked to gather email addresses whenever a customer comes into the bank to pay in money, it would go into the account table, not the transaction table.

system · February 25, 2021, 3:57pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.