?? mysql-script.html
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR/REC-html40/strict.dtd"><html><head><title>Interfacing MySQL with PHP and Perl DBI</title>
<link rel="stylesheet" href="mysql-script_files/home.css" type="text/css">
<link rel="stylesheet" href="mysql-script_files/webpub.css" type="text/css">
<style type="text/css">
.highlight { background: #FFFF66 }
</style></head>
<body bgcolor="#ffffff">
<a href="http://www.washington.edu/"><img id="toplogo" src="mysql-script_files/UWlogo150p.gif" alt="University of Washington" border="0"></a>
<div id="toolbar">
<span class="l1text"><a href="http://www.washington.edu/home/search.html">Search</a> |
<a href="http://www.washington.edu/home/directories.html">Directories</a> |
<a href="http://www.lib.washington.edu/research/">Reference
Tools</a></span></div>
<!--Section Banner Table -->
<div class="bannerwrapper">
<div id="topbanner"><img src="mysql-script_files/rib0.gif" alt="" height="16" width="6"></div>
</div>
<div id="crumbs"><span class="l1text"><a href="http://www.washington.edu/">UW Home</a> > <a href="http://www.washington.edu/uwin/">UWIN</a> > <a href="http://www.washington.edu/computing/">Computing and Networking</a> > <a href="http://www.washington.edu/computing/web/">Web</a> > <a href="http://www.washington.edu/computing/web/publishing/">Web Publishing</a> > <a href="http://www.washington.edu/computing/web/publishing/mysql.html">Using MySQL</a></span> </div><div class="forceclear"></div>
<table style="table-layout: fixed;" align="right" bgcolor="#ffffcc" border="1" cellpadding="5" cellspacing="0">
<tbody><tr>
<td width="120">
<p>This article applies to the following UW Web servers:</p>
<center>
<a href="http://faculty.washington.edu/">faculty</a><br>
<a href="http://staff.washington.edu/">staff</a><br>
<a href="http://students.washington.edu/">students</a><br>
<a href="http://courses.washington.edu/">courses</a><br>
<a href="http://depts.washington.edu/">depts</a><br>
</center>
</td>
</tr>
</tbody></table>
<h1>Interfacing MySQL with PHP and Perl DBI</h1>
<h4>Summary</h4>
<p>Perl and PHP can both be used to write dynamic Web pages that can
connect and communicate with a MySQL server. <a href="http://www.php.net/">PHP</a> has built-in functions for the task,
while Perl has a module called DBI for interfacing with databases like
MySQL. This article demonstrates some simple examples of how to use PHP
and Perl as separate front-ends for MySQL.</p>
<p><b>Note:</b> You must <a href="http://www.washington.edu/computing/web/publishing/mysql-install.html">install and start your MySQL server</a> before you can retrieve data from it.</p>
<!--<p><b>Note:</b> This page assumes some familiarity with SQL commands, PHP, and Perl. If you're not familiar with SQL commands,it may help to take a look at <a href="mysql-client">Using SQL (Structured Query Language) with the Command-Line MySQL Client</a>.For an introduction to PHP, see C&C's <a href="https://www.washington.edu/computing/web/publishing/php.html">PHP Hypertext Preprocessor</a> article.For help with Perl, visit the <a href="http://www.perldoc.com/">Official Perl Documentation Site</a>.</p>-->
<p><i>Included on this page:</i></p>
<ul>
<li><a href="#1">MySQL Scripting With PHP</a>
</li><li><a href="#2">MySQL Scripting With Perl DBI</a>
</li><li><a href="#3">Resources</a>
</li></ul>
<a name="1"><h4>MySQL Scripting With PHP</h4></a>
<!--<p>For those just starting out, PHP may be the easiest way to learn MySQL scripting.To try your hand at a PHP/MySQL script, copy and paste the following code into a file called "example.php", make the appropriate substitutions for thehighlighted portions, and then upload the file to a location in your Web directory.</p>-->
<p>Here is an example PHP script that connects to your MySQL server and prints out the first row of the <tt>user</tt> table from the
<tt>mysql</tt> database:</p>
<table class="code">
<tbody><tr><td>
<pre><html>
<body>
<?php
// connect and select a database
$link = mysql_connect("<i class="highlight">host</i>.u.washington.edu:<i class="highlight">port number</i>", "root", "<i class="highlight">rootpassword</i>")
or die ("Couldn't connect: Check to make sure that:<br>" .
"<ul><li>your MySQL server is running</li>" .
"<li>you used the correct hostname (<tt>vergil/ovid</tt>)<li>" .
"<li>you didn't forget the 'u' in the hostname</li>" .
"<li>you added a colon with your port number after the hostname</li>" .
"<li>you used the username 'root'</li>" .
"<li>you used the correct root password</li>" .
"<li>you didn't forget to close a set of quotation marks</li><br><br>");
print "Connected successfully.<br>";
$db = "mysql";
mysql_select_db($db) or die("Could not select the database '" . $db . "'. Are you sure it exists?");
// perform an SQL query
$query = "SELECT * FROM user";
$result = mysql_query($query) or die("Query failed");
// print the result of the first row (row counting starts at zero)
printf("Host: %s<br>\n", mysql_result($result, 0, "Host"));
printf("User: %s<br>\n", mysql_result($result, 0, "User"));
printf("Grant privilege: %s<br>\n", mysql_result($result, 0, "Grant_priv"));
// free result set
mysql_free_result($result);
// close the connection
mysql_close($link);
?>
</body>
</html>
</pre>
</td></tr>
</tbody></table>
<p>Be sure to replace <i>host</i> with either <tt>vergil</tt> or <tt>ovid</tt>, <i>port number</i> with the port your MySQL server is running on,
and <i>rootpassword</i> with the root password you selected when you set up your MySQL server.</p>
<p>Save the file as <tt>"mysql-test.php"</tt> in your <tt>public_html</tt> directory. When you view the file in a Web browser, you should see something similar to the following:</p>
<table class="code">
<tbody><tr><td>
Connected successfully.<br>
Host: localhost<br>
User: root<br>
Grant privilege: Y
</td></tr>
</tbody></table>
<p>This tells you that your script successfully connected to your MySQL
server. It then selected the database "mysql" that you configured
when you installed MySQL, and then output the host, user, and grant
privilege of the first row in the "user" table.
Not very exciting, but if you haven't created any databases yet, this
default mysql database is the only one you've got. To create your own,
see
<a href="http://www.washington.edu/computing/web/publishing/mysql-client.html">Using SQL (Structured Query Language) with the Command-Line MySQL Client</a>.</p>
<h4><a name="2">MySQL Scripting With Perl DBI</a></h4>
<!--<p>DBI (Database Interface) is a Perl module that was written to simplify interfacing with databases such as MySQL.While PHP's built-in functions are arguably more convenient than Perl's DBI module, Perl is an industry standard,and thus is more widely used. In order to use Perl DBI, you must first have installed on your system.The DBI module is already installed on Homer and Dante; if you're working on a different Unix or Linuxaccount, you can verify that DBI is installed by typing the following at the command prompt:</p><table class="code"><tr><td><code>% perldoc DBI</code></td></tr></table><p> A man page will come up if you have the DBI module; if you don't, you can download and install it from the<a href="http://www.perl.com/CPAN-local/modules/by-module/DBI/">perl.com DBI module page</a>.</p><p> You can then copy and paste the following sample program into a file called "example.cgi" and upload the file to alocation in your Web directory. Be sure to make the appropriate substitutions for the highlighted portions of the script.</p>-->
<p>Here is an example CGI program written with Perl DBI; the script that connects to your MySQL database and prints out
the privileges of each user in the <tt>user</tt> table from the <tt>mysql</tt> database:</p>
<table class="code">
<tbody><tr><td><pre>
#!/usr/local/bin/perl
use DBI;
print "Content-type:text/html\n\n";
$db_handle = DBI->connect("dbi:mysql:database=mysql;host=<i class="highlight">hostname</i>.u.washington.edu:<i class="highlight">port number</i>;user=root;password=<i class="highlight">rootpassword</i>")
or die "Couldn't connect to database: $DBI::errstr\n";
$sql = "SELECT * FROM user";
$statement = $db_handle->prepare($sql)
or die "Couldn't prepare query '$sql': $DBI::errstr\n";
$statement->execute()
or die "Couldn't execute query '$sql': $DBI::errstr\n";
while ($row_ref = $statement->fetchrow_hashref())
{
print "User <b>$row_ref->{User}</b> has privileges on <b>$row_ref->{Host}</b>.<br>";
}
$db_handle->disconnect();
</pre></td></tr></tbody></table>
<p><b>Tip:</b> Do not put quotation marks around any single element in the parameter to <tt>DBI->connect</tt>; there should only be one set of quotation marks around the entire argument. Be sure to replace <i>hostname</i> with either <tt>vergil</tt> or <tt>ovid</tt>, <i>port number</i> with the port your MySQL server is running on, and <i>rootpassword</i> with the root password you selected when you set up your MySQL server.</p>
<p>Save the file as <tt>"mysql-pl-test.cgi"</tt> in your <tt>public_html</tt> directory, and <a href="http://www.washington.edu/computing/unix/permissions.html">set its file permissions to 755</a>. When you view the file in a Web browser, you should see something similar to the following:</p>
<table class="code">
<tbody><tr><td>
User <b>root</b> has privileges on <b>localhost</b>.<br>
User <b>root</b> has privileges on <b>%.washington.edu</b>.
</td></tr>
</tbody></table>
<p>This tells you that the DBI module successfully connected to your MySQL server,
selected the database "mysql" that you configured when you installed MySQL,
and output the privileges of all users in the "user" table.</p>
<!-- RESOURCES -->
<h4><a name="3">Resources</a></h4>
The scripts in this article demonstrate the most basic MySQL connection and query functions.
For more information about PHP and DBI module functions, refer to the following links.
<p><b>PHP</b><br>
PHP.net has official <a href="http://www.php.net/manual/en/ref.mysql.php">MySQL Documentation</a>;
the Webmonkey <a href="http://hotwired.lycos.com/webmonkey/99/21/index2a.html">PHP/MySQL Tutorial</a>
is a gentle and methodical introduction to PHP scripting for MySQL.</p>
<p><b>Perl DBI</b><br>
Perl.com has a very good <a href="http://www.perl.com/pub/a/1999/10/DBI.html">Short Guide to DBI</a>.</p>
<div id="footer"><div id="footerseal">
<a href="http://www.washington.edu/"><img src="mysql-script_files/footersealW.gif" alt="UW Seal"></a>
</div>
<div id="addressright"><address>
A service provided by<br>
<a href="http://depts.washington.edu/cac/"><img src="mysql-script_files/CnC_full_UW_Purple-1.gif" alt="Computing & Communications" border="0" height="14" width="202"></a>
</address>
</div>
<div id="address"><address>
<a href="http://depts.washington.edu/cac/">Computing
& Communications</a><br>
help@cac.washington.edu<br>
Modified: May 16, 2005
</address>
</div>
</div>
<!--Created by chtml on Sep 15, 2006 1:01pm--></body></html>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -