Basic skills in PHP+MariaDB database operation

This article mainly introduces the basic skills of PHP MariaDB database operation. It summarizes and analyzes PHP MariaDB database connection, judgment, and related operation implementation skills and precautions based on PHP MariaDB such as user login, management, and deletion based on PHP MariaDB. It is necessary to Friends can refer to

The examples in this article summarize the basic skills of PHP MariaDB database operation. I share it with you for your reference. The details are as follows:

PHP MySQL is a relatively common combination. Since I subjectively don’t like Oracle very much, and after MySQL was acquired by Oracle, some changes have occurred in my bones, so I changed it. MariaDB, a brother who still adheres to MySQL's original open source belief. They are essentially the core of MySQL, so all the following database operation codes can be used directly in MySQL.

After setting up the basic environment of PHP Apache and installing the MySQL database at night, I wrote the simplest database connection code, and the result was the following classic error: Fatal error: Class 'mysqli' not found

<?php
  $db = new mysqli(&#39;localhost&#39;, &#39;root&#39;, &#39;admin&#39;, &#39;test&#39;);
  if (mysqli_connect_errno())
  {
    echo &#39;<p>&#39; . &#39;Connect DB error&#39;;
    exit;
  }
?>

In fact, this problem is relatively simple. From the error returned by the PHP engine, we know that mysqli has not been loaded correctly. Most of the problems are This happened in the configuration of the php.ini file. The default semicolon in front of the configuration item "extension=php_mysqli.dll" was not removed. I didn't make this mistake. There is also the file php_mysqli.dll in the ext directory in the PHP installation path. So where did the problem occur? The problem should still occur in the wrong place in the php.ini file. After some reading, I found that "extension_dir = "ext"" has not been modified. I didn't think much about it at the time, thinking that the PHP engine could automatically find this relative path. But then I thought about it, the PHP engine is loaded by Apache, and Apache does not know this relative relationship. Or honestly change this place to an absolute path, and it's OK. In fact, you can write this piece of code before this code to see if the mysqli component has been loaded. This method is suitable for pre-loading judgment of other components.

if (extension_loaded('mysqli'))
{
  echo 'yes';
}
else
{
  echo 'no';
}

The following uses a user registration and system login to record the most basic operation method of PHP MySQL.

1. Create database, tables and users.

DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`
USE `test`;
DROP TABLE IF EXISTS `tbl_user`;
CREATE TABLE `tbl_user` (
 `username` varchar(32) NOT NULL default '',
 `password` varchar(32) NOT NULL default '',
 PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

2. Create registration and login html pages, which are register.html and login.html respectively. As shown in the figure below:

3. Registration and login code:

register_do.php

<?php
  $username = $_POST[&#39;username&#39;];
  $password = $_POST[&#39;password&#39;];
  $db = new mysqli(&#39;localhost&#39;, &#39;root&#39;, &#39;admin&#39;, &#39;test&#39;);
  if (mysqli_connect_errno())
  {
    echo &#39;<p>&#39; . &#39;Connect DB error&#39;;
    exit;
  }
  $query = "select * from tbl_user where username = &#39;" . $username . "&#39;";
  echo &#39;<p>&#39; . $query;
  $result = $db->query($query);
  if ($result)
  {
    echo &#39;<p>&#39; . &#39;The user &#39;. $username .&#39; exist&#39;;
    echo &#39;<p>&#39; . &#39;<a href="register.html" rel="external nofollow" rel="external nofollow" >Back to register</a>&#39;;
  }
  else
  {
    $query = "insert into tbl_user values (&#39;". $username ."&#39;, &#39;". $password ."&#39;)";
    echo &#39;<p>&#39; . $query;
    $result = $db->query($query);
    if ($result)
    {
      echo &#39;<p>&#39; . &#39;<a href="register.html" rel="external nofollow" rel="external nofollow" >Register successful</a>&#39;;
    }
  }
?>

login_do.php

<?php
  $username = $_POST[&#39;username&#39;];
  $password = $_POST[&#39;password&#39;];
  $db = new mysqli(&#39;localhost&#39;, &#39;root&#39;, &#39;admin&#39;, &#39;test&#39;);
  if (mysqli_connect_errno())
  {
    echo &#39;<p>&#39; . &#39;Connect DB error&#39;;
    exit;
  }
  $query = "select * from tbl_user where username = &#39;" . $username . "&#39; and password = &#39;" . $password . "&#39;";
  echo &#39;<p>&#39; . $query;
  $result = $db->query($query);
  if ($result->num_rows)
  {
    echo &#39;<p>&#39; . &#39;<a href="login.html" rel="external nofollow" rel="external nofollow" >Login successful</a>&#39;;
  }
  else
  {
    echo &#39;<p>&#39; . &#39;<a href="login.html" rel="external nofollow" rel="external nofollow" >Login failed</a>&#39;;
  }
?>

userlist.php

<?php
  $db = new mysqli(&#39;localhost&#39;, &#39;root&#39;, &#39;admin&#39;, &#39;test&#39;);
  if (mysqli_connect_errno())
  {
    echo &#39;<p>&#39; . &#39;Connect DB error&#39;;
    exit;
  }
  echo &#39;<p>&#39; . &#39;All user as follows:&#39;;
  $query = "select * from tbl_user order by username";
  if ($result = $db->query($query))
  {
    while ($row = $result->fetch_assoc())
    {
      echo &#39;<p>&#39; . &#39;Username : &#39; . $row[&#39;username&#39;] . &#39;  <a href="userdelete.php?username=&#39; . $row[&#39;username&#39;] . &#39;" rel="external nofollow" >delete</a>&#39;;
    }
  }
?>

4. The final display effect of the page is as shown below:

5. The code to delete the user:

userdelete.php

<?php
  $username = $_GET[&#39;username&#39;];
  $db = new mysqli(&#39;localhost&#39;, &#39;root&#39;, &#39;admin&#39;, &#39;test&#39;);
  if (mysqli_connect_errno())
  {
    echo &#39;<p>&#39; . &#39;Connect DB error&#39;;
    exit;
  }
  $query = "delete from tbl_user where username = &#39;" . $username . "&#39;";
  echo $query;
  if ($result = $db->query($query))
  {
    echo &#39;<p>&#39; . &#39;Delete user &#39; . $username . &#39; successful&#39;;
  }
  else
  {
    echo &#39;<p>&#39; . &#39;Delete user &#39; . $username . &#39; failed&#39;;
  }
  echo &#39;<p>&#39; . &#39;<a href="userlist.php" rel="external nofollow" >Back to user list</a>&#39;;
?>

Prepare preprocessing

1. The book adding page is as shown below (bookadd.html):

2. The table creation script is as follows:

DROP DATABASE IF EXISTS `test`;
CREATE DATABASE IF NOT EXISTS `test`;
USE `test`;
DROP TABLE IF EXISTS `tbl_book`;
CREATE TABLE IF NOT EXISTS `tbl_book` (
 `isbn` varchar(32) NOT NULL,
 `title` varchar(32) NOT NULL,
 `author` varchar(32) NOT NULL,
 `price` float NOT NULL,
 PRIMARY KEY (`isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf-8;

3. The added logic processing code is as follows (bookadd_do.php ): What needs special attention here is the sentence "$db->query("set names utf-8")", which means that when writing data to the database, utf-8 encoding and decoding is used. Displays the encoding and decoding settings for database table operations to prevent Chinese garbled characters. I will record an article specifically on this technical point later.

<?php
  $isbn = $_POST[&#39;isbn&#39;];
  $title = $_POST[&#39;title&#39;];
  $author = $_POST[&#39;author&#39;];
  $price = $_POST[&#39;price&#39;];
  $db = new mysqli(&#39;localhost&#39;, &#39;root&#39;, &#39;admin&#39;, &#39;test&#39;);
  if (mysqli_connect_errno())
  {
    echo &#39;<p>&#39; . &#39;Connect DB error&#39;;
    exit;
  }
  $db->query("set names utf-8"); //特别注意这句话
  $stmt = $db->stmt_init();
  $stmt->prepare("insert into tbl_book values (?,?,?,?)");
  $stmt->bind_param("sssd", $isbn, $title, $author, $price);
  $stmt->execute();
  echo &#39;<p>&#39; . &#39;Affect rows is &#39; . $stmt->affected_rows;
  echo &#39;<p>&#39; . &#39;<a href="booklist.php" rel="external nofollow" >Go to book list page</a>&#39;;
?>

4. The logic code for displaying book information is as follows. Also pay attention to the sentence "$db->query("set names utf- 8")"：

<?php
  $db = new mysqli(&#39;localhost&#39;, &#39;root&#39;, &#39;admin&#39;, &#39;test&#39;);
  if (mysqli_connect_errno())
  {
    echo &#39;<p>&#39; . &#39;Connect DB error&#39;;
    exit;
  }
  $db->query("set names utf-8"); //特别注意这句话
  $stmt = $db->stmt_init();
  $stmt->prepare("select * from tbl_book");
  $stmt->bind_result($isbn, $title, $author, $price);
  $stmt->execute();
  while($stmt->fetch())
  {
    echo &#39;ISBN : &#39; . $isbn . &#39;<p>&#39;;
    echo &#39;Title : &#39; . $title . &#39;<p>&#39;;
    echo &#39;Author : &#39; . $author . &#39;<p>&#39;;
    echo &#39;Price : &#39; . $price . &#39;<p>&#39;;
    echo &#39;<p>&#39; . &#39;-----------------------------&#39; . &#39;<p>&#39;;
  }
?>

5. The displayed page is as shown below:

##Okay, the most basic part is recorded here. The PHP engine provides very convenient database operation methods, but development is by no means so simple. Various exceptions must be carefully considered how to catch and how to handle them. Database design is also a very interesting topic.

Articles you may be interested in:

PHP database operation class based on pdo

Contents related to mysql read-write separation implemented by PHP

PHP implements the method of compressing multiple files into zip format and downloading them locally

The above is the detailed content of Basic skills in PHP+MariaDB database operation. For more information, please follow other related articles on the PHP Chinese website!