Knightnet Site Design - MySQL Database handling routines

<?php require_once('debug.inc'); # ensure error() and other helper routines available /* mySQL db handling tools */ // NOTE: DEFAULT SETTINGS REMOVED FOR SECURITY - REPLACE WITH YOUR OWN function dbConnect($dbn='xxxxx',$dbpw='xxxxx',$dbu='xxxxx',$dbs='localhost') { // Connect to my database - returns link to caller // Only ever opens one link. // Saves putting db details in scripts // $dbn=Database Name, $dbs=database server, $dbu= db user, $dbpw=password for user $link = mysql_connect($dbs,$dbu,$dbpw) or die("db-mysql.inc:dbConnect: Could not connect to server: $dbs, $dbu (".mysql_error().')'); mysql_select_db($dbn, $link) or die("db-mysql.inc:dbConnect: Could not connect to database: $dbn (".mysql_error().')'); return $link; } /** * dbInsert() * Inserts or Updates a table single row * @param $dbLink = Link to a mySQL database * @param $dbTbl = Table name to update * @param $dbCols = ARRAY of column names for update * @param $dbVals = ARRAY of values, must match Cols * @param $dbupd = UPDATE or INSERT only * @param $dbWhere = Where clause - must be valid * @return TRUE if successful, else false */ function dbInsert($dbLink, $dbTbl, $dbCols, $dbVals, $dbUpd='INSERT', $dbWhere='',$ignoreNulls=TRUE) { // ERROR CHECKS // $dbUpd = strtoupper($dbUpd); if ($dbUpd!='INSERT' && $dbUpd!='UPDATE') { echo "\n<div>db-mysql.inc: dbInsert - Parameter 2 (dbUpd) can only be either 'INSERT' or 'UPDATE'</div>\n"; return FALSE; } if (count($dbCols)!=count($dbVals)) { echo "\n<div>db-mysql.inc: dbInsert - Array of Columns (Param 2) is NOT the same length as the array of Values (Param 3)<br>They MUST be the same length.</div>\n"; return FALSE; } //ensure that dbVals is accessible by numerical index $dbVals=array_values($dbVals); // Set up the update values // //** NEED TO QUOTE THE VALUES **// $strSet=""; $i=-1; foreach ($dbCols as $col) { $i++; # ignore NULL values if ($ignoreNulls===TRUE) { if ($dbVals[$i]=='NULL' || $dbVals[$i]=='') continue; else $strSet.="`$col`='".addslashes($dbVals[$i])."', "; } else { if ($dbVals[$i]=='NULL') $strSet.="`$col`=NULL, "; else $strSet.="`$col`='".addslashes($dbVals[$i])."', "; } } $strSet=rtrim($strSet, ", "); // Format the SQL required // if ($dbUpd=='INSERT') $strSQL="INSERT INTO `$dbTbl` SET $strSet"; else $strSQL="UPDATE `$dbTbl` SET $strSet WHERE $dbWhere"; // Do the Work // mysql_query($strSQL, $dbLink) or die("db-mysql.inc:dbInsert - $dbUpd of table '$dbTbl' failed (".mysql_error().')'); $x=mysql_affected_rows(); if ($x<1) { print mysql_errno() . ": " . mysql_error(). "\n<br>"; die("db-mysql.inc:dbInsert - $dbUpd of table '$dbTbl' failed, no rows were inserted". "\n<br>$strSQL". "\n<br>Rows:$x"); return FALSE; } return TRUE; } /* Create a new table in a given database Checks if the table exists & aborts if it does -------------------------------------------------------------------------------------------------- Parameters: $dbLink = mySQL link to a database (returned from dbConnect() call) $dbTbl = STRING, name of table to create $dbFields = ARRAY STRING, list of fields (with specifications to create) each entry MUST be a valid mySQL column with specs $dbComments = STRING, comments added to the table Returns: BOOLEAN = TRUE if the create works, FALSE otherwise -------------------------------------------------------------------------------------------------- */ function dbCreateTbl($dbLink,$dbTbl,$dbFields,$dbComment='') { // Check if table already exists and exit if it does do an ALTER rather than CREATE // NB: mySQL attempts to reformat any columns of same name but diff formats $strSQL="SHOW TABLE STATUS LIKE `$dbTbl`"; $result = mysql_query ($strSQL,$dbLink); if ($result) { // Table exists so $strSQL="ALTER TABLE "; // NEED to expand this to check existing fields & format ADD/MODIFY syntax etc // at present, you need to specify the correct syntax in the $dbFields parm } else { $strSQL="CREATE TABLE "; } $strSQL.=$dbTbl; $strSQL.=" ("; // for each column spec, add to sql str (MAY need to pull this into the if above in future) foreach ($dbFields as $dbField) { $strSQL.="`$dbField`, "; } $strSQL=rtrim($strSQL,", "); if ($dbComment!='') { $strSQL.=" comment='"; $strSQL.=$dbComment; $strSQL.="'"; } $return=mysql_query($strSQL, $dbLink); if ($return) { return TRUE; } else { echo("db-mysql.inc:dbCreate - CREATE/UPDATE of table '$dbTbl' failed"); return FALSE; } } // ---------------------------------------------------------- # Return an array containing the column names of all columns # in a table // ---------------------------------------------------------- function getCols($dblink,$tbl) { # get column specs for this table $r=mysql_query("SHOW FULL COLUMNS FROM `$tbl`", $dblink); # how many columns in the table? $nCols=mysql_num_rows($r); # Get all column names for ($i=0; $i<$nCols; $i++) { $inf=mysql_fetch_assoc($r); $cols[]=$inf['Field']; } # return column array return $cols; } // ---------------------------------------------------------- # Return table last update dt and comment in an array // ---------------------------------------------------------- function getInfo($dblink,$tbl) { $r=mysql_fetch_row(mysql_query("SHOW TABLE STATUS LIKE '$tbl'", $dblink)); return array($row[11],$row[14]); } // ---------------------------------------------------------- // ------------ HTML FORMATTED QUERIES --------------- // Simple query returns an HTML table # Automatically creates table headings from the SQL fields returned # WARNING: Needs error checking adding // function dbHTMLquery($strSQL) { $res=mysql_query($strSQL); $r='<table>'; $row=mysql_fetch_array($res, MYSQL_ASSOC); if ($row!='') { $r.='<tr><th>'.implode('</th><th>',array_keys($row)).'</th></tr>'; $r.='<tr><td>'.implode('</td><td>',$row).'</td></tr>'; } while ($row = mysql_fetch_array($res, MYSQL_NUM)) { $r.='<tr><td>'.implode('</td><td>',$row).'</td></tr>'; } $r.='</table>'; return $r; } /* * Return an HTML formatted query table * NOTE: No error checking */ function dbHtQuery($dbLink,$dbTbl,$dbFields='*',$dbSort='',$dbWhere='',$dbLimit='', $tClass='',$hClass='',$bClass='', $rOver='',$rOut='', $rClick='') { $strSQL='SELECT '; if (is_array($dbFields)) $strSQL.=implode(', ',$dbFields); else $strSQL.=$dbFields; $strSQL.=" FROM $dbTbl "; if ($dbWhere!='') $strSQL.=" WHERE $dbWhere "; if ($dbSort!='') $strSQL.=" ORDER BY $dbSort "; if ($dbLimit!='') $strSQL.=" LIMIT $dbLimit "; $result=mysql_query($strSQL, $dbLink); if (!$result) { error("db-mysql.inc:dbHtQuery - QUERY of table '$dbTbl' failed"); return FALSE; } $rows=mysql_num_rows($result); # number of rows in result if ($rows<1) { error("db-mysql.inc:dbHtQuery - QUERY of table '$dbTbl', no rows were returned"); return FALSE; } $cols=mysql_num_fields($result); # number of columns in result // set up table $ans="\n<table class='{$tClass}'>\n"; // set up table headings $ans.="\t<thead><tr class='{$hClass}'>"; for ($i=0; $i<$cols; $i++) { $ans.=sprintf("<th>%s</th>",strtoupper(mysql_field_name($result,$i))); } $ans.="\n\t</tr></thead><tbody>\n"; // set up the table body while ($row = mysql_fetch_row($result)) { $ans.="\t\t<tr class='{$bClass}' onmouseover='{$rOver}' onmouseout='{$rOut}' onclick='{$rClick}'>"; $i=0; foreach ($row as $col) { $meta=mysql_fetch_field($result,$i); switch ($meta->type) { case 'date': # if col is date then fmt $ans.=sprintf("<td style='text-align:center'>%s</td>",strftime('%d/%b/%Y',strtotime($col))); break; default: $ans.="<td>$col</td>"; } $i++; } $ans.="</tr>\n"; } $ans.="\t</tbody>\n"; $ans.="</table>\n"; return $ans; } /* * Return an HTML formatted query table - FROM RAW SQL */ function dbHtSQL($dbLink,$strSQL, $tClass='',$hClass='',$bClass='', $rOver='',$rOut='', $rClick='') { $result=mysql_query($strSQL, $dbLink); if (!$result) { error("db-mysql.inc:dbHtQuery - SQL QUERY failed"); error($strSQL); return FALSE; } $rows=mysql_num_rows($result); # number of rows in result if ($rows<1) { error("db-mysql.inc:dbHtQuery - QUERY of table '$dbTbl', no rows were returned"); return FALSE; } $cols=mysql_num_fields($result); # number of columns in result // set up table print "\n<table class='$tClass'>\n"; // set up table headings print "\t<thead><tr class='$hClass'>"; for ($i=0; $i<$cols; $i++) printf("<th>%s</th>",strtoupper(mysql_field_name($result,$i))); print "\n\t</tr></thead><tbody>\n"; // set up the table body while ($row = mysql_fetch_row($result)) { print "\t\t<tr class='$bClass' onmouseover='$rOver' onmouseout='$rOut' onclick='$rClick'>"; $i=0; foreach ($row as $col) { $meta=mysql_fetch_field($result,$i); switch ($meta->type) { case 'date': # if col is date then fmt printf("<td style='text-align:center'>%s</td>",strftime('%d/%b/%y',strtotime($col))); break; default: print "<td>$col</td>"; } $i++; } print("</tr>\n"); } print("\t</tbody>\n"); print("</table>\n"); return TRUE; } ?>