php - SQL即使出错，仍然将数据插入数据库

Question

我想做的是:我正在使用 Android 将数据插入数据库。但是它必须在第一个表中RETRIEVE/SEARCH这个车牌号，但是在将数据插入另一个表之前，它应该首先检查这个表中是否有这个车牌号，如果为真则插入将数据插入表中，如果错误则不要将数据插入表中。

我遇到的问题是:即使我输入的车牌号错误，我在 EditText 中输入的数据仍会INSERT 到数据库/表中。

我认为我的 SQL 语句有问题？

我的 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);

?>

我在 Android Studio 中的代码:

onButtonClick 代码:

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);
    }

后台 worker 类:

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

你的 php 中的这条语句看起来很粗略:

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

我认为 like 关键字找到了一个模式 [1]。为什么不做=那里？仅当两个条件都为真时，才会导致您的 if 语句 = true，并且如果在 PLATE_NUM 列中找到一个模式，它将始终为真。您希望特定的车牌号作为条件检查。

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

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

编辑，问题在这里，你总是在 if 语句检查之前运行 result2 查询。您只需要在 result1 返回成功时运行 result2。

改成

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

//注释掉 $result2 = mysqli_query($con, $sql_insert); $response = array();

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