How to escape mysql blank and duplicate data using php

Question

I am using this code to send data to mysql database and its functioning well though users are sending blank data and duplicating too.

<?php 
  require "conn.php";
  $lostidno = $_POST ["lostidno"];
  $phonenol = $_POST ["phonenol"];

  $mysql_qry = "INSERT INTO lostdb.lost (lostidno, phonenol) VALUES ('$lostidno', '$phonenol')";  

  if ($conn->query($mysql_qry) === TRUE) 
  {
    echo "Information Recieved!";
  }
  else
    echo "Sorry! An Error Occured:" . $mysql_qry . "br" . $conn->error;

  $conn->close();

?>

How do I prevent this?

By checking the condition. If condition gives true, then don't insert the record. — Virb, Sep 07 '16 at 12:15

score 0 · Answer 1 · answered Sep 07 '16 at 12:16

1. To avoid blank values

Check the values of variables before executing insert query on database using empty() PHP function.

2. Avoid duplicate values

You can do it in two ways, Either specify UNIQUE constriant in database TABLE or run SELECT query before inserting data into database.

score 0 · Accepted Answer · edited May 23 '17 at 12:16

You can use trim function to remove spaces at the beginning and the ending of a string. After that you are able to check if the two parameters aren't empty:

<?php 
require "conn.php";
$lostidno = trim($_POST["lostidno"]);
$phonenol = trim($_POST["phonenol"]);

if(!empty($lostidno) && !empty($phonenol))
{
    $mysql_qry = "INSERT INTO lostdb.lost (lostidno, phonenol) VALUES ('$lostidno', '$phonenol')";  

    if ($conn->query($mysql_qry) === TRUE) {
        echo "Information Recieved!";
    }
    else {
        echo "Sorry! An Error Occured:" . $mysql_qry . "br" . $conn->error;
    }

    $conn->close();
}
?>

You should also have a look at this to prevent SQL injections.

this has prevented blank data but duplicates are still coming in. — Henry Gathigira, Sep 07 '16 at 12:42

score 0 · Answer 3 · answered Sep 07 '16 at 12:17

//this to check the duplicate records
Fire the select query : select * from lostdb.lost where col1= $lostidno and col2 = $phonenol;

//this is to check the blank condition
if($lostidno !== "" && $phonenol != "")
{
  $mysql_qry = "INSERT INTO lostdb.lost (lostidno, phonenol) VALUES ('$lostidno', '$phonenol')"; 
}

Rahul Patel · Answer 4 · 2016-09-07T12:25:18.510

Use trim function to remove blank spaces from string.

<?php 
require "conn.php";
$lostidno = trim($_POST ["lostidno"]);
$phonenol = trim($_POST ["phonenol"]);

//Check record exist or not.
$unique_check = "select count(*) as cnt from lostdb.lost where lostidno='".$lostidno."' and phonenol='".$phonenol."'";
//Execute above query and check record already exist or not. If record not exist then only allow to insert it.

$result_unique_check = $conn->query($mysql_qry)->one();
$record_exist = $result_unique_check['cnt'];

if($record_exist > 0){
    echo "Record alreay exist.";    
}else{
    //Insert new record
    $mysql_qry = "INSERT INTO lostdb.lost (lostidno, phonenol) VALUES ('$lostidno', '$phonenol')";  

    if ($conn->query($mysql_qry) === TRUE) 
    {
    echo "Information Recieved!";
    }
    else

    echo "Sorry! An Error Occured:" . $mysql_qry . "br" . $conn->error;

    $conn->close();   
}

?>

score 0 · Answer 5 · answered Sep 07 '16 at 12:20

The best way to prevent duplicates is to mark the unique database columns as UNIQUE

ALTER TABLE <table_name> ADD UNIQUE(<column_name>);

To prevent blank data from ever getting into the database you need to check them with a few modifications in the code you posted:

if( ($lostidnol != "") && ($phonenol != "") )
{
    if ($conn->query($mysql_qry) === TRUE) 
    {
        echo "Information Recieved!";
    }
    else
    {
        //error
    }
}
else
{
    //Notify the user that they're trying to insert blanks
}

If you want to provide proper user experience you should also provide some client side validation using JavaScript or jQuery

How to escape mysql blank and duplicate data using php

5 Answers5

1. To avoid blank values

2. Avoid duplicate values