Common MySQL methods for building PHP environments and using PHP in MacOS _ MySQL

MAMP is an integrated environment package for Apache + MySQL + PHP of MacOS, this article briefly describes how to set up the PHP environment in MacOS and how to operate MySQL in PHP. Mac local environment setup
In Mac, we can use MAMP Pro (official website: https://www.mamp.info/en/) software to build local servers. After this software is installed, the website directory is in the/Applications/MAMP/htdocs folder. you only need to put the file in this folder, and you can use http: // localhost: 8888, or click the following red underline button to quickly access the site.

Install php in mac.

brew tap josegonzalez/homebrew-phpbrew install php54

After the installation is complete, you can use phpstorm for pleasant programming. The php installation path is in/usr/local/bin/php.

Basic database operations

1) the user's Web browser sends an HTTP request to request a specific Web page.
2) the Web server receives a. php request to obtain the file and transmits it to the PHP engine for processing. 3) The PHP engine starts parsing scripts. The script contains a database connection command and a query command. Life
PHP opens the connection to the MYSQL database and sends the appropriate query.
4) the MYSQL server receives and processes database queries. Return the result to the PHP engine.
5) PHP uses where you are going to run the script. Generally, this includes formatting the query result into HTML format. However
And then output HTML to the Web server.
6) the Web server sends HTML to the browser.
Common MySQL data types
Integer: TINYINT, SMALLINT, INT, BIGINT
Float: floa t, doub le, DECIMAL (M, D)
Character type: CHAR, VARCHAR
Date type: da tetime, da te, TIMESTA MP
Remarks: TINYTEXT, TEXT, LONGTEXT
MySQL database operations
1) display the existing database

  >SHOWDATABASES; 

2) select the database you need

  >USEguest; 

3) view the selected database

  >SELECTDATABASE(); 

4) view all the contents of a table

> SELECT * FROMguest; // you can use SHOWTABLES; to check the number of tables.

5) set Chinese encoding based on the database

  >SET NAMESgbk; //set names utf8; 

6) create a database

  >CREATEDATABASEbook; 

7) create a table in the database

> CREATETABLEusers (> username VARCHAR (20), // The not null setting cannot be blank> sex CHAR (1),> birth DATETIME );

8) display the table structure

  >DESCIRBEusers;

9) insert a data entry to the table

  >INSERT INTO users (username,sex,birth) VALUES('jack','male',NOW());

PHP connection to MySQL database
Connect to database

<? Php header ('content-Type: text/html; charset = utf-8 '); // sets the page encoding. if the file is gbk encoded, then charset also applies gbk // @ to indicate that if an error occurs, do not report an error. ignore the // parameter: server address, user name, and password echo (!! @ Mysql_connect ('localhost', 'root', '*****'); // 1?>

We use a double exclamation point !! To convert the resource handle to a Boolean value. The correct output is 1, and the error message is output. If the @ symbol is added, the error message is ignored and no error message is output.

For error message processing, we can use the mysql_error () function to output the error message:

Mysql_connect ('localhost', 'root', '*****') or die ('database connection failed, error message :'. mysql_error (); // password error prompt: database connection failed, error message: Access denied for user 'root' @ 'localhost' (using password: YES)
The die () function outputs a message and exits the current script. This is the alias of the exit () function.

Database connection parameters can be stored using constants, so that they cannot be modified at will, making it safer.

 <? Php // define the constant parameter define ('Db _ host', 'localhost'); define ('Db _ user', 'root'); define ('Db _ pwd ', '200'); // password $ connect = mysql_connect (DB_HOST, DB_USER, DB_PWD) or die ('database connection failed, error message :'. mysql_error (); echo $ connect; // Resource id #2?>

It is worth noting that constants in mysql_connect () brackets cannot be enclosed in quotation marks, otherwise errors will certainly occur.

Select the specified database

<? Php define ('Db _ host', 'localhost'); define ('Db _ user', 'root'); define ('Db _ pwd', '123 '); // password define ('Db _ name', 'triggkit '); // create a database named trigkit in phpmyadmin // connect to the database $ connect = mysql_connect (DB_HOST, DB_USER, DB_PWD) or die ('database connection failed, error message :'. mysql_error (); // select the specified database mysql_select_db (DB_NAME, $ connect) or die ('database connection error, error message :'. mysql_error (); // The error message indicating a table name error: database connection error, error message: Unknown database 'trigger'?>

Mysql_close () is usually not required, because the opened non-persistent connection will be automatically closed after the script is executed.

Mysql_select_db (database, connection): Select MySQL database
Retrieve record set

 <? Php define ('Db _ host', 'localhost'); define ('Db _ user', 'root'); define ('Db _ pwd', '123 '); // password define ('Db _ name', 'trigger'); // connect to the database $ connect = mysql_connect (DB_HOST, DB_USER, DB_PWD) or die ('database connection failed, error message :'. mysql_error (); // select the specified database mysql_select_db (DB_NAME, $ connect) or die ('data table connection error, error message :'. mysql_error (); // extract the table data FROM the database (obtain the record set) $ query = "SELECT * FROM class "; // create a new 'table '$ result = mysql_query ($ qu Ery) or die ('SQL error, error message :'. mysql_error (); // write the Table name incorrectly: SQL error, error message: Table 'triggkit. clas 'doesn' t exist?>

The mysql_query () function executes a MySQL Query.

Output data

 <? Php define ('Db _ host', 'localhost'); define ('Db _ user', 'root'); define ('Db _ pwd', '123 '); // password define ('Db _ name', 'trigger'); // connect to the database $ connect = mysql_connect (DB_HOST, DB_USER, DB_PWD) or die ('database connection failed, error message :'. mysql_error (); // select the specified database and set the character set mysql_select_db (DB_NAME, $ connect) or die ('data table connection error, error message :'. mysql_error (); mysql_query ('set NAMES utf8') or die ('character SET setting error '. mysql_error (); // extract the table data from the database (obtain the record set) $ query = "SELECT * FROM class"; $ result = mysql_query ($ query) or die ('SQL error, error message :'. mysql_error (); print_r (mysql_fetch_array ($ result, MYSQL_ASSOC);?>

Release result set Resources (you only need to call it when considering how much memory will be occupied when a large result set is returned .)

<?php  mysql_free_result($result); ?>

Add, delete, modify, and query
Add data

<? Php require 'index. php '; // add data $ query = "insert into class (name, email, point, regdate) VALUES ('xiaoming', 'xiaoming @ 163.com ', 100, NOW () "; @ mysql_query ($ query) or die ('add error :'. mysql_error ();?>

Save the above code as index. php and drop it into the/Applications/MAMP/htdocs/folder. Save the above code as demo. php and put it in the same directory. The path for obtaining files in Mac is very simple. you only need to pull the files into the terminal to display the path name.

Modify data
Assume that the name of the data to be modified is James and id is 2. change the point score to 80. the code is as follows:

<? Php require 'index. php'; // modify data $ query = 'update class SET point = 80 WHERE id = 2'; @ mysql_query ($ query);?>

Delete data

<? Php require 'index. php'; // DELETE data $ query = "delete from class WHERE id = 2"; @ mysql_query ($ query); mysql_close ();?>

Show data

<? Php require 'index. php '; // Display Data $ query = "SELECT id, name, email, regdate FROM class"; $ result = mysql_query ($ query) or die (' SQL statement error: '. mysql_error (); print_r (mysql_fetch_array ($ result); mysql_close ();?>

Or display the specified value data:

$ Data = mysql_fetch_array ($ result); echo $ data ['email ']; // Display emailecho $ data ['name']; // display name

Other common functions
Mysql_fetch_lengths (): Gets the length of each output in the result set.
Mysql_field_name (): obtains the field name of the specified field in the result.

Mysql _ fetch_row (): obtains a row from the result set as an enumeration array.
Mysql_fetch_assoc (): obtains a row from the result set as the associated array.
Mysql_fetch_array (): Gets a row from the result set as an associated array, or an array of numbers, or both.

Mysql_num_rows (): returns the number of rows in the result set.
Mysql_num_fields (): number of fields in the result set.

Mysql_get_client_info (): obtain MySQL client information
Mysql_get_host_info (): obtain MySQL host information
Mysql_get_proto_info (): obtain MySQL protocol information
Mysql_get_server_info (): obtain MySQL server information