douqi1625
2014-05-05 17:45
浏览 62
已采纳

如果记录不存在,我怎么才能插入记录?

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.

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dqd22496 2014-05-05 17:49
    已采纳

    Just make ip field a unique field.

    mysql will then prevent duplication

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • duancaoqin6683 2014-05-05 17:51

    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.

    评论
    解决 无用
    打赏 举报
  • douqi0090 2014-05-05 17:56

    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

    评论
    解决 无用
    打赏 举报