Some issues to note about operating MySQL databases in PHP 1. Exceptions to semicolons
For MySQL, the first thing you have to remember is that every line of command ends with a semicolon (;) but... there is no absolute thing, and it is the same here. When a line of MySQL is inserted into PHP code, it is best to omit the subsequent semicolon, for example:
mysql_query ("INSERT INTO tablename (first_name, last_name) VALUES ('$first_name', $last_name')");
This is because PHP also ends with a semicolon as a line. The extra semicolon sometimes makes the PHP syntax analyzer confused, so it is better to omit it. In this case, although the semicolon is omitted, PHP will automatically add it to you when executing MySQL commands.
There is also a situation where you don’t have a semicolon. When you want to display the verticals of the field instead of being horizontally arranged as usual, you can use G to end a line of SQL statements, and you will not use a semicolon, for example:
SELECT * FROM PENPALS WHERE USER_ID = 1G
2. TEXT, DATE, and SET data types
The fields of the MySQL data table must have a data type defined. There are about 25 options for this, most of which are straightforward and clear, so there is no need to talk much. 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")
Furthermore, subtraction can be performed between DATA data types to obtain the number of time and days of difference:
$age = ($current_date - $birthdate);
Collection SET is a useful data type. It is somewhat similar to enumeration ENUM, except that SET can save multiple values while ENUM can only save one value. Moreover, the SET type can only have up to 64 predefined values, while the ENUM type can handle up to 65,535 predefined values. And what should I do if I need a set of values greater than 64? At this time, multiple collections need to be defined to solve this problem together.
3. Wildcard
There are two types of wildcard characters in SQL: " * " and " % ". Used in different situations. For example: If you want to see all the contents of the database, you can query it like this:
SELECT * FROM dbname WHERE USER_ID LIKE '%';
Here, both wildcards are used. They mean the same meaning ?? are used to match any string, but they are used in different contexts. " * " is used to match the field name, while " % " is used to match the field value. Another thing that is not easy to notice is that the "%" wildcard needs to be used with the LIKE keyword.
There is also a wildcard character, which is the underscore "_", which represents a different meaning than above and 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 or server-side script to verify the data, as mentioned earlier. However, in the database, some fields are allowed to be empty and filled in nothing. For such records, 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 creating or modifying this field), MySQL will empty this field and fill in nothing. For a field of ENUM enum type, if you declare NOT NULL for it, MySQL will insert the first value of the enum set into the field. That is, MySQL takes the first value of the enumeration set as the default value of this enumeration type.
There are some differences between a record with a value of NULL and a empty record. The % wildcard can match empty records, but cannot match NULL records. At some point, this distinction can have some unexpected consequences. In my experience, any field should be declared NOT NULL . In this way, the following SELECT query statement can work normally:
if (!$CITY) {$CITY = "%";}
$selectresult = mysql_query ("SELECT * FROM dbname
WHERE FIRST_NAME = ' Liu '
AND LAST_NAME = 'Rufeng'
AND CITY LIKE '$CITY'
");
In the first line, if the user does not specify a CITY value, the wildcard % will be used to substitute the CITY variable, so that any CITY value will be taken into account during searching, even those records where the CITY field is empty.
But if there are some records, its CITY field value is NULL, then the problem arises. The above query cannot find these fields. A solution to the problem can be:
if (!$CITY) { $CITY = "%"; }
$selectresult = mysql_query ("SELECT * FROM dbname
WHERE FIRST_NAME = ' Liu '
AND LAST_NAME = 'Rufeng'
AND (CITY LIKE '$CITY' OR CITY IS NULL)
");
Note that when searching for NULL, the keyword "IS" must be used, and LIKE will not work properly.
Finally, 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 may be empty. This is also a bug in MySQL, so in this case, be especially careful when using SELECT queries.