毕业论文外文翻译-通过PHP访问MySQL

1、原文:getting php to talk to mysqlnow that youre comfortable using the mysql client tools to manipulate data in the database, you can begin using php to display and modify data from the database. php has standard functions for working with the database.first, we9re going to discuss php，s built-in datab

2、ase functions. weti also show you how to use the the php extension and application repository (pear) databasefunctions that provide the ability to use the same functions to access any supported database. this type of flexibil让y comes from a process called abstraction. in programming interfaces, abst

3、raction simplifies a complex interaction. it works by removing any nonessential parts of the interaction, allowing you to concentrate on the important parts. pear's db classes are one such database interface abstraction. the information you need to log into a database is reduced to the bare mini

4、mum. this standard format allows you to interact with mysql, as well as other databases using the same functions. similarly, other mysql-specific functions are replaced with generic ones that know how to talk to many databases. for example, the mysql-specific connect function is:mysql_connect($db_ho

5、st, $db_username, $db_password);versus pearls db connect function:sconnection=db:connect(hmysql:/$db_username:$db_password$db_host/$db_databasen);the same basic information is present in both commands, but the pear function also specifies the type of databases to which to connect. you can connect to

6、 mysql or other supported databases. well discuss both connection methods in detail.in this chapter, yoifll learn how to connect to a mysql server fromphp, how to use php to access and retrieve stored data, and how to correctly display information to the user.the processthe basic steps of performing

7、 a query, whether using the mysql command-line tool or php, are the same: connect to the database. select the database to use. build a select statement. perform the query. display the results.weti walk through each of these steps for both plain php and pear functions- resourceswhen connecting to a m

8、ysql database, you will use two new resources. the first is the link identifier that holds all of the information necessary to connect to the database for an active connection. the other resource is the results resource. it contains all information required to retrieve results from an active databas

9、e query，s result set. you'll be creating and assigning both resources in this chapter.querying the database with php functionsin this section, we introduce how to connect to a mysql database with php. it5s quite simple, and we41 begin shortly with examples, but we should talk briefly about what

10、actually happens. when you try connecting to a mysql database, the mysql server authenticates you based on your username and password. php handles connectingto the database for you, and it allows you to start performing queries and gathering data immediately.as in chapter & wetl need the same pi

11、eces of information to connect to the database: the ip address of the database server the name of the database the username the passwordbefore moving on, make sure you can log into your database using the mysql command-line client.figure 9-1 shows how the steps of the database interaction relate to

12、the two types of resources. building the select statement happens before the third function call, but it is not shown. it，s done with plain php code, not a mysql-specific php function.figure 9-1. the interaction between functions and resources when using the database including database login details

13、you're going to create a file to hold the information for logging into mysql. storing this information in a file you include is recommended. if you change the database password, there is only one place that you need to change it, regardless of how many php files you have that access the database

14、.you dont have to worry about anyone directly viewing the file and getting your database login details- the file, if requested by itself, is processed as a php file and returns a blank page.troubleshooting connection errorsone error you may get is:fatal error: call to undefined function mysql_connec

15、t() in c:program filesapache software foundationapache2.2htdocsdb_test.php on line 4this error occurs because php 5.x for windows was downloaded, and mysql support was not included by default. to fix this eitor, copy the php_mysql.dll file from the directory of the php zip file to c:php, and then c:

16、windowsphp.ini.make sure there are two lines that are not commented out by a semicolon (;) at the beginning of the line like these:extension_dir = hc:/php/ext/hextension=php_mysql.dllthis will change the extension to include the directory to c:/php and include the mysql extension, respectively. you

17、can use the search function of your text editor to check whether the lines are already there and just need to be uncommented, or whether they need to be added completely.youtl need to restart apache, and then mysql support will be enabled.selecting the databasenow that youre connected, the next step

18、 is to select which database to use with the mysql_select_db command it takes two parameters: the database name and, optionally, the database connection. if you don't specify the database connection, the default is the connection from the last mysql_connect:/ select the database$db_select=mysql_

19、select_db($db_database);if (!$db_select)die ("could not select the database: <br />m. mysql_error();again, its good practice to check for an error and display it every time you access the database.now that you've got a good database connection, you're ready to execute your sql que

20、ry.building the sql select querybuilding a sql query is as easy as setting a variable to the string that is your sql query. of course, you41 need to use a valid sql query, or mysql returns with an error when you execute the query. the variable name squery is used since the name reflects its purpose,

21、 but you can choose anything yottd like for a variable name. the sql query in this example is select * from books.you can build up your query in parts using the string concatenate () operator:executing the queryto have the database execute the query, use the mysqlquery function. it takes two paramet

22、ers一the query and, optionally, the database link一and returns the result. save a link to the results in a variable called, you guessed it, $result! this is also a good place to check the return code from mysql_query to make sure that there were no errors in the query string or the database connection

23、 by verifying that $result is not false:when the database executes the query, all of the results forma result set. these results correspond to the rows that you saw upon doing a query using the mysql command-line client. to display them, you process each row, one at a time.fetching and displayinguse

24、 mysql_fetch_row to get the rows from the result set. its syntax is:array mysql_fetch_row ( resource sresult);it takes the result you stored in $result fromthe query as a parameter. it returns one row at a time from the query until there are no more rows, and then it returns false. therefore, you do

25、 a loop on the result of mysql_fetch_row and define some code to display each row:the columns of the result row are stored in the array and can be accessed one at a time. the variable $result_row2 accesses the second attribute (as defined in the query's column order or the column order of the ta

26、ble if select * is used) in the result row.fetch typesthis is not the only way to fetch the results. using mysql_fetch_array, php can place the results into an array in one step. it takes a result as its first parameter, and the way to bind the results as an optional second parameter. if mysql_assoc

27、 is specified, the results are indexed in an array based on their column names in the query. if mysql_num is specified, then the number starting at zero accesses the results. the default value, mysql_both, returns a result array with both types. the mysql_fetch_assoc is an alternative to supplying t

28、he mysql_assoc argumen匸closing the connectionas a rule of thumb, you always want to close a connection to a database when you9redone using i匸 closing a database with mysql_close will tell php and mysql that you no longer will be using the connection, and will free any resources and memory allocated

29、to it:mysql_close($connection)installingpear uses a package manager that oversees which pear features you install.whether you need to install the package manager depends on which version of php you installed. if you5re running php 4.3.0 or newer, it's already installed. if you9rerunning php 5.0,

30、 pear has been split out into a separate package. the db package that you9re interested in is optional but installed by default with the package manager. so if you have the package manager, you're all set.unixyou can install the package manager on a unix systemby executing the following from the

31、 shell (command-line) prompt:lynx -source /1 phpthis takes the output of the site (which is actually the source php code) to install pear and passes it along to the php command for execution.windowsthe php 5 installation includes the pear installation script as c:phpgo-p

32、ear.bat. in case you didnt install all the files in chapter 2, go ahead and extract all the php files to c:/php from the command prompt, and execute the .bat file.creating a connect instancethe db.php file defines a class of type db. refer to chapter 5 for more information on working with classes an

33、d objects. wetl principally be calling the methods in the class. the db class has a connect method, which we'll use instead of our old connect function, mysql_connect. the double colons (:) indicate that we5re calling that function from the class in line 4:$connection=db:connect(nmysql:/$db_user

34、name:$db_password$db_host/$db_databaseh); when you call the connect function, it creates a new database connection that is stored in the variable $connection. the connect function attempts to connect to the database based on the connect string you passed to it.connect stringthe connect string uses t

35、his new format to represent the login information that you already supplied in separate fields:dbtype:/usemame:passwordhost/databasethis format may look familiar to you, as it's very similar to the connect string for a windows file share. the first part of the string is what really sets the pear

36、 functions apart fromthe plain php. the phptype field specifies the type of database to connect. supported databases include ibase, msql, mssql, mysql, oci8, odbc, pgsql, and sybase. all that5 s required for your php page to work with a different type of database is changing the phptype!the username

37、, password, host, and database should be familiar from the basic php connect. only the type of connection is required. however, you511 usually want to specify all fields.after the values from db_login.php are included, the connect string looks like the following:nmysql:/test:testlocalhost/testnif th

38、e connect method on line 6 was successful, a db object is created. it contains the methods to access the database as well as all of the information about the state of that database connectiomqueryingone of the methods it contains is called query. the query method works just like php，s query function

39、 in that it takes a sql statement. the difference is that the arrow syntax (->) is used to call it fromthe object. it also returns the results as another object instead of a result set:$query 二"select * from books'*sresult = $connection->query($query);based on the sql query, this code

40、 calls the query function fromthe connection object and returns a result object named sresult.fetchingline 22 uses the result object to call the fetchrow method it returns the rows one at a time, similar to mysql_fetch_row:while ($result_row = $result->fetchrow() echo title: $result_row 1 . !<

41、br />f;echo 'author: '.$result_row4 . '<br /> *;echo pages: $result_row2 . *<br /><br />'use another while loop to go through each row from fetchrow until it returns false. the code in the loop hasnt changed from the non-pear example.closingyou're finished wi

42、th the database connection, so close it using the object method disconnect:$connection->disconnect();pear error reportingthe function db:iserror will check to see whether the result that's been returned to you is an error. if it is an error, you can use db:errormessage to return a text descri

43、ption of the error that was generated. you need to pass db:errormessage, the return value from your function, as an argument.here you rewrite the pear code to use error checking:<?phpif ( db:iserror( sdemoresult = $db->query( $sql)echo db:errormessage($demoresult); elsewhile ($demorow = $demor

44、esult->fetchrow()echo $demorow2 . '<br />'?>therealso a new version of the pear database interface called pear:mdb2.the same results display, but there are more functions available in this version of the pear database abstraction layer.now that you have a good handle on connectin

45、g to the database and the various functions of pearo译文:通过php访问mysql现在你已经可以熟练地使用mysql客户端软件来操作数据库里的数据，我们也 可以开始学习如何使用php来显示和修改数据库里的数据了。php有标准的函 数用来操作数据库。我们首先学习php内建的数据库函数，然后会学习php扩展和应用程序库 (pear, php extension and application repository )中的数据库函数，我们可 以使用这些函数操作所有支持的数据库。这种灵活性源自于抽象。对于编程接口 而言，抽象简化了复杂的交互过程。它将

46、交互过程中无关紧要的部分屏蔽起来， 让你关注于重要的部分。pear的db类就是这样一种数据库接口的抽象。你登 录一个数据库所需要提供的信息被减少到最少。这种标准的格式可以通过同一个 函数来访问mysql以及其他的数据库。同样，一些mysql特定的函数被更一 般的、可以用在很多数据库上的函数所替代。比如，mysql特定的连接函数是: mysql_connect($db_host, $db_username, $db_password);而pear的db提供的连接函数是：sconnection=db:cormect(”mysql:/$db_usemame:$db_password$db_host/

47、$db_dat:abase”)； 两个命令都提供了同样的基木信息，但是pear的函数中还指定了要连接的数据 库的类型。你可以连接到mysql或者其他支持的数据库。我们会详细讨论这两 种连接方式。木章中，我们会学习如何从php连接到mysql的服务器，如何使用php访问 数据库中存储的数据，以及如何正确的向用户显示信息。步骤无论是通过mysql命令行工具，还是通过php,执行一个查询的基本步骤都是 一样的：连接到数据库选择要使用的数据库创建select语句执行查询显示结果我们将逐一介绍如何用php和pear的函数完成上面的每一步。资源当连接到mysql数据库的时候，你会使用到两个新的资源。第一个

48、是连接的标 识符，它记录了一个活动连接用来连接到数据库所必需的所有信息。另外一个资 源是结果资源，它包含了用来从一个有效的数据库查询结果中取岀结果所需要的 所有信息。本章中我们会创建并使用这两种资源。使用php函数查询数据库本节我们会介绍如何使用php连接mysql数据库。这非常简单，我们会用一 些例子说明。但是之前我们应该稍微了解一下幕后发生的事情。当你试图连接一 个mysql数据库的时候，mysql服务器会根据你的用户名和密码进行身份认 证。php为你建立数据库的连接，你可以立即开始查询并得到结果。我们需要同样的信息来连接数据库：数据库服务器的ip地址数据库的名字用户名密码在开始之前，首先

49、使用mysql的命令行客户端确认你登录到数据库。图91显示了数据库交互过程的各个步骤和两种类型资源之间的关系。创建 select语句发生在第三个函数调用z前，但是在图屮没有显示出來。它是通过 普通的php代码，而不是mysql特定的php函数完成的。图91：使用数据库时函数和资源之间的交互包含数据库登录细节我们先创建一个文件，用来保存登录mysql所用到的信息。我们建议你把这些 信息放在单独的文件里然后通过include来使用这个文件。这样一来如果你修改 了数据库的密码。无论有多少个php文件访问数据库，你只需要修改这一个文 件。连接到数据库我们需要做的头一件事情是连接数据库，并且检查连接是否

50、确实建立起來。通过 include包含连接信息的文件，我们可以在调用mysql_connect函数的时候使用这 些变量而不是将这些值写死在代码中。我们使用一个叫做db_test.php的文件， 往其屮增加这些代码段。诊断连接错误你可能遇到的一个错谋是：fatal error: call to undefined function mysql_connect( ) in c:program filesapachesoftware foundationapache2.2htdocsdb_test.php on line 4这个错谋发生的原因是下载安装的php5.x默认没有包括对mysql的支持。解

51、 决这个问题需要将php_mysql.dll文件从php压缩包例的ext/f录复制到c:/php, 并修改c:windowsphp.ini文件，确保下而两行没有被注释掉(注释的方法在 行首使用分号)。extension_dir = "c:/php/ext/"extension=php_mysql.dll这样php扩展的fl录就被设为c:php,mysql的扩展也会被使用。在编辑php.ini 文件的时候，你可以使用编辑器的搜索功能來检查这两行是否已经存在，只是需 要去掉注释，并且需要重新输入。重新启动apache,这样mysql的支持就会被打开了。选择数据库建立连接之后，下

52、一步就是使用mysql_select_db来选择我们要用的数据库。它 的参数有两个：数据库名和可选的数据库连接。如果不指定数据库连接，默认使 用上一条mysql_connect所建立的连接。/ select the database$db_select=mysql_select_db($db_database);if (!$db_select)die ("could not select the database: <br />". mysql_error();1同样的，每次访问数据库的时候最好能检查可能的错谋并且进行显示。现在我们做好了一切准备工作，可以开始执

53、行sql查询了。构建sql select查询构建sql查询非常容易就是将一个字符吊赋值给变量。这个字符吊就是我们的 sql查询，当然我们要给出有效的sql查询，否则执行这个查询的时候mysql 会返回错误。我们使用$耳"1了作为变量名，这个名字对应其fi的，你也可以选择 任何你喜欢的变量名。这个例子中的sql查询是"select * from books”。 你可以使用字符串连接操作符()来构建查询：执行查询使用mysql_query函数来告诉数据库执行查询。它有两个参数：查询和可选的数 据库连接，返回值是查询结果。我们将查询结果保存在一个变量里，也许你已经 猜到我们要用变

54、量名就是$resulto这里同样有必要检查mysql_query的返回值不 是false来确保查询字符串和数据库连接都没有问题。当数据库查询的时候，所有的结果构成一个结果集。这些结果跟使用mysql命令 行客户端执行同样查询所得到的行一致。要显示这些结果，你需要依次处理这些 行。取结果并显示使用mysql_fetch_row从结果集中取出一行，它的用法如下：array mysql_fetch_row ( resource $result);它的参数是sql查询返回的结果，我们将结果保存在$result中。每次调用它返 回一行数据，直到没有数据为止，这时候它返回falseo这样，我们可以使用 一

55、个循环，在循环内调用mysql_fetch_row并使用一些代码來显示每一行。结果行的所有列都保存在一个数组里，可以方便地进行访问。变量$result_row2 访问结果行的第二个属性(数组的顺序是查询是定义的列的顺序，如果使用 selece * ,那么数组顺序就是表的列的顺序)。取结果的方式去结果的方式不止一种。使用mysql_fetch_arrry可以一次性将所有结果放在一个 数组里。它的参数是查询结果和一个可选的结果绑定方式。如果绑定方式指定为 mysql.assoc,数组中的结果则使用查询中列的名字进行访问。如果指定了 mysql_num,那么就使用从0开始的数字来访问结果。默认使用的

56、方式是 mysql_both,这样返回的数组支持两种类型的访问。mysql_fetch_assoc是使 用mysql_assoc取结果的另外一种方式。关闭连接绝大部分情况下，我们在使用完一个数据库z后要关闭到它的连接。使用 mysql_close来关闭一个数据库，它会告诉php和mysql这个数据库连接已经 不再使用，所使用的所有资源和内存都可以释放。mysql_close($connection)安装pear使用包管理器來管理安装pear模块。是否需要安装包管理取决于你所使 用的php版本。如果你使用的版本是php440或者更新的版本，那么就已经安 装了包管理器。如果你使用的是php5.0,

57、则pear是一个单独的包。我们要用到 的db包是可选的，但是它会被包管理器默认安装。所以，如果你有包管理器， 那么就全搞定了。unix在unix系统下，可以通过在shell （命令行）下执行下而的命令来安装包 管理器：lynx source /1 php这个命令使用的输出（实际就是php源代码）来安装pear, 的输出被传给php命令执行。windows安装完php5后，会有一个pear安装脚本c:phpgo-pear.bato如果你在第二章 没有安装所以文件，那么现在把所有的php文件都解压到c:php下，然后执行

58、 这个批处理文件。创建连接示例db.php文件定义了类dbo参考第5章有关使用类和对彖的更多信息。我们将会 主要使用这个类提供的方法。类db有一个connect方法，我们会使用它来替换 前面使用的connect函数mysql_connecto双冒号（：）表示调用类的函数，如 第6行所示。sconnection=db:connect（"mysql:/$db_username:$db_password$db_host/$db_databasem）; 当调用connect函数的时候，它出创建一个新的数据库连接，保存在变量 sconnection屮。connect函数试图通过传递给它的连接字符串來连接数据库。 连接字符串连接字符吊使用新的格式来表示登录信息，这些信息我们己经通过单独的域提 供：dbtype:/useniame:passwordhost/datahase这个格式看起来也许会有些熟悉，它跟windows的文件共享所使用的连接字符 串非常相似。字符串的第一部分phptype是将pear函数与一般php函数区分 开来的关键部分。phptype域指定要连接的数据库类型，支持的数据库包括ibase. mysqr mssql、mysql> oci8> odbc> pgsql> 和 sybase如果需