这是编写用于插入数据的PHP代码的更好方法 [英] Which is better way for write PHP code for insert data

问题描述

我有些困惑，因为有些人像这样编写PHP代码来上传数据. 但是我的讲师总是使用$query和$link从SQL上传和检索数据.

I am some confused because some people write PHP code like this for upload data. But my instructor used $query and $link always to upload and retrieve data from SQL.

<?php
include 'connection.php';
function reg_in() {
    if (isset($_POST['submit'])) {
        $name = $_POST['name'];
        $email = $_POST['email'];
        $mob = $_POST['mobile'];
        $check_in = $_POST['in'];
        $check_out = $_POST['out'];
        $rm = $_POST['room'];
        $room_type = $_POST['type'];
        // Problem start from here
        if (mysql_query("INSERT INTO `book` (`name`,`email`,`mobile_no`,`check_in`,`check_out`,`room`,`room_type`) VALUES ('$name','$email','$mob','$check_in','$check_out','$rm','$room_type')")) {
            header('Location: C:/wamp/www/project/payment.html');
        } else {
            echo mysql_error();
        }
    }
}
if (isset($_POST['submit'])) {
    reg_in();
//echo ' succesfully inserted';
} else {
    echo 'Not book';
}

MySQL(由我的讲师指导):-

<?php

$link = mysqli_connect("myserver.com", "test", "sunil7117", "test");
if (mysqli_connect_error()) {
    die("please give correct permision");
}
//Is both are same!
//$query="INSERT INTO user(email,password) VALUES ('[e-mail]','sunil7117')";
$query = "UPDATE user SET email='[e-mail]' WHERE email='[e-mail]' LIMIT 1";
echo mysqli_query($link, $query);
echo "<br>";

$query = "SELECT * FROM user";
if ($result = mysqli_query($link, $query)) {
    echo "welcome to database<br>";

    $row = mysqli_fetch_array($result);
    echo "my E-mail id is <strong> ".$row[1]. "</strong> and passoword is <strong>".$row[2]."</strong>";
}

推荐答案

都不是！

您的第一个示例使用了几年前已从PHP中删除的函数. mysql_query()不存在，不应再使用.之所以删除它，是因为您应该使用准备好的语句.它们由mysqli或PDO扩展提供.

Your first example uses function which has been removed from PHP years ago. mysql_query() does not exist and should not be used anymore. The reason why it was removed is that you should use prepared statements instead. They are provided by either mysqli or PDO extensions.

您的第二个示例更好，但是太混乱了.

Your second example is better, but it is way too messy.

• 您不应该echo mysqli_query.此功能没有任何有用的输出.
• 养成始终使用准备好的语句的习惯，并对可变数据使用占位符.到目前为止，您的查询是不变的，但是如果以后需要添加参数，则使用预准备语句仍然是一种好习惯.
• 避免使用诸如mysqli_fetch_array()之类的功能.一个接一个地迭代结果选项很麻烦，而且很少有用.
• 从不检查mysqli调用的返回值.没有用.改为启用错误报告.请参阅如何在MySQLi中获取错误消息?
• 始终设置正确的字符集. 99％的时间应该是utf8mb4.
• SQL查询可以保存在单独的变量中，但是有什么意义呢?您仅要将其作为参数传递给query函数.不需要使用中间变量.
• 不要使用mysqli.您应该改为使用PDO.如果必须使用mysqli，则可以为此目的创建一个简单的包装函数或类，并执行通用函数，而不必弄乱mysqli函数.

• You should not echo mysqli_query. There's nothing useful to be printed out from this function.
• Get into a habit of using prepared statements all the time and use placeholders for variable data. As of now your queries are constant, but using prepared statements is still a good practice in case you need to add a parameter later on.
• Avoid using functions like mysqli_fetch_array(). Iterating the result option one by one is messy and rarely useful.
• Never check the return value of mysqli calls. It's pointless. Enable error reporting instead. See How to get the error message in MySQLi?
• Always set the correct charset. It should be utf8mb4 99% of the time.
• The SQL query can be saved in a separate variable, but what's the point? You are only going to pass it as an argument to the query function. There's no need to use an intermediate variable.
• Don't use mysqli. You should use PDO instead. If you have to use mysqli, then create a simple wrapper function or class for this purpose and execute your generic function instead of messing around with mysqli functions.

这是我将如何做的一个例子.首先，我为mysqli启用错误报告，我打开连接并设置字符集.然后，我声明一个接受3个参数并返回数组的函数.第一个参数是您刚刚打开的数据库连接.其次是带有占位符的SQL查询(如果有).第三个是可选的，它是将值绑定到占位符作为参数的值的数组.此函数适用于所有类型的SQL查询.其余代码变得非常简单.

Here is an example of how I would do it. First I enable error reporting for mysqli, I open the connection and set the charset. Then I declare a function which takes 3 parameters and returns an array. First parameter is your database connection you have just opened. Second is your SQL query with placeholders if there are any. Third is optional and it is an array of values to be bound to the placeholders as parameters. This function works for all kind of SQL queries. The rest of the code becomes really simple.

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = new mysqli("myserver.com", "test", "sunil7117", "test");
$link->set_charset('utf8mb4'); // always set the charset

/**
 * Executes an SQL query on the database.
 *
 * @param \mysqli $mysqli
 * @param string $query e.g. SELECT * FROM users WHERE username=?
 * @param array $params An array of parameters to be bound in the given order to the placeholders in SQL
 * @return array
 */
function prepared_query(\mysqli $mysqli, string $query, array $params = []): array {
    $stmt = $mysqli->prepare($query);
    if ($params) {
        $stmt->bind_param(str_repeat("s", count($params)), ...$params);
    }
    $stmt->execute();
    if ($result = $stmt->get_result()) {
        return $result->fetch_all(MYSQLI_BOTH);
    }
    return null;
}

prepared_query($link, "UPDATE user SET email='[e-mail]' WHERE email='[e-mail]' LIMIT 1");
echo "<br>";

$result = prepared_query($link, "SELECT * FROM user");
echo "welcome to database<br>";

if ($result) {
    $row = $result[0];
    echo "my E-mail id is <strong> ".$row[1]. "</strong> and passoword is <strong>".$row[2]."</strong>";
}

这篇关于这是编写用于插入数据的PHP代码的更好方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！