The method of PHP operating MySQL database in Mac environment sharing _php skills

MAC Local Environment Build

In the Mac system, we can use Mamp Pro software to build the local server. Install this software, the site directory in the/applications/mamp/htdocs folder, just put the file into the folder, you can access through the http://localhost:8888, or by clicking the following Red Underline button to quickly access the site.

Install PHP under the Mac system, two lines.

Brew tap josegonzalez/homebrew-php
Brew Install PHP54

After installation, you can use Phpstorm to program happily. Installed PHP path in/usr/local/bin/php

Database basic Operations

1 The user's Web browser sends an HTTP request requesting a specific Web page.
2 The Web server receives the. PHP request to get the file and upload it to the PHP engine and ask it to handle it. 3 The PHP engine begins parsing the script. There is a command to connect to the database in the script, and an order to execute a query. Life
PHP opens the connection to the MYSQL database and sends the appropriate query.
4 MYSQL server to receive database query and processing. Return the results to the PHP engine.
5 PHP with where you go to finish the script run, typically, this includes formatting the query results into HTML format. and
And 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
Memo Type: Tinytext,text,longtext
MySQL Database Operations

1 Displays the currently existing database
>SHOWDATABASES;
2 Select the database you need
>USEguest;
3 View the currently selected database
>selectdatabase ();
4 View all the contents of a table
>SELECT*FROMguest; You can see how many tables you have by Showtables first;
5 According to the database set Chinese code
>set NAMESGBK; Set names UTF8;
6) Create a database
>CREATEDATABASEbook;
7 Create a table in the database
>createtableusers (
>username VARCHAR (),//not null setting is not allowed to be null
>sex CHAR (1),
>birth DATETIME);
8 shows the structure of the table
>DESCIRBEusers;

9 Insert a data to the table

>insert into the Users (Username,sex,birth) VALUES (' Jack ', ' Male ', now ());

PHP connection MySQL Database

Connecting to a database

<?php
  Header (' content-type:text/html;charset=utf-8 ');//Set page encoding, if file is GBK encoding, CharSet also apply GBK//@
  indicates that if something goes wrong, Do not error, direct ignore
  //parameter: Server address, username and password

  echo (!!) @mysql_connect (' localhost ', ' root ', ' * * *]);//1
?> 

We use double exclamation!! To convert the resource handle to a Boolean value, correctly output 1, and error to output the error message. If the @ symbol is previously added, the error message is ignored and the error message is not printed.

For the processing of error messages, 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 ());//For password error: Database connection failed, error message: Access Denied for user ' root ' @ ' localhost ' (using Password:yes)
The Die () function prints a message and exits the current script. This function is an alias for the exit () function.

Database connection parameters, you can use constants to store, so you can not be arbitrarily modified, more secure.

<meta charset= "Utf-8" >
<?php
  //define constant parameter
  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 mysql_connect () brackets cannot be quoted, or there must be an error.

Select the specified database

<?php
  define (' db_host ', ' localhost ');
  Define (' Db_user ', ' root ');
  Define (' Db_pwd ', ' 345823 ');//password
  define (' db_name ', ' trigkit ');//Create a database
  //connection database named phpMyAdmin in Trigkit
  $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 ()); Error message: DB connection error, error message: Unknown database ' trigkt '
?> 

Typically, you do not need to use mysql_close (), because an open, non-persistent connection is automatically closed after the script has finished executing

mysql_select_db (database,connection): choose MySQL Database

Get recordset

<meta charset= "Utf-8" >
<?php
  define (' db_host ', ' localhost ');
  Define (' Db_user ', ' root ');
  Define (' Db_pwd ', ' 345823 ');//password
  define (' db_name ', ' Trigkit ');
  Connection 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 ());
  Put the data of the table in the database (get the recordset)
  $query = "SELECT * from Class";//Create a new ' table '
  $result = mysql_query ($query) or in the Trigkit database Die (' SQL error, error message: '. Mysql_error ())//intentionally incorrectly write table name: SQL error, error message: Table ' Trigkit.clas ' doesn ' t exist?>
 

The mysql_query () function executes a MySQL query.

Output data

<meta charset= "Utf-8" >
<?php
  define (' db_host ', ' localhost ');
  Define (' Db_user ', ' root ');
  Define (' Db_pwd ', ' 345823 ');//password
  define (' db_name ', ' Trigkit ');
  Connection database
  $connect = mysql_connect (db_host,db_user,db_pwd) or Die (' Database connection failed, error message: '. mysql_error ());
  Select the specified database, 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 setup error '. Mysql_error ());
  The data of the table is presented from the database (get the recordset)
  $query = "SELECT * from class";
  $result = mysql_query ($query) or Die (' SQL error, error message: '. mysql_error ());
  Print_r (Mysql_fetch_array ($result, Mysql_assoc));
? > 

Frees the result set resource (called only if you consider how much memory will be consumed when a large result set is returned). ）

<?php
  mysql_free_result ($result); 
? >

Check and delete and change

New data

<?php
  require ' index.php ';
  New data
  $query = "INSERT into CLASS (
       name,
       email, point
       ,
       regdate)
    VALUES (
    ' xiaoming ',
    ' Xiaoming@163.com ', M
    , 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 and put it in the same directory. The path to the Mac system to get the file is simple, just pull the file into the terminal to display the path name.

modifying data

We assume that the name of the data to be modified is xiaoming, with an ID of 2, which modifies his point score to 80 points, and 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 ();
? >

Display 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 email
echo $data [' name '];//display name

Other common functions

Copy Code code as follows:

Mysql_fetch_lengths (): Gets the length of each output in the result set
Mysql_field_name (): Gets the field name of the specified field in the result

MySQL _fetch_row (): Get one row from the result set as an enumeration array
MYSQL_FETCH_ASSOC (): Get one row from the result set as an associative array
Mysql_fetch_array (): Gets one row from the result set as an associative array, or an array of numbers, or both

Mysql_num_rows (): Number of rows in result set
Mysql_num_fields (): Gets 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 mentioned is the entire content of this article, I hope you can enjoy.