Getting Started with PHP - PHP Learning Notes_PHP Tutorial

Use PHP’s mysql method

PHP has provided MySQL function library from the beginning. Many programs rely on mysql_connect, mysql_query, mysql_fetch_assoc, etc., but the PHP manual recommends:

If the MySQL version you are using is after 4.1.3, it is strongly recommended to use the mysqli extension.

mysqli, or advanced extension for MySQL, has some advantages:

Has object-oriented interface

Prepared statements (prepared statements can effectively prevent SQL-injection attacks and improve performance)

Supports multiple statements and transactions

Also, if you want to support multiple databases then you should consider PDO.

3. Do not filter user input

It should be: Never trust user input. Use back-end PHP to verify and filter each input information. Don't trust Javascript. SQL statements like the following are easily attacked:

$username = $_POST["name"];

$password = $_POST["password"];

$sql = "SELECT userid FROM usertable WHERE username='$username'AND password='$password';"; // run query...

Such code, if the user enters “admin’;”, then it is equivalent to the following:

SELECT userid FROM usertable WHERE username='admin';

In this way, the intruder can log in as admin without entering a password.

4. Do not use UTF-8

Those users in British and American countries rarely consider language issues, which results in many products that cannot be used in other places. There are also some GBK encodings that will cause a lot of trouble.

UTF-8 solves many internationalization problems. Although PHP6 can solve this problem more perfectly, it does not prevent you from setting MySQL's character set to UTF-8.

5. Use PHP where SQL should be used

If you are new to MySQL, sometimes when solving problems you may first consider using a language you are familiar with. This may cause some waste and poor performance. For example: when calculating the average, the native MySQL AVG() method is not used. Instead, PHP is used to loop through all the values ​​and then accumulate them to calculate the average.

Also pay attention to PHP loops in SQL queries. Often it's more efficient to loop through PHP after all results have been obtained.

Generally, when processing large amounts of data, using powerful database methods can improve efficiency.

6. Not optimizing the query

99% of PHP performance problems are caused by the database. A bad SQL statement may make your entire program very slow. MySQL's EXPLAIN statement, Query Profiler, and many other tools can help you find those naughty SELECTs.

7. Using the wrong data type

MySQL provides a series of data types such as numbers, strings, time, etc. If you want to store dates, use the DATE or DATETIME type. Using integers or strings makes things more complicated.

Sometimes you want to use your own data type, for example, using strings to store serialized PHP objects. Adding databases may be easy, but then MySQL becomes unwieldy and may cause problems later.

8. Use *

Don’t use * to return all fields in the table, it will be very slow. You only need to take out the data fields you need. If you need to remove all fields, then maybe your table needs to be changed.

9. Under-indexing or over-indexing

Generally speaking, all fields that appear after WHERE in the SELECT statement should be indexed.

For example, let’s say our users table has a numeric ID (primary key) and email address. After logging in, MySQL should find the corresponding ID via email. Through indexing, MySQL can quickly locate emails through search algorithms. Without an index, MySQL would need to check every record until it is found.

In this case, you may want to add an index to each field, but the consequence of this is that when you update or add, the index will be redone. When the amount of data is large, there will be performance problems. . Therefore, only index the required fields.

10. No backup

It may not happen often, but database corruption, hard disk failure, service shutdown, etc., will cause catastrophic damage to the data. So you must make sure to automatically back up your data or save a copy.

11. In addition: other databases are not considered

MySQL may be the most commonly used database for PHP, but it is not the only choice. PostgreSQL and Firebird are also competitors. They are both open source and not controlled by certain companies. Microsoft provides SQL Server Express, Oracle has 10g Express, and these enterprise-level ones also have free versions. SQLite is also a good choice for some small or embedded applications.