Page 1 of 1

check if data already exist inside mysql database

Posted: Wed Jul 06, 2016 9:31 pm
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);

            }
            ?>

Re: check if data already exist inside mysql database

Posted: Wed Jul 06, 2016 11:02 pm
by requinix

Code: Select all

if ( mysqli_num_rows ( $result ) > 1 )
Think about that.

Re: check if data already exist inside mysql database

Posted: Sun Jul 10, 2016 4:08 pm
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);

            }
            ?>

Re: check if data already exist inside mysql database

Posted: Sun Jul 10, 2016 6:00 pm
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.

Re: check if data already exist inside mysql database

Posted: Sun Jul 10, 2016 7:04 pm
by TopCoder
Good Catch @Christopher

Re: check if data already exist inside mysql database

Posted: Mon Jul 11, 2016 12:34 pm
by Christopher
With enough eyeballs on the code! :drunk: