PHP Notes (Advanced PHP), PHP Notes_PHP Tutorial

PHP Notes (Advanced PHP Chapter), PHP Notes

The Advanced Chapter will cover the use of databases and Cookie and Session sessions to improve the development and operating efficiency of PHP

MySQL operations that PHP programmers need to master

• Design table for project
• Use SQL statement
• MySQL directory structure
  • The data directory stores library files
  • MySQL management commands are stored in the bin directory
  • *.ini files record MySQL configuration

Connect to MySQL DB:

• mysql -h sql address -u username -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, clusters, etc.
• SQL statement
  • Create database

  • <span>CREATE</span> <span>DATABASE</span> <span>[</span><span>IF NO EXISTS</span><span>]</span> DatabaseName

  • Create table

<span>CREATE</span> <span>TABLE</span> <span>[</span><span>IF NOT EXISTS</span><span>]</span><span> TableName (
colname1 type </span><span>[</span><span>property</span><span>]</span> <span>[</span><span>index</span><span>]</span><span>,
colname2 type </span><span>[</span><span>property</span><span>]</span> <span>[</span><span>index</span><span>]</span><span>,
...
)[tableType] [tableCharSet];</span>

• Modify table
  • alter table operation
• Data type
  • Numerical type
    • UNSIGNED: Specified as unsigned storage
    • Integer type
      • TINYINT 1 Byte (-128, 127) (0, 255) Small integer value
        SMALLINT 2 Byte (-32 768, 32 767) (0, 65 535) Large integer value
        MEDIUMINT 3 Byte ( -8 388 608, 8 388 607) (0, 16 777 215) Large integer value
        INT or INTEGER 4 Byte (-2 147 483 648, 2 147 483 647) (0, 4 294 967 295) Large integer value
        BIGINT 8 Byte (-9 233 372 036 854 775 808, 9 223 372 036 854 775 807) (0, 18 446 744 073 709 551 615) Extremely large integer value
    • 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 466 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
      TINYBLOB 0-255Byte binary string not exceeding 255 characters
      TINYTEXT 0-255Byte Short text string
      BLOB 0-65 535Byte Long text data in binary form
      TEXT 0-65 535Byte Long text data
      MEDIUMBLOB 0-16 777 215Byte Medium-length text data in binary form
      MEDIUMTEXT 0 -16 777 215Byte Medium length text data
      LOGNGBLOB 0-4 294 967 295Byte Very large text data in binary form
      LONGTEXT 0-4 294 967 295Byte Very large text data
    • CHAR is processed faster, VARCHAR has variable size
    • Binary save is mainly used to save non-text files
    • ENUM, an enumeration type, can store up to 65535 values, and a field can only store one value
    • SET, a collection type, can store up to 64 values, and one value segment can store multiple values
  • Date type
    • DATE 3Byte 1000-01-01/9999-12-31 YYYY-MM-DD date value
      TIME 3Byte '-838:59:59'/'838:59:59' HH:MM:SS time Value or duration
      YEAR 1Byte 1901/2155 YYYY Year value
      DATETIME 8Byte 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM: SS mixed date and time value
      TIMESTAMP 8Byte 1970-01-01 00:00:00/sometime in 2037 YYYYMMDD HHMMSS mixed date and time value, timestamp

※Any data type stored in the form of a string can be automatically converted

※Save the time as a php timestamp for easy calculation

Data field attributes

• unsigned: Set this field to an unsigned value, which can only be numeric type
• zerofill: When the value of the record in this field does not reach the specified number of digits, fill it with "0", which 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. It can only be numeric
• null and not null: Set whether the field is allowed to be empty. It is recommended to set it to non-empty and use it with default
• default: Set the default value of this field. If not entered, use the default value

Index

• Advantages:
  • Improve query speed
• Disadvantages:
  • The creation and maintenance costs are relatively high
  • Occupying resources
• Primary key: The index value must be unique, there is only one for each table
• Unique index (unique): The index value must be unique, but a table can have multiple
• Regular index: the most basic index without too many restrictions
• Full text index (filltext): can only be used on MyISAM. The larger the table, the better the effect, but the speed is slower
• To create and use, you can view the list of MySQL index types to make MySQL run efficiently

Data table type and storage location

• MySQL can choose the optimal storage engine for different storage engine needs
• The data table type is the storage engine
• Use the type or engine keyword to specify the table type
• Commonly used table types
  • MyISAM
    • Emphasis on fast read operations
    • Some functions are not supported (transactions)
  • InnoDB
    • Supports some features that MyISAM does not support
    • Full-text indexing is not supported
      • Taking up a lot of space

        功能	MyISAM	InnoDB
        事务处理	不支持	支持
        数据行锁定	不支持	支持
        外键约束	不支持	支持
        表空间占用	相对较小	较大
        全文索引	支持	不支持

MySQL default character set

• Recommended utf8
• Character set: used to define the way MySQL stores strings
  • Use the character set keyword to specify the character set
• Collation rules: The rules define how to compare strings
  • Use collate to specify collation rules

Data Manipulation Language (DML)

• There are three main forms:
  • 1) Insert: INSERT
    • insert into tablename[(field list)] values(value list 1)[,(value list 2)...]
      • After the table name, if there is a field list, the value list corresponds to the field list one-to-one. If there is no field list, the value list corresponds to the fields in the table one-to-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 composed of SELECT[ALL|DISTINCT] clause, FROM clause, WHERE
  Query block composed of
  • clauses:
    • SELECT
    • FROM
      [WHERE/GROUP BY/ORDER BY]
      DISTINCT means not to display duplicate records
      Use the as keyword to create aliases for field names that may cause ambiguity

      Data Control Language (DCL)

      • Definition: Used to grant or revoke certain privileges to access the database, control the time and effect of database manipulation transactions, monitor the database, etc.

      MySQL built-in functions

      • Position: select statement, and clause where order by having, update delete statement and clause
      • You can use the field name as a variable in the function, and the value of the variable is all the values ​​corresponding to the column
      • Commonly used
        • String functions
          • concat: Concatenate the incoming parameters into a string
          • insert(str,x,y,insert): Starting from the x position of str, replace the y-length string with insert
          • lower(str),upper(str): Convert string to uppercase, lowercase
          • left(str,x) right(str,x) returns x characters to the left (right) of str, if x is null, returns null
          • lpad(str,n,pad) rpad(str,n,pad) Use pad to pad the string str from the leftmost (right) until the total length n
          • trim(str), ltrim(str), rtrim(str) remove spaces on both sides, left and right
          • replace(str,a,b) replaces all string a
          with string b in string str

          • strcmp(s1,s2): If S1 is smaller than S2, return -1; if S1 is larger than S2, return 1; if they are equal, return 0 (the comparison is ASCII code)

          • substring(str,x,y) returns the substring of length y starting from position x in string str
        • Numerical function
          • abs(x): Returns the absolute value
          • ceil(x): Returns the smallest integer greater than x
          • floor(x): Returns the largest integer less than x
          • mod(x,y): Returns the modulus of x and y
          • rand(): Returns a random number between 0-1
          • round(x,y): Returns the rounding result of parameter x to y decimal places
          • truncate(x,y): Returns the result of number x truncated to y decimal places
        • Date function
          • 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 unix timestamp
          • from_unixtime(): Convert Unix timestamp to 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"): formatted return time
        • Process control function
          • if(value,t,f): If value is true, return t, if value is false, return f
          • ifnull(value1,value2): If value1 is empty, return value2, if value1 is not empty, return value1
          • case
            when value1 then value2
            when value3 then value4
            ......
            else fault END
            
            • When value1 is true, return value2, when value3 is true, return value4, and so on, otherwise return fault
            • Other usage: mysql statement case when
        • Other functions
          • database(): Returns the database name
          • version(): Returns the MySQL version
          • user(): Returns the MySQL user
          • inet_aton(ip): Convert IP to network byte order
          • inet_nton(): Convert network byte order to IP
          • password(): MySQL user password encryption
          • md5(str): Encrypt string

      PHP operation database

      • Connect to database
        • mysql_connect(IP,user,psw): IP is the database address, user is the username, psw is the user password. If the connection is successful, the database resource is returned. If the connection fails, false is returned
      • Select library
        • mysql_select_db($dbname,[$res]): $dbname is the library name; $res is the resource returned by connecting to the database. If this parameter is not added, the default is the recently created database resource
      • SQL statement input
        • mysql_query(): Execute the SQL statement. If the statement returns a result set, the function execution returns the result set successfully. If the statement does not return the result set, the function execution returns true
      • Resolve errors
        • mysql_errno(): Return error number
        • mysql_error(): Return error message
      • Close database resources
        • mysql_close(): Closes database resources, does not use parameters, closes open resources by default (recommended)
      • Function
        • mysql_insert_id(): Returns the automatically growing id. If AUTO_INCREMENT is not set, it returns false
        • mysql_affected_rows(): Get the number of affected rows
      • Retrieve data from the result set
        • mysql_fetch_row($result): Get a piece of data from the result set and return the index array
        • mysql_fetch_assoc($result): Get a piece of data from the result set and return an associative array
        • mysql_fetch_array($result): Get a piece of data from the result set and return the index array and associative array
        • mysql_fetch_object($result): Get a piece of data from the result set and return the object
        • mysql_data_seek($result,$row): Move the pointer to the specified position
      • Get fields from result set
        • mysql_num_rows($result): Get the number of fields in the result set
        • mysql_num_fields($result): Get the number of columns in the result set
        • mysql_field_name($result): Get the field name of the result set

      mysqli operation database

      • The newly added functions after PHP5 are all object-oriented, so mysqli is added in the form of objects
      • mysqli advantages
        • Indicates improvement
        • Function added
        • Greatly increased efficiency
        • More stable
      • Three classes provided by mysqli extension
        • mysqli: classes related to connections
          • Construction method
            • mysqli([$host [, $username [, $passd[, $dbname [,$port [, $socket ]]]]] )
            • The object is returned if the connection is successful, false if failed
          • View connection failure information
            • connect_errno(): Returns the connection error number
            • connect_error(): Returns connection error information
          • SQL statement input
            • query(sql): Execute a SQL statement. If the statement returns a result set, the function executes successfully and returns the result set object mysqli_result. If the statement does not return a result set, the function executes successfully and returns true
          • Method
            • affected-rows(): Returns the number of affected rows
            • errno(): Returns the error number
            • error(): returns error message
            • insert_id(): Returns the automatically growing id
          • Close resources
            
            • close(): Close the connection
        • mysqli_result: expresses the result set returned by a query to the database
          • Attributes:
            • $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(): Release the result set
            • Processing fields
              • fetch_field(): Fetch column information and return it as an object
              • fetch_fields(): Fetch all column information and return it as an object
              • field_seek(): Move field pointer
            • Execute multiple SQL statements
              
              • multi_query(sql1[;sql2]): Multiple sql statements can be executed. The statements are separated by ";". If there are multiple result sets, they will all be returned
              • next_result(): Returns the next result set of multi_query()
              • more_results(): Check whether it contains the next result set
        • mysqli_stmt: preprocessing class
          • Advantages:
            • Mysqil_stmt can complete the functions that mysqli and mysqli_result can complete
            • It is relatively efficient. It can execute multiple identical SQL statements. If only the data is different, there is no need to repeat the statement and the data can be transmitted directly
            • Prevent sql injection, because the incoming and outgoing data will only be used as value classes and not as executable statements
          • Create object
            • After creating the mysqli object, use the stmt_init() method of the object to initialize the mysqli_stmt object
          • Prepare and send statements
            • The parameter value in the statement should be replaced by the placeholder "?"
              • Use the prepare($sql) method in mysqli_stmt to send the statement to the server for preparation
              • No need to create a mysqli_stmt object, directly use prepare($sql) in mysqli to prepare the sql statement and return the mysqli_stmt object
          • Pass value to placeholder (bind parameter)
            • Use bind_param($type,$var1[,$var2...]) to bind parameters
              • $type can be i, d, s, b, representing integer, double, string and binary resources respectively
              • The number of types in $type must be the same as the number of placeholders, and the number of $var must be the same as the number of placeholders
            • Assign a value to variable $var
          • Execute sql statement
            • No result set returned
              • Use the execute() method to execute the inserted parameters and return a boolean type
            • A result set is returned
              • Use bind_result($var1[,$var2...]) to bind the result set
                • Use fetch() to execute the statement, get one result each time, and pass it to the variable in bind_result()
                • Use store_result() to execute the statement, retrieve all the results at once, return the result set, and then use fetch() to obtain each record
              • result_matedate() returns a result set, used to obtain field information
              • Use result_free() to release the result set
          • Close resources
            • Use the close() method to close
          • Function
            
            • mysqli and mysqli_result support functions, and mysqli_stmt basically supports them
      • Transaction processing
        • Create table
          • The table type is MyISAM and does not support transaction functions. You need to create an InnoDB type table
        • Turn off automatic submission
          • autocommit(): When the parameter is 0 or false, auto-commit is turned off
        • Submit transaction
          • commit(): Submit transaction (multiple executed sql statements)
        • Rollback transaction
          • rollback(): rollback transaction (multiple executed sql statements)
      • Other methods
        • set_charset($string): Set and retrieve the character set

      PDO

      • Advantages:
        • When changing the database, there is no need to change the code
      • Disadvantages:
        • Not as efficient as mysql and mysqli
      • Three categories
        • PDO: represents a connection between PHP and database services
          • Create PDO object
            • dpo($dsn,$username,$passd[,$array]): When $dsn connects to the mysql database, it is set to 'mysql:host=ip:port;dbname=$string', and $array is the tuning parameter
            • DSN (data source name) data source: including host location, library name and drivers required for different databases
            • You can use getattribute($attribute) to view attributes and setattribute($attribute,$value) to set attributes
          • Execute sql statement
            
            • query($string): Execute the statement that returns the result set and return the preprocessing object PDOStatement
            • exec($string): Execute statements that affect the table and return the number of affected rows
          • Design error reporting
            • Use setAttribute() to set error reporting mode
            • ERRMODE_SILENT: No errors are displayed, developers can check errors themselves
              • errorCode: Return error number
              • errorInfo: Returns an array of error information
            • ERRMODE_WARNING: An error occurred and an E_WARNING message is displayed
            • ERRMODE_EXCEPTION: An error occurred and PDOException was thrown
          • Transaction processing
            • Use setAttribute() to enable transaction processing and turn off automatic submission
            • Use commit() to submit the executed sql statement
            • Use rollback() to roll back the executed sql statement
        • PDOStatement: represents a prepared statement and represents a related result set after the statement is executed.
          • Function
            • Prepare a statement
            • Processing result sets
          • Prepare and send statements
            • The parameter value in the statement can use the placeholder "?"
            • placeholder ":placeholder name" instead of
      • • • • 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 value to placeholder (bind parameter)
            • Use bind_param($key,$value) to bind parameters
              • "?" placeholder
                • $key is set to the index number,
                • $value is set to the transmitted value
              • Name placeholder
                • $key is set to the key name
                • $value is set to the transmitted value
      • • • SQL statement execution
            • Use the execute() method to execute a statement with bound parameters
            • Use execute($array) to add parameters to the $array array to avoid binding parameters
          • Record acquisition
            
            • Use fetch() to get each record in the result set and return a mixed array of index and association
              • The parameter is PDO::FETCH_ASSOC and returns an associative array
              • The parameter is PDO::FETCH_NUM, and the index array is returned
              • The parameter is PDO::FETCH_BOTH, and the index associative mixed array is returned
            • fetchAll() obtains each record in the result set and returns a two-dimensional array
            • Use setFatchMode() to set the acquisition mode to avoid having to set the mode every time
          • Field acquisition
            
            • columnCount() gets the number of fields
            • getColumnMeta() returns the metadata of a column in the result set
        • PDOException: Represents an error generated by PDO. Your own code should not throw a PDOException exception
          • Use try catch to catch various exceptions, including connection exceptions, sql statement exceptions, etc.

      mamcache/memcached

      • A high-performance distributed memory object cache system. Maintain data in memory by maintaining a huge hash table in memory
      • How it works
        • When PHP queries data for the first time, it will store the data in mamcache. The next time it queries, mamcache will be accessed first.
      • Installation
        • Installation under Linux

          • Based on libevent events, so the libevent library must be installed first

        • Installation under 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

      PHP中使用memcache

      • 类：memcache
      • 连接：memcache::connect($host,$port)

      • <span>1</span> <?<span>php
        </span><span>2</span>     <span>$memcache</span> = <span>new</span><span> Memcache;
        </span><span>3</span>     <span>$memcache</span>->connect("localhost",11211) or <span>die</span>("could not connect");

      • 其他方法
        • add：添加数据
        • set/replace：修改数据
        • get：获取数据
        • delete：删除数据
        • ......
      • 何时使用memcache
        • 数据库中读出来的数据，方便下次使用
        • 会话控制中使用
      • 技巧
        • 用sql语句作为key
        • 用md5()修改sql语句，使sql语句变短，便于保存

      会话控制：面向连接的可靠的连接方式，通过会话控制，判断用户的登录行为

      • cookie技术
        • 服务器给客户端的一个文件，通过客户端的这个文件，保存用户信息，服务器根据文件，区分用户
        • 设置cookie
          • setcookie($key,$value,$time)：头信息，不能有任何输出
        • 获取cookie
          • 使用全局数组$_COOKIE[]获取cookie内容
        • 删除cookieti
          • 用setcookie设置$value为空或不设置，$time设置为0或不设置
      • session技术
        • 在服务器中保存用户数据，会产生一个SessionID，可使用cookie和url传递该id
        • session配置
          • 配置服务器端的php.ini
        • 开启会话
          • session_start()：让php的核心程序将和session有关的内建环境变量预先载入到内存中
            • 开启一个会话
              • 基于cookie的session，使用该函数不能有任何输出
            • 返回已开启的会话
        • 设置和获取session
          • 使用$_SESSION[]设置和获取session
          • session_id()获取和设置session的id
        • 删除session
          • $_SESSION=array()；将session设置为空数组
          • 删除cookie中的session
          • session_destory()：销毁session
        • 基于url传递sessionid，设置url的参数为session_name，session_start()后，会自动寻找该参数
        • 常量SID，当用户关闭cookie时，该常量表示session_name和session_id；当用户开启cookie时，该常量为空
        • 设置php.ini中的session.use_trans_sid=1，会使页面跳转（超链接、header、表单）后面自动添加SID
      • session高级技术
        • php.ini中，session的设置
          • session_name：设置存在cookie以及SID中的session_name
          • session.use_trans_sid：设置SID是否开启，开启后，可自动添加SID
          • session.save_path：设置session文件的保存位置，如果不设置，则不生成session文件
          • session.gc_maxlifetime：设置session文件有效时间，超过该时间session未刷新，session文件将失效
          • session.gc_probability和session.gc_divisor结合使用，定义session垃圾回收概率，算法为session.gc_probability/session.gc_divisor
          • session.use_cookie：设置session写入到cookie中
          • session.cookie_path：设置哪些文件的session写入到cookie中
          • session.cookie_lifetime：设置session的生命周期
          • session.save_handler：设置session写入方式及位置，当值为user时，可使用session_set_save_handler()函数
        • session_set_save_handler(open(),close(),read(),write(),destroy(),gc())：可自定义session文件的存储路径及存储方式等
          • 使用该函数定义了各个方法，像往常一样使用session
          • open()：在执行session_start()时，被调用
          • close()：在执行session_write_close()时，被调用
          • read()：在调用open()后，被调用
          • write()：脚本结束时和session_write_close()执行时，被调用
          • destroy()：当session使用session_destroy()或者session_regenerate_id()被销毁时，被调用
          • gc()：由session.gc_probability和session.gc_divisor决定，任何时候军可能被调用 
          • 具体用法
          • 将Session写入数据库
          • 将Session写入Memcache

      至此，PHP的基础学习算是完成了，需要多做多学，方能提高！

      http://www.bkjia.com/PHPjc/980026.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/980026.htmlTechArticlePHP笔记（PHP高级篇），php笔记 高级篇中将涉及数据库的使用以及Cookie和Session会话，提高PHP的开发效率和运行效率 PHP程序员需要掌握的MyS...