PHP向MySQL添加空行 - PHP adding empty rows to MySQL

我有一个文本区，它将被发送到PHP。

在PHP脚本中，我将用换行符展开textarea

然后，我将创建一个循环来迭代数组以检查值是否已经存在于MySQL中。如果它已经存在，则执行continue操作，否则执行insert操作。

我的代码是这样的:

$route = $_POST['gatewayvalue'];
$account_number = $_POST['account_number'];
$route = strtoupper($route);
$routelist = explode("'n", $route);
$count = count($routelist);
$invalid = 0;
for($i=0;$i<$count;$i++){
if(preg_match("/000F26/i", $routelist[$i])){
    $routeinsert = str_replace("000F26", "", $routelist[$i]);
    $verify = "SELECT route FROM gw_gateway WHERE route='$routeinsert'";
    $result = mysql_query($verify);
    $row = mysql_fetch_array($result);
    $checkroute = $row['route'];
    if($checkroute == $routeinsert){
        $validate = false;
        $invalid++;
        continue;
    }
    else{
        $validate = true;
    }
}
else{
    $routeinsert = $routelist[$i];
    $verify = "SELECT route FROM gw_gateway WHERE route='$routeinsert'";
    $result = mysql_query($verify);
    $row = mysql_fetch_array($result);
    $checkroute = $row['route'];
    if($checkroute == $routeinsert){
        $validate = false;
        $invalid++;
        continue;
    }
    else{
        $validate = true;
    }
}
if($validate == true){
    $updateroutesql = "INSERT INTO gw_gateway SET route='$routeinsert', gw_reseller1='$account_number'";
    $updateroutesqlres=mysql_query($updateroutesql) or die("Can not perform update query !".mysql_error());
}
}

但是，当我在文本区域插入2个类似的值时，例如，030301两次。第一个值将被拒绝，但第二个值将作为空值插入。

My table has 3 fields.

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| route          | varchar(20) | NO   | PRI |         |       |
| account_number | varchar(20) | YES  |     | NULL    |       |
| gw_reseller1   | varchar(45) | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

当我尝试插入两个相似的值

时就会发生这种情况

+----------+----------------+--------------+
| route    | account_number | gw_reseller1 |
+----------+----------------+--------------+
| 030302   | NULL           | NULL         |
| NULL     | 201-000001     |              |
| 030301   | NULL           | 201-000001   |
+----------+----------------+--------------+

首先，尽量缩短代码。你有两次相同的代码部分，这是无用的…然后，净化你的输入。最后，继续PDO或至少mysqli_*的功能，因为mysql_*现在已被弃用。

新代码:

$route = strtoupper($_POST['gatewayvalue']);
$account_number = mysql_real_escape_string($_POST['account_number']);
$routelist = explode("'n", $route);
$count = count($routelist);
$invalid = 0;
for($i=0; $i < $count; $i++) {
    if(preg_match("/000F26/i", $routelist[$i])){
        $routeinsert = str_replace("000F26", "", $routelist[$i]);
    } else {
        $routeinsert = $routelist[$i];
    }
    $verify = mysql_query("SELECT 1 FROM gw_gateway WHERE route='" . mysql_real_escape_string($routeinsert) . "' LIMIT 1");
    if(mysql_num_rows($verify) || !trim($routeinsert)) { // if the route is present in DB mysql_num_rows will return 1 row, or when $routeinsert is an empty string - continue...
        $invalid++;
        continue;
    } else {
        $updateroutesql = "INSERT INTO gw_gateway SET route='" . mysql_real_escape_string($routeinsert) . "', gw_reseller1='$account_number'";
        $updateroutesqlres=mysql_query($updateroutesql) or die("Can not perform update query !".mysql_error());
    }
}

尝试编辑后的代码-应该可以。如果仍然有一些值被插入为空或空，这意味着你在文本区输入了一个空行，你也应该检查路由不是空的，当插入…

试试这个，这是在数据库中插入数据的正确方法:

$updateroutesql = "INSERT INTO gw_gateway (route,gw_reseller1) VALUES('".$routeinsert".','".$account_number."'";

但首先，转义字符串(出于安全考虑)，如下所示:

$routeinsert = mysql_real_escape_string($routeinsert);
$account_number = mysql_real_escape_string($account_number);

由于主键字段，当您第二次插入相同的值时，它已被拒绝，因为它已插入一次。

尝试使用两个不同的值