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>";
    }
}
?>

You can run a SELECT query and then use count() to check if a row exists of the same. If it doesn't, do something; else... do something else. That's one way of doing it. — Funk Forty Niner
– Funk Forty Niner, Commented Apr 5, 2015 at 3:28
Thanks for the reply Fred. However I don't don't have any idea how to do it. Can you show me how? The codes that I'm currently working on are all results of research for the past few days. I just started coding PHP 2 months ago. I'm still trying to learn as I code. — Htennek Nitram Anilro
– Htennek Nitram Anilro, Commented Apr 5, 2015 at 14:25
You're welcome. See this Q&A stackoverflow.com/q/7232704 and stackoverflow.com/q/11180706 being a few results of many when Googling "mysql check if row exists in two tables". — Funk Forty Niner
– Funk Forty Niner, Commented Apr 5, 2015 at 14:58

Htennek Nitram Anilro · Accepted Answer · 2015-04-05 19:08:40Z

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.

edited Apr 5, 2015 at 19:08

Htennek Nitram Anilro

254 bronze badges

answered Apr 5, 2015 at 15:48

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Htennek Nitram Anilro Over a year ago

Ha! It worked! I just made 1 change in the query you made. I changed the id after the "WHERE rooms.id" into "WHERE rooms.roomid". Thank you sir. Now all I have to do is to modify and combine it with the codes that I am working on. And of course I'll try to do some more research about the LEFT and INNER functions so that I understand how it works. One last question tho, I can use variables replacing the NB201 value in the query, right? As I have said, I have an input form that has a post method, so I just have to declare the variable, "$roomid=$_POST['roomid']" and replace the NB201?

Funk Forty Niner Over a year ago

@HtennekNitramAnilro Yes, you can assign it to a variable first then use $roomid in place of it like so WHERE rooms.roomid= '$roomid'. The quotes are required also, since it's a string. Cheers

Funk Forty Niner Over a year ago

@HtennekNitramAnilro You're welcome, was glad to have been of help. Here's a nice guide on JOINS codeproject.com/Articles/33052/…

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related