_php tutorial on the issues needing attention in PHP to operate MySQL

1. Each line of command is separated by a semicolon (;) As the end

For MySQL, the first thing you have to keep in mind is that each line of the command is separated by a semicolon (;) As the end, but ... There is no absolutely absolute thing, also here, when a line of MySQL is inserted in 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 makes PHP parser can not understand, so it is good to omit. In this case, although the semicolon is omitted, PHP will automatically help you when executing the MySQL command.

There is also a case where no semicolon is added. You can use G to end a row of SQL statements when you want to display the vertical of the field, rather than the usual arrangement, and then use a semicolon, for example:

SELECT * from penpals WHERE user_id = 1G

2. TEXT, DATE, and SET data types

A field in a MySQL data table must have a data type defined. There are about 25 options, most of which are straightforward and not much of a waste of breath. But there are a few that need to be mentioned.

TEXT is not a data type, although it may be said in some books. It should actually be "LONG VARCHAR" or "Mediumtext".

The format of the DATE data type is YYYY-MM-DD, for example: 1999-12-08. 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 data types to get the difference in number of days:

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

Set set is a useful data type that is somewhat similar to enum enum, except that set can hold multiple values and ENUM can hold only one value. Moreover, the SET type can only have up to 64 predetermined values, while the ENUM type can handle up to 65,535 predefined values. What if you need a collection of more than 64 values? At this point, you need to define multiple collections to solve this problem together.

3. Wildcard characters

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

SELECT * from dbname WHERE user_id like '% ';

Here, two wildcard characters are used. They mean the same thing?? are used to match any string, but they are used in a different context. "*" is used to match the field name, while "%" is used to match the field value. Another area that is not easily noticed is that the "%" wildcard needs to be used with the LIKE keyword.

There is also a wildcard, that is, the underscore "_", which represents the meaning and above, is used to match any single character.

4. Not null and empty records

What happens if the user presses the submit button without filling in anything? If you do need a value, you can use a client script or a server-side script for data validation, as mentioned earlier. In the database, however, some fields are allowed to be empty and nothing is filled out. For this type of record, MySQL will do something for it: Insert the value NULL, which is the default operation.
If you declare not NULL for it in the field definition (when you create or modify the field), MySQL will empty the field and leave nothing to fill. 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 value of null and a blank record are some differences. The% wildcard can match an empty record, but it cannot match a null record. At some point, this distinction can have some unintended consequences. As far as my experience is concerned, any field should be declared as not NULL. This allows the following SELECT query statement to function:

if (! $CITY) {$CITY = "%";}

$selectresult = mysql_query ("SELECT * FROM dbname
WHERE first_name = ' Willow '
and last_name = ' like wind '
Like ' $CITY '
");

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

However, if there are some records, the value of its 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 = ' Willow '
and last_name = ' like wind '
and (city like ' $CITY ' OR city is NULL)
");

Note that when you search for NULL, you must use the IS keyword, and like does not work correctly.

The last point to mention is that if you have some records in the database before adding or modifying a new field, the value of the newly added field in the original record may be null or empty. This is a MySQL Bug, so in this case, use SELECT query to be particularly careful.

http://www.bkjia.com/PHPjc/446868.html www.bkjia.com true http://www.bkjia.com/PHPjc/446868.html techarticle 1. Each line of command is separated by a semicolon (;) As an end to MySQL, the first thing you have to keep in mind is that each line of the command is separated by a semicolon (;) As the end, but ... Not completely absolute ...

