Basic Tutorial on PHP and MySQL (4)

The content of this article is about the basic tutorial of PHP and MySQL (4). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

SQL in MySQL

For MySQL, the first thing you have to remember is that every line of its commands ends with a semicolon (;), but... there is no absolute thing, here also the same.

I have mentioned before that when a line of MySQL 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 also ends a line with a semicolon. In addition The semicolon sometimes confuses the PHP parser, so it's better to omit it. In this case, although the semicolon is omitted, PHP will automatically add it for you when executing the MySQL command.

There is another situation where you don’t need to add a semicolon. When you want to display the vertical arrangement of the fields you want instead of the usual horizontal arrangement, you can use G to end a line of SQL statements. In this case, there is no need for a semicolon, for example:

SELECT * FROM PENPALS 
WHERE USER_ID = 1G

TEXT, DATE, and SET data types

Fields in MySQL data tables must have a data type defined. There are about 25 options, most of which are straightforward and require no further explanation. But there are a few that need to be mentioned.

TEXT is not a data type, although some books may say so. It should actually be "LONG VARCHAR" or "MEDIUMTEXT".

DATE data type format 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")

And, you can subtract between DATA data types to get the difference in time in days:

$age = ($current_date - $birthdate);

The set SET is a useful data Type, it is somewhat similar to the enumeration ENUM, except that SET can save multiple values ​​and ENUM can only save one value. Moreover, the SET type can only have a maximum of 64 predefined values, while the ENUM type can handle a maximum of 65,535 predefined values. And what if we need a collection with more than 64 values? At this time, you need to define multiple collections to solve this problem together.

Wildcards

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 like this:

SELECT * FROM dbname 
WHERE USER_ID LIKE '%';

Here, both wildcards are used. They mean the same thing - they both match any string - but they are used in different contexts. " * " is used to match field names, and " % " is used to match field values. Another thing that is not easy to notice is that the "%" wildcard character needs to be used together with the LIKE keyword.

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

NOT NULL and empty records

What happens if the user presses the submit button without filling in anything? If you really need a value, you can use client-side script or server-side script for data validation, as mentioned earlier. However, in the database, some fields are allowed to be left blank and filled with nothing. For such records, MySQL will do a few things:

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 leave this field empty and fill it with nothing.
For an ENUM enumeration type field, if you declare NOT NULL for it, MySQL will insert the first value of the enumeration set into the field. In other words, MySQL uses the first value in the enumeration set as the default value for this enumeration type.

There are some differences between a record with a value of NULL and an empty record. The % wildcard character can match empty records, but it cannot match NULL records. At some point, this distinction can have unintended consequences. As far as my experience goes, any field should be declared NOT NULL . In this way, the following SELECT query statement can run normally:

if (!$CITY) {$CITY = "%";} 
$selectresult = mysql_query ("SELECT * FROM dbname 
WHERE FIRST_NAME = ' 柳 ' 
AND LAST_NAME = ' 如风 ' 
AND CITY LIKE '$CITY' 
");

In the first line, if the user does not specify a CITY value, then the wildcard character % will be used to substitute the CITY variable, so that any CITY will be included in the search All values ​​are taken into account, even those records with an empty CITY field.

But if there are some records whose CITY field value is NULL, then a problem arises. The above query cannot find these fields. A solution to the problem can be like this:

if (!$CITY) {$CITY = "%";} 
$selectresult = mysql_query ("SELECT * FROM dbname 
WHERE FIRST_NAME = ' 柳 ' 
AND LAST_NAME = ' 如风 ' 
AND (CITY LIKE '$CITY' OR CITY IS NULL) 
");

Note that when searching for NULL, you must use the "IS" keyword, and LIKE will not work properly.

The last thing to mention is that if you already 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 NULL. Is empty. This can be regarded as a bug of MySQL, so in this case, you must be particularly careful when using SELECT queries.

The above is the content of the PHP and MySQL basic tutorial (4). For more related content, please pay attention to the PHP Chinese website (www.php.cn)! M Related recommendations: y Summary of the basic knowledge of MySQL

PHP and MySQL basic tutorials (1)

pHP and MySQL basic tutorial (2)

PHP and MySQL basic tutorial (3)

PHP and MySQL basic tutorial (4)

mysql manual tutorial:

http://www.php.cn/course/37.html

mysql video tutorial:

http://www.php .cn/course/list/51.html