What are some of the things you should be aware of when operating MySQL in PHP?

1, the exception of the semicolon

In MySQL, each line of command is terminated with a semicolon (;), but when a row of MySQL commands is inserted into the PHP code, it is best to omit the following semicolon, for example:

mysql_query ("INSERT into TableName (first_name, last_name) VALUES (' $first _name ', ' $last _name ')");

This is because PHP is also a semicolon as the end of a line, the extra semicolon sometimes causes the PHP parser to make the wrong analysis, so it is omitted good. In this case, although the semicolon is omitted, PHP automatically adds a semicolon when executing the MySQL command.

There is also a case without a semicolon. When you want to display the vertical arrangement of the fields instead of being lined up as usual, you can use \g to end a line of SQL statements, with no semicolons, for example:

SELECT * from penpals WHERE user_id = 1\g

2, TEXT, DATE, and SET data types

TEXT is not a data type, it should be "LONG VARCHAR" or "Mediumtext".

The format of the date data type is YYYY-MM-DD, for example: 2001-10-01. You can easily use the date function to get the current system time in this format:

Date ("y-m-d")

Also, you can subtract between the data types to get the difference in the number of days:

$age = ($current _date-$birthdate);

Set set is a useful data type and is somewhat similar to enum enum, except that a set can hold multiple values and an enum can only hold one value. Also, a SET type can have up to 64 predetermined values, whereas an ENUM type can handle up to 65,535 predefined values. And what if you need a collection of more than 64 values? Then you need to define multiple sets to solve the problem together.

3. Wildcard characters

There are two kinds of wildcard characters for SQL: "*" and "%". Used in different situations respectively. For example: If you want to see all of the contents of a database, you can query like this:

SELECT * from dbname WHERE user_id like '% ';

Here, two wildcard characters have been used. Do they mean the same thing? are used to match any of the strings, but they are used in different contexts. "*" is used to match field names, and "%" is used to match field values. Another area that is not easily noticed is that the% wildcard character needs to be used in conjunction with the LIKE keyword.

There is also a wildcard, which is the underscore "_", which means different from the above, is used to match any single character.

4, not null and empty records

What happens if a user presses the submit button without filling in anything? If you really need a value, then you can use client script or server-side script for data validation, as already mentioned earlier. However, in the database, some fields are allowed to be empty and nothing to fill. For this type of record, MySQL will take the following steps:

Inserts a value of NULL, which is the default action.

If you declare not NULL for it in the field definition (when you create or modify this field), MySQL will empty the field and fill out nothing.

For a field of an enum enum type, if you declare not NULL for it, MySQL inserts the first value of the enumeration set into the field. In other words, MySQL takes the first value of the enumeration set as the default value for this enumeration type.

A record with a null value is somewhat different from an empty record. % wildcard characters can match empty records, but they cannot match null records. At some point, this difference can cause some unintended consequences. As far as experience is concerned, any field should be declared to not NULL. This allows the following SELECT query statement to function correctly:

if (! $CITY) {$CITY = "%";}
$selectresult = mysql_query ("SELECT * from dbname")
WHERE first_name = ' Bill '
and last_name = ' Gates '
And city like ' $CITY '
");

In the first row, if the user does not specify a city value, then the wildcard% will be used to substitute the city variable, so that the search will take any city values into account, even if the City field is empty records.

But if there are some records, the value of the City field is NULL, then the problem arises. The above query is not able to find these fields. One solution to the problem could be this:

if (! $CITY) {$CITY = "%";}
$selectresult = mysql_query ("SELECT * from dbname")
WHERE first_name = ' Bill '
and last_name = ' Gates '
and (city like ' $CITY ' OR city is NULL)
");

Note that when searching for NULL, you must use the "is" keyword, while like does not work correctly.

The last thing to mention is that if you have some records in the database before you add or modify a new field, the value of the newly added field in the original record may or may not be null, and in this case, use SELECT queries with special care.