The SQL still inserts the data into the database even if it's wrong

Question

What I want to do is: I am inserting a data into the database using Android. But it has to RETRIEVE/SEARCH for this plate number in the first table BUT before inserting the data into the other table, it should check first if there is this plate number in this table, if true then INSERT the data into the table, if wrong then don't INSERT the data into the table.

The problem that I am having is: The data that I typed in my EditText's will still INSERT into the database/table even though the Plate Number that I typed is wrong.

I think something is wrong with my SQL statement?

My codes in PHP:

<?php
$host='localhost';
$user='root';
$password='';
$db='employee101';

$PLATE_NUM = $_POST["PLATE_NUM"];
$PUV_TYPE = $_POST["PUV_TYPE"];
$content = $_POST["content"];

$sql = "select * from employee_data where PLATE_NUM like '$PLATE_NUM';";//first table where you retrieve the plate number first
$sql_insert = "insert into employee_content (PLATE_NUM, PUV_TYPE, content) values('$PLATE_NUM', '$PUV_TYPE', '$content')";//insert the table

$con = mysqli_connect($host,$user,$password,$db);

$result = mysqli_query($con, $sql);
$result2 = mysqli_query($con, $sql_insert);
$response = array();

if (mysqli_num_rows($result)> 0 && ($result2)=== TRUE ){
        echo"Log-In Success!!!!!!!!!!!!!!!!!!!!!!!!!!!!!";
}else{
    echo "Log-In not success";
}

mysqli_close($con);

?>

My codes in Android Studio:

onButtonClick codes:

public void Button(View view) {

        String puv_plate = report_plate.getText().toString();
        String puv_type = report_type.getText().toString();
        String content = report_content.getText().toString();
        String type="report";
        BackgroundWorker backgroundWorker = new BackgroundWorker(this);
        backgroundWorker.execute(type, puv_plate,puv_type,content);
    }

Background Worker class:

if(type.equals("report")){
                try {
                    String id_ret = params[1];
                    String puv_type = params[2];
                    String report = params[3];

                    URL url = new URL(retrieve_id);
                    HttpURLConnection httpURLConnection = (HttpURLConnection)url.openConnection();
                    httpURLConnection.setRequestMethod("POST");
                    httpURLConnection.setDoOutput(true);
                    httpURLConnection.setDoInput(true);
                    OutputStream outputStream = httpURLConnection.getOutputStream();
                    BufferedWriter bufferedWriter = new BufferedWriter(new OutputStreamWriter(outputStream, "UTF-8"));
                    String post_data = URLEncoder.encode("PLATE_NUM","UTF-8")+"="+URLEncoder.encode(id_ret,"UTF-8") + "&"
                            + URLEncoder.encode("PUV_TYPE", "UTF-8") + "=" + URLEncoder.encode(puv_type, "UTF-8") + "&"
                            + URLEncoder.encode("content", "UTF-8") + "=" + URLEncoder.encode(report, "UTF-8");;
                    bufferedWriter.write(post_data);
                    bufferedWriter.flush();
                    bufferedWriter.close();
                    outputStream.close();
                    InputStream inputStream = httpURLConnection.getInputStream();
                    BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream, "iso-8859-1"));
                    String result = "";
                    String line = "";
                    while((line = bufferedReader.readLine())!= null){
                        result += line;
                    }
                    bufferedReader.close();
                    inputStream.close();
                    httpURLConnection.disconnect();
                    return result;
                } catch (MalformedURLException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }

Answer 1

You can do this two way

1) check manually in the database using some php script. Then you can insert the data. the logic for this is

write onesql query to check the count where the PLATE_NUM='the value'

$query="SELECT COUNT(*) FROm table_name WHERE PLATE_NUM='the value';"

Then get thecount value from the result. If the value is greater than the 0. means the value already exist in the database

if($count>0){
    echo "PLATE_NUM already exist";
    // if you want to update. Here you can update.
}else{
    // perform your actions like insert.
}

2)Make the PLATE_NUM field as unique key in the databadse.. The this will automatically checks for the duplication

Fetching count using mysqli_bind parameter concept

$stmt = $mysqli->prepare($query) or trigger_error($mysqli->error."[$sql]");
$stmt->bind_param('d', $plate_no_value);// here d for number and s for string 
$stmt->bind_result($count);
$stmt->execute();

Answer 2

this statement in your php looks sketchy:

$sql = "select * from employee_data where PLATE_NUM like '$PLATE_NUM';"

I thought the like keyword finds a pattern[1]. Why not do = there? Cause your if statement = true only if both conditions are true, and if a pattern is found in PLATE_NUM column, it will always be true. You want a specific plate number to be the conditional check.

$sql = "select * from employee_data where PLATE_NUM = '$PLATE_NUM';"

[1] https://www.w3schools.com/sql/sql_like.asp

Edit, problem is here, you are always running the result2 query even before the if statement checks. You need to only run result2 when result1 returns success.

change it to

$result = mysqli_query($con, $sql);

// comment this out $result2 = mysqli_query($con, $sql_insert); $response = array();

if (mysqli_num_rows($result)> 0 ){
        $result2 = mysqli_query($con, $sql_insert);

Answer 3

Actually, You wrote the query but you didn't check that the first query whether it's available or not without checking you inserted the data,

Instead, you can simply use one UPDATE query to achieve this,

$sql_insert = "UPDATE employee_content SET PLATE_NUM = '$PLATE_NUM', PUV_TYPE = '$PUV_TYPE', content = '$content' WHERE PLATE_NUM='$PLATE_NUM' )";