Php for mysql

When operating mysql in php, I first spit out mysqli and mysql. The former is the ultimate version of the latter. my ps does not support mysql-related functions, and I am reading the tutorials on w3school, the code on w3school uses the old version of mysql functions. I will go and let me use the mysql series functions one by one...

For versions above php5, mysqli and PDO are recommended. There's a website I just found: http://www.runoob.com

Mysqli and PDO have their own advantages. PDO is a common method used in 12 databases, while mysqli is dedicated to mysql. Both are object-oriented, while mysqli also improves API interfaces. Both support pre-processing statements. pre-processing statements can prevent SQL injection, which is very important for the security of web projects. Because I am dedicated to developing mysql projects, I just need to know about mysqli. For more information about PDO, see the above website.

The following describes how to operate a database using mysqli.

Make a small Directory:

1. connect to the database

2. close the connection

3. create a database

4. create a table

5. Insert data

6. Insert multiple data entries

7. pre-processing statement

8. read data

9. where

10. order

11. update

12. delete

13. ODBC

Connect to the database:

Mysqli (object-oriented)

 Connect_error) {die ("Connection failed:". $ conn-> connect_error);} echo "Connected successfully";?>

Note that $ connect_error is added in PHP 5.2.9 and 5.3.0 in the above object-oriented instance. If you need to be compatible with earlier versions, replace the following code: // Check the connection if (mysqli_connect_error () {die ("Database connection failed:". mysqli_connect_error ());}

Mysqli (process-oriented)

Close connection:

Mysqli (object-oriented)

$conn->close();

Mysqli (process-oriented)

mysqli_close($conn); 

Create a database:

The create database statement is used to CREATE a DATABASE:

Mysqli object-oriented: tdb0.

 "; // Create database $ SQL =" create database tdb0 "; if ($ conn-> query ($ SQL) === TRUE) {echo" Database created successfully ";} else {echo "Error creating database :". $ conn-> error;} $ conn-> close ();?>

Note: When creating a new database, you must specify three parameters (servername, username, and password) for the mysqli object ). Tip: If you use another port (3306 by default), add an empty string to the database parameter, for example, new mysqli ("localhost", "username", "password ","", port)

Mysqli process-oriented:

Create a table:

The create table statement is used to CREATE a MySQL TABLE.

Mysqli object-oriented:

 Connect_error) {die ("Connection failed :". $ conn-> connect_error);} // SQL to create table $ SQL = "CREATE TABLE MyGuests (id INT (6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR (30) NOT NULL, lastname VARCHAR (30) not null, email VARCHAR (50), reg_date TIMESTAMP) "; if ($ conn-> query ($ SQL) === TRUE) {echo "Table MyGuests created successfully";} else {echo "Error creating table :". $ conn-> error;} $ Conn-> close ();?>

Mysqli process-oriented:

Note:

The data type specifies the type of data that a column can store. For complete data types, see our data types reference manual.

After setting the data type, you can specify the attributes of other options for each column:

• Not null-no row must contain values (cannot be blank), and null values are NOT allowed.
• DEFAULT value-set the DEFAULT value
• 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.

Each table should have a primary key (this column is "id"), and the primary key must contain a unique value.

Insert data:

The following are some syntax rules:

• The SQL query statement in PHP must use quotation marks.
• The string value in the SQL query statement must be enclosed by quotation marks.
• No quotation marks are required for the value of a value.
• NULL values do not require quotation marks

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,...) 

Note: If the column is set to AUTO_INCREMENT (such as the "id" column) or TIMESTAMP (such as the "reg_date" column), we do not need to specify the value in the SQL query statement; mySQL automatically adds a value for this column.

Mysqli object-oriented:

 Connect_error) {die ("Connection failed :". $ conn-> connect_error);} $ SQL = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe ', 'John @ example.com ')"; if ($ conn-> query ($ SQL) === TRUE) {echo "New record created successfully" ;}else {echo "Error :". $ SQL."
". $ Conn-> error;} $ conn-> close ();?>

Mysqli process-oriented:

 ". Mysqli_error ($ conn);} mysqli_close ($ conn);?>

Insert multiple statements:

The mysqli_multi_query () function can be used to execute multiple SQL statements.

Mysqli object-oriented:

 Connect_error) {die ("Connection failed :". $ conn-> connect_error);} $ SQL = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe ', 'John @ example.com '); "; $ SQL. = "insert into MyGuests (firstname, lastname, email) VALUES ('Mary ', 'Moe', 'Mary @ example.com');"; $ SQL. = "insert into MyGuests (firstname, lastname, email) VALUES ('julil', 'doole', 'Julie @ example.com ')"; if ($ conn-> multi_query ($ SQL) === TRUE) {echo "New records created successfully" ;}else {echo "Error :". $ SQL."
". $ Conn-> error;} $ conn-> close ();?>

Mysqli process-oriented:

 ". Mysqli_error ($ conn);} mysqli_close ($ conn);?>

Preprocessing statements and binding parameters

The pre-processing statement is used to execute multiple identical SQL statements, and the execution efficiency is higher.

The working principle of the pre-processing statement is as follows:

1. Preprocessing: Create an SQL statement template and send it to the database. The reserved value uses the parameter "? "Tag. Example: insert into MyGuests (firstname, lastname, email) VALUES (?, ?, ?)
2. Database parsing, compilation, query optimization for SQL statement templates, and no output of stored results
3. Run: Finally, pass the value bound to the application to the parameter ("? "Mark), the database executes the statement. The application can execute statements multiple times if the parameter values are different.

Compared with directly executing SQL statements, preprocessing statements have two main advantages:

• The pre-processing statement greatly reduces the analysis time and only performs one query (although the statement is executed multiple times)
• Binding parameters reduces server bandwidth. you only need to send the query parameters instead of the entire statement.
• The pre-processing statement is very useful for SQL injection, because different protocols are used after the parameter values are sent, ensuring data legitimacy.

 Connect_error) {die ("Connection failed:". $ conn-> connect_error);} else {$ SQL = "INSERT INTO MyGuests VALUES (?, ?, ?) "; // Initialize the statement object for mysqli_stmt_prepare () $ stmt = mysqli_stmt_init ($ conn); // preprocessing statement if (mysqli_stmt_prepare ($ stmt, $ SQL )) {// bind the parameter mysqli_stmt_bind_param ($ stmt, 'SS', $ firstname, $ lastname, $ email); // set the parameter and execute $ firstname = 'John '; $ lastname = 'Doe '; $ email = 'John @ example.com'; mysqli_stmt_execute ($ stmt); $ firstname = 'Mary '; $ lastname = 'Moe '; $ email = 'Mary @ example.com '; mysqli_stmt_execute ($ Stmt); $ firstname = 'julil'; $ lastname = 'doole'; $ email = 'Julie @ example.com '; mysqli_stmt_execute ($ stmt) ;}}?>

 Connect_error) {die ("Connection failed :". $ conn-> connect_error);} // prepare and bind $ stmt = $ conn-> prepare ("insert into MyGuests (firstname, lastname, email) VALUES (?, ?, ?) "); $ Stmt-> bind_param (" sss ", $ firstname, $ lastname, $ email); // set the parameter and run $ firstname =" John "; $ lastname = "Doe"; $ email = "john@example.com"; $ stmt-> execute (); $ firstname = "Mary"; $ lastname = "Moe "; $ email = "mary@example.com"; $ stmt-> execute (); $ firstname = "Julie"; $ lastname = "Dooley"; $ email = "julie@example.com "; $ stmt-> execute (); echo "New records created successfully"; $ stmt-> close (); $ conn-> close ();?>

We can see that modularization is used in the above instances to solve the problem. We can create code blocks for easier reading and management.

Note the parameter binding. Let's take a look at the code in mysqli_stmt_bind_param:

Mysqli_stmt_bind_param ($ stmt, 'SS', $ firstname, $ lastname, $ email );

This function is bound to a parameter query and passed to the database. The second parameter is "sss ". The following lists the parameter types. The s character tells mysql that the parameter is a string.

This argument may be one of four types:

• I-integer
• D-double
• S-string
• B-BLOB

Each parameter must specify a type to ensure data security. You can determine the types to reduce the risks caused by SQL injection vulnerabilities.

Read data from MySQL database

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

SELECT column_name(s) FROM table_name 

Mysqli object-oriented:

 Connect_error) {die ("Connection failed :". $ conn-> connect_error);} $ SQL = "SELECT id, firstname, lastname FROM MyGuests"; $ result = $ conn-> query ($ SQL ); if ($ result-> num_rows> 0) {// output each row of data while ($ row = $ result-> fetch_assoc () {echo"
Id :". $ row ["id"]. "-Name :". $ row ["firstname"]. "". $ row ["lastname"] ;}} else {echo "0 results" ;}$ conn-> close () ;?>

Where clause

The WHERE clause is used to extract records that meet the specified criteria.

SELECT column_name(s)FROM table_nameWHERE column_name operator value 

 ";}?> 

Order By keyword

The order by keyword is used to sort the data in the record set.

The order by keyword sorts records in ascending order by default. The default value is ASC.

If you want to sort data in descending order, use the DESC keyword.

SELECT column_name(s)FROM table_nameORDER BY column_name(s) ASC|DESC 

 ";}mysqli_close($con);?> 

Sort by two columns

You can sort multiple columns. When sorting by multiple columns, the second column is used only when the values of the first column are the same:

SELECT column_name(s)FROM table_nameORDER BY column1, column2 

Update

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

UPDATE table_nameSET column1=value, column2=value2,...WHERE some_column=some_value 

Note: pay attention to the WHERE clause in the UPDATE syntax. The WHERE clause specifies which records need to be updated. If you want to save the WHERE clause, all records will be updated!

Delete

The delete from statement is used to DELETE records FROM database tables.

DELETE FROM table_nameWHERE some_column = some_value 

Note: pay attention to the WHERE clause in the DELETE syntax. The WHERE clause specifies which records need to be deleted. If you want to save the WHERE clause, all records will be deleted!

ODBC

ODBC is an Application Programming Interface (API) that enables us to connect to a data source (such as a MS Access database ).

Create an ODBC Connection

With an ODBC Connection, you can connect to any database on any computer in your network, as long as the ODBC connection is available.

This is how to create an ODBC Connection to the MS Access database:

1. Open in Control PanelManagement ToolsIcon.
2. Double-clickData source (ODBC)Icon.
3. SelectSystem DSNTab.
4. ClickAdd.
5. SelectMicrosoft Access Driver. ClickComplete.
6. On the next page, clickSelectTo locate the database.
7. Start a databaseData Source Name (DSN).
8. ClickOK.

Note that this configuration must be completed on the computer where your website is located. If your computer is running Internet Information Service (IIS), the preceding command takes effect. However, if your website is on a remote server, you must have physical access to the server, or ask your host provider to create a DSN for you.

Connect to ODBC

The odbc_connect () function is used to connect to the ODBC data source. This function has four parameters: data source name, user name, password, and optional pointer type.

The odbc_exec () function is used to execute SQL statements.

Instance

The following instance creates a connection to the DSN named northwind without the user name and password. Then create and execute an SQL statement:

$conn=odbc_connect('northwind','','');$sql="SELECT * FROM customers";$rs=odbc_exec($conn,$sql); 

Retrieve records

The odbc_fetch_row () function is used to return records from the result set. If the row can be returned, the function returns true; otherwise, false.

This function has two parameters: ODBC result identifier and optional row number:

odbc_fetch_row($rs) 

Retrieve fields from records

The odbc_result () function is used to read fields from records. This function has two parameters: ODBC result identifier and field number or name.

The following code line returns the value of the first field from the record:

$compname=odbc_result($rs,1); 

The following code line returns the value of the field "CompanyName:

$compname=odbc_result($rs,"CompanyName"); 

Close ODBC Connection

The odbc_close () function is used to close ODBC connections.

odbc_close($conn); 

ODBC instance

The following example shows how to create a database connection, create a result set, and then display data in the HTML table.

 ";echo "Companyname";echo "Contactname";while (odbc_fetch_row($rs)){$compname=odbc_result($rs,"CompanyName");$conname=odbc_result($rs,"ContactName");echo "$compname";echo "$conname";}odbc_close($conn);echo "";?>