Sharing how to operate mysql database with PHP under Mac environment, macmysql

Mac local environment setup

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.

brew tap josegonzalez/homebrew-php
brew install php54

Copy after login

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 the database query and processes it. Return results to the PHP engine.
5) PHP runs the script as you go. Typically, this involves formatting the query results into HTML format. Ran
Then output HTML back 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 operations

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);
8) Display the structure of the table
>DESCIRBEusers;

9) Insert a piece of data into the table

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

Copy after login

PHP connects to MySQL database

Connect to database

<&#63;php
  header('COntent-Type:text/html;charset=utf-8');//设置页面编码，如果文件是gbk编码，则charset也应用gbk
  //@表示如果出错了，不要报错，直接忽略
  //参数：服务器地址，用户名和密码

  echo (!!@mysql_connect('localhost','root','*****'));//1
&#63;>

Copy after login

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. If the @ symbol is added in front, the error message will be ignored and no error message will be 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()); // Tips 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.

<meta charset="utf-8">
<&#63;php
  //定义常量参数
  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 
&#63;>

Copy after login

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

<&#63;php
  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'
&#63;>

Copy after login

Normally 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 record set

<meta charset="utf-8">
<&#63;php
  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
&#63;>

Copy after login

The mysql_query() function executes a MySQL query.

Output data

<meta charset="utf-8">
<&#63;php
  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));
&#63;>

Copy after login

Release result set resources (only needs to be called when considering how much memory will be used when returning a large result set.)

<&#63;php
  mysql_free_result($result); 
&#63;>

Copy after login

Add, delete, modify and check

New data

<&#63;php
  require 'index.php';
  //新增数据
  $query = "INSERT INTO CLASS(
       name,
       email,
       point,
       regdate)
    VALUES (
    '小明',
    'xiaoming@163.com',
    100,
    NOW()
    )";

  @mysql_query($query) or die('新增错误：'.mysql_error());

&#63;>

Copy after login

We save the above code as index.php and throw it into the /Applications/MAMP/htdocs/ folder. Save the above code as demo.php and put it in 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:

<&#63;php
  require 'index.php';

  //修改数据
  $query = 'UPDATE class SET point=80 WHERE id=2';
  @mysql_query($query);
&#63;>

Copy after login

Delete data

<&#63;php
  require 'index.php';

  //删除数据
  $query = "DELETE FROM class WHERE id=2";
  @mysql_query($query);

  mysql_close();
&#63;>

Copy after login

Show data

<&#63;php
  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();
&#63;>

Copy after login

Or display specified value data:

$data = mysql_fetch_array($result);
echo $data['email'];//显示email
echo $data['name'];//显示name

Copy after login

Other commonly used functions

Copy code The code is as follows:
mysql_fetch_lengths(): Get the length of each output in the result set
mysql_field_name(): Get the field name of the specified field in the result

mysql _fetch_row(): Get a row from the result set as an enumeration array
mysql_fetch_assoc(): Get a row from the result set as an associative array
mysql_fetch_array(): Gets a row from the result set as an associative array, a numeric array, or both

mysql_num_rows(): Get the number of rows in the result set
mysql_num_fields(): Get the number of fields in the result set

mysql_get_client_info(): Get MySQL client information
mysql_get_host_info(): Get MySQL host information
mysql_get_proto_info(): Get MySQL protocol information
mysql_get_server_info(): Get MySQL server information

The above is the entire content of this article, I hope you all like it.