Php and MySQL (basic operations) and phpmysql basic operations

Php and MySQL (basic operations) and phpmysql basic operations
PHP connection to MySQL

Before accessing the MySQL database, we need to first connect to the database server and connect to the server. We use the mysqli_connect () function.

Before using this function, Let's first look at the syntax of this function:

mysqli_connect(host,username,password,dbname,port,socket); 

• Parameter description

• Return Value

If the connection is successful, an object representing the connection to the MySQL server is returned.

Here, I useWampFor the integrated mysql database, we use the above method to connect to our database. (The user name is root by default, and the password is blank );

$ Conn = mysqli_connect ("localhost", "root", ""); if (! $ Conn) {die ("Connection failed:". mysqli_connect_error (); // if the Connection fails, output a message and exit the current script}

Create Database mysqli_query ()

In php, The mysqli_query () method is used to execute mysql statements. So before creating a database, let's take a look at the usage of mysqli_query:

mysqli_query(connection,query,resultmode); 

• Parameter description

• Return Value

For successful SELECT, SHOW, DESCRIBE, or EXPLAIN queries, A mysqli_result object is returned. For other successful queries, TRUE is returned. If it fails, FALSE is returned.

Create a database

The create database statement is used to CREATE a DATABASE. The statement must be executed using the mysqli_query () method to take effect. (Note: In php, all mysql statements need to be executed in this way to take effect, so we will not explain it again below)

Next we will create a database named test01 in our local database.

$conn = mysqli_connect('localhost','root','');$sql = "CREATE DATABASE test01";mysqli_query($conn,$sql);

After executing the preceding statement, we can use the show databases statement to check whether the database has been successfully created. Http://jingyan.baidu.com/article/335530daae2e6119cb41c335.html)

• Open cmd and enter mysql-u username-p and press enter to enter the password as prompted. At this time, you can go to the mysql Database Console. If you enter the prompt that mysql is not an internal or external command, you only need to find the mysql installation directory (bin directory) and copy it, configure the environment variable as the variable value.
• Enter the show databases statement. At this time, we can see that the database we just created test01 already exists, as shown in

Create a data table select a database

After the database is created, we will start to create a data table. Before creating a table, we must first select the database for creating the table. We will use the mysqli_select_db () method to select the database. Similarly, before using this method, let's take a look at the usage of this method:

mysqli_select_db(connection,dbname); 

Parameter description:

If the call succeeds, TRUE is returned. If the call fails, FALSE is returned. Now let's use this method and select the table we just created.

Mysqli_select_db ($ conn, 'test01'); // select a database

Create a data table

Use the create table statement to CREATE a TABLE. Next, use this statement to CREATE an admin TABLE.

$ SQL = "CREATE TABLE admin (id INT (6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR (20) NOT NULL, password CHAR (6) NOT NULL, email VARCHAR (50) not null) "; mysqli_query ($ conn, $ SQL); // create a database

• Not null-each row must contain a value (cannot be blank), and a null value is NOT allowed.
• UNSIGNED-use the UNSIGNED numeric type, 0 and positive number
• Auto increment-set the MySQL field value to automatically increase by 1 each time a new record is added
• Primary key-set the unique identifier of each record in the data table. The primary key of a column is usually set to the ID value, which is used with AUTO_INCREMENT.

Insert data

After creating a database and a table, we can add data to the table.

The insert into statement is usually used to add new records to the MySQL table:

INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

Instance:

$sql="INSERT INTO admin(username,password,email) VALUES('admin','123456','123456789@qq.com')";mysqli_query($conn,$sql);

After the execution, we can check whether a piece of data has just been created in the database. Right-click it and you can see that the data has been successfully created in our table.

Delete data

Delete from statement is used to DELETE records FROM database tables.

DELETE FROM table_name WHERE some_column = some_value

$sql="DELETE FROM admin WHERE username='admin'";mysqli_query($conn,$sql);

Update Data

The UPDATE statement is used to UPDATE existing records in the database table.

UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

Instance:

$sql="UPDATE admin SET email='309123793@qq.com'"; mysqli_query($conn,$sql);

Read data

The SELECT statement is used to read data from a data table:

SELECT column_name(s) FROM table_name

Instance:

 $sql = "SELECT id, username, email FROM admin"; mysqli_query($conn,$sql);

Order by keyword

 SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

Note: The default value is ascending. If you want to sort data in descending order, use the DESC keyword.

 $sql="SELECT * FROM admin ORDER BY username";

 mysqli_query($conn,$sql);