Sharing how to operate mysql database with PHP under Mac environment_PHP tutorial

Sharing how to use PHP to operate mysql database in Mac environment

Today we have set up a PHP environment on mac, and we will share with you how to use PHP to operate mysql database. Please refer to it if you need it.

Setting up Mac local environment

On Mac system, we can use MAMP Pro software to build a local server. After installing this software, the directory of the website is in the /Applications/MAMP/htdocs folder. Just put the file into the folder and you can access it through http://localhost:8888, or click on the red color below Underline buttons for quick site access.

To install php under mac system, just two lines.

　?

1 2 brew tap josegonzalez/homebrew-php brew install php54 1 2

brew tap josegonzalez/homebrew-php brew install php54

After installation and configuration, you can use phpstorm to program happily. The installed php path is /usr/local/bin/php

Basic database operations

　1) The user's web browser issues an HTTP request to request a specific web page.

　2) The web server receives the .php request to obtain the file, and passes it to the PHP engine, asking it to process it. 3) The PHP engine starts parsing the script. The script contains a command to connect to the database and a command to execute a query. Life

PHP opens a connection to the MYSQL database and sends the appropriate query.

　4) The MYSQL server receives database queries and processes them. Return results to the PHP engine.

5) PHP runs the script as you go. Usually, this includes formatting the query results into HTML format. Ran

Then output the HTML and return it to the web server.

　6) The web server sends HTML to the browser.

　MySQL common data types

Integer type: TINYINT, SMALLINT, INT, BIGINT

Floating point type: FLOA T, DOUB LE, DECIMAL (M, D)

Character type: CHAR, VARCHAR

Date type: DA TETIME, DA TE, TIMESTA MP

Remark type: TINYTEXT, TEXT, LONGTEXT

　MySQL database operation

　1) Display the currently existing database

　>SHOWDATABASES;

　2) Select the database you need

　>USEguest;

　3) View the currently selected database

　>SELECTDATABASE();

4) View all contents of a table

　>SELECT*FROMguest; //You can first check how many tables there are through SHOWTABLES;

　5) Set Chinese encoding according to the database

　>SET NAMESgbk; //set names utf8;

　6) Create a database

　>CREATEDATABASEbook;

　7) Create a table in the database

　>CREATETABLEusers (

　>username VARCHAR(20),//NOT NULL setting is not allowed to be empty

　>sex CHAR(1),

　>birth DATETIME);

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

8) Display the structure of the table

　>DESCIRBEusers;

9) Insert a piece of data into the table

1 2 3 4 5 6 7

header('COntent-Type:text/html;charset=utf-8');//设置页面编码，如果文件是gbk编码，则charset也应用gbk //@表示如果出错了，不要报错，直接忽略 //参数：服务器地址，用户名和密码 echo (!!@mysql_connect('localhost','root','*****'));//1 ?>

　?

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

PHP connects to MySQL database Connect to database 　?

1 2 3 4 5 6 7

<🎜>header('COntent-Type:text/html;charset=utf-8');//Set the page encoding. If the file is gbk encoded, the charset also applies to gbk<🎜> <🎜>//@ means if something goes wrong, don’t report an error, just ignore it<🎜> <🎜>//Parameters: server address, username and password<🎜> <🎜> <🎜> <🎜>echo (!!@mysql_connect('localhost','root','*****'));//1<🎜> <🎜>?>

We use double exclamation marks!! to convert the resource handle into a Boolean value, and output 1 if it is correct, and an error message if it is incorrect. And if the @ symbol is added in front,

will ignore the error message and will not output the error message.

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());//Prompt for wrong password:

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 function is an alias for the exit() function.

Database connection parameters can be stored as constants, so they cannot be modified at will and are safer.

　?

1 2 3 4 5 6 7 8 9 //定义常量参数 define('DB_HOST','localhost'); define('DB_USER','root'); define('DB_PWD','345823');//密码 $connect = mysql_connect(DB_HOST,DB_USER,DB_PWD) or die('数据库连接失败，错误信息：'.mysql_error()); echo $connect;//Resource id #2 ?> 1 2 3 4 1 2 3 4 5 6 7 8 9 10 define('DB_HOST','localhost'); define('DB_USER','root'); define('DB_PWD','345823');//密码 define('DB_NAME','trigkit');//在phpmyadmin创建一个名为trigkit的数据库 //连接数据库 $connect = mysql_connect(DB_HOST,DB_USER,DB_PWD) or die('数据库连接失败，错误信息：'.mysql_error()); //选择指定数据库 mysql_select_db(DB_NAME,$connect) or die('数据库连接错误，错误信息：'.mysql_error());//将表名字故意写错， 提示的错误信息：数据库连接错误，错误信息：Unknown database 'trigkt' ?> 5 6 7 8 1 2 3 4 5 6 7 8 9 10 11 12 13 14 define('DB_HOST','localhost'); define('DB_USER','root'); define('DB_PWD','345823');//密码 define('DB_NAME','trigkit'); //连接数据库 $connect = mysql_connect(DB_HOST,DB_USER,DB_PWD) or die('数据库连接失败，错误信息：'.mysql_error()); //选择指定数据库 mysql_select_db(DB_NAME,$connect) or die('数据表连接错误，错误信息：'.mysql_error()); //从数据库里把表的数据提出来（获取记录集） $query = "SELECT * FROM class";//在trigkit数据库中新建一张'表' $result = mysql_query($query) or die('SQL错误，错误信息：'.mysql_error());//故意将表名写错：SQL错误，错误信息：Table 'trigkit.clas' doesn't exist ?> 9

<🎜>//Define constant parameters<🎜> <🎜>define('DB_HOST','localhost');<🎜> <🎜>define('DB_USER','root');<🎜> <🎜>define('DB_PWD','345823');//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 the constants in the brackets of mysql_connect() cannot be quoted, otherwise an error will occur. Select the specified database 　?

1 2 3 4 5 6 7 8 9 10

<🎜>define('DB_HOST','localhost');<🎜> <🎜>define('DB_USER','root');<🎜> <🎜>define('DB_PWD','345823');//Password<🎜> <🎜>define('DB_NAME','trigkit');//Create a database named trigkit in phpmyadmin<🎜> <🎜>//Connect to 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());//Write the table name deliberately wrong, <🎜> <🎜>Error message prompted: Database connection error, error message: Unknown database 'trigkt'<🎜> <🎜>?>

Usually there is no need to use mysql_close() because the opened non-persistent connection will be automatically closed after the script is executed mysql_select_db(database,connection): Select MySQL database Get the record set 　?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<🎜>define('DB_HOST','localhost');<🎜> <🎜>define('DB_USER','root');<🎜> <🎜>define('DB_PWD','345823');//Password<🎜> <🎜>define('DB_NAME','trigkit');<🎜> <🎜>//Connect to 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 (get the record set) <🎜> <🎜>$query = "SELECT * FROM class";//Create a new 'table' in the trigkit database<🎜> <🎜>$result = mysql_query($query) or die('SQL error, error message: '.mysql_error());//Intentional wrong table name: SQL error, error message: Table 'trigkit.clas' doesn't 't exist<🎜> <🎜>?>

The mysql_query() function executes a MySQL query.

Output data

　?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 define('DB_HOST','localhost'); define('DB_USER','root'); define('DB_PWD','345823');//密码 define('DB_NAME','trigkit'); //连接数据库 $connect = mysql_connect(DB_HOST,DB_USER,DB_PWD) or die('数据库连接失败，错误信息：'.mysql_error()); //选择指定数据库，设置字符集 mysql_select_db(DB_NAME,$connect) or die('数据表连接错误，错误信息：'.mysql_error()); mysql_query('SET NAMES UTF8') or die('字符集设置出错'.mysql_error()); //从数据库里把表的数据提出来（获取记录集） $query = "SELECT * FROM class"; $result = mysql_query($query) or die('SQL错误，错误信息：'.mysql_error()); print_r(mysql_fetch_array($result,MYSQL_ASSOC)); ?> 1 2 3 1 2 3 mysql_free_result($result); ?> 4 5 6 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 require 'index.php'; //新增数据 $query = "INSERT INTO CLASS( name, email, point, regdate) VALUES ( '小明', 'xiaoming@163.com', 100, NOW() )"; @mysql_query($query) or die('新增错误：'.mysql_error()); ?> 7 8 9 10 11 12 1 2 3 4 5 6 7 require 'index.php'; //修改数据 $query = 'UPDATE class SET point=80 WHERE id=2'; @mysql_query($query); ?> 13 14 15 16

<🎜>define('DB_HOST','localhost');<🎜> <🎜>define('DB_USER','root');<🎜> <🎜>define('DB_PWD','345823');//Password<🎜> <🎜>define('DB_NAME','trigkit');<🎜> <🎜>//Connect to 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 (get 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 the result set resources (only need to be called when considering how much memory will be occupied when returning a large result set.) 　?

1 2 3

<🎜>mysql_free_result($result);<🎜> <🎜>?>

Add, delete, modify and check New data 　?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

<🎜>require 'index.php';<🎜> <🎜>//New data<🎜> <🎜>$query = "INSERT INTO CLASS(<🎜> <🎜>name,<🎜> <🎜>email,<🎜> <🎜>point,<🎜> <🎜>regdate)<🎜> <🎜>VALUES (<🎜> <🎜>'Xiao Ming',<🎜> <🎜>'xiaoming@163.com',<🎜> <🎜>100,<🎜> <🎜>NOW()<🎜> <🎜>)";<🎜> <🎜> <🎜> <🎜>@mysql_query($query) or die('New error:'.mysql_error());<🎜> <🎜> <🎜> <🎜>?>

We save the above code as index.php and throw it into the /Applications/MAMP/htdocs/ folder. Save the above code as demo.php, into the same directory. It is very simple for Mac system to obtain the path of a file. Just pull the file into the terminal and the path name will be displayed. Modify data We assume that the name of the data to be modified is Xiao Ming, the id is 2, and his point score is modified to 80 points. The code is as follows: 　?

1 2 3 4 5 6 7

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

Delete data

　?

1 2 3 4 5 6 7 8 9 require 'index.php'; //删除数据 $query = "DELETE FROM class WHERE id=2"; @mysql_query($query); mysql_close(); ?> 1 2 3 1 2 3 4 5 6 7 8 9 10 require 'index.php'; //显示数据 $query = "SELECT id,name,email,regdate FROM class"; $result = mysql_query($query) or die('sql语句错误：'.mysql_error()); print_r(mysql_fetch_array($result)); mysql_close(); ?> 4 5 1 2 3 $data = mysql_fetch_array($result); echo $data['email'];//显示email echo $data['name'];//显示name 6 7 8 9

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

Display data