check if data already exist inside mysql database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
grngmdn
Forum Newbie
Posts: 1
Joined: Wed Jul 06, 2016 8:49 pm

check if data already exist inside mysql database

Post by grngmdn »

i am new in PHP so i am struggling to solve this issue.

here is my code which stores the variable values in specific table rows inside the table "booked".

i want to make sure mysql rejects the insertion of the value inside the database table "booked" if all the variable values already exist inside the database.

note: all the values have to be identical to be rejected. for example: if all the values are identical except for one value, the system should still accept the insertion.

the code i wrote below first checks if the data is already present inside the database. if it is present it echos "data already exists", if not then it inserts the new value inside the database.

however this code doesn't seem to work, so i was wondering if you guys could check my code to see what i am doing wrong?


Code: Select all

<?php

            $servername = "localhost";
            $name = "root";
            $password = "root";
            $dbname = "my computer";

            // Create connection
            $conn = mysqli_connect($servername, $name, $password, $dbname);
            // Check connection
            if (!$conn) {
               die("Connection failed: " . mysqli_connect_error());
            }



            $query = "SELECT * FROM `booked` WHERE 

            `name` = '{$username}'
            `date` = '{$date}'
            `computer_id` = '{$select3}'
            `start_time` = '{$select1}'
            `end_time` = '{$select2}'
            `room` = '{$room}'

            ";

            $result = mysqli_query($conn, $query);



            if ( mysqli_num_rows ( $result ) > 1 )
            {

            echo "data already exists";

            }




            else
            {
            $sql = "INSERT INTO booked (date, computer_id, name, start_time, end_time, room)
                VALUES ('$date', '$select3', '$username', '$select1', '$select2', '$room')";



            if (mysqli_query($conn, $sql)) {
               echo "New record created successfully";
                } else {
               echo "Error: " . $sql . "<br>" . mysqli_error($conn);
                }

            mysqli_close($conn);

            }
            ?>
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: check if data already exist inside mysql database

Post by requinix »

Code: Select all

if ( mysqli_num_rows ( $result ) > 1 )
Think about that.
TopCoder
Forum Newbie
Posts: 8
Joined: Sun Jul 10, 2016 3:38 pm

Re: check if data already exist inside mysql database

Post by TopCoder »

@requinix Lol.

What he's saying is you should change:

Code: Select all

 if ( mysqli_num_rows ( $result ) > 1 )
to

Code: Select all

 if ( mysqli_num_rows ( $result ) >= 1 )
But, there is actually a better way. Look into the INSERT IGNORE statement from MySQL - If you put an UNIQUE Index on 6 Columns in MYSQL - Then you can accomplish it all in 1 query and you won't have do 2 and all the additional error checking.

Code: Select all

     $sql = "INSERT INTO booked (date, computer_id, name, start_time, end_time, room)
                VALUES ('$date', '$select3', '$username', '$select1', '$select2', '$room')";
Becomes..

Code: Select all

    $sql = "INSERT IGNORE INTO booked (date, computer_id, name, start_time, end_time, room)
                VALUES ('$date', '$select3', '$username', '$select1', '$select2', '$room')";
Take a look.. See how much you can simply the code? But make sure you have the unique index in place first.

Code: Select all

<?php

            $servername = "localhost";
            $name = "root";
            $password = "root";
            $dbname = "my computer";

            // Create connection
            $conn = mysqli_connect($servername, $name, $password, $dbname);
            // Check connection
            if (!$conn) {
               die("Connection failed: " . mysqli_connect_error());
            }

            $result = mysqli_query($conn, $query);

            $sql = "INSERT IGNORE INTO booked (date, computer_id, name, start_time, end_time, room)
                VALUES ('$date', '$select3', '$username', '$select1', '$select2', '$room')";

            if ( mysqli_num_rows ( $result ) >= 1 )
            {
               echo "New record created successfully";
             } else {
               echo "data already exists";
            }

             mysqli_close($conn);

            }
            ?>
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: check if data already exist inside mysql database

Post by Christopher »

grngmdn wrote:

Code: Select all

<?php
            $query = "SELECT * FROM `booked` WHERE 

            `name` = '{$username}'
            `date` = '{$date}'
            `computer_id` = '{$select3}'
            `start_time` = '{$select1}'
            `end_time` = '{$select2}'
            `room` = '{$room}'

            ";
Also, your WHERE clause does not have any logical operators. There should be AND or OR operators to between the conditions.
(#10850)
TopCoder
Forum Newbie
Posts: 8
Joined: Sun Jul 10, 2016 3:38 pm

Re: check if data already exist inside mysql database

Post by TopCoder »

Good Catch @Christopher
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: check if data already exist inside mysql database

Post by Christopher »

With enough eyeballs on the code! :drunk:
(#10850)
Post Reply