11 Most common MySQL errors for PHP programmers, mysql_PHP tutorial for php programmers

The 11 most common MySQL errors for PHP programmers are mysql for php programmers. PHP programmers often make 11 MySQL errors. for most web applications, php programmer mysql is a very basic part. If you are using PHP, you are likely to encounter 11 most common MySQL errors by PHP programmers.

For most web applications, databases are a basic part. If you are using PHP, you are likely to be part of the MySQL-LAMP series.

For many new users, using PHP can easily write code with specific functions in just a few hours. However, building a stable and reliable database requires time and related skills. The following lists the 11 most serious MySQL-related errors I have ever made (some are also reflected in the use of other languages/databases ).

1. use MyISAM instead of InnoDB

MySQL has many database engines, but you are most likely to encounter MyISAM and InnoDB.

MySQL uses MyISAM by default. However, in many cases this is a bad choice unless you are creating a very simple or experimental database. Foreign key constraints or transaction processing are very important for data integrity, but none of MyISAM supports these. In addition, when a record is inserted or updated, the entire data table is locked. when the usage increases, the operation efficiency is very poor.

Conclusion: InnoDB is used.

2. use the mysql function of PHP

The MySQL library function (or near as makes no difference) is provided on the date of generation of PHP ). Many applications still use functions such as mysql_connect, mysql_query, and mysql_fetch_assoc, even though the PHP manual says:

If you are using MySQL v4.1.3 or the latest version, we strongly recommend that you use the mysqli extension.

Mysqli (MySQL enhanced version extension) has the following advantages:

Optional object-oriented interfaces

Prepared expression, which can prevent SQL injection attacks and improve performance.

Supports more expressions and transaction processing

In addition, if you want to support multiple database systems, you can also consider PDO.

3. user input not processed

This can also be said as #1: never trust user input. Verify each string with PHP on the server, and do not send it to JavaScript. The simplest SQL injection attack uses the following code:

$ Username = $ _ POST ["name"];

$ Password = $ _ POST ["password"];

$ SQL = "SELECT userid FROM usertable WHERE username = '$ username' AND password =' $ password ';";

// Run query...

Enter "admin";-"in the username field. the corresponding SQL statement is as follows:

SELECT userid FROM usertable WHERE username = 'admin ';

Hackers can log on to the system as admin. they do not need to know the password because the password segment is commented out.

4. UTF-8 not used

In the United States, Britain, and Australia, we seldom consider languages other than English. We are proud to complete our "masterpiece", but we find that they cannot run normally elsewhere.

UTF-8 solves many internationalization problems. Although it was not well supported before PHP v6.0, it does not affect setting the MySQL character set as a UTF-8.

5. PHP is preferred over SQL.

If you are not familiar with MySQL soon, you will prefer to use the language you have mastered to solve the problem, which will lead to writing redundant and inefficient code. For example, if you do not use the MySQL AVG () function, you will first sum the values in the record set and then use the PHP loop to calculate the average value.

In addition, pay attention to SQL queries in PHP loops. Generally, executing a query is more efficient than performing iteration in the result.

Therefore, when analyzing data, please take advantage of the database system, understanding some SQL knowledge will be of great benefit.

6. database query not optimized

99% of PHP performance problems are caused by databases. a poor SQL query can completely paralyze your web applications. MySQL's EXPLAIN statement, Query Profiler, and many other tools will help you find these evil SELECT statements.

7. The data type cannot be correctly used.

MySQL provides data types such as numeric, string, and date. If you want to store a time, use the DATE or DATETIME type. If the INTEGER or STRING type is used at this time, the SQL query will be very complicated, provided that you can use INTEGER or STRING to define that type.

Many people tend to customize some data formats without authorization, such as using string to store serialized PHP Objects. In this way, database management may become simpler, but MySQL may become a bad data storage and may cause faults.

8. use * in query *

Never use * to return data in all columns of a data table. This is laziness: you should extract the data you need. Even if you need all the fields, your data table will inevitably change.

9. no indexes or excessive use of indexes

The general principle is as follows: indexes should be used for fields represented by any where clause in the select statement.

For example, suppose we have a user table, including numeric ID (primary key) and email address. During login, MySQL must find the correct ID based on an email. If an index is used (email here), MySQL can use a faster search algorithm to locate the email, or even implement it in real time. Otherwise, MySQL can only check each record sequentially until the correct email address is found.

Some people will add indexes to each field. Unfortunately, after performing INSERT or UPDATE, these indexes need to be re-generated, which will affect the performance. Therefore, you only need to add an index as needed.

10. forgot to back up

Although rare, the database is still at risk of crash. The hard disk may be damaged, the server may crash, and the web host provider may go bankrupt! The loss of MySQL data will be disastrous, so make sure that you have used automatic backup or copied it in place.

11. Bonus mistake-do not consider using other databases

For PHP developers, MySQL may be the most widely used database system, but it is not the only choice. PostgreSQL and Firebird are the strongest competitors: both are open-source and not acquired by companies. Microsoft provides SQL server Express and Oracle provides 10g Express, both of which are free versions of enterprise-level databases. Sometimes, SQLite is a feasible alternative for a small web application or embedded application.

Php programmers interview questions, how does mysql prevent injection

The most common method is Keyword Filtering. you can use JS client filtering, PHP Server filtering, or PHP sensitive word filtering. MYSQL also has corresponding functions. check the manual.
 

Php programmers interview questions, how does mysql prevent injection

The most common method is Keyword Filtering. you can use JS client filtering, PHP Server filtering, or PHP sensitive word filtering. MYSQL also has corresponding functions. check the manual.
 

Databases are a basic part of most web applications. If you are using PHP, you are likely...