PHP MySQL, check for duplicates in two tables

Question

I am working on a program that allows students to sign up for a placement test. If we have the student ID in the student_data table, we add information to the additional_data table that stores test date and time info. If student is not in the student_data table, they're added to untracked_attendants that stores test date and time info.

Here is what I need to do: If the student is in additional_data or untracked_attendants and the test date is the same as the new entry, I don't want the seats taken to increment.

I've looked for an answer to this for a couple of days now and tried different variations of my query with no success. Below are the answers I've gotten tips from:

The $query1 below check if the student exists in the additional_data table already but I need to also check if the placement date is the same. If it is, then I don't want to increment the number of seats taken. The AND clause here isn't working for me. Without the AND, it's fine.

$query1 = $db->prepare("SELECT AES_DECRYPT(student_data_id, '".KEY."')
    FROM additional_data
    WHERE AES_DECRYPT(student_data_id, '".KEY."') = :studentDataId
    AND placement_date = :placementDate");
$query1->bindParam(":studentDataId", $studentDataId);
$query1->execute();

$query2 just doesn't work at all. It always returns 0 event if I can see that the id exists

$query2 = $db->prepare("SELECT AES_DECRYPT(stu_id, '".KEY."')
    FROM untracked_attendants
    WHERE AES_DECRYPT(stu_id, '".KEY."') = :stuId
    AND test_date = :placementDate");
$query2->bindParam(":stuId", $stuId);
$query2->execute();

Here is the entire code from my document. (the code between the two lines of *** is what I've changed from when I inherited the program when it incremented seats no matter what:

//Return test dates as JSON
if($_POST['action'] == "getDates") {
    $query = $db->prepare("SELECT * FROM test_dates WHERE active = 1 ORDER BY date ASC");
    $query->execute();
    echo json_encode($query->fetchAll());
}

//Checks if the test date is still activated and returns an appropriate response
if($_POST['action'] == "checkDate") {
    $id = strip_tags($_POST['id']);
    $query = $db->prepare("SELECT id FROM test_dates WHERE id = :id AND active = 1");
    $query->bindParam(":id", $id);
    $query->execute();
    if($query->rowCount() > 0) {
        echo "open";
    }
    else {
        echo "closed";
    }
}

//Return test types as JSON
if($_POST['action'] == "getTests") {
    $query = $db->prepare("SELECT * FROM test_types");
    $query->execute();
    echo json_encode($query->fetchAll());
}

//Save the placement test registration
if($_POST['action'] == "save") {
    $uid = filter_var(strip_tags(trim($_POST['uid'])), FILTER_SANITIZE_STRING);
    $id = filter_var(strip_tags(trim($_POST['id'])), FILTER_SANITIZE_NUMBER_INT);
    $fname = filter_var(strip_tags(trim($_POST['firstName'])), FILTER_SANITIZE_STRING);
    $lname = filter_var(strip_tags(trim($_POST['lastName'])), FILTER_SANITIZE_STRING);
    $stuId = filter_var(strip_tags(trim($_POST['stuId'])), FILTER_SANITIZE_NUMBER_INT);
    $email = filter_var(strip_tags(trim($_POST['emailAddress'])), FILTER_SANITIZE_EMAIL);
    $retake = filter_var(strip_tags(trim($_POST['retake'])), FILTER_SANITIZE_NUMBER_INT);
    $location = filter_var(strip_tags(trim($_POST['location'])), FILTER_SANITIZE_STRING);
    $testDate = filter_var(strip_tags(trim($_POST['testDate'])), FILTER_SANITIZE_NUMBER_INT);
    if(isset($_POST['testTypes'])) {
        $testTypes = strip_tags(trim($_POST['testTypes']));
        $testTypes = str_replace("|", " ", $testTypes);
    }
    else {
        $testTypes = "";
    }

    //If the student already exists then add data relating to that record
    $query = $db->prepare("SELECT id,
            AES_DECRYPT(student_id, '".KEY."') AS student_id
            FROM student_data
            WHERE AES_DECRYPT(student_id, '".KEY."') = :student_id");
    $query->bindParam(":student_id", $stuId);
    $query->execute();

    if($query->rowCount() > 0) {
        $row = $query->fetch();
        $studentDataId = $row['id'];

        $query = $db->prepare("SELECT AES_DECRYPT(student_data_id, '".KEY."')
                FROM additional_data
                WHERE AES_DECRYPT(student_data_id, '".KEY."') = :studentDataId");
        $query->bindParam(":studentDataId", $studentDataId);
        $query->execute();

        //If there is already additional data then update it
        if($query->rowCount() > 0) {
            $query = $db->prepare("UPDATE additional_data
                    SET uid = :uid,
                    placement_date = :placementDate,
                    placement_room = :placementRoom,
                    placement_time = :placementTime,
                    tests_needed = :testsNeeded
                    WHERE AES_DECRYPT(student_data_id, '".KEY."') = :studentDataId");
            $query->bindParam(":uid", $uid);
            $query->bindParam(":studentDataId", $studentDataId);
            $query->bindParam(":placementDate", date("Y-m-d", $testDate));
            $query->bindParam(":placementRoom", $location);
            $query->bindParam(":placementTime", date("H:i:s", $testDate));
            $query->bindParam(":testsNeeded", $testTypes);
            $query->execute();
        }

        //If not insert a new record
        else {
            $query = $db->prepare("INSERT INTO additional_data
            (uid,
            student_data_id,
            placement_date,
            placement_room,
            placement_time,
            tests_needed)
            VALUES
            (:uid,
            AES_ENCRYPT(:studentDataId, '".KEY."'),
            :placementDate,
            :placementRoom,
            :placementTime,
            :testsNeeded)");
            $query->bindParam(":uid", $uid);
            $query->bindParam(":studentDataId", $studentDataId);
            $query->bindParam(":placementDate", date("Y-m-d", $testDate));
            $query->bindParam(":placementRoom", $location);
            $query->bindParam(":placementTime", date("H:i:s", $testDate));
            $query->bindParam(":testsNeeded", $testTypes);
            $query->execute();
        }
    }

    //If the student does not exist in then add it into an untracked attendants table
    else {
        $query = $db->prepare("INSERT INTO untracked_attendants
                VALUES(null,
                :uid,
                AES_ENCRYPT(:fname, '".KEY."'),
                AES_ENCRYPT(:lname, '".KEY."'),
                AES_ENCRYPT(:stuId, '".KEY."'),
                AES_ENCRYPT(:email, '".KEY."'),
                :testDate,
                :location,
                :testTime,
                :retake,
                :testsNeeded)");
        $query->bindParam(":uid", $uid);
        $query->bindParam(":fname", $fname);
        $query->bindParam(":lname", $lname);
        $query->bindParam(":stuId", $stuId);
        $query->bindParam(":email", $email);
        $query->bindParam(":testDate", date("Y-m-d", $testDate));
        $query->bindParam(":location", $location);
        $query->bindParam(":testTime", date("H:i:s", $testDate));
        $query->bindParam(":retake", $retake);
        $query->bindParam(":testsNeeded", $testTypes);
        $query->execute();
    }

    /*************************************************************************
    *************************************************************************/
    $query1 = $db->prepare("SELECT AES_DECRYPT(student_data_id, '".KEY."')
                FROM additional_data
                WHERE AES_DECRYPT(student_data_id, '".KEY."') = :studentDataId
                AND placement_date = :placementDate");
    $query1->bindParam(":studentDataId", $studentDataId);
    $query1->execute();

    $query2 = $db->prepare("SELECT AES_DECRYPT(stu_id, '".KEY."')
                FROM untracked_attendants
                WHERE AES_DECRYPT(stu_id, '".KEY."') = :stuId
                AND test_date = :placementDate");
    $query2->bindParam(":stuId", $stuId);
    $query2->execute();

    // if the test date on the additional_data and untracked_attandants
    // table for the current student is different from the date on the 
    // current submission, increment seats filled
    if($query2->rowCount() == 0){
        //Increment the number of seats filled for the test in question
        $query = $db->prepare("UPDATE test_dates SET seats_filled = seats_filled + 1 WHERE id = :id");
        $query->bindParam(":id", $id);
        $query->execute();

        //Check if the date should be closed off
        $query = $db->prepare("SELECT date, location, seats_max, seats_filled FROM test_dates WHERE id = :id");
        $query->bindParam(":id", $id);
        $query->execute();
        $row = $query->fetch();
        if($row['seats_max'] == $row['seats_filled']) {
            $query = $db->prepare("UPDATE test_dates SET active = 0 WHERE id = :id");
            $query->bindParam(":id", $id);
            $query->execute();

            //Check if there's another room with the same date that should be opened automatically
            //If this is true, activate the room
            $query = $db->prepare("UPDATE test_dates SET active = 1 WHERE id != :id AND date = :date AND seats_max != seats_filled");
            $query->bindParam(":id", $id);
            $query->bindParam(":date", $row['date']);
            $query->execute();
        }
    }
    /*********************************************************************/
    /*************************************************************************/
}
}

EDIT: Here is my schema

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE additional_data (
  id int(11) NOT NULL AUTO_INCREMENT,
  uid char(23) NOT NULL,
  student_data_id blob NOT NULL,
  placement_code int(2) NOT NULL,
  sent_wr_to_english tinyint(1) NOT NULL,
  mail_date date NOT NULL,
  tests_needed varchar(20) NOT NULL,
  placement_date date NOT NULL,
  placement_room varchar(30) NOT NULL,
  placement_time time NOT NULL,
  orientation_date date NOT NULL,
  orientation_group varchar(20) NOT NULL,
  confirm_test tinyint(1) NOT NULL,
  attended_test tinyint(1) NOT NULL,
  rsvp_orientation tinyint(1) NOT NULL,
  attended_orientation tinyint(1) NOT NULL,
  tech_prep_complete tinyint(1) NOT NULL,
  student_accounts tinyint(1) NOT NULL,
  it_letters tinyint(1) NOT NULL,
  sent_email_reminder tinyint(1) NOT NULL,
  sent_august_mail tinyint(1) NOT NULL,
  no_schedule tinyint(1) NOT NULL,
  change_test date NOT NULL,
  notes text NOT NULL,
  honors tinyint(1) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE student_data (
  id int(11) NOT NULL AUTO_INCREMENT,
  student_id blob NOT NULL,
  last_name blob NOT NULL,
  first_name blob NOT NULL,
  address1 blob NOT NULL,
  address2 blob NOT NULL,
  city blob NOT NULL,
  state blob NOT NULL,
  zip blob NOT NULL,
  major blob NOT NULL,
  major2 blob NOT NULL,
  sex blob NOT NULL,
  student_type blob NOT NULL,
  phone blob NOT NULL,
  satr blob NOT NULL,
  satm blob NOT NULL,
  attdent_type blob NOT NULL,
  gpa blob NOT NULL,
  ptma blob NOT NULL,
  ptwr blob NOT NULL,
  ptre blob NOT NULL,
  ptlg blob NOT NULL,
  pths blob NOT NULL,
  waiver blob NOT NULL,
  tbrdepo_term_code blob NOT NULL,
  goremal_email_address blob NOT NULL,
  gobtpac_external_user blob NOT NULL,
  download_date date NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY student_id (student_id(16))
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE test_dates (
  id int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  location varchar(30) NOT NULL,
  seats_max tinyint(3) NOT NULL,
  seats_filled tinyint(3) NOT NULL DEFAULT '0',
  MA tinyint(1) NOT NULL,
  RE tinyint(1) NOT NULL,
  WR tinyint(1) NOT NULL,
  LG tinyint(1) NOT NULL,
  active tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE test_types (
  id int(11) NOT NULL AUTO_INCREMENT,
  test_type varchar(10) NOT NULL,
  active tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE untracked_attendants (
  id int(11) NOT NULL AUTO_INCREMENT,
  uid char(23) NOT NULL,
  fname blob NOT NULL,
  lname blob NOT NULL,
  stu_id blob NOT NULL,
  email blob NOT NULL,
  test_date date NOT NULL,
  location varchar(30) NOT NULL,
  test_time time NOT NULL,
  retake tinyint(1) NOT NULL,
  tests_needed varchar(20) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE uploaded_exports (
  id int(11) NOT NULL AUTO_INCREMENT,
  filename varchar(255) NOT NULL,
  uploaded timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(50) NOT NULL,
  email varchar(100) NOT NULL,
  `password` char(128) NOT NULL,
  PRIMARY KEY (id),
  KEY login (username,`password`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE writing_test_data (
  id int(11) NOT NULL AUTO_INCREMENT,
  uid char(23) NOT NULL,
  student_id blob NOT NULL,
  fname blob NOT NULL,
  mi blob NOT NULL,
  lname blob NOT NULL,
  email blob NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

score 0 · Answer 1 · answered Mar 05 '15 at 18:16

0

Ok, so you have a student_id or an attendant_id. In additional data you reference these data to one of these with its id and maybe its type.

if you now want to enter a new record to additional data you just have to perform an duplicate check.

if untracked attendants have their own additional data somewhere, it should work the same.

it is the same principle with test dates. I think you should check your database/table scheme and the keys you are using for relations.

answered Mar 05 '15 at 18:16

Micha El

73
1
8

Thanks for answering. I'm just not sure if I'm typing the query wrong. I think somewhere in the AND clause I'm getting something wrong. Are you saying my query should be working the way it is now? – Besart Bytyqi Mar 06 '15 at 13:43
maybe you should post the table schemas – Micha El Mar 06 '15 at 15:54
"seats_filled" from TABLE test_dates shall not be incremented, right? – Micha El Mar 09 '15 at 12:43
in untracked_attendants there is only the id unique...what makes the "person" there unique? which combination of fields? – Micha El Mar 09 '15 at 12:45
stu_id is unique for each person. But they could potentially sign up for more than one test. So, I want to not increment the number of filled seats only if the stu_id AND test_date are the same in the table as what the student submitted. I've thought about just adding a checkbox when the students sign up that says, "I want to reschedule" or something to that effect. Just not sure if there's a loophole I'm not thinking of that would throw my numbers off. – Besart Bytyqi Mar 09 '15 at 14:04
Ok, so here is my proposal: How about adding/updating the student and trigger a calculation afterwards for the seats taken. Are you familiar with stored procedures/ triggers in mySQL? – Micha El Mar 09 '15 at 14:11
Or add a relation table which combines tests with users untracked or not. To be honest, the data structure is a bit strange. you have students, non_students, tests and so on..... – Micha El Mar 09 '15 at 14:13

PHP MySQL, check for duplicates in two tables

1 Answers1