php - 如何检查数据是否已经存在于数据库中？

Question

如何检查数据($chassis，$pin)是否已存在于数据库中，如果存在，我希望它返回一个错误信息“User exists”。我正在使用 json 来显示错误，现在使用下面的代码插入数据，即使底盘和引脚已经存在于数据库中。

<?php
session_start();

require("config/config.php");
date_default_timezone_set('Asia/Calcutta');
if (isset($_POST['user_submit']))
    $chassis = mysql_real_escape_string($_POST['chassis']);
$pin           = mysql_real_escape_string($_POST['pin']);
$title         = mysql_real_escape_string($_POST['title']);
$fname         = mysql_real_escape_string($_POST['fname']);
$lname         = mysql_real_escape_string($_POST['lname']);
$email         = mysql_real_escape_string($_POST['email']);
$mobile        = mysql_real_escape_string($_POST['mobile']);
$dob           = mysql_real_escape_string($_POST['dob']);
$anniversary   = mysql_real_escape_string($_POST['anniversary']);
$company       = mysql_real_escape_string($_POST['company']);
$designation   = mysql_real_escape_string($_POST['designation']);
$home_business = mysql_real_escape_string($_POST['home_business']);
$add1          = mysql_real_escape_string($_POST['add1']);
$add2          = mysql_real_escape_string($_POST['add2']);
$city          = mysql_real_escape_string($_POST['city']);
$state         = mysql_real_escape_string($_POST['state']);
$pincode       = mysql_real_escape_string($_POST['pincode']);
$date          = date('y.m.d h:i:s A');

$hostname = '';
$database = '';
$username = '';
$password = '';

$conn = mysql_connect($hostname, $username, $password);
if (!$conn) {
    $json['error'] = "Unable to Connect server!" . mysql_error();
}
if (empty($json)) {
    mysql_select_db($database) or die("Unable to select database!" . mysql_error());
    $sql = mysql_query('SELECT chassis,pin FROM checking_chassis WHERE chassis="' . $chassis . '" && pin="' . $pin . '" ');

    if (mysql_num_rows($sql) == 1) {

        $sql1 = mysql_query('SELECT chassis,pin FROM taj_contact_info WHERE chassis="' . $chassis . '" && pin="' . $pin . '" ');
        if (mysql_num_rows($sql1) == 1) {
            $json['error'] = "User already exists";
            exit();
        } else {
            $query = 'INSERT INTO taj_contact_info (chassis,pin,title,fname,lname,email,mobile,dob,anniversary,company,designation,home_business,add1,add2,city,state,pincode,date_added) VALUES("' . $chassis . '","' . $pin . '","' . $title . '","' . $fname . '","' . $lname . '","' . $email . '","' . $mobile . '","' . $dob . '","' . $anniversary . '","' . $company . '","' . $designation . '","' . $home_business . '","' . $add1 . '","' . $add2 . '","' . $city . '","' . $state . '","' . $pincode . '","' . $date . '")';

            $sql1 = mysql_query($query);

            $message = "Thank you for registering. You can indulge in the fine dining experience with the Taj gift certificate, which will reach you within 30 days. Team Purple Club";

            $sms = "http://bulkpush.mytoday.com/BulkSms/SingleMsgApi?feedid=340479&UserName=&password=&sendername=PURPLE&To=" . $mobile . "&message=" . urlencode($message) . "";

            $work = file_get_contents($sms);

            $json['success'] = "Successfully inserted";
        }
    } else {
        $json['error1'] = "Please enter valid chassis number and pin";

    }

}

echo json_encode($json);
?>

Javascript

<script type="text/javascript">
    $(document).ready(function (){

        $("#user_submit_form").submit(function(){
            var user_data = $("#user_submit_form").serialize();

                                var mobile = new Array();                                                            
                                                mobile = $('#mobile').val().split("");

                                                var pincode = new Array();                                                          
                                                pincode = $('#pincode').val().split("");

                                                if($('#chassis').val() =='')
                                                {                                              
                                                                                alert('Please enter chassis');
                                                }              
                                                else if ($('#pin').val() =='')
                                                {                                              
                                                                                alert('Please enter pin');
                                                }


                                                else        {

                                                                                $.ajax({
                                                                                type                                                            : "post",
                                                                                url                                                             : "validate_user.php",
                                                                                data                                                            : user_data,
                                                                                dataType                                                        : "json",
                                                                                success: function(json){

                                                                                                if(json.error)
                                                                                                {
                                                                                                                alert(json.error)
                                                                                                }
                                                                                                else if(json.error1)

                                                                                                {
                                                                                                                alert(json.error1)

                                                                                                }
                                                                                                else
                                                                                                {
                                                                                                                alert(json.success)
                                                                                                                /*location.reload();

                                                                                                                window.location="http://clps.cequitysolutions.com/taj/thankyou.php";*/

                                                                                                }
                                                                                }


                                                                                });


                                                                                }
        });                   

                });
      function validateEmail(email) 
                {
                                var re = /^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\".+\"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;                                                                                            
                                return re.test(email);
                }
</script>

Answer 1

我会说您应该为 chassis 和 pin 创建唯一索引。避免重复正是唯一索引存在的原因:

alter table taj_contact_info add unique index(chassis, pin)

现在您可以安全地运行插入查询了:

$query = 'INSERT INTO taj_contact_info (chassis,pin,title,fname,lname,email,mobile,dob,anniversary,compa ...

然后检查是否已插入记录:

if (mysql_affected_rows()>0) {
  //success
} else {
   $json['error'] = "User already exists"
}

如果 mysql_affected_rows 返回 -1，则插入脚本失败。如果(我猜)底盘无效，就像空字符串一样，就会发生这种情况。