1. Exceptions of the semicolon
For MySQL, the first one you have to keep in mind is that its commands are ended with a semicolon (;), but ... not completely absolute things, the same thing here, when a line MySQL is inserted in PHP code In time, it is best to omit the rear semicolon, for example:
MySQL_Query ("INSERT INTO TABLENAME (First_name, Last_name)
VALUES ('$ first_name', '$ last_name')
");
This is because PHP is also the end of a line as a line, and the extra semicolon sometimes makes PHP's grammar analyzer does not understand, so it is omitted. In this case, although the semicolon is omitted, PHP will automatically help you when the mysql command is executed.
There is also a case where don't add points. When you want to display the vertical arrangement of the field, instead of being arranged in the usual, you can use / g to end a row of SQL statements, then you can use the semicolil, for example:
SELECT * from penpals where user_id = 1 / g
2. Text, Date, and SET Data Types
The field of the mysql data sheet must have a defined data type. There are about 25 kinds of options, most of them are directly clear, and there are not many expenses. But there are a few need to mention it.
Text is not a data type, although there may be some books. It should actually be "long varchar" or "mediatext".
The format of the DATE data type is YYYY-MM-DD, such as: 1999-12-08. You can easily use the Date function to get the current system time of this format:
Date ("Y-M-D")
Also, subtraction can be subtracted between the DATA data type to obtain a difference of time days:
$ AGE = ($ CURRENT_DATE - $ BIRTHDATE);
Collection set is a useful data type, which is a bit similar to enumeration Enum, but the set can save multiple values and Enum can only save a value. Moreover, the SET type can only have 64 predetermined values, while the ENUM type can handle up to 65,535 predefined values. And if you need a collection of more than 64 values, what should I do? At this time, you need to define multiple collections to solve this problem together.
3. Wildcard
There are two types of SQL's wildcards: "*" and "%". It is used in different situations, respectively. For example: If you want to see all the contents of the database, you can query like this:
Select * from dbname where user_id like '%';
Here, both wildcards were used. They expressed the same meaning? They are all used to match any strings, but they are used in different contexts. "*" Is used to match the field name, and "%" is used to match the field value. Another place that is not easy to pay attention to is "%" wildcard needs to be used with the LIKE keyword.
There is also a wildcard, which is the underscore "_", which represents the meaning and above, is used to match any single character.
4. Not null and empty record
What if the user presses the subs button without filling anything. If you really need a value, you can use the client script or the server-side script to verify that this is already said in the previous. However, in the database, some fields are allowed to be empty. For such a record, MySQL will perform some things for this: insert value null, this is the default operation.
If you declare Not Null in the field definition (when establishing or modifying this field), MySQL will empty this field is not filled.
For a field of an enum enumeration type, if you declare Not null, MySQL will insert the first value of the enumeration into the field. That is, MySQL uses the first value of the enumeration as the default value of this enumeration type.
A record that a value null and a null record are different. % Wildcards can match air records, but they cannot match the null record. At some point, this difference will cause some unexpected consequences. For my experience, any field should be declared as NOT NULL. The following SELECT query statement can be running normally:
IF (! $ city) {$ city = "%";
$ SELECTRESULT = MySQL_Query ("SELECT * from DBNAME
Where first_name = 'Liu'
And last_name = 'like wind'
And City Like '$ City'
");
In the first line, if the user does not specify a CITY value, it will be resemble the CITY variable with wildcard%, so that any city value will be taken into account, and even the record of the city field is empty.
But if there are some records, its city field value is NULL, and the problem will appear. The above query is not able to find these fields. One solution to the problem can be like this:
IF (! $ city) {$ city = "%";
$ SELECTRESULT = MySQL_Query ("SELECT * from DBNAME
Where first_name = 'Liu'
And last_name = 'like wind'
AND (City Like '$ City' or City is Null)
");
Note When searching NULL, you must use the "IS" keyword, while Like will not work properly.
In the end, if you have some records in the database before joining or modify a new field, the value of the newly added field in the original record may be NULL, or may air. This is also a bug of MySQL, so in this case, use the SELECT query to be particularly careful.
Author: unknown
Original: http://www.chinabyte.net
Excerpt from:
Code laboratory
