PHP中的MySQL错误代码0 ?无法使用PHP将数据插入数据库

So trying to insert some data from a PHP page into my SQL database. This page is ONLY accessible via myself so I'm not worried about it being accessed or SQL injectable etc. My issue is no matter what code I use it doesn't go into the database. I've tried coding it myself, using template codes, taking from php.net etc nothing has worked!

尝试从PHP页面插入一些数据到SQL数据库中。这个页面只能通过我自己访问，所以我不担心它被访问或SQL注入等。我的问题是不管我使用什么代码，它都不会进入数据库。我尝试过自己编写它，使用模板代码，从php。net等等什么都没用!

It now redirects me with the success message but still nothing in the database.

它现在用成功消息重定向我，但在数据库中仍然没有。

Code will be put below and I'll edit some of my details for privacy reasons.

代码将放在下面，我将编辑我的一些细节，以保护隐私。

<?php
require connect.php

   // If the values are posted, insert them into the database.
    if (isset($_POST['username']) && isset($_POST['password'])){
        $username = $_POST['username'];
        $isadminB = $_POST['isadmin'];
        $password = $_POST['password'];

        $query = "INSERT INTO `users` (user_name, password, isadmin) VALUES ('$username', '$password', '$isadminB')";
        $result = mysql_query($query);
        if($result){
            $msg = "User Created Successfully.";
        }
    }
    $link = mysql_connect("localhost", "root", "password");
echo mysql_errno($link) . ": " . mysql_error($link). "\n";

The echo mysql_errno($link) . ": " . mysql_error($link). "\n"; was the code that gave me error code 0?

回声mysql_errno(链接)。“:”。mysql_error(链接)。“\ n”;给我错误码的代码是0吗?

As requested the code for the form from my previous page.

按照之前页面中表单的要求。

<form action="account_create_submit.php" method="post">
Username: <input type="text" name="username" id="username"> <br /><br />
Password: <input type="password" name="password" id="password"> <br /><br />
<span id="isadmin">Is Admin: Yes<input type="radio" name="isadmin" id="1" value="1"> | No<input type="radio" name="isadmin" id="0" value="0"><br /></span>
<span id="submit"><input type="submit" value="Create Account"></span>
</form>

Ok so changed the form code so method is now POST. Great! All data is being read correctly although that wasn't my issue as even typing in hard data for the code to submit wasn't working at least its a future issue resolved already. The new error code is no longer 0 but rather the following:

改变了表单代码，方法现在发布了。太棒了!所有的数据都被正确地读取了，尽管这不是我的问题，因为即使输入硬数据来提交代码也不能正常工作，至少这是未来要解决的问题。新的错误代码不再是0，而是如下:

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''user_name', 'password', 'isadmin') VALUES ('testZ', 'lol', '1')' at line 1

您的SQL语法有错误;检查与MySQL服务器版本对应的手册，以便正确使用“user_name”、“password”、“isadmin”)值(“testZ”、“lol”、“1”)。

Connect.php

Connect.php

    <?php
$connection = mysql_connect('localhost', 'root', 'password');
if (!$connection){
    die("Database Connection Failed" . mysql_error());
}
$select_db = mysql_select_db('Default_DB');
if (!$select_db){
    die("Database Selection Failed" . mysql_error());
}

4 个解决方案

#1

Firstly, for those of you getting the misconception about password for a column name:

首先，对于那些误解了一个列名密码的人:

Sure, it's MySQL "keyword", but not a "reserved" word; more specifically, it is a function (see ref). Notice there is no (R) next to the "function (keyword) name": https://dev.mysql.com/doc/refman/5.5/en/keywords.html therefore it's perfectly valid as a column name.

当然，它是MySQL“关键字”，但不是“保留”字;更具体地说，它是一个函数(参见ref)。注意，在“函数(关键字)名称”旁边没有(R): https://dev.mysql.com/doc/refman/5.5/en/keywords.html，因此它作为列名是完全有效的。

Ref: https://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html#function_password

裁判:https://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html # function_password

Ticks are only required if it is used in order to prevent it from being recognized as a "function", which it clearly is not in the OP's case. So, get your information and facts straight.

只有当它被用于防止它被识别为“函数”时，才需要滴答声，这在OP中显然不是这样的。所以，把你的信息和事实弄清楚。

More specifically, if a table named as PASSWORD and without spaces between the table name and the column declaration:

更具体地说，如果表名命名为密码，表名和列声明之间没有空格，则:

I.e.: INSERT INTO PASSWORD(col_a, col_b, col_c) VALUES ('var_a', 'var_b', 'var_c')

即。:插入密码(col_a, col_b, col_c)值('var_a'， 'var_b'， 'var_c')

which would throw a syntax error, since the table name is considered as being a function.

这将抛出语法错误，因为表名被认为是一个函数。

Therefore, the proper syntax would need to read as

因此，正确的语法需要阅读。

INSERT INTO `PASSWORD` (col_a, col_b, col_c) VALUES ('var_a', 'var_b', 'var_c')

(Edit:) To answer the present question; you're using $connection in your connection, but querying with $link along with the missing db variables passed to your query and the quotes/semi-colon I've already outlined here.

(编辑:)回答目前的问题;您正在您的连接中使用$connection，但是使用$link和传递给查询的缺失的db变量以及我在这里已经列出的引号/分号进行查询。

That's if you want to get that code of yours going, but I highly discourage it. You're using a deprecated MySQL library and MD5 as you stated. All old technology that is no longer safe to be used, nor will it be supported in future PHP releases.

如果你想让你的代码运行起来，我强烈反对。正如您所说，您正在使用一个废弃的MySQL库和MD5。所有旧技术都不再安全，在未来的PHP版本中也不会支持。

You're missing a semi-colon here require connect.php and quotes.

这里缺少一个分号，需要连接。php和报价。

That should read as require "connect.php";

应该按照要求读取“connector .php”;

You should also remove this:

您还应该删除以下内容:

$link = mysql_connect("localhost", "root", "password");
echo mysql_errno($link) . ": " . mysql_error($link). "\n";

you're already trying to include a connection file.

您已经在尝试包含一个连接文件。

Use this in your connection file: (modified, using connection variable connection parameter)

在您的连接文件中使用:(使用连接变量连接参数修改)

$connection = mysql_connect('localhost', 'root', 'password');
if (!$connection){
    die("Database Connection Failed" . mysql_error());
}
$select_db = mysql_select_db('Default_DB', $connection);
if (!$select_db){
    die("Database Selection Failed" . mysql_error());
}

and pass the $connection to your query as the 2nd parameter.

并将$连接作为第二个参数传递给查询。

$result = mysql_query($query, $connection);

Add error reporting to the top of your file(s) right after your opening PHP tag for example <?php error_reporting(E_ALL); ini_set('display_errors', 1); then the rest of your code, to see if it yields anything.

在打开PHP标记(例如

Also add or die(mysql_error()) to mysql_query().

还可以向mysql_query()添加或die(mysql_error())。

If that still gives you a hard time, you will need to escape your data.

如果这仍然给您带来困难，您将需要转义您的数据。

I.e.:

例如:

$username = mysql_real_escape_string($_POST['username'], $connection);

and do the same for the others.

对其他人也一样。

Use a safer method: (originally posted answer)

May as well just do a total rewrite and using mysqli_ with prepared statements.

也可以对已准备好的语句进行全面重写和使用sqmyli_。

Fill in the credentials for your own.

自己填写凭证。

Sidenote: You may have to replace the last s for an i for the $isadminB that's IF that column is an int.

Sidenote:您可能要将最后一个s替换为$isadminB的i，即如果该列是int型。

$link = new mysqli('localhost', 'root', 'password', 'demo');
if ($link->connect_errno) {
    throw new Exception($link->connect_error, $link->connect_errno);
}

if (!empty($_POST['username']) && !empty($_POST['password'])){
    $username = $_POST['username'];
    $isadminB = $_POST['isadmin'];
    $password = $_POST['password'];

// now prepare an INSERT statement
    if (!$stmt = $link->prepare('INSERT INTO `users` 
          (`user_name`, `password`, `isadmin`) 
           VALUES (?, ?, ?)')) {
        throw new Exception($link->error, $link->errno);
    }

    // bind parameters
    $stmt->bind_param('sss', $username, $password, $isadminB);

        if (!$stmt->execute()) {
            throw new Exception($stmt->error, $stmt->errno);
        }

    }

    else{
        echo "Nothing is set, or something is empty.";
    }

---

I noticed you may be storing passwords in plain text. If this is the case, it is highly discouraged.

我注意到你可能是用纯文本存储密码。如果是这样的话，这是非常不鼓励的。

I recommend you use CRYPT_BLOWFISH or PHP 5.5's password_hash() function. For PHP < 5.5 use the password_hash() compatibility pack.

我建议您使用CRYPT_BLOWFISH或PHP 5.5的password_hash()函数。对于PHP < 5.5，使用password_hash()兼容包。

You can also use this PDO example pulled from one of ircmaxell's answers:

你也可以使用这个来自ircmaxell的回答的PDO例子:

Just use a library. Seriously. They exist for a reason.

只使用一个图书馆。认真对待。它们的存在是有原因的。

• PHP 5.5+: use password_hash()
• PHP 5.5 +:使用password_hash()
• PHP 5.3.7+: use password-compat (a compatibility pack for above)
• PHP 5.3.7+:使用密码-compat(上面的兼容包)
• All others: use phpass
• 其他:phpass使用

Don't do it yourself. If you're creating your own salt, YOU'RE DOING IT WRONG. You should be using a library that handles that for you.

不要做自己。如果你在创造你自己的盐，你做错了。您应该使用一个为您处理这个问题的库。

$dbh = new PDO(...);

$username = $_POST["username"];
$email = $_POST["email"];
$password = $_POST["password"];
$hash = password_hash($password, PASSWORD_DEFAULT);

$stmt = $dbh->prepare("insert into users set username=?, email=?, password=?");
$stmt->execute([$username, $email, $hash]);

And on login:

和登录:

$sql = "SELECT * FROM users WHERE username = ?";
$stmt = $dbh->prepare($sql);
$result = $stmt->execute([$_POST['username']]);
$users = $result->fetchAll();
if (isset($users[0]) {
    if (password_verify($_POST['password'], $users[0]->password) {
        // valid login
    } else {
        // invalid password
    }
} else {
    // invalid username
}

#2

You are using "get" as your form submission method. "post" variables won't be recognized.

您正在使用“get”作为表单提交方法。“post”变量不会被识别。

Also...

也……

It looks like you're missing the second parameter of your mysql_query() function which is your link identifier to the MySQL connection. I'm assuming you've created the connection in connection.php.

看起来您丢失了mysql_query()函数的第二个参数，它是到MySQL连接的链接标识符。我假设您已经在connector .php中创建了连接。

Typically, the mysql_query() function would be

通常，mysql_query()函数是

$result = mysql_query($query, $conn);

with $conn having been pre-defined in your connection.php file.

在您的连接中预先定义了$conn。php文件。

#3

password is a special word in MySQL, and it might be necessary to put the word in quotes like `password`.

在MySQL中，password是一个特殊的单词，可能需要将该单词放在“password”这样的引号中。

#4

Why are you putting all the information from the form in the link on submit? ex: account_create_submit.php?username=myusername&password=mypassword&isadmin=0

你为什么要把表格中的所有信息都放在提交的链接里?例:account_create_submit.php ?用户名= myusername&password = mypassword&isadmin = 0

---

I can see that $username = $_POST['username']; doesn't match the username in your query string.

我可以看到$username = $_POST['username'];与查询字符串中的用户名不匹配。

$query = "INSERT INTOusers(user_name, password, isadmin) VALUES ('$username', '$password', '$isadminB')";

$query =“插入INTOusers(user_name、password、isadmin)值('$username'、'$password'、'$isadminB')”;

While your fixing that why don't you just make $isadminB and $_POST['isadmin'] the same. Use 'isadminB' in both places.

当你修复它的时候，为什么你不让$isadminB和$_POST['isadmin']相同。在两个地方都使用“isadminB”。

Check that out and see what happens!

看看会发生什么!