Top 20 Mysql_Connect Trong Php Mới Nhất 2022

     
Want khổng lồ improve this post? Provide detailed answers lớn this question, including citations & an explanation of why your answer is correct. Answers without enough detail may be edited or deleted.
What are the technical reasons for why one shouldn"t use mysql_* functions? (e.g. Mysql_query(), mysql_connect() or mysql_real_escape_string())?

Why should I use something else even if they work on my site?

If they don"t work on my site, why vì I get errors lượt thích

Warning: mysql_connect(): No such tệp tin or directory


*

*

The MySQL extension:

Is not under active developmentHas been removed entirely as of PHP 7.0 (released December 2015)Lacks an OO interfaceDoesn"t support:Non-blocking, asynchronous queriesStored proceduresMultiple StatementsTransactionsThe "new" password authentication method (on by default in MySQL 5.6; required in 5.7)Any of the new functionality in MySQL 5.1 or later

Since it is deprecated, using it makes your code less future proof.

Lack of support for prepared statements is particularly important as they provide a clearer, less error-prone method of escaping & quoting external data than manually escaping it with a separate function call.

Bạn đang xem: Top 20 mysql_connect trong php mới nhất 2022

See the comparison of SQL extensions.


giới thiệu
Improve this answer
Follow
edited Jul 21, 2019 at 10:15
community wiki
21 revs, 13 users 52%Quentin
5
showroom a bình luận |
1342
+100
PHP offers three different APIs lớn connect lớn MySQL. These are the mysql(removed as of PHP 7), mysqli, & PDO extensions.

The mysql_* functions used to be very popular, but their use is not encouraged anymore. The documentation team is discussing the database security situation, & educating users to move away from the commonly used ext/mysql extension is part of this (check php.internals: deprecating ext/mysql).

And the later PHP developer team has taken the decision lớn generate E_DEPRECATED errors when users connect lớn MySQL, whether through mysql_connect(), mysql_pconnect() or the implicit connection functionality built into ext/mysql.

ext/mysql was officially deprecated as of PHP 5.5 and has been removed as of PHP 7.

See the Red Box?

When you go on any mysql_* function manual page, you see a red box, explaining it should not be used anymore.

Why

Moving away from ext/mysql is not only about security, but also about having access to lớn all the features of the MySQL database.

ext/mysql was built for MySQL 3.23 và only got very few additions since then while mostly keeping compatibility with this old version which makes the code a bit harder to lớn maintain. Missing features that is not supported by ext/mysql include: (from PHP manual).

Encryption (SSL)CompressionFull Charset support

Reason khổng lồ not use mysql_* function:

Not under active developmentRemoved as of PHP 7Lacks an OO interfaceDoesn"t tư vấn non-blocking, asynchronous queriesDoesn"t tư vấn stored proceduresDoesn"t tư vấn multiple statementsDoesn"t support all of the functionality in MySQL 5.1

Above point quoted from Quentin"s answer

Lack of support for prepared statements is particularly important as they provide a clearer, less error prone method of escaping và quoting external data than manually escaping it with a separate function call.

See the comparison of SQL extensions.

Suppressing deprecation warnings

While code is being converted khổng lồ MySQLi/PDO, E_DEPRECATED errors can be suppressed by setting error_reporting in php.ini khổng lồ exclude E_DEPRECATED:

error_reporting = E_ALL ^ E_DEPRECATEDNote that this will also hide other deprecation warnings, which, however, may be for things other than MySQL. (from PHP manual)

The article PDO vs. MySQLi: Which Should You Use? by Dejan Marjanovic will help you to lớn choose.

And a better way is PDO, & I am now writing a simple PDO tutorial.

A simple & short PDO tutorial

Q. First question in my mind was: what is `PDO`?

A. “PDO – PHP Data Objects – is a database access layer providing a uniform method of access lớn multiple databases.”

*

Connecting to MySQL

With mysql_* function or we can say it the old way (deprecated in PHP 5.5 và above)

$link = mysql_connect("localhost", "user", "pass");mysql_select_db("testdb", $link);mysql_set_charset("UTF-8", $link);With PDO: All you need to bởi is create a new PDO object. The constructor accepts parameters for specifying the database source PDO"s constructor mostly takes four parameters which are DSN (data source name) and optionally username, password.

Here I think you are familiar with all except DSN; this is new in PDO. A DSN is basically a string of options that tell PDO which driver khổng lồ use, & connection details. For further reference, check PDO MySQL DSN.

$db = new PDO("mysql:host=localhost;dbname=testdb;charset=utf8", "username", "password");Note: you can also use charset=UTF-8, but sometimes it causes an error, so it"s better to lớn use utf8.

If there is any connection error, it will throw a PDOException object that can be caught to lớn handle Exception further.

Good read: Connections và Connection management ¶

You can also pass in several driver options as an array to the fourth parameter. I recommend passing the parameter which puts PDO into exception mode. Because some PDO drivers don"t tư vấn native prepared statements, so PDO performs emulation of the prepare. It also lets you manually enable this emulation. To use the native server-side prepared statements, you should explicitly set it false.

The other is lớn turn off prepare emulation which is enabled in the MySQL driver by default, but prepare emulation should be turned off to lớn use PDO safely.

I will later explain why prepare emulation should be turned off. To find reason please kiểm tra this post.

It is only usable if you are using an old version of MySQL which I vì chưng not recommended.

Below is an example of how you can vì it:

$db = new PDO("mysql:host=localhost;dbname=testdb;charset=UTF-8", "username", "password", array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));Can we mix attributes after PDO construction?

Yes, we can also phối some attributes after PDO construction with the setAttribute method:

$db = new PDO("mysql:host=localhost;dbname=testdb;charset=UTF-8", "username", "password");$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Error Handling

Error handling is much easier in PDO than mysql_*.

A common practice when using mysql_* is:

//Connected to MySQL$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));OR die() is not a good way to handle the error since we can not handle the thing in die. It will just kết thúc the script abruptly & then echo the error lớn the screen which you usually vày NOT want khổng lồ show lớn your kết thúc users, & let bloody hackers discover your schema. Alternately, the return values of mysql_* functions can often be used in conjunction with mysql_error() to lớn handle errors.

Xem thêm: Please Wait - Soạn Bài Thuyết Minh Về Một Danh Lam Thắng Cảnh

PDO offers a better solution: exceptions. Anything we bởi with PDO should be wrapped in a try-catch block. We can force PDO into one of three error modes by setting the error mode attribute. Three error handling modes are below.

PDO::ERRMODE_SILENT. It"s just setting error codes & acts pretty much the same as mysql_* where you must check each result and then look at $db->errorInfo(); to get the error details.PDO::ERRMODE_WARNING Raise E_WARNING. (Run-time warnings (non-fatal errors). Execution of the script is not halted.)PDO::ERRMODE_EXCEPTION: Throw exceptions. It represents an error raised by PDO. You should not throw a PDOException from your own code. See Exceptions for more information about exceptions in PHP. It acts very much lượt thích or die(mysql_error());, when it isn"t caught. But unlike or die(), the PDOException can be caught and handled gracefully if you choose to bởi so.

Good read:

Like:

$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );And you can wrap it in try-catch, lượt thích below:

try //Connect as appropriate as above $db->query("hi"); //Invalid query! catch (PDOException $ex) echo "An Error occured!"; //User friendly message/message you want khổng lồ show khổng lồ user some_logging_function($ex->getMessage());You bởi not have khổng lồ handle with try-catch right now. You can catch it at any time appropriate, but I strongly recommend you khổng lồ use try-catch. Also it may make more sense to catch it at outside the function that calls the PDO stuff:

function data_fun($db) $stmt = $db->query("SELECT * FROM table"); return $stmt->fetchAll(PDO::FETCH_ASSOC);//Then latertry data_fun($db);catch(PDOException $ex) //Here you can handle error and show message/perform action you want.Also, you can handle by or die() or we can say lượt thích mysql_*, but it will be really varied. You can hide the dangerous error messages in production by turning display_errors off và just reading your error log.

Now, after reading all the things above, you are probably thinking: what the heck is that when I just want to start leaning simple SELECT, INSERT, UPDATE, or DELETE statements? Don"t worry, here we go:

Selecting Data

*

So what you are doing in mysql_* is:

Now in PDO, you can bởi vì this like:

query("SELECT * FROM table");while($row = $stmt->fetch(PDO::FETCH_ASSOC)) echo $row<"field1">;Or

query("SELECT * FROM table");$results = $stmt->fetchAll(PDO::FETCH_ASSOC);//Use $resultsNote: If you are using the method like below (query()), this method returns a PDOStatement object. So if you want to lớn fetch the result, use it lượt thích above.

query("SELECT * FROM table") as $row) echo $row<"field1">;In PDO Data, it is obtained via the ->fetch(), a method of your statement handle. Before calling fetch, the best approach would be telling PDO how you’d lượt thích the data lớn be fetched. In the below section I am explaining this.

Fetch Modes

Note the use of PDO::FETCH_ASSOC in the fetch() và fetchAll() code above. This tells PDO to return the rows as an associative array with the field names as keys. There are many other fetch modes too which I will explain one by one.

First of all, I explain how to lớn select fetch mode:

$stmt->fetch(PDO::FETCH_ASSOC)In the above, I have been using fetch(). You can also use:

Now I come khổng lồ fetch mode:

PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result setPDO::FETCH_BOTH (default): returns an array indexed by both column name và 0-indexed column number as returned in your result set

There are even more choices! Read about them all in PDOStatement Fetch documentation..

Getting the row count:

Instead of using mysql_num_rows to lớn get the number of returned rows, you can get a PDOStatement và do rowCount(), like:

query("SELECT * FROM table");$row_count = $stmt->rowCount();echo $row_count." rows selected";Getting the Last Inserted ID

exec("INSERT INTO table(firstname, lastname) VAULES("John", "Doe")");$insertId = $db->lastInsertId();

Insert and Update or Delete statements

*

What we are doing in mysql_* function is:

And in pdo, this same thing can be done by:

exec("UPDATE table phối field="value"");echo $affected_rows;In the above query PDO::exec execute an SQL statement và returns the number of affected rows.

Insert and delete will be covered later.

The above method is only useful when you are not using variable in query. But when you need to use a variable in a query, bởi vì not ever ever try lượt thích the above and there for prepared statement or parameterized statement is.

Prepared Statements

Q. What is a prepared statement and why bởi I need them?A. A prepared statement is a pre-compiled SQL statement that can be executed multiple times by sending only the data to lớn the server.

The typical workflow of using a prepared statement is as follows (quoted from Wikipedia three 3 point):

Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled ? below):

INSERT INTO product (name, price) VALUES (?, ?)

The DBMS parses, compiles, & performs query optimization on the statement template, và stores the result without executing it.

Execute: At a later time, the application supplies (or binds) values for the parameters, & the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply "Bread" for the first parameter and 1.00 for the second parameter.

You can use a prepared statement by including placeholders in your SQL. There are basically three ones without placeholders (don"t try this with variable its above one), one with unnamed placeholders, và one with named placeholders.

Q. So now, what are named placeholders & how vì I use them?A. Named placeholders. Use descriptive names preceded by a colon, instead of question marks. We don"t care about position/order of value in name place holder:

$stmt->bindParam(":bla", $bla);bindParam(parameter,variable,data_type,length,driver_options)

You can also bind using an execute array as well:

prepare("SELECT * FROM table WHERE id=:id & name=:name");$stmt->execute(array(":name" => $name, ":id" => $id));$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);Another nice feature for OOP friends is that named placeholders have the ability to lớn insert objects directly into your database, assuming the properties match the named fields. For example:

class person public $name; public $add; function __construct($a,$b) $this->name = $a; $this->add = $b; $demo = new person("john","29 bla district");$stmt = $db->prepare("INSERT INTO table (name, add) value (:name, :add)");$stmt->execute((array)$demo);Q. So now, what are unnamed placeholders & how vì chưng I use them?A. Let"s have an example:

prepare("INSERT INTO folks (name, add) values (?, ?)");$stmt->bindValue(1, $name, PDO::PARAM_STR);$stmt->bindValue(2, $add, PDO::PARAM_STR);$stmt->execute();and

$stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");$stmt->execute(array("john", "29 bla district"));In the above, you can see those ? instead of a name like in a name place holder. Now in the first example, we assign variables lớn the various placeholders ($stmt->bindValue(1, $name, PDO::PARAM_STR);). Then, we assign values to those placeholders và execute the statement. In the second example, the first array element goes to the first ? và the second lớn the second ?.

Xem thêm: Học Phí Trường Nguyễn Tất Thành Mới Nhất Năm 2021, Học Phí Trường Đại Học Nguyễn Tất Thành 2021

NOTE: In unnamed placeholders we must take care of the proper order of the elements in the array that we are passing khổng lồ the PDOStatement::execute() method.

SELECT, INSERT, UPDATE, DELETE prepared queries

SELECT:

$stmt = $db->prepare("SELECT * FROM table WHERE id=:id và name=:name");$stmt->execute(array(":name" => $name, ":id" => $id));$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);INSERT:

$stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");$stmt->execute(array(":field1" => $field1, ":field2" => $field2));$affected_rows = $stmt->rowCount();DELETE:

$stmt = $db->prepare("DELETE FROM table WHERE id=:id");$stmt->bindValue(":id", $id, PDO::PARAM_STR);$stmt->execute();$affected_rows = $stmt->rowCount();UPDATE:

$stmt = $db->prepare("UPDATE table mix name=? WHERE id=?");$stmt->execute(array($name, $id));$affected_rows = $stmt->rowCount();

NOTE:

However PDO and/or MySQLi are not completely safe. Check the answer Are PDO prepared statements sufficient lớn prevent SQL injection? by ircmaxell. Also, I am quoting some part from his answer:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);$pdo->query("SET NAMES GBK");$stmt = $pdo->prepare("SELECT * FROM kiểm tra WHERE name = ? LIMIT 1");$stmt->execute(array(chr(0xbf) . Chr(0x27) . " OR 1=1 /*"));