无法将数据插入MySQL数据库

So I'm having kind of a problem here. I'm trying to do something very basic, which is store data via Apache localhost website into a MySQL Database. After I send the data on my website, it's not stored into the database. I will post my code first and then give you some more info:

这里有个问题。我正在尝试做一些非常基本的事情，即通过Apache localhost网站将数据存储到MySQL数据库中。在我将数据发送到我的网站后，它不会被存储到数据库中。我会先发布我的代码，然后给你更多的信息:

Code in header:

在标题的代码:

<?php
$link=mysqli_connect("localhost","root","mypassword","database") or die("Couldn't connect to DB");
?>

Code:

代码:

<?php include ( "./inc/header.inc.php" ); ?>
<?php
$reg = @$_POST['reg'];
// declaring variables to prevent errors
$fn = ""; // First Name
$ln = ""; // Last Name
$un = ""; // Username
$em = ""; // Email
$emc = ""; // Emailcheck
$pswd = ""; // Password
$pswdc = ""; // Passwordcheck
$d = ""; // Signup Date
$u_check = ""; // Check if username exists
// registration form
$fn = strip_tags(@$_POST['fname']);
$ln = strip_tags(@$_POST['lname']);
$un = strip_tags(@$_POST['username']);
$em = strip_tags(@$_POST['email']);
$emc = strip_tags(@$_POST['emailcheck']);
$pswd = strip_tags(@$_POST['password']);
$pswdc = strip_tags(@$_POST['passwordcheck']);
date_default_timezone_set('Europe/Berlin');
$d = date("Y-M-D"); // Year - Month - Day

if ($reg) {
    // Check if emails are equal
    if ($em==$emc) {
        // Check if user already exists
        $u_check = mysqli_query($link, "SELECT username FROM users WHERE username= '$un'");
        // Count the amount of rows where username = $un
        $check = mysqli_num_rows($u_check);
        if ($check == 0) {
            // Check all of the fields have been filled in
            if ($fn&&$ln&&$un&&$em&&$emc&&$pswd&&$pswdc) {
                // Check that passwords match
                if ($pswd==$pswdc) {
                    // Check the max length of username/first name/last name does not exeed 25 characters
                    if (strlen($un)>25||strlen($fn)>25||strlen($ln)>25) {
                        echo "The maximum limit for username/first name/last name is 25 characters!";
                    } else {
                        // Check the max length of password does not exeed 25 characters and is not less than 5 characters
                        if (strlen($pswd)>30||strlen($pswd)<5) {
                            echo "Your password must be between 5 and 30 characters long. So should your ######!";
                        } else {
                            // encrypt password and passwordcheck using md5 before sending to database
                            $pswd = md5($pswd);
                            $pswdc = md5($pswdc);
                            $query = mysqli_query($link, "INSERT INTO users (id, username, first_name, last_name, email, password, sign_up_date, activated) VALUES ('','$un','$fn','$ln','$em','$pswd','$d','0')");
                            die("<h2>Welcome to #######</h2>Login to your account to get ######!");
                        }
                    }
                } else {
                    echo "Your passwords don't match.";
                }
            } else {
                echo "Please fill in all of the fields.";
            }
        } else {
            echo "This Username already exists.";
        }
    } else {
        echo "Your Emails don't match.";
    }
}
?>
        <div id="wrapper2">
            <table>
                <tr>
                    <td width="60%" valign="top">
                        <h2>Join ####### today!</h2>
                    </td>
                    <td width="40%" valign="top">
                        <h2>Sign up below.</h2>
                        <form action="#" method="POST">
                            <input type="text" name="fname" size="25" placeholder="First Name" /><br /><br />
                            <input type="text" name="lname" size="25" placeholder="Last Name" /><br /><br />
                            <input type="text" name="username" size="25" placeholder="User Name" /><br /><br />
                            <input type="text" name="email" size="25" placeholder="E-Mail" /><br /><br />
                            <input type="text" name="emailcheck" size="25" placeholder="E-Mail Check" /><br /><br />
                            <input type="text" name="password" size="25" placeholder="Password" /><br /><br />
                            <input type="text" name="passwordcheck" size="25" placeholder="Password Check" /><br /><br />
                            <input type="submit" name="reg" value="Sign Up!">
                        </form>
                    </td>
                </tr>
            </table>
<?php include ( "./inc/footer.inc.php" ); ?>

So the connection to the database is working. I already checked that. The data is correctly stored in the variables. I manually inserted a 'Test' username via phpMyAdmin in the database and if I put the same username in my website, the code correctly fetches the username from the database and compares it with the input (ergo the result is "Username already exists"). Like I already said, when I fill the form correctly with data, the code terminates but the data is not stored into the database.

因此，与数据库的连接正在工作。我已经检查了。数据被正确地存储在变量中。我在数据库中通过phpMyAdmin手动插入了一个“测试”用户名，如果我在我的网站中输入了相同的用户名，代码就会正确地从数据库中获取用户名，并与输入进行比较(ergo的结果是“用户名已经存在”)。如前所述，当我用数据正确地填充表单时，代码终止，但数据没有存储到数据库中。

Here some version info: Apache/2.4.16 (Unix) PHP/5.5.29

这里有一些版本信息:Apache/2.4.16 (Unix) PHP/5.5.29

Basically used this guide to set up Apache, PHP, MySQL, phpMyAdmin: Guide for Mac OSX

基本上使用这个指南来设置Apache、PHP、MySQL、phpMyAdmin: Mac OSX的指南

I checked for errors via die(mysqli_error($link));, and got this response:

我通过die(mysqli_error($link))检查了错误，并得到了这个响应:

Incorrect integer value: '' for column 'id' at row 1 ->Changed argument '' to NULL.

不正确的整数值:“对于第1行的列'id'， >将参数更改为NULL”。

Second error check gave me this:

第二次错误检查给了我以下信息:

Incorrect date value: '2015-Nov-Wed' for column 'sign_up_date' at row 1

不正确的日期值:第1行“sign_up_date”列“2015- 11 - wed”

The NOW() method did the trick. And thanks for pointing out the date mistake: should be $d = date("Y-m-d");

NOW()方法起了作用。感谢您指出了日期错误:应该是$d = date(“Y-m-d”);

2 个解决方案

#1

Try this:

试试这个:

INSERT INTO users (username, first_name, last_name, email, password, sign_up_date, activated) VALUES ('$un','$fn','$ln','$em','$pswd','$d','0')`

If your id column is auto increment, you're not required to fill this up by yourself.

如果您的id列是自动增量，您不必自己填写。

And for your date, you can check this link for date reference. date structured column requires YY-MM-DD format, which you can get by:

对于您的约会，您可以查看这个链接以获取日期参考。日期结构化列需要YY-MM-DD格式，您可以通过:

$d = date("Y-m-d");

And does users from your system get to upload videos? :P

你的系统用户可以上传视频吗?:P

#2

To start add the following to the top of your php file (right after opening [

要开始向php文件顶部添加以下内容(在打开[

error_reporting(E_ALL); ini_set('display_errors', 'on');

error_reporting(E_ALL);报错(‘display_errors’,‘上’);

This will show you what errors you have on the page.

这将显示页面上的错误。

Then check your query error by using

然后使用以下命令检查查询错误

$query = mysqli_query($link, "INSERT INTO users (id, username, first_name, last_name, email, password, sign_up_date, activated) VALUES ('','$un','$fn','$ln','$em','$pswd','$d','0')") or die(mysqli_error($link);

The second will print the mysql error which you can then use to fix your problem .

第二个将打印mysql错误，然后您可以使用它来修复您的问题。

You can always use the above to find problems, yours is probably the following

你可以使用上面的方法来发现问题，你的方法可能是下面的

In your query your sending an empty value for id.

在查询中，发送id的空值。

Change

改变

"INSERT INTO users (id, username, first_name, last_name, email, password, sign_up_date, activated) VALUES ('','$un','$fn','$ln','$em','$pswd','$d','0')"

into

成

"INSERT INTO users (username, first_name, last_name, email, password, sign_up_date, activated) VALUES ('$un','$fn','$ln','$em','$pswd','$d','0')"

#1