6 Practical tips in MySQL programming

Each line command ends with a semicolon (;)

For MySQL, the first thing you have to keep in mind is that each line of its command is terminated with a semicolon (;), but when a row of MySQL is inserted into the PHP code, it is best to omit the following semicolon, for example:

The

code is as follows:


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 let the PHP parser can not understand, so it is omitted to drop the good. In this case, although the semicolon is omitted, PHP will automatically help you when executing the MySQL command.

Using associative array to access query results

Look at the following example:

[Code]
$connection =mysql_connect ("localhost", "Albert", "Shhh");

mysql_select_db ("Winestore", $connection);

$result =mysql_query ("Select Cust_id,surname,

FirstName from Customer ", $connection);

while ($row =mysql_fetch_array ($result))

{

echo "id:t{$row [" cust_id "]}n";

echo "surnamet{$row [" Surname "]}n";

echo "name:t{$row [" FirstName "]}nn";

}

The function mysql_fetch_array () puts a row of query results into an array and can be referenced in two ways, such as cust_id can be in the following two ways: $row ["cust_id"] or $row[0]. Obviously, the former is much more readable than the latter.

In multiple table links, if you have two column names, it's best to separate them with aliases:

The

code is as follows:


SELECT Winery.name as wname,region.name as Rname,from winery,region WHERE winery.region_id=region.region_id;

The reference to the column name is: $row ["Wname"] and $row["Rname"]

In the case of specifying a table name and a column name, only the column name is referenced:

The

code is as follows:


SELECT winery.region_id

From Winery

The reference to the column name is: $row ["region_id"]

A reference to a clustered function is a reference name:

The

code is as follows:


SELECT Count (*)

from customer;

The reference to the column name is: $row [Count (*)]

TEXT, DATE, and set data types

The MySQL data table fields must have a data type defined. There are about 25 options, most of which are straightforward, and it's 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") and can be subtracted between the data types to obtain a time difference of days:

The

code is as follows:


$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 if you need a collection that is greater than 64 values, then you need to define multiple collections to solve the problem together.

Develop fast scripts with mysql_unbuffered_query ()

This function can be used to replace the mysql_query () function, the main difference is that Mysql_unbuffered_query () after the execution of the query immediately return, do not need to wait or lock the database. However, the number of rows returned cannot be checked with the mysql_num_rows () function because the result set size of the output is unknown.

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:

The

code is as follows:


select*from dbname WHERE user_id like '% ';

Here, two wildcard characters are 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.

Not null and empty records

What happens if a user presses the submit button without filling in anything? If you really need a value, you can use client script or server-side scripts for data validation. However, in the database, some fields are allowed to be empty and nothing to fill. For this type of record, MySQL is going to do something about it: Insert a value of NULL, or 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 enumeration type, if you declare not Null,mysql, the first value of the enumeration set is inserted 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 my experience is concerned, any field should be declared not NULL. So many select query statements can function correctly. Note When searching for NULL, you must use the ' is ' keyword, and like does not work properly. 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. This is also a MySQL bug, so in this case, use select query to be particularly careful.