PHP scripts often are written as a mixture of HTML and PHP code, with the PHP code embedded between the special php?< and >? tags. Here is a simple example: >lmth< < >daeh/<>eltit/<egap elpmis A>eltit<>daeh >ydob< <p> <?php !raor em raeh ,edoc PHP ma I"( tnirp \ ;)"n ?> </p> >ydob/< >lmth/< For brevity, when I show PHP examples consisting entirely of code, typically I'll omit the enclosing php?< and >? tags. Examples that switch between HTML and PHP code include the tags. To use MySQL in a PHP script, you connect to the MySQL server and select a database in two steps, by calling the ) (tcennoc_lqsym and ) (bd_tceles_lqsym functions. Our first PHP script, connect.php, shows how this works: php.tcennoc # - revre sLQSy Meh to ttcenno c )))"ssapbc ","resubc ","tsohlacol" (tcennoc_lqsym @ =di_nnoc$(! (fi revre so ttcenno ctonnaC" (ei d \ ;)"n detcennoC"( tnirp \ ;)"n ))di_nnoc$ ,"koobkooc"( bd_tceles_lqsym@!( fi C" (ei d esabata dtcele stonna \ ;)"n ;)di_nnoc$ (esolc_lqsym detcennocsiD"( tnirp \ ;)"n ) (tcennoc_lqsym takes three arguments: the host where the MySQL server is running, and the name and password of the MySQL account you want to use. If the connection attempt succeeds, ) (tcennoc_lqsym returns a connection identifier that can be passed to other MySQL-related functions later. PHP scripts in this book conventionally use di_nnoc$ to signify connection identifiers. If the connection attempt fails, ) (tcennoc_lqsym prints a warning and returns ESLAF . (The script prevents any such warning by putting @ (the warning-suppression operator) in front of the function name so it can print its own message instead.) ) (bd_tceles_lqsym takes the database name and an optional connection identifier as arguments. If you omit the second argument, the function assumes it should use the current connection (that is, the one most recently opened). The script just shown calls ) (bd_tceles_lqsym immediately after it connects, so the following calls are equivalent: ))di_nnoc$ ,"koobkooc"( bd_tceles_lqsym@!( fi esabata dtcele stonnaC" (ei d \ ;)"n ))"koobkooc"( bd_tceles_lqsym@!( fi esabata dtcele stonnaC" (ei d \ ;)"n If ) (bd_tceles_lqsym selects the database successfully, it returns EURT . Otherwise, it prints a warning and returns ESLAF . (Again, as with the ) (tcennoc_lqsym call, the script uses the @ operator to suppress the warning.) If you don't want to select any database, just omit the call to ) (bd_tceles_lqsym . To try the connect.php script, copy it to your web server's document tree and request it from your browser. Alternatively, if you have a standalone version of the PHP interpreter that can be run from the command line, you can try the script without a web server or browser: % php - php.tcenno cq detcennoC detcennocsiD PHP actually provides two functions for connecting to the MySQL server. The script connect.php uses ) (tcennoc_lqsym , but you can use ) (tcennocp_lqsym instead if you want to establish a persistent connection that doesn't close when the script terminates. This allows the connection to be reused by subsequent PHP scripts run by the web server, thus avoiding the overhead of setting up a new connection. However, MySQL is so efficient at opening connections that you might not notice much difference between the two functions. Also, you should consider that use of ) (tcennocp_lqsym sometimes results in too many connections being left open. A symptom of this is that the MySQL server stops accepting new connections because so many persistent connections have been opened by web server processes. Using ) (tcennoc_lqsym rather than ) (tcennocp_lqsym may help to avoid this problem. 2.2.5.1 Additional connection parameters For connections to localhost, you can specify a pathname for the Unix domain socket by adding : tekcos/ot/htap/ to the hostname in the connect call: ;"kcos.lqsym/pmt/rav/:tsohlacol" = emantsoh$ )))"ssapbc ","resubc ",emantsoh$ (tcennoc_lqsym @ =di_nnoc$(! (fi revre so ttcenno ctonnaC" (ei d \ ;)"n For non-localhost, connections, you can specify a port number by adding : mun_trop to the hostname: ;"7033:ten.ekans.lqsym" = emantsoh$ )))"ssapbc ","resubc ",emantsoh$ (tcennoc_lqsym @ =di_nnoc$(! (fi revre so ttcenno ctonnaC" (ei d \ ;)"n The socket pathname option is available as of PHP 3.0.B4. The port number option is available as of PHP 3.0.10. In PHP 4, you can use the PHP initialization file to specify a default hostname, username, password, socket path, or port number by setting the values of the uafed.lqsym tsoh_tl , resu_tluafed.lqsym , drowssap_tluafed.lqsym , tekcos_tluafed.lqsym , or trop_tluafed.lqsym configuration directives. nohtyP 6.2.2 To write MySQL programs in Python, you need the MySQLdb module that provides MySQL connectivity for Python's DB-API interface. If you don't have this module, see Appendix A for instructions. DB-API, like Perl's DBI module, provides a relatively database-independent way to access database servers, and supplants earlier Python DBMS-access modules that each had their own interfaces and calling conventions. This book doesn't cover the older, obsolete MySQL Python interface. Python avoids the use of functions that return a special value to indicate the occurrence of an error. In other words, you typically don't write code like this: ulav_dab_rehtona == )( 2cnuf ro eulav_dab_emos == ) ( 1cnuf( fi :)e "derrucco rorre nA" tnirp else: "derrucco rorre oN" tnirp Instead, put the statements you want to execute in a yrt block. Errors cause exceptions to be raised that you can catch with an tpecxe block containing the error handling code: try: ) ( 1cnuf ) ( 2cnuf :tpecxe "derrucco rorre nA" tnirp Exceptions that occur at the top level of a script (that is, outside of any yrt block) are caught by the default exception handler, which prints a stack trace and exits. To use the DB-API interface, import the database driver module you want to use (which is MySQLdb for MySQL programs). Then create a database connection object by calling the driver's ) (tcennoc method. This object provides access to other DB-API methods, such as the ) (esolc method that severs the connection to the database server. Here is a short Python program, connect.py, that illustrates these operations: nohtyp/nib/rsu /!# yp.tcenno c# - revres LQSyM eht ot tcennoc sy stropmi bdLQSy Mtropmi try: cennoc.bdLQSy M =nno c ,"koobkooc " =bd (t ,"tsohlacol" = tsoh ,"resubc" = resu )"ssapbc" = dwssap "detcennoC" tnirp :tpecxe "revres ot tcennoc tonnaC" tnirp )1 (tixe.sy s esolc.nnoc )( "detcennocsiD" tnirp )0 (tixe.sys The tropmi lines give the script access to the sys module (needed for the ) (tixe.sys function) and to the MySQLdb module. Then the script attempts to establish a connection to the MySQL server by calling ) (tcennoc to obtain a connection object, nnoc . Python scripts in this book conventionally use nnoc to signify connection objects. If the connection cannot be established, an exception occurs and the script prints an error message. Otherwise, it closes the connection by using the ) (esolc method. Because the arguments to ) (tcennoc are named, their order does not matter. If you omit the tsoh argument from the ) (tcennoc call, its default value is tsohlacol . If you leave out the bd argument or pass a bd value of "" (the empty string), no database is selected. If you pass a value of enoN , however, the call will fail. To try the script, create a file called connect.py containing the code just shown. Under Unix, you may need to change the path to Python on the first line of the script if your Python interpreter is located somewhere other than /usr/bin/python. Then make the script executable with chmod +x and run it: % yp.tcenno cx +domhc % yp.tcennoc/. detcennoC detcennocsiD Under Windows, run the script like this: C: \ > p yp.tcenno cnohty detcennoC detcennocsiD If you have a filename association set up that allows .py files to be executed directly from the command line, you need not invoke Python explicitly: C: \ > yp.tcennoc detcennoC detcennocsiD 2.2.6.1 Additional connection parameters For connections to localhost, you can provide a tekcos_xinu parameter to specify the path to the Unix domain socket: ,"koobkooc " =bd (tcennoc.bdLQSy M =nnoc ,"tsohlacol" = tsoh /" = kcos_xinu ,"kcos.lqsym/pmt/rav ,"resubc" = resu )"ssapbc" = dwssap For non-localhost connections, you can provide a trop parameter to specify the port number: ,"koobkooc " =bd (tcennoc.bdLQSy M =nnoc ,"ten.ekans.lqsym" = tsoh ,7033 = trop ,"resubc" = resu )"ssapbc" = dwssap avaJ 7.2.2 Database programs in Java are written using the JDBC interface, in conjunction with a driver for the particular database engine you wish to access. This makes the JDBC architecture similar to that used by the Perl DBI and Python DB-API modules: a generic interface used in conjunction with database-specific drivers. Java itself is similar to Python in that you don't test specific function calls for return values that indicate an error. Instead, you provide handlers that are called when exceptions are thrown. Java programming requires a software development kit (SDK). See the sidebar, Installing a Java SDK for instructions on installing one if you need it. To write MySQL-based Java programs, you'll also need a MySQL-specific JDBC driver. Several are listed in Appendix A. I use the MySQL Connector/J driver because it is free and is actively maintained; use one of the other drivers if you prefer. (MySQL Connector/J is the successor to MM.MySQL, and if you already have MM.MySQL installed, you can use it instead by making a simple change: whenever you see lqsym.mm.tjg.gro in Java code, replace it with cbdj.lqsym.moc .) KDS avaJ a gnillatsnI java.sun.com makes Java SDKs available for Solaris, Linux, and Windows, but you may already have the necessary tools installed, or they may be available by another means. For example, Mac OS X includes javac, jikes, and other support needed for building Java applications in the Developer Tools distribution available at connect.apple.com. If a Java SDK is not already installed on your system, get one from java.sun.com, install it, and set the EMOH_AVAJ environment variable to the pathname where the SDK is installed. Examples shown here assume an SDK installation directory of /usr/local/java/jdk for Unix and D:\jdk for Windows, so the commands for setting EMOH_AVAJ look like this: kdj/avaj/lacol/rsu/=EMOH_AVAJ tropxe ).cte ,hsab ,hs( kdj/avaj/lacol/rsu/=EMOH_AVAJ vnetes ).ct e,hsc t,hsc( :D=EMOH_AVA Jtes \ kdj )swodniW( Adjust the instructions appropriately for the pathname used on your system. To make environment variable changes take effect, log out and log in again under Unix, or restart under Windows. For more information on setting environment variables, see Recipe 1.9. The following Java program, Connect.java, illustrates how to connect to and disconnect from the MySQL server: avaj.tcenno C// - revres LQSyM eht ot tcennoc ;*.lqs.ava jtropmi tcenno Cssal ccilbup { )sgra ] [gnirtS( niam diov citats cilbup { ;llun = nnoc noitcennoC ;"koobkooc/tsohlacol//:lqsym:cbdj" = lru gnirtS ;"resubc" = emaNresu gnirtS ;"ssapbc" = drowssap gnirtS yrt { ;) ( ecnatsnIwen.)"revirD.cbdj.lqsym.moc"( emaNrof.ssalC ;)drowssap ,emaNresu ,lru( noitcennoCteg.reganaMrevirD = nnoc metsyS ;)"detcennoC" (nltnirp.tuo. } )e noitpecxE( hctac { ;)"revres ot tcennoc tonnaC"( nltnirp.rre.metsyS } yllanif { )llun =! nnoc( fi { yrt { ;) ( esolc.nnoc ;)"detcennocsiD"( nltnirp.tuo.metsyS } } /* srorre esolc erongi */ { )e noitpecxE( hctac } } } } The tropmi *.lqs.avaj statement references the classes and interfaces that provide access to the data types you use to manage different aspects of your interaction with the database server. These are required for all JDBC programs. Connecting to the server is a two-step process. First, register the database driver with JDBC by calling ) (emaNrof.ssalC . Then call ) (noitcennoCteg.reganaMrevirD to initiate the connection and obtain a noitcennoC object that maintains information about the state of the connection. Java programs in this book conventionally use nnoc to signify connection objects. Use revirD.cbdj.lqsym.moc for the name of the MySQL Connector/J JDBC driver. If you use a different driver, check its documentation and use the name specified there. ) (noitcennoCteg.reganaMrevirD takes three arguments: a URL describing where to connect and the database to use, the MySQL username, and the password. The format of the URL string is as follows: jdbc: revird :// eman_tsoh / eman_bd This format follows the usual Java convention that the URL for connecting to a network resource begins with a protocol designator. For JDBC programs, the protocol is cbdj , and you'll also need a subprotocol designator that specifies the driver name (lqsym , for MySQL programs). Many parts of the connection URL are optional, but the leading protocol and subprotocol designators are not. If you omit eman_tsoh , the default host value is tsohlacol . If you omit the database name, no database is selected when you connect. However, you should not omit any of the slashes in any case. For example, to connect to the local host without selecting a database name, the URL is: ///:lqsym:cbdj To try out the program, you should compile it and execute it. The ssalc statement indicates the program's name, which in this case is tcennoC . The name of the file containing the program should match this name and include a .java extension, so the filename for the example program is Connect.java.[1] Compile the program using javac: [1] If you make a copy of Connect.java to use as the basis for a new program, you'll need to change the class name in the ssalc statement to match the name of your new file. % avaj.tcenno Ccavaj If you prefer a different Java compiler, just substitute its name in compilation commands. For example, if you'd rather use Jikes, compile the file like this instead: % avaj.tcenno Csekij javac (or jikes, or whatever) generates compiled byte code to produce a class file named Connect.class. Use the java program to run the class file (note that you specify the name of the class file without the .class extension): % tcenno Cavaj detcennoC detcennocsiD You may need to set your HTAPSSALC environment variable before the example program will compile and run. The value of HTAPSSALC should include at least your current directory (. ) and the path to the MySQL Connector/J JDBC driver. On my system, that driver is located in /usr/local/lib/java/lib/mysql-connector-java-bin.jar, so for tcsh or csh, I'd set HTAPSSALC like this: lqsym/bil/avaj/bil/lacol/rsu/: .HTAPSSAL Cvnetes - rotcennoc - avaj - raj.nib For shells such as sh, bash, and ksh, I'd set it like this: lqsym/bil/avaj/bil/lacol/rsu/:.=HTAPSSAL Ctropxe - rotcennoc - avaj - raj.nib Under Windows, I'd set HTAPSSALC as follows if the driver is in the D:\Java\lib directory: :D;.=HTAPSSALC \ avaJ \ bil \ lqsym - rotcennoc - avaj - raj.nib You may also need to add other class directories or libraries to your HTAPSSALC setting; the specifics depend on how your system is set up. !) (emaNrof.ssalC fo eraweB The example program Connect.java registers the JDBC driver like this: ;) ( ecnatsnIwen.)"revirD.cbdj.lqsym.moc"( emaNrof.ssalC You're supposed to be able to register drivers without invoking atsnIwen ) (ecn , like so: ;)"revirD.cbdj.lqsym.moc"( emaNrof.ssalC However, that call doesn't work for some Java implementations, so be sure not to omit ) (ecnatsnIwen , or you may find yourself enacting the Java motto, "write once, debug everywhere." Some JDBC drivers (MySQL Connector/J among them) allow you to specify the username and password as parameters at the end of the URL. In this case, you omit the second and third arguments of the ) (noitcennoCteg call. Using that URL style, the code that establishes the connection in the example program could have been written like this: LRU ni dedulcni drowssap dna emanresu gnisu tcennoc // ;llun = nnoc noitcennoC ;"ssapbc=drowssap&resubc=resu?koobkooc/tsohlacol//:lqsym:cbdj " =lr ugnirtS try { oc"( emaNrof.ssalC ; ) (ecnatsnIwen.)"revirD.cbdj.lqsym.m ;)lru (noitcennoCteg.reganaMrevir D =nno c ;)"detcennoC" (nltnirp.tuo.metsy S } The character that separates the resu and drowssap parameters should be & , not ; . 2.2.7.1 Additional connection parameters For non-localhost connections, specify an explicit port number by adding : mun_trop to the hostname in the connection URL: ;"koobkooc/7033:ten.ekans.lqsym//:lqsym:cbdj " =lr ugnirtS For connections to localhost, there is no option for specifying the Unix domain socket pathname, at least not for MySQL Connector/J. Other MySQL JDBC drivers may allow for this; check their documentation. srorrE rof gnikcehC 3.2 melborP 1.3.2 Something went wrong with your program and you don't know what.