Check value before insert and if already exists don't insert it on mysql using php

Question

I have code check ips addresses if duplicated will redirected to another link but the code is running good when insert value in (ip,user_agent,time) in table (click) first time but when i try to check if running with some value it written again on database I need any value (ip,user_agent,time) write just one time after written to database not written the some value (ip,user_agent,time)

<?php
    $servername = "localhost";
    $username = "root";
    $password = "rootroot";
    $dbname = "data_clicks";
    //Create connection to database using mysqli
    $conn = new mysqli($servername, $username , $password, $dbname);



    //Set variables according to user input on previous form
    if(isset($_SERVER['REMOTE_ADDR']) && isset($_SERVER['HTTP_USER_AGENT'])){
        $ip = $_SERVER['REMOTE_ADDR'];
        $user = $_SERVER['HTTP_USER_AGENT'];
        $datetime = date_create()->format('d-m-Y ');
    }


    //Define the INSERT statement with IGNORE keyword
        $sql = "INSERT IGNORE INTO clicks (ip, user_agent, time )
        VALUES ('.$ip.', '.$user.', CURRENT_TIMESTAMP)";
        if ($conn->query($sql) === false) {
            die("Database error:".$conn->error);
        }
    // link offer
    $file = "md5.txt";
    $f = fopen($file, 'r');
    $line = fgets($f);
    fclose($f);
    $link="https://facebook.com/?".$line;
    //do smth

    $contents = file($file, FILE_IGNORE_NEW_LINES);
    $first_line = array_shift($contents);
    file_put_contents($file, implode("\r\n", $contents));

    // Check for success
        if ($conn->affected_rows == 0) {
            print "<br>Error! <p></p>";
            echo "" . $ip .''.$user. " already exists in the DCR";
            header("Location: http://www.google.com/");
        } else {

    //Success and return new id
            echo "<br><p></p>Record Added!<p></p>";
            header("Location: $link");
        }
    ?>

Does this answer your question? [How to 'insert if not exists' in MySQL?](https://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — Sherif, Jan 19 '20 at 06:16
the code insert information if not exist but i need to check before insert info and if ip and user agent already exists don't inserted and show message — DialyVi, Jan 19 '20 at 06:21
I think maybe the table in your host and local are different. — TsaiKoga, Jan 19 '20 at 06:26
Can't you just do a SELECT query to probe for existing entries? Or is there some more concrete difficulty? — mario, Jan 19 '20 at 06:26
@mario I can't check if value already exists and after check if already exist on my database don't insert again — DialyVi, Jan 19 '20 at 06:34
Don't do this. Or, if you must, do it as part of a transaction. Instead, just execute the INSERT and, if it fails, then you know data is duplicated. Alternatively, you can build the logic into the INSERT itself. — Strawberry, Jan 19 '20 at 07:28

score 0 · Answer 1 · answered Jan 20 '20 at 23:14

I resolve th problem and now script is good working...

<?php
$servername = "localhost";
$username = "root";
$password = "rootroot";
$dbname = "data_clicks";
//Create connection to database using mysqli
$conn = new mysqli($servername, $username , $password, $dbname);
//Set variables according to user input on previous form
if(isset($_SERVER['REMOTE_ADDR']) && isset($_SERVER['HTTP_USER_AGENT'])){
    $ip = $_SERVER['REMOTE_ADDR'];
    $user = $_SERVER['HTTP_USER_AGENT'];
    $datetime = date_create()->format('d-m-Y ');

}
//Open file md5
$file = "emails/md5.txt";
$f = fopen($file, 'r');
$line = fgets($f);
fclose($f);
$link="https://www.facebook.com/?".$line;
//do smth

$contents = file($file, FILE_IGNORE_NEW_LINES);
$first_line = array_shift($contents);
file_put_contents($file, implode("\r\n", $contents));

//End file md5
// connect mysql


$result = mysqli_query($conn,"SELECT ip FROM clicks WHERE ip='".$ip."'");
$count=mysqli_num_rows($result);

if($count==0)  {
$sql="INSERT INTO clicks (ip, user_agent, time )
VALUES('".$ip."','".$user."', CURRENT_TIMESTAMP)";
if (!mysqli_query($conn,$sql))
{

        }
echo "<br><p></p>Record Added!<p></p>";
        header("Location: $link");
}
else
{ 
    print "<br>Error! <p></p>";
    echo "" . $ip .''.$user. " already exists in the DCR";
    header("Location: http://www.google.com/");
}

//end test
//Define the INSERT statement with IGNORE keyword
    $sql = "INSERT IF NOT EXISTS INTO clicks (ip, user_agent, time )
    VALUES ('$ip', '$user', CURRENT_TIMESTAMP)

)";

// link offer

// Check for success

?>

Check value before insert and if already exists don't insert it on mysql using php

1 Answers1