PHP notes (PHP advanced) advanced articles will involve database usage and Cookie and Session sessions, improving PHP development efficiency and operation efficiency
MySQL operations that PHP programmers need to master
Design tables for projects
Use SQL statements
MySQL directory structure
The data directory stores library files.
The bin directory stores MySQL management commands.
* The INI file records the MySQL configuration.
Connect to MySQL DB:
Mysql-h SQL address-u user name-p password, such as mysql-h localhost-u root-p123456
Safe Method: first enter "mysql-h SQL address-u username-p", press enter, and then enter the password
Data Definition Language (DDL)
Definition: used to create various objects in the database-tables, views, indexes, synonyms, clustering, etc.
SQL statement
Create a database
CREATE DATABASE [IF NO EXISTS] DatabaseName
Create a table
CREATE TABLE [IF NOT EXISTS] TableName (colname1 type [property] [index],colname2 type [property] [index],...)[tableType] [tableCharSet];
Modify table
Alter table operation
Data type
Numeric type
UNSIGNED: specify as UNSIGNED storage
Integer
TINYINT 1 Byte (-128,127) (0,255) small integer
SMALLINT 2 Byte (-32 768,32 767) (535) large integer
MEDIUMINT 3 Byte (-8 388 608 388 607) (777 215) large integer
INT or INTEGER 4 Byte (-2 147 483 147 483, 2 647 294 967) (295) large INTEGER
BIGINT 8 Byte (-9 233 372 036 854 775 223 372 854 775 807 446 744 709) (551 615) extremely integer
Floating point type
FLOAT 4 bytes (-3.402 823 466 E + 38, 1.175 494 351 E-38), 0, (1.175 494 351 E-38, 3.402 823 466 351 E + 38) 0, (1.175 494 351 E-38, 3.402 823 E + 38)
DOUBLE 8 bytes (1.797 693 134 862 315 7 E + 308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E + 308) 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E + 308)
Character type
CHAR 0-255Byte fixed length string,
VARCHAR 0-255Byte variable-length string. the length must be specified.
A binary string of no more than 255 characters between TINYBLOB and bytes.
TINYTEXT 0-255Byte short text string
BLOB 0-65 535 bytes long text data in binary format
TEXT 0-65 535 bytes long TEXT data
MEDIUMBLOB 0-16 777 215 Byte binary text data of medium length
MEDIUMTEXT 0-16 777 215 Byte medium-length text data
LOGNGBLOB 0-4 294 967 295 Byte extremely large text data in binary format
LONGTEXT 0-4 294 967 295 Byte large text data
CHAR processing speed is relatively fast, VARCHAR has a variable size
Binary storage is mainly used to save non-text files.
ENUM: enumeration type. up to 65535 values can be stored. only one value can be stored in one field.
SET, SET type, can store up to 64 values, one value segment can store multiple values
Date type
DATE 3 Byte 1000-01-01/9999-12-31 YYYY-MM-DD DATE value
TIME 3 Byte '-838: 59: 59'/'2017: 59: 59' HH: MM: ss time value or duration
YEAR 1 Byte 1901/2155 yyyy year value
DATETIME 8 Byte 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH: MM: SS mixed date and time value
TIMESTAMP 8 Byte 2037 00:00:00/when yyyymmdd hhmmss mixed date and time value, TIMESTAMP
※Any data type is saved as a string and can be converted automatically.
※Save the time as a php timestamp for easy calculation.
Data field attributes
Unsigned: set this field to an unsigned value. it can only be a numeric value.
Zerofill: fill in "0" when the record value of this field does not reach the specified number of digits. it can only be numeric.
Auto_increment: set the value of this field to automatically increase, you can also set a custom value, you need to set the index or primary key at the same time, can only be numeric type
Null and not null: set whether the field is allowed to be null. we recommend that you set it to non-null, and use it with default.
Default: set the default value of this field. if not entered, use the default value.
Index
Advantages:
Improves query speed
Disadvantages:
High creation and maintenance costs
Resource occupation
Primary key index (primary key): the index value must be unique and each table has only one
Unique index: the index value must be unique, but one table can have multiple
Regular index: the most basic index with no restrictions
Full-text index (filltext): It can only be used on MyISAM. the larger the table, the better the effect, but the slower the speed.
Create and use, you can view the MySQL index type list to make MySQL run efficiently
Data table type and storage location
MySQL can select the optimal storage engine for different storage engine requirements
The data table type is the storage engine.
Use the type or engine keyword to specify the table type
Common table types
MyISAM
Fast read operations
Unsupported for some features (transactions)
InnoDB
Support some features not supported by MyISAM
Full-text index not supported
Large occupied space
| Function |
MyISAM |
InnoDB |
| Transaction processing |
Not supported |
Supported |
| Data row locking |
Not supported |
Supported |
| Foreign key constraint |
Not supported |
Supported |
| Tablespace occupation |
Relatively small |
Large |
| Full-text index |
Supported |
Not supported |
MySQL default character set
Utf8 recommended
Character Set: used to define how MySQL stores strings
Use the character set keyword to specify the character set
Checking Rules: defines a string comparison method for rules.
Use collate to specify proofreading rules
Data operation language (DML)
There are three main forms:
1) INSERT: INSERT
Insert into tablename [(field list)] values (value list 1) [, (value list 2)...]
If a field list exists after the table name, the value list corresponds to the field list one by one. If no field list exists, the value list corresponds to the field list in the table one by one.
2) UPDATE: UPDATE
Update tablename set field name = 'value' [condition]
3) DELETE: DELETE
Delete from tablename [condition]
You can use operators, including arithmetic operators, logical operators, comparison operators, and bitwise operators.
Data Query Language (DQL)
The basic structure is defined by the SELECT [ALL | DISTINCT] clause, FROM clause, WHERE
Query Block composed of clauses:
SELECT <字段列表>
FROM <表或视图名>
[WHERE <查询条件> /Group by/order by]
DISTINCT indicates that duplicate records are not displayed.
You can use the as keyword to Alias a field name and use it for a field name that may be ambiguous.
Data Control Language (DCL)
Definition: used to grant or revoke a certain privilege to access the database, control the time and effect of database operations, and monitor the database.
MySQL built-in functions
Location: select statement, and clause where order by having, update delete statement and clause
The field name can be used as a variable in the function. The value of the variable is all the values corresponding to the column.
Common
String functions
Concat: concatenates input parameters into a string.
Insert (str, x, y, insert): starting from the position x of str, replace the string of the y length with insert
Lower (str), upper (str): converts a string to uppercase and lowercase letters.
Left (str, x) right (str, x) returns x characters on the left (right) of str. if x is null, null is returned.
Lpad (str, n, pad) rpad (str, n, pad) pads string str from the leftmost (rightmost) with pad until the total length is n
Trim (str), ltrim (str), rtrim (str) remove both sides, left, right Space
Replace (str, a, B) replace string a with string B in String str
Strcmp (s1, s2): If S1 is smaller than S2,-1 is returned. If S1 is larger than S2, 1 is returned. If s1 is equal, 0 is returned (ASCII code is compared)
Substring (str, x, y) returns the substring from position x in str with the length of y.
Numeric functions
Abs (x): returns the absolute value.
Ceil (x): returns the smallest integer greater than x.
Floor (x): returns the largest integer smaller than x.
Mod (x, y): returns the modulo of x and y.
Rand (): returns a random number between 0 and 1.
Round (x, y): returns the rounding result of the y decimal places of x.
Truncate (x, y): returns the result of truncating x to y decimal places.
Date functions
Curdate (): returns the current year, month, and day.
Curtime (): returns the current hour, minute, and second.
Now (): returns the current date
Unix_timestamp (time): returns the unix timestamp.
From_unixtime (): converts a Unix timestamp to a date.
Week (): returns the week of the timestamp.
Year (): returns the year of the timestamp.
Hour (): returns the hour of the timestamp.
Minute (): returns the minute of the timestamp.
Month (): returns the month of the timestamp.
Date_format (time, "% Y-% m-% d % H: % I: % s"): format the return time
Process control functions
If (value, t, f): if the value is true, t is returned. if the value is false, f is returned.
Ifnull (value1, value2): If value1 is null, value2 is returned. if value1 is not empty, value1 is returned.
Case
When value1 then value2
When value3 then value4
......
Else fault END
If value1 is true, value2 is returned. if value3 is true, value4 is returned, and so on. otherwise, fault is returned.
Other usage: mysql statement case when
Other functions
Database (): returns the database name.
Version (): returns the MySQL version.
User (): the user that returns MySQL
Inet_aton (ip): converts an IP address to a network byte
Inet_nton (): converts network byte order to IP address
Password (): MySQL User password encryption
Md5 (str): string encryption
PHP database operations
Connect to database
Mysql_connect (IP, user, psw): IP is the database address, user is the user name, psw is the user password, connection is successful, database resources are returned, connection fails, false is returned
Select database
Mysql_select_db ($ dbname, [$ res]): $ dbname indicates the database name. $ res indicates the resource returned when the database is connected. If this parameter is not added, the database resource created recently is used by default.
SQL statement input
Mysql_query (): execute an SQL statement. if the statement returns a result set, the function returns the result set after successful execution. if the statement does not return a result set, the function returns true if execution is successful.
Resolution error
Mysql_errno (): error code returned
Mysql_error (): Error message returned
Disable database resources
Mysql_close (): disables database resources. If no parameter is used, the enabled resources are disabled by default (recommended)
Function
Mysql_insert_id (): returns the auto-increment id. if AUTO_INCREMENT is not set, false is returned.
Mysql_affected_rows (): gets the number of affected rows
Retrieve data from the result set
Mysql_fetch_row ($ result): retrieves a data entry from the result set and returns an index array.
Mysql_fetch_assoc ($ result): retrieves a data entry from the result set and returns an associated array.
Mysql_fetch_array ($ result): retrieves a data entry from the result set and returns the index array and associated array.
Mysql_fetch_object ($ result): obtains a data entry from the result set and returns an object.
Mysql_data_seek ($ result, $ row): move the pointer to the specified position
Obtain fields from the result set
Mysql_num_rows ($ result): obtains the number of fields in the result set.
Mysql_num_fields ($ result): gets the number of columns in the result set.
Mysql_field_name ($ result): obtains the field name of the result set.
Mysqli database operations
New functions added after PHP5 are object-oriented, so mysqli is added as an object
Advantages of mysqli
Indicates improvement
Feature added
High efficiency
More stable
Three classes provided by mysqli extension
Mysqli: connection-related class
Constructor
Mysqli ([$ host [, $ username [, $ passd [, $ dbname [, $ port [, $ socket])
An object is returned when the connection is successful. if the connection fails, false is returned.
View connection failure information
Connect_errno (): The connection error number is returned.
Connect_error (): The connection error message is returned.
SQL statement input
Query (SQL): execute an SQL statement. if the statement returns a result set, the result set object mysqli_result is returned after the function is successfully executed. if the statement does not return a result set, true is returned after the function is successfully executed.
Method
Affected-rows (): returns the number of affected rows.
Errno (): error code returned
Error (): error message returned
Insert_id (): returns the automatically increasing id.
Close resources
Close (): close the connection
Mysqli_result: indicates the result set returned by queries to the database.
Attribute:
$ Num_rows: number of records in the result set
$ Field_count: number of fields in the result set
$ Current_field: get the position of the current column
Method:
Processing records
Fetch_row (): consistent with mysql_fetch_row ()
Fetch_assoc (): consistent with mysql_fetch_assoc ()
Fetch_array (): consistent with mysql_fetch_array ()
Fetch_object (): consistent with mysql_fetch_object ()
Data_seek (): consistent with mysql_data_seek ()
Free (): releases the result set.
Processing field
Fetch_field (): retrieves the column information and returns it as an object.
Fetch_fields (): retrieves information about all columns and returns the information as an object.
Field_seek (): move the field pointer
Execute multiple SQL statements
Multi_query (sql1 [; sql2]): multiple SQL statements can be executed, separated by ";". if multiple result sets exist, all statements are returned.
Next_result (): returns the next result set of multi_query ().
More_results (): Check whether the next result set exists.
Mysqli_stmt: preprocessing class
Advantages:
Functions that can be completed by mysqli and mysqli_result can be completed by mysqil_stmt.
High efficiency: execute multiple identical SQL statements. if data is different, you can directly transmit data without repeating the statements.
Prevents SQL injection, because incoming and outgoing data is only used as value classes and not as executable statements.
Create object
After creating a mysqli object, use the stmt_init () method of the object to initialize the mysqli_stmt object.
Prepare and send statements
The placeholder "?" must be used for the parameter values in the statement. Replace
Use the prepare ($ SQL) method in mysqli_stmt to send the statement to the server for preparation.
You do not need to create a mysqli_stmt object. use prepare ($ SQL) in mysqli to prepare the SQL statement and return the mysqli_stmt object.
Pass a value to the placeholder (bind a parameter)
Bind_param ($ type, $ var1 [, $ var2. ..])
$ Type can be I, d, s, and B, representing integer, double, string, and binary resources respectively.
The number of types in $ type must be the same as the placeholder, and the number of $ var values must be the same as the number of placeholders.
Assign a value to the variable $ var.
Execute SQL statements
No result set returned
Execute the inserted parameters using the execute () method. The return value belongs to the boolean type.
Returned result set
Bind_result ($ var1 [, $ var2. ..]) is used to bind the result set.
Execute the statement using fetch (), obtain a result each time, and pass it to the variable in bind_result ().
Use store_result () to execute the statement, retrieve all results at a time, return the result set, and use fetch () to obtain each record.
Result_matedate () returns the result set, used to obtain field information
Use result_free () to release the result set
Close resources
Close using the close () method
Function
Functions supported by mysqli and mysqli_result are basically supported by mysqli_stmt.
Transaction processing
Create a table
The table type MyISAM does not support the transaction function. InnoDB tables must be created.
Disable automatic submission
Autocommit (): If the parameter is 0 or false, disable automatic submission.
Commit transactions
Commit (): commit a transaction (multiple SQL statements after execution)
Roll back a transaction
Rollback (): roll back a transaction (multiple executed SQL statements)
Other methods
Set_charset ($ string): sets the retrieved character set.
PDO
Advantages:
No need to change the code when changing the database
Disadvantages:
Less efficient than mysql and mysqli
Three types
PDO: represents a connection between PHP and database services.
Create a PDO object
Dpo ($ dsn, $ username, $ passd [, $ array]): $ When the dsn is used to connect to the mysql database, it is set to 'MySQL: host = ip: port; dbname = $ string ', $ array is the tuning parameter
DSN (data source name) data source: including the host location, database name, and drivers required for different databases
You can use getattribute ($ attribute) to view attributes and use setattribute ($ attribute, $ value) to set attributes.
Execute SQL statements
Query ($ string): executes the statement returned by a result set and returns the preprocessing object PDOStatement.
Exec ($ string): executes statements that affect the table and returns the number of affected rows.
Design error report
Use setAttribute () to set the error report mode
ERRMODE_SILENT: no error is displayed. check the error manually.
ErrorCode: error number returned
ErrorInfo: returned error message array
ERRMODE_WARNING: an error occurs and an E_WARNING message is displayed.
ERRMODE_EXCEPTION: an error occurs and a PDOException is thrown.
Transaction processing
Use setAttribute () to enable transaction processing and disable automatic submission.
Use commit () to submit executed SQL statements
Roll back executed SQL statements using rollback ()
PDOStatement: indicates a pre-processing statement. after the statement is executed, it indicates a related result set.
Function
Prepare a statement
Processing result set
Prepare and send statements
The placeholder "?" can be used for parameter values in the statement.
Placeholder ": placeholder name" instead
Use the PDO: prepare ($ SQL) method to send the statement to the server for preparation, return the PDOStatement object, and store the result set.
Pass a value to the placeholder (bind a parameter)
Bind_param ($ key, $ value)
"?" Placeholder
$ Key is set to index number,
$ Value is set to transfer value
Name placeholder
$ Key is set as the key name
$ Value is set to transfer value
SQL statement execution
Execute the statement of the bound parameter using the execute () method
Use execute ($ array) and $ array to add parameters to avoid parameter binding.
Record acquisition
Use fetch () to obtain each record in the result set, and return the index and associated Hybrid array.
The parameter is PDO: FETCH_ASSOC, and the associated array is returned.
The parameter is PDO: FETCH_NUM, and the index array is returned.
The parameter is PDO: FETCH_BOTH, and the index is associated with a hybrid array.
FetchAll () gets each record of the result set and returns a two-dimensional array.
You can use setFatchMode () to set the retrieval mode to avoid setting the retrieval mode every time.
Field acquisition
ColumnCount ()
GetColumnMeta () returns the metadata of a column in the result set.
PDOException: indicates an error generated by PDO. Should not throwPDOExceptionException
Use try catch to catch various exceptions, including connection exceptions and SQL statement exceptions.
Mamcache/memcached
A high-performance distributed memory object cache system. Maintain a large hash table in the memory to maintain data in the memory.
Working Principle
When PHP queries data for the first time, it stores the data in the mamcache. when it queries data for the next time, it first accesses the mamcache.
Install
Linux installation
Based on libevent events, you must first install the libevent Library
Install in Windows
Default port 11211
Memcache command
Command Description Example
| Get |
Reads a value |
Get mykey |
| Set |
Set a key unconditionally |
Set mykey 0 60 5 |
| Add |
Add a new key |
Add newkey 0 60 5 |
| Replace |
Overwrite existing key |
Replace key 0 60 5 |
| Append |
Append data to existing key |
Append key 0 60 15 |
| Prepend |
Prepend data to existing key |
Prepend key 0 60 15 |
| Incr |
Increments numerical key value by given number |
Incr mykey 2 |
| Decr |
Decrements numerical key value by given number |
Decr mykey 5 |
| Delete |
Deletes an existing key |
Delete mykey |
| Flush_all |
Invalidate specific items immediately |
Flush_all |
| Invalidate all items in n seconds |
Flush_all 900 |
| Stats |
Prints general statistics |
Stats |
| Prints memory statistics |
Stats slabs |
| Prints memory statistics |
Stats malloc |
| Print higher level allocation statistics |
Stats items |
| |
Stats detail |
| |
Stats sizes |
| Resets statistics |
Stats reset |
| Version |
Prints server version. |
Version |
| Verbosity |
Increases log level |
Verbosity |
| Quit |
Terminate telnet session |
Quit |
Use memcache in PHP
Class: memcache
Connection: memcache: connect ($ host, $ port)
1
connect("localhost",11211) or die("could not connect");
Other methods
Add: add data
Set/replace: modify data
Get: get data
Delete: delete data
......
When to use memcache
The data read from the database for future use.
Used in Session Control
Tips
Use SQL statements as Keys
Use md5 () to modify the SQL statement to shorten the SQL statement, making it easy to save
Session Control: Connection-Oriented Reliable connection method. it uses session control to determine user logon behavior.
Cookie technology
A file from the server to the client. This file is used by the client to save user information. the server distinguishes users based on files.
Set cookie
Setcookie ($ key, $ value, $ time): header information. no output is allowed.
Get cookie
Use a global array $ _ COOKIE [] to obtain cookie content
Delete cookieti
Use setcookie to set $ value to null or not, and set $ time to 0 or not.
Session technology
When user data is saved on the server, a SessionID is generated. you can use cookies and URLs to pass this id.
Session configuration
Configure php. ini on the server
Enable session
Session_start (): Enables php core programs to pre-load session-related built-in environment variables into the memory.
Start a session
Cookie-based session, this function cannot have any output
Returns the enabled session.
Set and retrieve sessions
Use $ _ SESSION [] to set and obtain a session
Session_id () gets and sets the session id
Delete session
$ _ SESSION = array (); sets the session to an empty array.
Delete session in cookie
Session_destory (): destroys a session.
The sessionid is passed based on the url. after the url parameter is set to session_name and session_start (), the parameter is automatically searched.
Constant SID. when the cookie is disabled, this constant indicates session_name and session_id. when the cookie is enabled, this constant is null.
Set session. use_trans_sid = 1 in php. ini to automatically add SID after Page jump (hyperlink, header, form)
Session advanced technology
Session settings in php. ini
Session_name: Set the cookie and session_name in the SID.
Session. use_trans_sid: Sets whether the SID is enabled. after it is enabled, the SID can be automatically added.
Session. save_path: set the location where the session file is stored. If this parameter is not set, the session file is not generated.
Session. gc_maxlifetime: sets the validity period of the session file. if the session is not refreshed after this time, the session file will become invalid.
Session. gc_probability and session. gc_pisor are used together to define the session garbage collection probability. the algorithm is session. gc_probability/session. gc_pisor.
Session. use_cookie: Set the session to be written to the cookie.
Session. cookie_path: sets the files whose sessions are written to the cookie.
Session. cookie_lifetime: sets the session lifecycle.
Session. save_handler: Set the write mode and location of the session. when the value is user, you can use the session_set_save_handler () function.
Session_set_save_handler (open (), close (), read (), write (), destroy (), gc (): You can customize the session file storage path and storage method.
Use this function to define each method and use session as usual
Open (): called when session_start () is executed.
Close (): called when session_write_close () is executed.
Read (): After open () is called
Write (): it is called when the script end time and session_write_close () are executed.
Destroy (): called when the session uses session_destroy () or session_regenerate_id () to be destroyed
Gc (): it is determined by session. gc_probability and session. gc_pisor. The military may be called at any time.
Usage
Write Session to database
Write Session into Memcache
At this point, the basic learning of PHP is complete, and more learning is required to improve it!
