2 if ( empty ( $PHP_SELF ) && ! empty ( $_SERVER ) &&
3 ! empty ( $_SERVER['PHP_SELF'] ) ) {
4 $PHP_SELF = $_SERVER['PHP_SELF'];
6 if ( ! empty ( $PHP_SELF ) && preg_match ( "/\/includes\//", $PHP_SELF ) ) {
7 die ( "You can't access this file directly!" );
10 // This file contains some convenient functions for editing rows
12 // You need to define the tables (typically this is done in tables.php).
14 // $tablear - array that defines table (see tables.php)
15 // $fieldname - name of field
16 function dbtable_get_field_index ( $tablear, $fieldname ) {
18 for ( $j = 0; $j < count ( $tablear ); $j++ ) {
19 if ( $tablear[$j]["name"] == $fieldname ) {
20 //echo "found $fieldname $j<br />";
24 $error = "dbtable_get_field_index: Invalid fieldname \"$fieldname\".";
29 // Create a table for editing a database table entry
30 // $tablear - array that defines table (see tables.php)
31 // $valuesar - array of current values
32 // $action - URL to post form to (or "" for display-only)
33 // $actionlabel - Value to put on submit form ("Save", etc.)
34 // $hidden - array of hidden form variables (name1,value1,name2,value2,...)
35 function dbtable_to_html ( $tablear, $valuesar, $action="", $formname="",
36 $actionlabel="", $hidden="" ) {
38 $ret = "<table style=\"border-width:0px;\" cellspacing=\"0\" cellpadding=\"0\">" .
39 "<tr><td style=\"background-color:#000000;\">" .
40 "<table style=\"border-width:0px; width:100%;\" cellspacing=\"1\" cellpadding=\"2\">" .
41 "<tr><td style=\"width:100%; background-color:$CELLBG;\">" .
42 "<table style=\"border-width:0px; width:100%;\">\n";
43 if ( ! is_array ( $tablear ) ) {
44 return "Error: dbtable_to_html parameter 1 is not an array!\n<br />\n";
46 if ( $action != "" ) {
47 $ret .= "<form action=\"$action\" method=\"post\"";
48 if ( $formname != "" )
49 $ret .= " name=\"$formname\"";
51 if ( is_array ( $hidden ) ) {
52 for ( $i = 0; $i < count ( $hidden ); $i += 2 ) {
53 $ret .= "<input type=\"hidden\" name=\"" . $hidden[$i] .
54 "\" value=\"" . $hidden[$i+1] . "\" />";
58 for ( $i = 0; $i < count ( $tablear ); $i++ ) {
59 if ( ! empty ( $tablear[$i]["hide"] ) )
61 if ( ! empty ( $action ) && ! empty ( $tablear[$i]["calculated"] ) )
63 $ret .= "<tr><td style=\"vertical-align:top;\">";
64 if ( ! empty ( $tablear[$i]["prompt"] ) ) {
66 if ( ! empty ( $tablear[$i]["tooltip"] ) )
67 $ret .= " class=\"tooltip\" title=\"" . $tablear[$i]["tooltip"] . "\"";
68 $ret .= ">" . $tablear[$i]["prompt"] . ":</b></td>\n";
70 $ret .= " </td>";
72 $ret .= "<td style=\"vertical-align:top;\">";
73 if ( empty ( $tablear[$i]["noneditable"] ) && ! empty ( $action ) ) {
74 if ( $tablear[$i]["type"] == "text" ||
75 $tablear[$i]["type"] == "int" || $tablear[$i]["type"] == "float" ) {
76 $ret .= "<input type=\"text\" name=\"" . $tablear[$i]["name"] .
78 if ( ! empty ( $tablear[$i]["maxlength"] ) )
79 $ret .= " maxlength=\"" . $tablear[$i]["maxlength"] . "\"";
80 if ( ! empty ( $tablear[$i]["length"] ) )
81 $ret .= " size=\"" . $tablear[$i]["length"] . "\"";
82 if ( ! empty ( $valuesar[$i] ) )
83 $ret .= " value=\"" . htmlspecialchars ( $valuesar[$i] ) . "\"";
85 } else if ( $tablear[$i]["type"] == "boolean" ) {
86 $ret .= "<input type=\"radio\" value=\"Y\" name=\"" . $tablear[$i]["name"] . "\"";
87 if ( $valuesar[$i] == "Y" )
88 $ret .= " checked=\"checked\"";
89 $ret .= ">" . translate("Yes") . " ";
90 $ret .= "<input type=\"radio\" value=\"N\" name=\"" . $tablear[$i]["name"] . "\"";
91 if ( $valuesar[$i] != "Y" )
92 $ret .= " checked=\"checked\"";
93 $ret .= ">" . translate("No");
94 } else if ( $tablear[$i]["type"] == "date" ) {
95 $ret .= date_selection_html ( $tablear[$i]["name"], $valuesar[$i] );
96 } else if ( $tablear[$i]["type"] == "dbdate" ) {
98 $y = substr ( $valuesar[$i], 0, 4 );
99 $m = substr ( $valuesar[$i], 5, 2 );
100 $d = substr ( $valuesar[$i], 8, 2 );
101 $date = sprintf ( "%04d%02d%02d", $y, $m, $d );
102 $ret .= date_selection_html ( $tablear[$i]["name"], $date );
104 $ret .= "(type " . $tablear[$i]["type"] . " not supported)";
107 if ( ! empty ( $valuesar[$i] ) ) {
108 if ( $tablear[$i]["type"] == "text" ||
109 $tablear[$i]["type"] == "int" || $tablear[$i]["type"] == "float" ) {
110 $ret .= htmlentities ( $valuesar[$i] );
111 } else if ( $tablear[$i]["type"] == "boolean" ) {
112 if ( $valuesar[$i] == "Y" || empty ( $valuesar[$i] ) )
113 $ret .= translate("Yes");
115 $ret .= translate("No");
116 } else if ( $tablear[$i]["type"] == "date" ) {
117 $ret .= date_to_str ( $valuesar[$i] );
118 } else if ( $tablear[$i]["type"] == "dbdate" ) {
119 $y = substr ( $valuesar[$i], 0, 4 );
120 $m = substr ( $valuesar[$i], 5, 2 );
121 $d = substr ( $valuesar[$i], 8, 2 );
122 $date = sprintf ( "%04d%02d%02d", $y, $m, $d );
123 $ret .= date_to_str ( $date );
125 $ret .= "(type " . $tablear[$i]["type"] . " not supported)";
129 $ret .= "</td></tr>\n";
131 if ( ! empty ( $actionlabel ) )
132 $ret .= "<tr><td colspan=\"2\" style=\"text-align:center;\">\n" .
133 "<input type=\"submit\" value=\"" . htmlspecialchars ( $actionlabel ) . "\" />" .
134 "</td></tr></form>\n";
135 $ret .= "</table>\n</td></tr></table>\n</td></tr></table>\n";
140 // Print rows of a table into an HTML table. The first column will
141 // include (optionally) href links to a page which can show further
143 // $tablear - db table (defined in tables.php)
144 // $tablename - db table name
145 // $href - URL (%0 will be replaced with field field 0)
146 // $fields - list of fields to include in table.
147 // $keys - array of db key fields (field tagged with "iskey" => 1)
148 // $order - SQL order text
149 function dbtable_html_list ( $tablear, $tablename, $href, $fields,
151 global $THBG, $THFG, $CELLBG;
152 if ( ! is_array ( $tablear ) )
153 return "Error: dbtable_to_html_list parameter 1 is not an array!\n<br />\n";
154 if ( ! is_array ( $fields ) )
155 return "Error: dbtable_to_html_list parameter 2 is not an array!\n<br />\n";
156 if ( ! is_array ( $keys ) )
157 return "Error: dbtable_to_html_list parameter 3 is not an array!\n<br />\n";
158 $ret = "<table style=\"border-width:0px;\" cellspacing=\"0\" cellpadding=\"0\">" .
159 "<tr><td style=\"background-color:#000000;\">" .
160 "<table style=\"border-width:0px; width:100%;\" cellspacing=\"1\" cellpadding=\"2\">" .
161 "<tr><td style=\"width:100%; background-color:$CELLBG;\">" .
162 "<table style=\"border-width:0px; width:100%;\">\n";
165 for ( $i = 0; $i < count ( $fields ); $i++ ) {
166 $ind = dbtable_get_field_index ( $tablear, $fields[$i] );
169 echo "Error: dbtable_html_list invalid fieldname \"$fields[$i]\" $i\n"; exit;
171 if ( empty ( $tablear[$ind]["hide"] ) )
172 $ret .= "<th style=\"background-color:$THBG; color:$THFG;\">" .
173 $tablear[$ind]["prompt"] . "</th>";
176 $sql = "SELECT " . $fields[0];
177 for ( $i = 1; $i < count ( $fields ); $i++ ) {
178 $sql .= ", " . $fields[$i];
180 $sql .= " FROM " . $tablename . " ";
181 if ( is_array ( $keys ) && count ( $keys ) > 0 ) {
184 for ( $i = 0; $i < count ( $tablear ); $i++ ) {
185 if ( ! empty ( $tablear[$i]["iskey"] ) ) {
186 if ( empty ( $keys[$tablear[$i]["name"]] ) ) {
187 //echo "Error: key value for " . $tablear[$i]["name"] . " not set.\n";
194 $sql .= $tablear[$i]["name"] . " = " ;
195 if ( $tablear[$i]["type"] == "int" ||
196 $tablear[$i]["type"] == "float" || $tablear[$i]["type"] == "date" )
197 $sql .= $keys[$tablear[$i]["name"]];
199 $sql .= "'" . $keys[$tablear[$i]["name"]] . "'";
204 if ( ! empty ( $order ) )
205 $sql .= " ORDER BY " . $order;
206 //echo "SQL: $sql<br />\n";
207 $res = dbi_query ( $sql );
209 while ( $row = dbi_fetch_row ( $res ) ) {
213 for ( $i = 0; $i < count ( $fields ); $i++ ) {
214 // check data type (date)
215 $ind = dbtable_get_field_index ( $tablear, $fields[$i] );
216 if ( empty ( $tablear[$ind]["hide"] ) ) {
217 $ret .= "<td style=\"background-color:$CELLBG; vertical-align:top;\">";
218 if ( $tablear[$ind]["type"] == "date" )
219 $val = date_to_str ( $row[$i], "", 1, 1 );
220 else if ( $tablear[$ind]["type"] == "dbdate" ) {
221 $y = substr ( $row[$i], 0, 4 );
222 $m = substr ( $row[$i], 5, 2 );
223 $d = substr ( $row[$i], 8, 2 );
224 $date = sprintf ( "%04d%02d%02d", $y, $m, $d );
225 $val = date_to_str ( $date, "", 1, 1 );
227 $val = htmlentities ( $row[$i] );
228 if ( $first_href && ! empty ( $href ) ) {
231 for ( $j = count ( $fields ) - 1; $j >= 0; $j-- ) {
232 $url = str_replace ( "%$j", $row[$j], $url );
234 $ret .= "<a href=\"$url\">" . $val . "</a>";
244 echo translate("Database error") . ": " . dbi_error (); exit;
246 $ret .= "</table>\n</td></tr></table>\n</td></tr></table>\n";
250 // Load a single row of a db table
251 // $tablear - db table (defined in tables.php)
252 // $tablename - db table name
253 // $keys - array of db key fields (field tagged with "iskey" => 1)
254 function dbtable_load ( $tablear, $tablename, $keys ) {
257 if ( ! is_array ( $tablear ) ) {
258 echo "Error: dbtable_load parameter 1 is not an array!<br />\n";
261 if ( ! is_array ( $keys ) ) {
262 echo "Error: dbtable_load parameter 3 is not an array!<br />\n";
266 for ( $i = 0; $i < count ( $tablear ); $i++ ) {
271 if ( empty ( $tablear[$i]["name"] ) ) {
272 echo "Error: dbtable_load $tablename field $i does not define name.\n";
275 $sql .= $tablear[$i]["name"];
277 $sql .= " FROM " . $tablename . " WHERE ";
279 for ( $i = 0; $i < count ( $tablear ); $i++ ) {
280 if ( ! empty ( $tablear[$i]["iskey"] ) ) {
281 if ( empty ( $keys[$tablear[$i]["name"]] ) ) {
282 //echo "Error: key value for " . $tablear[$i]["name"] . " not set.\n";
289 $sql .= $tablear[$i]["name"] . " = " ;
290 if ( $tablear[$i]["type"] == "int" ||
291 $tablear[$i]["type"] == "float" || $tablear[$i]["type"] == "date" )
292 $sql .= $keys[$tablear[$i]["name"]];
294 $sql .= "'" . $keys[$tablear[$i]["name"]] . "'";
298 //echo "SQL: $sql<br />\n";
299 $res = dbi_query ( $sql );
301 if ( $row = dbi_fetch_row ( $res ) ) {
303 for ( $i = 0; $i < count ( $tablear ); $i++ ) {
307 $ret = false; // not found
309 dbi_free_result ( $res );
311 echo translate("Database error") . ": " . dbi_error (); exit;
316 // Delete a single row of a db table
317 // $tablear - db table (defined in tables.php)
318 // $tablename - db table name
319 // $keys - array of db key fields (field tagged with "iskey" => 1)
320 function dbtable_delete ( $tablear, $tablename, $keys ) {
322 if ( ! is_array ( $tablear ) ) {
323 echo "Error: dbtable_delete parameter 1 is not an array!<br />\n";
326 if ( ! is_array ( $keys ) ) {
327 echo "Error: dbtable_delete parameter 3 is not an array!<br />\n";
330 $sql = "DELETE FROM $tablename WHERE ";
332 for ( $i = 0; $i < count ( $tablear ); $i++ ) {
333 if ( ! empty ( $tablear[$i]["iskey"] ) ) {
334 if ( empty ( $keys[$tablear[$i]["name"]] ) ) {
335 //echo "Error: key value for " . $tablear[$i]["name"] . " not set.\n";
343 $sql .= $tablear[$i]["name"] . " = " ;
344 if ( $tablear[$i]["type"] == "int" ||
345 $tablear[$i]["type"] == "float" || $tablear[$i]["type"] == "date" )
346 $sql .= $keys[$tablear[$i]["name"]];
348 $sql .= "'" . $keys[$tablear[$i]["name"]] . "'";
352 //echo "SQL: $sql<br />";
353 if ( ! dbi_query ( $sql ) ) {
354 echo translate("Database error") . ": " . dbi_error (); exit;
359 // Add a row into a table (SQL insert)
360 // $tablear - db table (defined in tables.php)
361 // $tablename - db table name
362 // $valuesar - array of values
363 function dbtable_add ( $tablear, $tablename, $valuesar ) {
366 $sql = "INSERT INTO " . $tablename . " (";
367 if ( ! is_array ( $tablear ) ) {
368 echo "Error: dbtable_add parameter 1 is not an array!<br />\n";
371 if ( ! is_array ( $valuesar ) ) {
372 echo "Error: dbtable_add parameter 3 is not an array!<br />\n";
376 for ( $i = 0; $i < count ( $tablear ); $i++ ) {
381 if ( empty ( $tablear[$i]["name"] ) ) {
382 echo "Error: dbtable_load $tablename field $i does not define name.\n";
385 $sql .= $tablear[$i]["name"];
387 $sql .= " ) VALUES (";
389 for ( $i = 0; $i < count ( $tablear ); $i++ ) {
394 if ( empty ( $valuesar[$i] ) )
396 else if ( $tablear[$i]["type"] == "date" ||
397 $tablear[$i]["type"] == "int" )
398 $sql .= $valuesar[$i];
400 $sql .= "'" . $valuesar[$i] . "'";
403 //echo "SQL: $sql<br />\n";
404 if ( ! dbi_query ( $sql ) ) {
405 // Shouldn't happen... complain if it does.
406 $error = translate("Database error") . ": " . dbi_error ();
412 // Update a row in a table (SQL update)
413 // $tablear - db table (defined in tables.php)
414 // $tablename - db table name
415 // $valuesar - array of values
416 function dbtable_update ( $tablear, $tablename, $valuesar ) {
418 $sql = "UPDATE " . $tablename . " SET";
419 if ( ! is_array ( $tablear ) ) {
420 echo "Error: dbtable_update parameter 1 is not an array!<br />\n";
423 if ( ! is_array ( $valuesar ) ) {
424 echo "Error: dbtable_update parameter 3 is not an array!<br />\n";
428 for ( $i = 0; $i < count ( $tablear ); $i++ ) {
429 if ( ! empty ( $tablear[$i]["iskey"] ) )
435 if ( empty ( $tablear[$i]["name"] ) ) {
436 echo "Error: dbtable_update $tablename field $i does not define name.\n";
439 $sql .= " " . $tablear[$i]["name"] . " = ";
440 if ( empty ( $valuesar[$i] ) ) {
442 } else if ( $tablear[$i]["type"] == "int" ||
443 $tablear[$i]["type"] == "date" ) {
444 $sql .= $valuesar[$i];
446 $sql .= "'" . $valuesar[$i] . "'";
450 for ( $i = 0; $i < count ( $tablear ); $i++ ) {
451 if ( empty ( $tablear[$i]["iskey"] ) )
457 if ( empty ( $valuesar[$i] ) ) {
458 echo "Error: you must set field $i (" . $tablear[$i]["name"] .
459 ") by hand. Cannot be empty.";
462 $sql .= " " . $tablear[$i]["name"] . " = '" . $valuesar[$i] . "'";
464 //echo "SQL: $sql <p>\n";
465 if ( ! dbi_query ( $sql ) ) {
466 // Shouldn't happen... complain if it does.
467 $error = translate("Database error") . ": " . dbi_error ();
475 function dbtable_genid ( $tablename, $field ) {
478 $sql = "SELECT MAX(" . $field . ") FROM " . $tablename;
479 $res = dbi_query ( $sql );
481 if ( $row = dbi_fetch_row ( $res ) ) {
484 dbi_free_result ( $res );
489 // Convert an array of db values (with index values 0,1,2,... into
490 // an associative array (with index values of table column names).
491 // $tablear - db table (defined in tables.php)
492 // $valuesar - array of values
493 function dbtable_build_name_index ( $tablear, $valuesar ) {
495 for ( $i = 0; $i < count ( $tablear ); $i++ ) {
496 $ret[$tablear[$i]["name"]] = $valuesar[$i];