This script connects to the MySQL database "classdb" (with information about CS210 class members), and runs a query to get all the data in the entire table. It displays some of the data in a tabular layout, including user login name, full name, and freeshell web site URL. The URL is clickable, and opens that web site in a separate browser window or tab.
(This listing is edited slightly to hide the MySQL password.)
1 <html>
2 <head>
3 <title>HTML / PHP / MySQL demo — 210 class roster database</title>
4 <style type="text/css">
5 badd {color:red; font-weight:bold}
6 </style>
7 </head>
8 <body>
9 <strong>L. J. Jaeckel</strong><br />
10 <strong>CMPSC 210 — Linux System Administration</strong><br />
11 <strong>March 5, 2011</strong><br />
12
13 <center><h1>HTML / PHP / MySQL demo</h1></center>
14 <center><h2>Display some of the data in MySQL 210 class roster database</h2></center>
15 <br />
16
17 <?php
18 $hostnm = `hostname` ;
19 $scr_fname = $_SERVER['SCRIPT_NAME'] ;
20 $srvname = $_SERVER['SERVER_NAME'] ;
21
22 // Define the MySQL database access parameters according to
23 // which of the known host machines we are running on.
24 // Some machines provide a meaningful SERVER_NAME, while some
25 // other just say "localhost", in which case we look at
26 // the host name via the `hostname` program.
27
28 if ( strpos($srvname, 'doggie.freeshell.org') !== FALSE ) {
29 $db_host = "localhost" ;
30 $db_user = "doggie" ;
31 $db_psw = "xxxxxxxx" ;
32 $db_name = "classdb" ;
33 }
34 else if ( strpos($srvname, 'csrh1.yosemite.edu') !== FALSE ) {
35 $db_host = "localhost" ;
36 $db_user = "root" ;
37 $db_psw = "xxxxxxxx" ;
38 $db_name = "jaeckell" ;
39 }
40 else if ( strpos($hostnm, 'frazzle') !== FALSE ) {
41 $db_host = "localhost" ;
42 $db_user = "jay" ;
43 $db_psw = "xxxxxxxx" ;
44 $db_name = "classdb" ;
45 }
46 else if ( strpos($hostnm, 'debian5vm') !== FALSE ) {
47 $db_host = "localhost" ;
48 $db_user = "jeremy" ;
49 $db_psw = "xxxxxxxx" ;
50 $db_name = "classdb" ;
51 }
52 else {
53 $db_host = "localhost" ;
54 $db_user = "xxxxxxxx" ;
55 $db_psw = "xxxxxxxx" ;
56 $db_name = "xxxxxxxx" ;
57 }
58
59 echo "This demonstration script file is:\n" ;
60 echo "<a href=\"roster210_php_list.html\"><strong>$scr_fname</strong></a> " ;
61 echo "on $srvname<br />\n" ;
62 echo "Click the file name above to see a listing of this script.<br /><br />\n\n" ;
63
64 // Connect to the MySQL database, giving host name, MySQL user name, and password:
65
66 echo "Note: This script displays a lot of messages telling step-by-step what it's\n" ;
67 echo "doing because it's a DEMO.<br /><br />\n\n" ;
68 echo "Connecting to MySQL database \"$db_name\" on server \"$db_host\" as " .
69 "user \"$db_user\" . . .<br /><br />\n\n" ;
70 if ( $db_conn = mysql_connect( $db_host, $db_user, $db_psw ) ) {
71
72 echo "Database connection established to MySQL server on \"$db_host\"!<br /><br />\n\n" ;
73
74 // Next, select the database we want to work with:
75
76 echo "Next, select the database \"$db_name\" to work with . . .<br /><br />\n\n" ;
77 if ( mysql_select_db( $db_name ) ) {
78
79 // Next, create the query we want to run: select * from users
80 // and run it. Note, queries run this way do not end with a semi-colon:
81
82 $db_query = "select * from users" ;
83 echo "Running the query:   <font face=\"courier\">$db_query</font>   . . .<br /><br />\n\n" ;
84 if ( $db_result = mysql_query( $db_query ) ) {
85
86 // If we got any rows, retrieve them and display some of the data there:
87
88 $nrows = mysql_num_rows ( $db_result ) ;
89 if ( $nrows > 0 ) {
90 echo "Query returned $nrows row" . ( $nrows == 1 ? "" : "s" ) . " of data.<br /><br />\n\n" ;
91
92 // Get the result records, one row at a time, and format them
93 // into a tabular display:
94
95 echo " <center>\n" ;
96 echo " <strong>Web links will open in a new browser window or tab.</strong><br /><br />\n\n" ;
97 echo " <table cellpadding=5 border=1>\n" ;
98 echo " <tr bgcolor=\"#FFCCCC\">\n" ;
99 echo " <th>User login</th>" ;
100 echo " <th>Full user name</th>" ;
101 echo " <th>Freeshell web site URL</th>\n" ;
102 echo " <th>Freeshell e-mail address</th>\n" ;
103 echo " </tr>\n" ;
104 $winder = 0 ;
105
106 while ( $db_row = mysql_fetch_array( $db_result ) ) {
107 $winder = $winder + 1 ;
108 echo " <tr>\n" ;
109 echo " <td>" . $db_row['username'] . "</td>\n" ;
110 echo " <td>" . $db_row['firstname'] . " " . $db_row['lastname'] . "</td>\n" ;
111 $sdf_url = $db_row['sdf_website'] ;
112 echo " <td><a href=\"http://" . $sdf_url . "\" target=\"x" . $winder . "x\">" . $sdf_url . "</a></td>\n" ;
113 echo " <td>" . $db_row['sdf_email'] . "</td>\n" ;
114 echo " </tr>\n" ;
115 }
116
117 echo " </table>\n" ;
118 echo " </center>\n" ;
119 echo " <br />\n" ;
120
121 }
122 else {
123
124 // Query succeeded technically, but returned 0 rows of data:
125
126 echo "<badd>Query succeeded (technically), but returned ZERO rows of data!</badd><br /><br />\n\n";
127 }
128
129 }
130 else {
131
132 // Query failed:
133
134 echo "<badd>mysql_query( \"$db_query\" ) failed!</badd><br />\n" ;
135 echo mysql_error() ;
136 echo "<br /><br />\n\n" ;
137 }
138
139 }
140 else {
141
142 // If mysql_select_db() failed to select the desired database:
143
144 echo "<badd>mysql_select_db( \"$db_name\" ) failed to select the desired database!</badd><br />\n" ;
145 echo mysql_error() ;
146 echo "<br /><br />\n\n" ;
147 }
148
149 // Finally, close the connection to the database:
150
151 echo "Closing connection to MySQL server.<br /><br />\n\n" ;
152 mysql_close( $db_conn ) ;
153
154 }
155 else {
156
157 // If mysql_connect() failed to connect:
158
159 echo "<br /><badd>Connection failed!</badd><br />\n" ;
160 echo mysql_error() ;
161 echo "<br /><br />\n\n" ;
162 }
163
164
165 ?>
166
167 <br />
168 <hr />
169 <br />
170 <strong>End of this demo</strong><br />
171 <br />
172 (What you see is what you get!)<br />
173 <br />
174 <hr />
175 </body>
176 </html>
177