Home >Backend Development >PHP Tutorial >Sharing how to operate mysql database with PHP under Mac environment_PHP tutorial
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.
?
2
|
|
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()); |
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.
?
2 3 4
5 6 7 8
|
<🎜>//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<🎜> <🎜>?> |
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'<🎜> <🎜>?> |
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
?
2 3
4 5 6
8 9 10 11 12
|
<🎜>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));<🎜> <🎜>?> |
1 2 3 | <🎜>mysql_free_result($result);<🎜> <🎜>?> |
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());<🎜> <🎜> <🎜> <🎜>?> |
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
?
2 3
4 5
7 8 9
|
require 'index.php';
//Delete data $query = "DELETE FROM class WHERE id=2"; @mysql_query($query);
mysql_close(); ?> |