Dabbler Rookie Learn Web development-PHP Learning 5-Database

Database 1 steps for database operations

1. Connecting to a database Mysqli_connect
2. Select Database mysqli_select_db
3. Set file Encoding Mysqli_query ("set name Utf-8")
4. Send database operation instructions such as Query mysql_query ("SELECT * from user");
5. Receive return results this many, self-check manuals
6. Release resources, close Database Mysqli_free_result () mysql_close ();

2 connection to the database

It is particularly important to note that PHP7.0 does not support the mysql_connect () function , and instead is mysqli_connect or PDO.

$link = mysqli_connect(‘127.0.0.1‘, ‘code1‘, ‘‘) or die(‘数据库连接失败‘);mysqli_select_db(‘code1‘);mysqli_query("set names ‘utf8‘");$result = mysqli_query(‘select * from user limit 1‘);$row = mysqli_fetch_assoc($result);print_r($row);

The corresponding MySQL connection operation is
mysql -h localhost -u code1 -p

3 Selecting a Database

When the connection succeeds, we need to select an operational database and select the database through the mysql_select_db function.
mysqli_select_db(‘code1‘);
Usually we will first set up the current connection using the character encoding, in general we will use UTF8 encoding.
mysqli_query("set names ‘utf8‘");

4 Executing query instructions

Execute mysqli_query function to send query statement first
Then execute the mysqli_fetch_arry function to retrieve the query data

$res = mysqli_query(‘select * from user limit 1‘);$row = mysqli_fetch_array($res);var_dump($row);

5 Inserting new data

Insert data is used mysqli_query to execute MySQL INSERT statement, in MySQL, after executing the INSERT statement, you can get the self-increment of the primary key ID, through the PHP mysql_insert_id function can get the ID.

$name = ‘李四‘;$age = 18;$class = ‘高三一班‘;$sql = "insert into user(name, age, class) values(‘$name‘, ‘$age‘, ‘$class‘)";mysql_query($sql); //执行插入语句$uid = mysql_insert_id();echo $uid;

6 Retrieving data

The most common thing to retrieve data is mysqli_fetch_array (retrieving data is usually retrieving one row of data)
Used mysqli_fetch_row to get a numeric index array
Used mysqli_fetch_assoc to get an associative index array

$row = mysql_fetch_row($result);$row = mysql_fetch_array($result, MYSQL_NUM); //这两个方法获取的数据是一样的$row = mysql_fetch_assoc($result);$row = mysql_fetch_array($result, MYSQL_ASSOC);

Use loop traversal to get all the numbers

$data = array();while ($row = mysql_fetch_array($result)) {    $data[] = $row;}

7 Querying paging data

We use pagination to display data, showing 10 to 20 data per page

mysqllimitcan implement paging, which limit m,n means fetching n rows of data from M rows,

Assuming the current page is $page , each page shows the $n bar data, and M is all the data in front of the current page, i.e.$m = ($page - 1) * $n

$page = 2;$pagesize = 2;//在这里构建分页查询$offset = ($page - 1) * $pagesize;$sql = "select * from user limit $offset,$pagesize";//获取翻页数据$result = mysql_query($sql);$data = array();while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {    $data[] = $row;}print_r($data);

8 Delete and update operations

This only needs to be done via MySQL's update and delete.

Update action

$sql = "update user set name = ‘曹操‘ where id=2 limit 1";if (mysql_query($sql)) {    echo ‘更新成功‘;}

Delete operation

$sql = "delete from user where id=2 limit 1";if (mysql_query($sql)) {    echo ‘删除成功‘;}

Mysql_affected_rows function to get the number of updated rows of data

echo ‘数据更新行数：‘.mysql_affected_rows();

9 Closing the database

mysql_close();

Or

$link = mysql_connect($host, $user, $pass);mysql_close($link);

Dabbler Novice Web Development-PHP Learning 5-Database