php - 无法将新用户插入 mysql 数据库

Question

我有一个表单，允许我将用户输入到我的数据库中。但是，每当我单击提交时，我都会收到 query failed 错误消息。以下是我构建的表单:

注册-admin.php

<form id="resgisterform" name="registerform" method="post" action="register-admin-exec.php">
  <table width="300" border="0" align="center" cellpadding="2" cellspacing="0">
    <tr>
      <th>Username </th>
      <td><input name="username" type="text" class="textfield" id="username" /></td>
    </tr>
    <tr>
      <th>First Name </th>
      <td><input name="first_name" type="text" class="textfield" id="first_name" /></td>
    </tr>
    <tr>
      <th>Last Name </th>
      <td><input name="last_name" type="text" class="textfield" id="last_name" /></td>
    </tr>
    <tr>
      <th>Muvdigital Email </th>
      <td><input name="muvdigital_email" type="text" class="textfield" id="muvdigital_email" /></td>
    </tr>
    <tr>
      <th>Personal Email </th>
      <td><input name="personal_email" type="text" class="textfield" id="personal_email" /></td>
    </tr>
    <tr>
      <th>Title </th>
      <td><input name="title" type="text" class="textfield" id="title" /></td>
    </tr>
    <tr>
      <th>Address 1 </th>
      <td><input name="address_1" type="text" class="textfield" id="address_1" /></td>
    </tr>
    <tr>
      <th>Address 2 </th>
      <td><input name="address_2" type="text" class="textfield" id="address_2" /></td>
    </tr>
    <tr>
      <th>City </th>
      <td><input name="city" type="text" class="textfield" id="city" /></td>
    </tr>
    <tr>
      <th>State </th>
      <td><input name="state" type="text" class="textfield" id="state" /></td>
    </tr>
    <tr>
      <th>Zip Code </th>
      <td><input name="zip" type="text" class="textfield" id="zip" /></td>
    </tr>
    <tr>
      <th>Phone </th>
      <td><input name="phone" type="text" class="textfield" id="phone" /></td>
    </tr>
    <tr>
      <th>Password </th>
      <td><input name="password" type="password" class="textfield" id="password" /></td>
    </tr>
    <tr>
      <th>Confirm Password </th>
      <td><input name="cpassword" type="password" class="textfield" id="cpassword" /></td>
    </tr>
    <tr>
      <td>&nbsp;</td>
      <td><input type="submit" name="Submit" value="Register" /></td>
    </tr>
  </table>
</form>

然后将此表单中的值带到下面的 register-admin-exec.php 页面

<?php
    //Start session
    session_start();

    //Include database connection details
    require_once('config.php');

    //Array to store validation errors
    $errmsg_arr = array();

    //Validation error flag
    $errflag = false;



    //Function to sanitize values received from the form. Prevents SQL injection
    function clean($str) {
        $str = @trim($str);
        if(get_magic_quotes_gpc()) {
            $str = stripslashes($str);
        }
        return mysql_real_escape_string($str);
    }

    //define and validate the post values
    /*if (isset ($_POST['admin_id']) && !empty ($_POST['admin_id'])) {
        $admin_id = $_POST['admin_id'];
    } else {
        echo 'Error: admin id not provided!';
    }*/

    if (isset ($_POST['username']) && !empty ($_POST['username'])) {
$username = clean($_POST['username']);
} else {
    echo 'Error: username not provided!';
}

if (isset ($_POST['first_name']) && !empty ($_POST['first_name'])) {
$first_name = clean($_POST['first_name']);
} else {
    echo 'Error: first name not provided!';
}

if (isset ($_POST['last_name']) && !empty ($_POST['last_name'])) {
$last_name = clean($_POST['last_name']);
} else {
    echo 'Error: last name not provided!';
}

if (isset ($_POST['muvdigital_email']) && !empty ($_POST['muvdigital_email'])) {
$muvdigital_email = clean($_POST['muvdigital_email']);
} else {
    echo 'Error: muvdigital email not provided!';
}

if (isset ($_POST['personal_email']) && !empty ($_POST['personal_email'])) {
$personal_email = clean($_POST['personal_email']);
} else {
    echo 'Error: personal email not provided!';
}

if (isset ($_POST['title']) && !empty ($_POST['title'])) {
$title = clean($_POST['title']);
} else {
    echo 'Error: title not provided!';
}

if (isset ($_POST['phone']) && !empty ($_POST['phone'])) {
$phone = clean($_POST['phone']);
} else {
    echo 'Error: phone not provided!';
}

if (isset ($_POST['address_1']) && !empty ($_POST['address_1'])) {
$address_1 = clean($_POST['address_1']);
} else {
    echo 'Error: address 1 not provided!';
}

$address_2 = clean($_POST['address_2']);

if (isset ($_POST['city']) && !empty ($_POST['city'])) {
$city = clean($_POST['city']);
} else {
    echo 'Error: city not provided!';
}

if (isset ($_POST['state']) && !empty ($_POST['state'])) {
$state = clean($_POST['state']);
} else {
    echo 'Error: state not provided!';
}

if (isset ($_POST['zip']) && !empty ($_POST['zip'])) {
$zip = clean($_POST['zip']);
} else {
    echo 'Error: zip not provided!';
}

if (isset ($_POST['password']) && !empty ($_POST['password'])) {
$password = clean($_POST['password']);
} else {
    echo 'Error: password not provided!';
}

if (isset ($_POST['cpassword']) && !empty ($_POST['cpassword'])) {
$cpassword = clean($_POST['cpassword']);
} else {
    echo 'Error: confirm password not provided!';
}



    //encrypt the password
    $salt = sha1($username);
    $password = sha1($salt.$password);


    //Check for duplicate login ID
    if($username != '') {
        $qry = "SELECT * FROM members WHERE username='".$username."'";
        $result = mysql_query($qry);
        if($result) {
            if(mysql_num_rows($result) > 0) {
                $errmsg_arr[] = 'Login ID already in use';
                $errflag = true;
            }
            @mysql_free_result($result);
        }
        else {
            die("Query failed");
        }
    }

    //If there are input validations, redirect back to the registration form
    if($errflag) {
        $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
        session_write_close();
        header("location: register-admin.php");
        exit();
    }

    //Create INSERT query
    $qry = "INSERT INTO admins (
            'username',
            'password',
            'first_name', 
            'last_name', 
            'muvdigital_email', 
            'personal_email', 
            'titles', 
            'phone', 
            'address_1', 
            'address_2',
            'city',
            'state',
            'zip')
    VALUES (
            '$username',
            '$password',
            '$first_name', 
            '$last_name', 
            '$muvdigital_email', 
            '$personal_email', 
            '$title', 
            '$phone', 
            '$address_1', 
            '$address_2',
            '$city',
            '$state',
            '$zip')";
    $result = mysql_query($qry);

    //Check whether the query was successful or not
    if($result) {
        header("location: register-success.php");
        exit();
    }else {
        die("Query failed $qry");
    }
?>

我知道它在我的插入语句中失败了，因为我已经尝试注释掉之前对重复登录 ID 的验证检查，但它仍然失败。我无法弄清楚为什么我的插入语句不起作用。在回显 $qry 之后，我得到了

INSERT INTO admins ( 'username', 'password', 'first_name', 'last_name', 'muvdigital_email', 'personal_email', 'titles', 'phone', 'address_1', 'address_2', 'city', 'state', 'zip') VALUES ( 'johndoe', '7afbb2186cf26d85bdfe948d367fb6baa6739283', 'john', 'doe', 'john.doe@muvdigital.com', 'jdoe@gmail.com', 'intern ', '6024013776', '18b main st', 'apt 12', 'Hooksett', 'NH', '03106')

因此 $_POST 函数正在运行。我尝试在命令行手动输入插入语句，但收到 ERROR 1054 (42S22): Unknown column 'johndoe' in 'field list'。

admin_id 是一个 auto_increment 字段，这就是我将其注释掉的原因(我尝试取消注释并手动创建一个 admin_id，但仍然不起作用)

有人知道为什么会这样吗？

Answer 1

您用单引号引用了所有列名称，这是不正确的。它们应该不带引号，除非您使用了 MySQL reserved keyword。 (你没有)

// Column names are unquoted, but VALUES() should be quoted.
$qry = "INSERT INTO admins (
        username,
        password,
        first_name, 
        last_name, 
        muvdigital_email, 
        personal_email, 
        titles, 
        phone, 
        address_1, 
        address_2,
        city,
        state,
        zip)
VALUES (
        '$username',
        '$password',
        '$first_name', 
        '$last_name', 
        '$muvdigital_email', 
        '$personal_email', 
        '$title', 
        '$phone', 
        '$address_1', 
        '$address_2',
        '$city',
        '$state',
        '$zip')";
$result = mysql_query($qry);