Php Mysql 简明教程
PHP & MySQL - Overview
MySQL 可与 PERL、C、C++、JAVA 和 PHP 等各种编程语言完美结合使用。在这些语言中，PHP 因其 Web 应用程序开发功能而最受欢迎。
MySQL works very well in combination of various programming languages like PERL, C, C++, JAVA and PHP. Out of these languages, PHP is the most popular one because of its web application development capabilities.
PHP 提供了各种功能来访问 MySQL 数据库并在 MySQL 数据库中操作数据记录。您需要像调用任何其他 PHP 函数一样调用 PHP 函数。
PHP provides various functions to access the MySQL database and to manipulate the data records inside the MySQL database. You would require to call the PHP functions in the same way you call any other PHP function.
用于 MySQL 的 PHP 函数采用以下常规格式：
The PHP functions for use with MySQL have the following general format −
mysql_function(value,value,...);函数名的第二部分特定于函数，通常是一个描述函数执行操作的单词。以下是我们将在本教程中使用的两个函数：
The second part of the function name is specific to the function, usually a word that describes what the function does. The following are two of the functions, which we will use in our tutorial −
mysqli_connect($connect);
mysqli_query($connect,"SQL statement");以下示例显示 PHP 调用任何 MySQL 函数的通用语法。
The following example shows a generic syntax of PHP to call any MySQL function.
<html>
<head>
<title>PHP with MySQL</title>
</head>
<body>
<?php
$retval = mysql_function(value, [value,...]);
if( !$retval ) {
die ( "Error: a related error message" );
}
// Otherwise MySQL or PHP Statements
?>
</body>
</html>从下一章开始，我们将看到所有重要的 MySQL 功能以及 PHP。
Starting from the next chapter, we will see all the important MySQL functionality along with PHP.
PHP & MySQL - Environment Setup
为了开发和运行 PHP 网页，您的计算机系统上需要安装三个重要组件。
In order to develop and run PHP Web pages, three vital components need to be installed on your computer system.
-
Web Server − PHP works with virtually all Web Server software, including Microsoft’s Internet Information Server (IIS) but most often used is Apache Server. Download Apache for free here − https://httpd.apache.org/download.cgi
-
Database − PHP works with virtually all database software, including Oracle and Sybase but most commonly used is MySQL database. Download MySQL for free here − https://www.mysql.com/downloads/
-
PHP Parser − In order to process PHP script instructions, a parser must be installed to generate HTML output that can be sent to the Web Browser. This tutorial will guide you how to install PHP parser on your computer.
PHP Parser Installation
在您进行之前，在计算机上确保设置好适当的环境，以使用 PHP 开发您的网络程序非常重要。将以下 php 文件储存在 Apache 的 htdocs 文件夹中。
Before you proceed, it is important to make sure that you have proper environment setup on your machine to develop your web programs using PHP. Store the following php file in Apache’s htdocs folder.
phpinfo.php
<?php
phpinfo();
?>在您的浏览器的地址框中输入以下地址。
Type the following address into your browser’s address box.
http://127.0.0.1/phpinfo.php如果这样做会显示一个页面，显示您的 PHP 安装相关信息，则表示已正确安装了 PHP 和 Web 服务器。否则，您必须按照给定的程序在计算机上安装 PHP。
If this displays a page showing your PHP installation related information, then it means you have PHP and Webserver installed properly. Otherwise, you have to follow the given procedure to install PHP on your computer.
本部分将指导您在以下四个平台上安装和配置 PHP −
This section will guide you to install and configure PHP over the following four platforms −
Apache Configuration
如果您使用 Apache 作为 Web 服务器，那么本部分将指导您编辑 Apache 配置文件。
If you are using Apache as a Web Server, then this section will guide you to edit Apache Configuration Files.
Check here − PHP Configuration in Apache Server
PHP.INI File Configuration
PHP 配置文件 php.ini 是影响 PHP 功能的最后一种直接方法。
The PHP configuration file, php.ini, is the final and immediate way to affect PHP’s functionality.
在此处查看 − PHP.INI File Configuration
Check here − PHP.INI File Configuration
Windows IIS Configuration
要在 Windows 机器上配置 IIS，您可以参考随 IIS 一起提供的 IIS 参考手册。
To configure IIS on your Windows machine, you can refer your IIS Reference Manual shipped along with IIS.
Install MySQL Database
当然，您将需要最重要的实际运行数据库，其中包含您可以查询和修改的表。
The most important thing you will need, of course is an actual running database with a table that you can query and modify.
-
MySQL DB − MySQL is an open source database. You can download it from MySQL Official Site. We recommend downloading the full Windows installation.
-
In addition, download and install MySQL Administrator as well as MySQL Query Browser. These are GUI based tools that will make your development much easier.
-
Finally, download and unzip MySQL Connector/J (the MySQL JDBC driver) in a convenient directory. For the purpose of this tutorial we will assume that you have installed the driver at C:\Program Files\MySQL\mysql-connector-java-5.1.8.
-
Accordingly, set CLASSPATH variable to C:\Program Files\MySQL\mysql-connector-java-5.1.8\mysql-connector-java-5.1.8-bin.jar. Your driver version may vary based on your installation.
Set Database Credential
当我们安装 MySQL 数据库时，它的管理员 ID 设置为 root ，并提供设置密码的选项。
When we install MySQL database, its administrator ID is set to root and it gives provision to set a password of your choice.
使用 root ID 和密码，您可以创建另一个用户 ID 和密码，也可以为您的 JDBC 应用程序使用 root ID 和密码。
Using root ID and password you can either create another user ID and password, or you can use root ID and password for your JDBC application.
有各种数据库操作，例如数据库创建和删除，这需要管理员 ID 和密码。
There are various database operations like database creation and deletion, which would need administrator ID and password.
对于 JDBC 教程的其余部分，我们将使用 MySQL 数据库， guest 为 ID， guest123 为密码。
For rest of the JDBC tutorial, we would use MySQL Database with guest as ID and guest123 as password.
如果您没有足够权限创建新用户，那么您可以请求数据库管理员 (DBA) 为您创建用户 ID 和密码。
If you do not have sufficient privilege to create new users, then you can ask your Database Administrator (DBA) to create a user ID and password for you.
Create Database
要创建 TUTORIALSPOINT 数据库，请执行以下步骤 −
To create the TUTORIALSPOINT database, use the following steps −
Step 1
打开 Command Prompt 并更改到安装目录，如下所示 −
Open a Command Prompt and change to the installation directory as follows −
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>Note − mysqld.exe 的路径可能因系统上 MySQL 的安装位置而异。您还可以查看有关如何启动和停止数据库服务器的文档。
Note − The path to mysqld.exe may vary depending on the install location of MySQL on your system. You can also check documentation on how to start and stop your database server.
Create Table
要创建 Employees 表中 TUTORIALSPOINT 数据库，请执行以下步骤 −
To create the Employees table in TUTORIALSPOINT database, use the following steps −
Step 1
打开 Command Prompt 并更改到安装目录，如下所示 −
Open a Command Prompt and change to the installation directory as follows −
C:\>
C:\>cd Program Files\MySQL\bin
C:\Program Files\MySQL\bin>Create Data Records
最后，在 Employee 表中创建一些记录，如下所示 −
Finally you create few records in Employee table as follows −
mysql> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');
Query OK, 1 row affected (0.00 sec)
mysql>如需全面了解 MySQL 数据库，请学习 MySQL Tutorial 。
For a complete understanding on MySQL database, study the MySQL Tutorial.
现在，您可以开始尝试使用 PHP 了。
Now you are ready to start experimenting with PHP.
PHP & MySQL - Connect Database Example
PHP 提供 mysqli 构造或 mysqli_connect() 函数来打开一个数据库连接。此函数采用六个参数，并且在成功时返回一个 MySQL 链接标识符，在失败时返回 FALSE。
PHP provides mysqli contruct or mysqli_connect() function to open a database connection. This function takes six parameters and returns a MySQL link identifier on success or FALSE on failure.
Syntax
$mysqli = new mysqli($host, $username, $passwd, $dbName, $port, $socket);Sr.No. |
Parameter & Description |
1 |
$host Optional − The host name running the database server. If not specified, then the default value will be localhost:3306. |
2 |
$username Optional − The username accessing the database. If not specified, then the default will be the name of the user that owns the server process. |
3 |
$passwd Optional − The password of the user accessing the database. If not specified, then the default will be an empty password. |
4 |
$dbName Optional − database name on which query is to be performed. |
5 |
$port Optional − the port number to attempt to connect to the MySQL server.. |
6 |
$socket Optional − socket or named pipe that should be used. |
你可以随时使用另一个 PHP 函数 close() 来断开 MySQL 数据库连接。
You can disconnect from the MySQL database anytime using another PHP function close().
Example
尝试以下示例以连接到 MySQL 服务器 −
Try the following example to connect to a MySQL server −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Connecting MySQL Server</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
$mysqli->close();
?>
</body>
</html>PHP & MySQL - Create Database Example
PHP 使用 mysqli query() 或 mysql_query() 函数来创建或删除 MySQL 数据库。这个函数接收两个参数，并在成功时返回 TRUE，并在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to create or delete a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli->query($sql,$resultmode)Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to create a MySQL database. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
尝试以下示例来创建数据库 −
Try the following example to create a database −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head><title>Creating MySQL Database</title></head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
if ($mysqli->query("CREATE DATABASE TUTORIALS")) {
printf("Database TUTORIALS created successfully.<br />");
}
if ($mysqli->errno) {
printf("Could not create database: %s<br />", $mysqli->error);
}
$mysqli->close();
?>
</body>
</html>PHP & MySQL - Drop Database Example
PHP 使用 mysqli query() 或 mysql_query() 函数来删除 MySQL 数据库。这个函数接收两个参数，并在成功时返回 TRUE，并在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to drop a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli->query($sql,$resultmode)Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to drop a MySQL database. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
尝试以下示例来删除数据库−
Try the following example to drop a database −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head><title>Dropping MySQL Database</title></head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
if ($mysqli->query("Drop DATABASE TUTORIALS")) {
printf("Database TUTORIALS dropped successfully.<br />");
}
if ($mysqli->errno) {
printf("Could not drop database: %s<br />", $mysqli->error);
}
$mysqli->close();
?>
</body>
</html>PHP & MySQL - Select Database Example
PHP 使用 mysqli_select_db 函数来选择要执行查询的数据库。此函数接受两个参数，并成功时返回 TRUE，失败时返回 FALSE。
PHP uses mysqli_select_db function to select the database on which queries are to be performed. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
mysqli_select_db ( mysqli $link , string $dbname ) : boolSr.No. |
Parameter & Description |
1 |
$link Required - A link identifier returned by mysqli_connect() or mysqli_init(). |
2 |
$dbname Required - Name of the database to be connected. |
Example
尝试以下示例以选择数据库 −
Try the following example to select a database −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Selecting MySQL Database</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysqli_error($conn));
}
echo 'Connected successfully<br />';
$retval = mysqli_select_db( $conn, 'TUTORIALS' );
if(! $retval ) {
die('Could not select database: ' . mysqli_error($conn));
}
echo "Database TUTORIALS selected successfully\n";
mysqli_close($conn);
?>
</body>
</html>PHP & MySQL - Create Table
PHP 使用 mysqli query() 或 mysql_query() 函数来创建 MySQL 表。这个函数接收两个参数，并在成功时返回 TRUE，并在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to create a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli->query($sql,$resultmode)Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to create a MySQL table. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
尝试以下示例以创建一个表 −
Try the following example to create a table −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Creating MySQL Table</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
$sql = "CREATE TABLE tutorials_tbl( ".
"tutorial_id INT NOT NULL AUTO_INCREMENT, ".
"tutorial_title VARCHAR(100) NOT NULL, ".
"tutorial_author VARCHAR(40) NOT NULL, ".
"submission_date DATE, ".
"PRIMARY KEY ( tutorial_id )); ";
if ($mysqli->query($sql)) {
printf("Table tutorials_tbl created successfully.<br />");
}
if ($mysqli->errno) {
printf("Could not create table: %s<br />", $mysqli->error);
}
$mysqli->close();
?>
</body>
</html>PHP & MySQL - Drop Table Example
PHP 使用 mysqli query() 或 mysql_query() 函数来删除 MySQL 表。这个函数接收两个参数，并在成功时返回 TRUE，并在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to drop a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli->query($sql,$resultmode)Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to drop a table. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
尝试以下示例来删除一个表格 −
Try the following example to drop a table −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head><title>Dropping MySQL Table</title></head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
if ($mysqli->query("Drop Table tutorials_tbl")) {
printf("Table tutorials_tbl dropped successfully.<br />");
}
if ($mysqli->errno) {
printf("Could not drop table: %s<br />", $mysqli->error);
}
$mysqli->close();
?>
</body>
</html>PHP & MySQL - Insert Records Example
PHP 使用 mysqli_query 函数在表中插入记录。此函数采用三个参数，成功时返回 TRUE，失败时返回 FALSE。
PHP uses mysqli_query function to insert records in table. This function takes three parameters and returns TRUE on success or FALSE on failure.
Syntax
mysqli_query ( mysqli $link, string $query, int $resultmode = MYSQLI_STORE_RESULT ) : mixedSr.No. |
Parameter & Description |
1 |
$link Required - A link identifier returned by mysqli_connect() or mysqli_init(). |
2 |
$query Required - SQL query to drop a table. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
此示例会从用户那里获取三个参数，并将它们插入 MySQL 表格 -
This example will take three parameters from the user and will insert them into the MySQL table −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Add New Record in MySQL Database</title>
</head>
<body>
<?php
if(isset($_POST['add'])) {
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysqli_error($conn));
}
if(! get_magic_quotes_gpc() ) {
$tutorial_title = addslashes ($_POST['tutorial_title']);
$tutorial_author = addslashes ($_POST['tutorial_author']);
} else {
$tutorial_title = $_POST['tutorial_title'];
$tutorial_author = $_POST['tutorial_author'];
}
$submission_date = $_POST['submission_date'];
$sql = "INSERT INTO tutorials_tbl ".
"(tutorial_title,tutorial_author, submission_date) "."VALUES ".
"('$tutorial_title','$tutorial_author','$submission_date')";
mysqli_select_db( $conn, 'TUTORIALS' );
$retval = mysqli_query( $conn, $sql );
if(! $retval ) {
die('Could not enter data: ' . mysqli_error($conn));
}
echo "Entered data successfully\n";
mysqli_close($conn);
} else {
?>
<form method = "post" action = "<?php $_PHP_SELF ?>">
<table width = "600" border = "0" cellspacing = "1" cellpadding = "2">
<tr>
<td width = "250">Tutorial Title</td>
<td><input name = "tutorial_title" type = "text" id = "tutorial_title"></td>
</tr>
<tr>
<td width = "250">Tutorial Author</td>
<td><input name = "tutorial_author" type = "text" id = "tutorial_author"></td>
</tr>
<tr>
<td width = "250">Submission Date [ yyyy-mm-dd ]</td>
<td><input name = "submission_date" type = "text" id = "submission_date"></td>
</tr>
<tr>
<td width = "250"> </td>
<td></td>
</tr>
<tr>
<td width = "250"> </td>
<td><input name = "add" type = "submit" id = "add" value = "Add Tutorial"></td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>Output
访问部署在 apache Web 服务器上的 mysql_example.php，输入详细信息，并在提交表单时验证输出。
Access the mysql_example.php deployed on apache web server, enter details and verify the output on submitting the form.
Entered data successfully在执行数据插入时，最好使用函数 get_magic_quotes_gpc() 来检查当前 magic quote 配置是否已设置。如果此函数返回 false，则使用函数 addslashes() 在引号前添加反斜杠。
While doing a data insert, it is best to use the function get_magic_quotes_gpc() to check if the current configuration for magic quote is set or not. If this function returns false, then use the function addslashes() to add slashes before the quotes.
您可以进行许多验证，以检查输入的数据是否正确，并可以采取适当的操作。
You can put many validations around to check if the entered data is correct or not and can take the appropriate action.
PHP & MySQL - Select Records Example
您可以将相同的 SQL SELECT 命令用于 PHP 函数 mysql_query() 。此函数用于执行 SQL 命令，然后稍后可以使用另一个 PHP 函数 mysql_fetch_array() 来获取所有选定的数据。此函数将行作为关联数组、数字数组或两者返回。如果不再有行，则此函数将返回 FALSE。
You can use the same SQL SELECT command into a PHP function mysql_query(). This function is used to execute the SQL command and then later another PHP function mysql_fetch_array() can be used to fetch all the selected data. This function returns the row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.
以下程序是一个简单的示例，它将展示如何从 tutorials_tbl 表中获取/显示记录。
The following program is a simple example which will show how to fetch / display records from the tutorials_tbl table.
Example
以下代码块将显示 tutorials_tbl 表中的所有记录。
The following code block will display all the records from the tutorials_tbl table.
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Selecting Records</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysqli_error($conn));
}
echo 'Connected successfully<br />';
mysqli_select_db( $conn, 'TUTORIALS' );
$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";
$retval = mysqli_query( $conn, $sql );
if(! $retval ) {
die('Could not get data: ' . mysqli_error($conn));
}
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC)) {
echo "Tutorial ID :{$row['tutorial_id']} ".
"Title: {$row['tutorial_title']} ".
"Author: {$row['tutorial_author']} ".
"Submission Date : {$row['submission_date']} ".
"--------------------------------";
}
echo "Fetched data successfully\n";
mysqli_close($conn);
?>
</body>
</html>Output
访问部署在 apache Web 服务器上的 mysql_example.php，输入详细信息，并在提交表单时验证输出。
Access the mysql_example.php deployed on apache web server, enter details and verify the output on submitting the form.
Entered data successfully在执行数据插入时，最好使用函数 get_magic_quotes_gpc() 来检查当前 magic quote 配置是否已设置。如果此函数返回 false，则使用函数 addslashes() 在引号前添加反斜杠。
While doing a data insert, it is best to use the function get_magic_quotes_gpc() to check if the current configuration for magic quote is set or not. If this function returns false, then use the function addslashes() to add slashes before the quotes.
您可以进行许多验证，以检查输入的数据是否正确，并可以采取适当的操作。
You can put many validations around to check if the entered data is correct or not and can take the appropriate action.
PHP & MySQL - Update Records Example
PHP 使用 mysqli query() 或 mysql_query() 函数来更新 MySQL 表中的记录。此函数接收两个参数，成功时返回 TRUE，失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to update records in a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli->query($sql,$resultmode)Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to update records in a MySQL table. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
尝试使用以下示例来更新表中的记录-
Try the following example to update a record in a table −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Updating MySQL Table</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
if ($mysqli->query('UPDATE tutorials_tbl set tutorial_title = "Learning Java" where tutorial_id = 4')) {
printf("Table tutorials_tbl updated successfully.<br />");
}
if ($mysqli->errno) {
printf("Could not update table: %s<br />", $mysqli->error);
}
$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
$row["tutorial_id"],
$row["tutorial_title"],
$row["tutorial_author"],
$row["submission_date"]);
}
} else {
printf('No record found.<br />');
}
mysqli_free_result($result);
$mysqli->close();
?>
</body>
</html>Output
访问在 apache web 服务器上部署的 mysql_example.php，并验证输出。在运行选择脚本之前，我们在此表中输入了多条记录。
Access the mysql_example.php deployed on apache web server and verify the output. Here we’ve entered multiple records in the table before running the select script.
Connected successfully.
Table tutorials_tbl updated successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 4, Title: Learning Java, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021PHP & MySQL - Delete Records Example
PHP 使用 mysqli query() 或 mysql_query() 函数来删除 MySQL 表中的记录。这个函数接收两个参数，并在成功时返回 TRUE，并在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to delete records in a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli->query($sql,$resultmode)Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to delete records in a MySQL table. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
尝试使用以下示例来删除表中的记录 -
Try the following example to delete a record in a table −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Deleting MySQL Table record</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
if ($mysqli->query('DELETE FROM tutorials_tbl where tutorial_id = 4')) {
printf("Table tutorials_tbl record deleted successfully.<br />");
}
if ($mysqli->errno) {
printf("Could not delete record from table: %s<br />", $mysqli->error);
}
$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
$row["tutorial_id"],
$row["tutorial_title"],
$row["tutorial_author"],
$row["submission_date"]);
}
} else {
printf('No record found.<br />');
}
mysqli_free_result($result);
$mysqli->close();
?>
</body>
</html>Output
访问在 apache web 服务器上部署的 mysql_example.php，并验证输出。在运行选择脚本之前，我们在此表中输入了多条记录。
Access the mysql_example.php deployed on apache web server and verify the output. Here we’ve entered multiple records in the table before running the select script.
Connected successfully.
Table tutorials_tbl record deleted successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021PHP & MySQL - Where Clause Example
PHP 使用 mysqli query() 或 mysql_query() 函数通过 where 子句选择 MySQL 表中的记录。此函数接受两个参数，并在成功时返回 TRUE，在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to select records in a MySQL table using where clause. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli->query($sql,$resultmode)Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to select records in a MySQL table using Where Clause. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
尝试以下示例，通过表中的 where 子句选择记录 −
Try the following example to select a record using where clause in a table −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Using Where Clause</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
$sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl where tutorial_author = "Mahesh"';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
$row["tutorial_id"],
$row["tutorial_title"],
$row["tutorial_author"],
$row["submission_date"]);
}
} else {
printf('No record found.<br />');
}
mysqli_free_result($result);
$mysqli->close();
?>
</body>
</html>Output
访问在 apache web 服务器上部署的 mysql_example.php，并验证输出。在运行选择脚本之前，我们在此表中输入了多条记录。
Access the mysql_example.php deployed on apache web server and verify the output. Here we’ve entered multiple records in the table before running the select script.
Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021PHP & MySQL - Like Clause Example
PHP 使用 mysqli query() 或 mysql_query() 函数使用 Like 子句来选择 MySQL 表中的记录。此函数接受两个参数并在成功时返回 TRUE，在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to select records in a MySQL table using Like clause. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli->query($sql,$resultmode)Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to select records in a MySQL table using Like Clause. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
尝试以下示例，以在表中使用 like 子句选择记录 -
Try the following example to select a record using like clause in a table −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Using Like Clause</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
$sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl where tutorial_author like "Mah%"';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
$row["tutorial_id"],
$row["tutorial_title"],
$row["tutorial_author"],
$row["submission_date"]);
}
} else {
printf('No record found.<br />');
}
mysqli_free_result($result);
$mysqli->close();
?>
</body>
</html>Output
访问在 apache web 服务器上部署的 mysql_example.php，并验证输出。在运行选择脚本之前，我们在此表中输入了多条记录。
Access the mysql_example.php deployed on apache web server and verify the output. Here we’ve entered multiple records in the table before running the select script.
Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021PHP & MySQL - Sorting Data Example
PHP 使用 mysqli query() 或 mysql_query() 函数从 MySQL 表中获取排序记录。这个函数接收两个参数，并在成功时返回 TRUE，并在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to get sorted records from a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli->query($sql,$resultmode)Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to get sorted records from a table. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
Example
尝试使用以下示例从表中获取排序记录 -
Try the following example to get sorted records from a table −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Sorting MySQL Table records</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
$sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl order by tutorial_title asc";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
printf("Id: %s, Title: %s, Author: %s, Date: %d <br />",
$row["tutorial_id"],
$row["tutorial_title"],
$row["tutorial_author"],
$row["submission_date"]);
}
} else {
printf('No record found.<br />');
}
mysqli_free_result($result);
$mysqli->close();
?>
</body>
</html>Output
访问部署在 Apache Web 服务器上的 mysql_example.php, 并验证输出。
Access the mysql_example.php deployed on apache web server and verify the output.
Connected successfully.
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021PHP & MySQL - Using Joins Example
PHP 使用 mysqli query() 或 mysql_query() 函数来使用联接从 MySQL 表中获取记录。这个函数接收两个参数，并在成功时返回 TRUE，并在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to get records from a MySQL tables using Joins. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli->query($sql,$resultmode)Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to get records from multiple tables using Join. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
首先使用以下脚本在 MySQL 中创建表并插入两条记录。
First create a table in MySQL using following script and insert two records.
create table tcount_tbl(
tutorial_author VARCHAR(40) NOT NULL,
tutorial_count int
);
insert into tcount_tbl values('Mahesh', 3);
insert into tcount_tbl values('Suresh', 1);Example
尝试以下示例以使用连接获取两个表中的记录。−
Try the following example to get records from a two tables using Join. −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Using joins on MySQL Tables</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a, tcount_tbl b
WHERE a.tutorial_author = b.tutorial_author';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
printf("Id: %s, Author: %s, Count: %d <br />",
$row["tutorial_id"],
$row["tutorial_author"],
$row["tutorial_count"]);
}
} else {
printf('No record found.<br />');
}
mysqli_free_result($result);
$mysqli->close();
?>
</body>
</html>PHP & MySQL - Handling NULL Example
可以使用 if…else 条件根据 NULL 值准备查询。
You can use the if…else condition to prepare a query based on the NULL value.
以下示例从外部获取 tutorial_count，然后将其与表中可用值进行比较。
The following example takes the tutorial_count from outside and then compares it with the value available in the table.
Example
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Handling NULL</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
$tutorial_count = null;
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
if( isset($tutorial_count )) {
$sql = 'SELECT tutorial_author, tutorial_count
FROM tcount_tbl
WHERE tutorial_count = ' + $tutorial_count;
} else {
$sql = 'SELECT tutorial_author, tutorial_count
FROM tcount_tbl
WHERE tutorial_count IS NULL';
}
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
printf("Author: %s, Count: %d <br />",
$row["tutorial_author"],
$row["tutorial_count"]);
}
} else {
printf('No record found.<br />');
}
$mysqli->close();
?>
</body>
</html>PHP & MySQL - Database Info Example
MySQL 中有几个重要命令可以在 MySQL 提示符处执行，也可以使用 PHP 等任何脚本来获取有关数据库服务器的各种重要信息。
There are a few important commands in MySQL which can be executed either at the MySQL prompt or by using any script like PHP to get various important information about the database server.
Sr.No. |
Command & Description |
1 |
SELECT VERSION( ) Server version string |
2 |
SELECT DATABASE( ) Current database name (empty if none) |
3 |
SELECT USER( ) Current username |
4 |
SHOW STATUS Server status indicators |
5 |
SHOW VARIABLES Server configuration variables |
Example
尝试以下示例以获取数据库信息 −
Try the following example to get database info −
将以下示例复制粘贴为 mysql_example.php：
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Getting MySQL Database Info</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
$tutorial_count = null;
if($mysqli->connect_errno ) {
printf("Connect failed: %s<br />", $mysqli->connect_error);
exit();
}
printf('Connected successfully.<br />');
if ($result = mysqli_query($mysqli, "SELECT DATABASE()")) {
$row = mysqli_fetch_row($result);
printf("Default database is %s<br />", $row[0]);
mysqli_free_result($result);
}
$mysqli->close();
?>
</body>
</html>