CODEWITHSUNDEEP
phpmysqlsql
Matt Kent
Matt Kent

Reputation: 1185

How can I only insert a record if it does not exists?

I am currently making a simple IP banning script and I am at the stage of inserting the IP address into the database. The HTML form I created for this consists of two fields; the IP address and a reason:

<td width="70%"><h4>Ban New IP:</h4>(Enter IP to be banned)</td>
    <td width="30%">
    <form method="post" action="">
     IP Address: <input type="text" id="ip" name="ip" autocomplete="off" maxlength="15" /><br /><br />
     Enter Reason: <input type="text" id="reason" name="reason" autocomplete="off" maxlength="255" /><br /><br />
     <input type="submit" id="submit" name="submit" value="Ban IP" /><br /><br />

My database contains one table titled banned_ips and three columns id, ip and reason. The insert statement I have created works perfectly however I want to check to see if the IP already exists. At the moment it will just insert the IP even if there is one matching it. This is my current query:

<?php

if ($_POST) {
    if ($_POST['ip'] != '') {
        if (strlen($_POST['ip']) <= 15) {
            $ip = $_POST['ip'];

            $ip_clean = htmlentities($ip);

            if (isset($ip_clean)) {
                // SQL insert
                $insert_ip = $db->prepare("INSERT INTO `banned_ips` (ip) VALUES (?)");
                $insert_ip->bind_param('s', $ip_clean);

                if ($insert_ip->execute()) {
                    // Successful insert
                    $_SESSION['successful_insert'] = 'Success';
                }
            }
        }
    }
}

 ?>

What I would like to do is check to see if the IP address already exists when they go to insert a new IP. I have looked at other questions asked here on stackoverflow and a lot of the time I saw WHERE NOT EXISTS however none that I found showed any examples with prepared statements.

Upvotes: 3

Views: 142

Answers (3)

Marco Chirico
Marco Chirico

Reputation: 65

You have 2 way:

1) make ip a unique key, than when you Will try to write the same ip, statement, return you an error.

2) make a previously query, to check if the ip is alredy stored in your table.

Anyway the first solution is the best!

Bye

Upvotes: 0

roun512
roun512

Reputation: 149

You can make the ip column UNIQUE and by doing that if the same IP exists it won't insert it or by doing query like this :

$check_ip = $db->prepare("SELECT `ip` FROM `banned_ips` WHERE `ip` = ?");
$check_ip->bind_param('s', $ip_clean);
$res = $check_ip->execute();
if($res) {
    echo "IP Already exists";
} else {
    //insert it to db
}

If you put the code then first it will check if the IP exists, if it exists it will not insert else if it doesn't exist it will insert it to the db.

Upvotes: 2

meda
meda

Reputation: 45490

Just make ip field a unique field.

mysql will then prevent duplication

Upvotes: 5

Related Questions