PHP与MySQL入门：打造动态网站的理想组合

5星 · 超过95%的资源需积分: 10 68 浏览量更新于2024-07-24 收藏 1.28MB PDF 举报

"这是一份关于PHP基础入门的教程，涵盖了PHP语言的基本概念、与HTML的结合使用以及PHP与MySQL的协同工作，特别是PHP5以后的mysql和mysqli接口的区别与应用。" PHP，全称为PHP：Hypertext Preprocessor，是一种广泛应用于网页开发的脚本编程语言。它允许开发者在HTML文档中嵌入代码，由服务器端执行，类似于微软的ActiveServer Pages（ASP）技术。PHP的这种特性使得动态网页的创建变得简单。 PHP与MySQL的组合是构建动态网站的理想选择，原因在于它们的部署简便、运行速度快且成本低廉（两者都是开源免费的）。因此，在本教程中，关于PHP的内容会比其他编程语言章节更为详尽。自PHP5开始，提供了两种与MySQL交互的接口：一是原有的mysql函数，这些函数在早期版本的PHP中就已经被广泛应用；二是面向对象的mysqli接口，mysqli带来了更优雅的编程方式，如支持新的MySQL函数，尤其是通过预处理语句高效执行带参数的SQL命令。不过，mysqli接口要求PHP版本至少为5，并且MySQL版本需为4.1或更高。学习PHP基础时，首先需要理解基本语法和流程控制结构，例如变量声明、条件语句（if...else）、循环（for, while）等。然后，掌握如何在HTML页面中嵌入PHP代码，以及如何使用PHP与服务器进行通信，例如通过$_GET和$_POST获取表单数据。在数据库操作方面，了解如何使用mysql_connect()连接到MySQL服务器，mysql_query()执行SQL查询，以及mysql_fetch_array()获取查询结果。对于mysqli接口，需要学习如何创建连接（mysqli_connect()），预处理语句（mysqli_prepare()），绑定参数（mysqli_stmt_bind_param()）以及执行和获取结果（mysqli_stmt_execute(), mysqli_stmt_get_result()）。此外，安全性是任何Web开发中的重要环节。在PHP中，应了解如何防止SQL注入（使用预处理语句或参数化查询），以及如何使用htmlspecialchars()和stripslashes()来避免XSS（跨站脚本攻击）。最后，了解错误处理和调试技巧，如使用echo、print_r()、var_dump()等输出变量信息，以及设置错误报告级别（error_reporting()）和显示错误信息（ini_set('display_errors', 'On')）。这个PHP基础入门教程将引导初学者逐步掌握PHP语言的基本用法，以及如何利用PHP与MySQL配合构建动态网站，同时强调了安全性和效率的重要性。通过深入学习，你可以开启Web开发的旅程，并为进阶PHP高级技术和框架打下坚实的基础。

The following lines show how to evaluate the object array in a loop:

foreach($result->fetch_fields() as $meta)

printf(" Name=%s Table=%s Len=%d Type=%s\n",

$meta->name, $meta->table, $meta->max_length, $meta->type);

Executing Several SQL Commands at Once

With query you can execute only a single SQL command. Sometimes, however, it is useful to execute

several commands simultaneously. To do so, pass a character string to the method multi_query con-

taining the commands, separated by semicolons. The method returns TRUE if the first command was

executed without error. Otherwise, it returns FALSE. (The return value TRUE gives no information as

to whether the remaining commands were executed without error; see the remark below.)

Since each command can return results, the use of multi_query also changes how you evaluate

results: The result of the first command can be read with the method use_result or store_result. The

difference between the two methods is that in using use_result, the result remains on the server and

is transferred to the client record by record. With store_result, all results are immediately transferred

to the client, which in general is more efficient.

You can test whether there are additional results with more_results. This method returns TRUE or

FALSE as result. If you want to process the next result, execute next_result. This method also returns

TRUE or FALSE, depending on whether there are further results. If there are, they must be read with

use_result or store_result.

The following lines show a typical application of these methods: With the first SQL command

the variable @a is initialized. This variable is passed to a stored procedure (that is, a user-defined SQL

function). Then the values of @a and @b are output with SELECT. Each result object is displayed with

the function show_result, which formats the result as a table. The code of show_result appears later in

the chapter. (In this example the third SQL command simply returns a result.)

$sql = "SET @a=12; CALL mysp(@a, @b); SELECT @a, @b";

$ok = $mysqli->multi_query($sql);

if($ok)

do {

$result = $mysqli->store_result();

if($result) {

show_table($result);

$result->close();

}

} while($mysqli->next_result());

■Caution The methods

multi_query

and

next_result

are problematic if errors occur during the processing

of commands. The return value of the method

multi_query

and the

mysqli

properties

errno

error

info

,and

warning_count

relate to only the first SQL command. There is no way of determining errors from the second

command on.

The source of this problem is the implementation of the method

next_result

, which in PHP 5.0.3 returns only

TRUE

FALSE

. However, the underlying C-API function

mysql_next_result

distinguishes three variants (further

results, no further results, and error).

SQL Commands with Parameters (Prepared Statements)

With many PHP pages, similar queries must be executed that differ only in the values of individual

parameters. In such cases, MySQL, from version 4.1, provides prepared statements: The complete

CHAPTER 15 ■ PHP 415

command is sent to the MySQL server only once; thereafter, only the parameters vary. This reduces

considerably the amount of data to be transmitted and also makes the processing of the commands

more efficient, since the MySQL server must analyze the structure of the command only once.

From the point of view of PHP programming, the greatest advantage of prepared statements is

that the code is much more elegant and understandable. It is no longer necessary to compose a valid

command out of the individual parameters (including the quoting of character strings and BLOBs).

Instead, it suffices to store the parameters in simple PHP variables and then execute execute.

Before you do so, some preparations are required. First you must specify the SQL command

with $mysqli->prepare. In the command you place question marks in place of the parameters. As

result, you obtain a mysqli_stmt object, which becomes the basis for all further operations. The fol-

lowing text assumes that this object has been stored in the variable $stmt.

Then, with $stmt->bind_param you bind the parameters to the PHP variables. (Take care to

place things in the correct order.) Each parameter’s data type must be specified by the appropriate

character, as shown in Table 15-1.

Table 15-1. Data Types for bind_param

Character Meaning

i whole number (integer)

d floating-point (double)

s character string (string)

b binary data (BLOBs, binary)

In order actually to execute the SQL command, store the desired values in the PHP variables and

then execute $stmt->execute. You can repeat the process as often as you like. When you no longer

need the mysqli_stmt object, you should release it explicitly at once. You thereby not only free the

object from local memory, you also signal the MySQL server that no further commands are to follow

and it can therefore delete the prepared statement.

The following example clarifies the process:

// example mysqli-prepared.php

// prepare SQL command with parameters

$stmt = $mysqli->prepare(

"INSERT INTO titles (title, subtitle, langID) VALUES (?, ?, ?)");

$stmt->bind_param('ssi', $title, $subtitle, $langID);

// execute command multiple times

$title = "new Linux title 1";

$subtitle = "new subtitle 1";

$langID = 1;

$stmt->execute();

$title = "new MySQL title 2";

$subtitle = "new subtitle 2";

$langID = 2;

$stmt->execute();

// release command

$stmt->close();

After INSERT, UPDATE, and DELETE commands, $stmt->affected_rows returns the number

of changed records. $mysqli->insert_id (not $stmt->insert_id!) returns the last-generated

AUTO_INCREMENT value.

CHAPTER 15 ■ PHP416

Evaluating SELECT Results (Prepared Statements)

Unlike $mysqli->query, $stmt->execute does not return a mysqli_result object. Instead, the

mysqli_stmt object offers a significantly more elegant way of evaluating SELECT results: After carry-

ing out the query, use $stmt->bind_result to bind the columns of the result to PHP variables. Then

$stmt->fetch moves the next data record into these variables. fetch returns the value TRUE or FALSE,

depending on whether a record was found or all records have been processed.

By default, the SELECT results remain on the MySQL server and are transmitted to the client by

fetch record by record. If you wish to have all the results transferred to the client at once, then execute

$stmt->store_result. This is more efficient and relieves the server if you need to read all the records. In

the additional code for reading the data, store_result changes nothing. That is, the method is optional.

// example mysqli-prepared.php

// execute query

$stmt = $mysqli->prepare("SELECT titleID, title FROM titles");

$stmt->execute();

// transfer all results to the client (optional!)

$stmt->store_result();

// bind SELECT result to variables

$stmt->bind_result($titleID, $title);

// loop through all results

while($stmt->fetch())

printf(" %d %s\n",

htmlspecialchars($titleID), htmlspecialchars($title));

$stmt->close();

If you wish to know how many records the SELECT command has returned, you can retrieve

that number with $stmt->num_rows. However, this property functions only if you first execute

$stmt->store_result.

Of course, you can combine the method bind_result presented here with the method

bind_param presented in the previous section, for example if you wish to execute a command

of the form SELECT … FROM … WHERE column = ? several times. The following lines provide

an example.

// example mysqli-prepared.php

// preparation

$stmt = $mysqli->prepare(

"SELECT titleID, title FROM titles WHERE title LIKE ?");

$stmt->bind_param('s', $pattern);

// first SELECT command

$pattern="%Linux%";

$stmt->execute();

$stmt->store_result();

$stmt->bind_result($titleID, $title);

echo "\n";

while($stmt->fetch())

printf(" %d %s\n", $titleID, htmlspecialchars($title));

// second SELECT command

$pattern="%MySQL%";

$stmt->execute();

$stmt->store_result();

$stmt->bind_result($titleID, $title); // possibly unnecessary

echo "\n";

while($stmt->fetch())

printf(" %d %s\n", $titleID, htmlspecialchars($title));

// clean up

$stmt->close();

CHAPTER 15 ■ PHP 417

Unfortunately, it cannot be determined from the PHP documentation whether it is sufficient

to execute bind_result after the first call of the execute method, or whether bind_result must be exe-

cuted after every query. The above example worked in my tests even without the second bind_result

call. It is unclear whether one can rely on such behavior. You will be safer (and not lose too much

time) if you use execute and bind_result in pairs.

Transactions

The mysqli interface currently has no method corresponding to the SQL commands BEGIN

TRANSACTION and START TRANSACTION. If you have to use transactions, then you must first

deactivate auto commit mode with $mysqli->autocommit(0). (There is also no method or prop-

erty that gives information on the current commit mode. You must execute a query with SELECT

@@autocommit. Note that at present, MySQL supports transactions only for InnoDB tables.)

Once autocommit has been deactivated, all the SQL commands form a transaction, which you

can close with the mysqli method commit or abort with rollback. The next transaction begins along

with commit or rollback. Caution: If you forget commit or if an error occurs or a break in the connec-

tion interrupts before commit, then all SQL commands in the current transaction are rolled back.

Instead of the methods autocommit, commit, and rollback, you could simply execute the corre-

sponding SQL commands with real_query, for example $mysqli ->real_query(“START TRANSACTION”).

Outsourcing Database Functions into a Class

Most PHP projects consist of a large number of PHP files, and in most of the files the same code

fragments appear again and again: building a connection to the MySQL database, executing simple

queries, etc. Such a way of doing things is impractical for a variety of reasons:

Redundancy: Redundant code is generally to be avoided. If you discover an error, you may

have to correct it dozens of times. The same is true when access data change due to a change

in provider, and you have to change user name and password in dozens of files.

Security: For reasons of security, the MySQL user name and password should never appear

in plain text in a PHP file that is directly accessible over the web.

Error-handling: For convenience, and to achieve the most compact and readable code possi-

ble, one is tempted to minimize error handling routines or to avoid them altogether.

The solution to all three problems is to store all the MySQL access data as well as all functions

that are used repeatedly in a separate PHP file. This file can then be included in every project file.

More Security with a Separate Password File

Theoretically, visitors to your website should never see any of your PHP source code. (The code is

evaluated by the web server, but the user normally sees only the resulting HTML document.) But

there are some possible errors that can allow visitors to read a PHP file in plain text:

• The web server (for example, during an update) is unconfigured or incorrectly configured,

and it displays the PHP file without evaluating it.

• The PHP page is accessible via anonymous FTP. (That is a serious configuration error! If

there is a directory for anonymous FTP, it must be kept separate from the directory with

HTML and PHP files!)

CHAPTER 15 ■ PHP418

剩余76页未读，继续阅读

soul16

粉丝: 0
资源: 2

PHP与MySQL入门：打造动态网站的理想组合

php基础入门教程

mysql零基础入门

推荐一个好的PHP学习教程

php语言基础知识seaof

docker 入门基础实战完整.pdf

lamp兄弟连php视频教程

php新闻管理系统(简单)学习教程

macromedia dreamweaver 8教程

php web 网站开发书籍推荐

最全的Linux教程，Linux从入门到精通

最新资源