PHP MySQL - Insert if the value doesn't exist in two tables

Question

So I have 2 tables, "rooms" and "scheds". I also have an input form where the values are being inserted into these 2 tables.

This is how the input looks like:
Room ID: ________________
Capacity:________________
-[Submit Button]-

Procedure:
When I input the Room ID and Capacity, it should insert the value in the rooms table just as is, then into the scheds table it will insert 2 same values of Room ID in to the roomid column and automatically adds "1st" and "2nd" consecutively in to the semester column.

The only part that I am having problems with is, I'm thinking of 2 ways on how should I approach this, but unfortunately I can't seem to get the right code for this:

1. If Room ID has a same exact value inside the rooms table, then it should no longer insert it in both of the tables, instead echo that the room already exist.

2. If Room ID already has the "1st" and "2nd" semester value inside the scheds table, then echo that the room already exist.


This is the "rooms" table.

+----+--------+----------+
| id | roomid | capacity |
+----+--------+----------+
| 1  | NB201  | 30       |
+----+--------+----------+
| 2  | NB202  | 30       |
+----+--------+----------+

This is the "scheds" table.

+----+--------+----------+
| id | roomid | semester |
+----+--------+----------+
| 1  | NB201  | 1st      |
+----+--------+----------+
| 2  | NB201  | 2nd      |
+----+--------+----------+
| 3  | NB202  | 1st      |
+----+--------+----------+
| 4  | NB202  | 2nd      |
+----+--------+----------+

Below are the codes that I'm currently working on.

<?php
 if($_POST){  
    try{
        //write query
        $query = "INSERT INTO rooms SET roomid = ?, capacity = ?, roomimage = ?";

        //prepare query for excecution
        $stmt = $con->prepare($query);

        //bind the parameters
        $stmt->bindParam(1, $_POST['roomid']);

        $stmt->bindParam(2, $_POST['capacity']);

        $stmt->bindParam(3, $_POST['roomimage']);

        // Execute the query
        if($stmt->execute()){
            echo "<div class='btn-success'>Room was successfully saved.</div>";
        }else{
            echo "<div class='btn-danger'>Unable to save room.</div>";
        }

    }catch(PDOException $exception){ //to handle error
        echo "<div class='btn-danger'>Error: " . $exception->getMessage() . "</div>";
    }
}
?>

<?php
 if($_POST){ 
    try{        
            //-----2 semesters-----
            if($_POST['semester']=='2'){
            //write query
            $query_roomsched1 = "INSERT INTO sched_2014_2015 SET roomid = ?, semester = ?";

            //prepare query for excecution
            $stmt = $con->prepare($query_roomsched1);

            //bind the parameters
            $stmt->bindParam(1, $_POST['roomid']);

            $stmt->bindValue(2, '1st');

                // Execute the query
                if($stmt->execute()){
                    echo "<div class='btn-success'>Schedule table for 1st semester  was successfully created.</div>";
                }else{
                    echo "<div class='btn-danger'>Unable to create schedule table for 1st semester.</div>";
                }

            //write query
            $query_roomsched2 = "INSERT INTO sched_2014_2015 SET roomid = ?, semester = ?";

            //prepare query for excecution
            $stmt = $con->prepare($query_roomsched2);

            //bind the parameters
            $stmt->bindParam(1, $_POST['roomid']);

            $stmt->bindValue(2, '2nd');

                // Execute the query
                if($stmt->execute()){
                    echo "<div class='btn-success'>Schedule table for 2nd semester was successfully created.</div>";
                }else{
                    echo "<div class='btn-danger'>Unable to create schedule table for 2nd semester.</div>";
                }
             }catch(PDOException $exception){ //to handle error
        echo "<div class='btn-danger'>Error: " . $exception->getMessage() . "</div>";
    }
}
?>

Answer 1

If you want to check if both "id's" exist in two tables, you can use the following using a JOIN and a conditional statement. You can base yourself on that to do the insert or not.

<?php
$user = 'xxxx';
$pass = 'xxxx';

$con = new PDO("mysql:host=localhost;dbname=your_db", $user, $pass);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $con->prepare("

    SELECT * 
    FROM rooms 
    LEFT JOIN scheds 
        ON rooms.roomid=scheds.roomid 
    WHERE rooms.roomid= 'NB201'

");

$stmt->execute();

if($stmt->rowCount() > 0){
    echo "It exists."; // do NOT INSERT
}

else{
    echo "It does not exist."; // do the INSERT
}

---

You can also try an INNER JOIN by changing

LEFT JOIN scheds

to

INNER JOIN scheds

if you're not getting the results you're looking to get using a LEFT JOIN.