In this article, I try to use the simplest and easy-to-understand language to explain the basic techniques of using PHP and MySQL to make a dynamic website. Reading this article requires simple basic knowledge of HTML and basic knowledge of programming (in any programming language) (such as variables, values, loops, the concept of sentence blocks, etc.).
Alibaba Cloud Simple Application Server: Anti COVID-19 SME Enablement Program
$300 coupon package for all new SMEs and a $500 coupon for paying customers.
PHP basics
Overview
PHP is an interpreted language that can be used to preprocess web pages. The PHP script runs on
the server side, and the result of its operation is a web page that can be used for display. Although many similar tasks can be done, one big difference between JavaScript and PHP is that JavaScript runs on the browser side. In fact, the browser receives the JavaScript code and runs it, so the user can view the JavaScript code. However, PHP will not hand over the original code to the browser, but will only hand over the results of its operation to the browser, so it is safe and reliable to use PHP to handle issues such as user login and user permissions.
PHP and HTML
When actually writing, the usual way is to create a file with a php extension (webpage files are essentially text files). There is not much difference between writing php code and writing html code, and the most convenient place is that in a php file, the two codes can be mixed.
Rule: The php code needs to be included in the <?php ... ?> tag, like this:
<?php
// code goes here
?> Hint: This is a more vivid example of mixing php and html.
<?php
if ($var == true):
?>
<html id="ie6">
<?php
else:
?>
<html id="ie8">
<?php
endif;
?>
The meaning here is that if the value of the variable $var in php is true, one label is placed, otherwise another label is placed. PHP's if statement can be written as above or in C/C++ style:
<?php
if ($var == true) {
// do something
} else {
// do other things
}
?>
About operators in PHP
The operators used by PHP are similar to C/C++. For example, = means assignment, == means equality comparison, and <and> (less than, greater than) comparison operators,! Negation, && logical AND, || logical OR Wait. Of course, mathematical expressions such as +-*/ are also supported.
About variables in PHP
The naming of variables in PHP always starts with the symbol $ and underscores can be used. For example, $is_logged_in is a clearly ideographic variable name. Unlike most programming languages, variables in PHP have no concept of type and can be used directly without declaration. Although it's cool, it's easy to get confused when there are too many variables. This requires special attention.
About statements in PHP
In this regard, PHP is similar to many other common programming languages. You can also use if...else selection statements (I have seen it before). PHP also includes while loops, foreach loops, etc., which will be introduced in detail later.
MySQL basics
Using a MySQL database is a way to store data. MySQL needs to cooperate with PHP to complete queries to the database (here the term "query" includes write, update, read, etc.) operations. With MySQL, you can create many databases, each database can contain multiple tables, and each table contains several fields. In order to be efficient, it is generally used to maintain multiple tables by classification, rather than storing all data in the same table.
MySQL requires
server support. The first step is to create a database, you can use the corresponding graphical tool (such as phpMyAdmin) to create the database, or you can use the following SQL statement directly in the terminal to create a database named database_name:
CREATE DATABASE database_name;
After creating the database, you need to create a table. A table named table_name can be created by the following SQL statement:
USE database_name;
CREATE TABLE table_name (
first_name varchar(30),
last_name varchar(30),
);
The first sentence describes the database in which to add the table, and the second describes the details of the added table. Here we have added two fields to the table, called first_name and last_name, their types are varchar(30). Among them, varchar is a variable-length character type, and 30 represents the maximum length.
Other common data types are as follows:
VARCHAR(100) - variable character
CHARACTER(1) --fixed length
INTEGER --Integer
DECIMAL(10, 2) - decimal (digits before and after the decimal point)
TIMESTAMP --Date and time
DATE --Date
As you may have seen, the comment character in MySQL is --. You may find it useless, but it is the key to an attack that will be mentioned later. In addition, the general string should use the variable-length VARCHAR type instead of the fixed-length CHARACTER type, because the latter will take up more space, which is unnecessary.
Make PHP and MySQL work together
The first way
Now you have created the SQL data table and have an overview of the PHP language. Let's go straight to the topic and learn how to query the data table.
In order to enable PHP and MySQL to interact, you need to provide PHP with your database user name, password, database name, and data table name. Of course, the most important thing is the SQL statement for query operations. Let's observe how it is achieved one by one.
<?php
define('DB_HOST','localhost');
define('DB_USER','renfei');
define('DB_PASS','root');
define('DB_NAME','database_name');
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
$query = "INSERT INTO table_name (column1, column2) VALUES ('value1','value2')";
mysqli_query($dbc, $query);
?> Let's explain the working principle of this code.
First of all, lines 3 to 6 are defined in PHP. The effect is obvious. DB_HOST is defined as localhost. You can use DB_HOST instead of localhost in the following code. The advantage of this is that if the mysqli_connect function appears multiple times in the code, you only need to modify the define statement when modifying the parameters, which is very convenient.
Then there is a function called mysqli_connect(), which requires four variables, namely host name, user name, password, and database name. The return value of this function is passed to the variable $dbc, which contains a database connection. Note that this variable name is arbitrary and it is not mandatory to be called $dbc.
Then, we assign the SQL statement corresponding to the operation to be performed on the database to the variable $query in the form of a string. The variable name is also arbitrary. It should be noted that the SQL statement here does not end with a semicolon.
Finally, we execute the mysqli_query(); function, which has two parameters, a database connection and an SQL query operation. After executing this function, the corresponding query operation is executed.
If you save these codes as a web page, when the user opens the web page, if the parameters are correct, it will run completely.
The meaning of the SQL statement here is to insert a row into the table called table_name, in which the value of the colume# field is set to value# accordingly. Only the values of two fields are set here (there can be other fields in the table; fields that are not explicitly stated are left blank or use the default values specified by the data table). The general form of this statement is:
INSERT INTO table_name (column1, column2, ...) VALUES ('value1','value2', ...)
If all you have to do is execute a SQL statement, then you can use this mode. As a reminder, the $dbc variable is often reused.
Another commonly used SQL statement is to modify a row. Its form is:
UPDATE table_name SET
column1 ='preferred_value1',
column2 ='preferred_value2',
...,
WHERE id ='$id'
Of course, this sentence should be written on one line, but I will write it separately for clarity. Its meaning is to modify all rows in the table named table_name where the value of the field id is the value of the variable $id, set the value of the column1 field to preferred_value1, and set the value of the column2 field to preferred_value2, and so on. Here we also see that the value can be represented by a constant or a variable.
Note: All rows that meet the conditions defined by the WHERE clause will be modified (if the WHERE clause is omitted, all rows will be modified). The WHERE clause can set multiple conditions, and can also use comparison operators. E.g:
WHERE age> 20 AND gender ='male'
WHERE is_admin ='true' OR id ='$id'
(If you want to ask why AND and OR are not the symbols && and ||, I would like to remind you not to confuse the PHP language with the SQL language. This is the SQL language, and I only talked about the PHP language and C/C++. similar).
