10 PHP usefull functions for MySQL stuff

Here is my personal collection of 10 php function that I always include in my database function library.
I think they are very usefull and I’d like to share with you.

Here are the mysqli versions of the functions. (NEW)

FUNCTION 1: CONNECT TO A DB
Connect function. I know, everybody already has this function in its library. But I have to add it. The user data are stored in constants, so you don’t have to specify any variable when you call the connect function:

function connectDb() {
	// connect and set the
	// working db
	if (mysql_connect( DBHOST, DBUSER, DBPWD ) && mysql_select_db( DBNAME )) return true; else return false;
}

FUNCTION 2: RUN SCRIPT FOR REPAIR AND OPTIMIZE TABLES
This function will scan all the tables of a db and run repair and optimize for each table. Usefull, for example, when you want to run a fix on the db every night calling it through a cron job on the server.

function fixTables($dbname) {
	// search for all the tables of
	// a db and run repair and optimize
	// note: this can take a lot of time
	// if you have big/many tables.
	$result = mysql_list_tables($dbname) or die(mysql_error());
	while ($row = mysql_fetch_row($result)) {
		mysql_query("REPAIR TABLE $row[0]");
		mysql_query("OPTIMIZE TABLE $row[0]");
	}
}

EXAMPLE: So, make a php file cronfixdb.php with this code and call everynight to fix your tables:

<?
include("db.lib.php");  //file with the Connect and fixTables function
if connectDb() fixTables(DB);
?>

FUNCTION 3: CONVERT A RECORDSET TO HTML TABLE
This code receive a record set coming from a mysql_query output and print it in a simple html table, quick and usefull. This script could be really improved with some css (and, eventually pagination… can you?)

function getHtmlTable($result){
	// receive a record set and print
	// it into an html table
	$out = '<table>';
	for($i = 0; $i < mysql_num_fields($result); $i++){
		$aux = mysql_field_name($result, $i);
		$out .= "<th>".$aux."</th>";
	}
	while ($linea = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$out .= "<tr>";
		foreach ($linea as $valor_col) $out .= '<td>'.$valor_col.'</td>';
		$out .= "</tr>";
	}
	$out .= "</table>";
	return $out;
}

EXAMPLE: Use this code in this way:

<?
include("db.lib.php");
if connectDb() {
   $rs = mysql_query("select * from users limit 0,100");
   echo getHtmlTable($rs);
}
?>

FUNCTION 4: MAKE A STRING WITH FIELD NAMES
Found the fields-name of a table and return them in a comma-separeted string. This is usefull to automatic build queries in some applications. The $excepts parameter can be used to skip some field in the output string:

function getCommaFields( $table, $excepts = ""){
	// get a string with the names of the fields of the $table,
	// except the onews listed in '$excepts' param
	$out = "";
	$result = mysql_query( "SHOW COLUMNS FROM `$table`" );
	while($row = mysql_fetch_array($result)) if ( !stristr(",".$row['Field']."," , $excepts) ) $out.= ($out?",":"").$row['Field'];
	return $out ;
}

FUNCTION 5: MAKE A STRING WITH COMMA SEPARATED VALUES
Run a query and get all the first value of each row into a comma separeted string. Usefull for old mysql version db that do not support sub select. But also for other things:

function getCommaValues($sql) {
	// execute a $sql query and return
	// all the first value of the rows in
	// a comma separated string
	$out = "";
	$rs = mysql_query($sql) or die(mysql_error().$sql);
	while($r=mysql_fetch_row($rs)) $out.=($out?",":"").$r[0];
	return $out;
}

EXAMPLE: find items for a subselect statement:

...
$ids = getCommaValues("select id from users where status=-1");
$sql = "delete from users where id in (".$ids.")";
if ($ids) mysql_query($sql);
// note since mysql 5 you can use sub select in delete statements.
...

FUNCTION 6: GET VALUES OF A ENUM/SET FIELD
I’ve also dedicated a post for this function, I think this script, even if isn’t so nice (it could be rewritten with regular expressions) it’s very usefull if you use ENUM fields in your tables.

function getEnumSetValues( $table , $field ){
	// get an array of the allowed values
	// of the enum or set $field of $table
	$query = "SHOW COLUMNS FROM `$table` LIKE '$field'";
	$result = mysql_query( $query ) or die( 'error getting enum field ' . mysql_error() );
	$row = mysql_fetch_array($result);
	if(stripos(".".$row[1],"enum(") > 0) $row[1]=str_replace("enum('","",$row[1]);
		else $row[1]=str_replace("set('","",$row[1]);
	$row[1]=str_replace("','","\n",$row[1]);
	$row[1]=str_replace("')","",$row[1]);
	$ar = split("\n",$row[1]);
	for ($i=0;$i<count($ar);$i++) $arOut[str_replace("''","'",$ar[$i])]=str_replace("''","'",$ar[$i]);
	return $arOut ;
}

FUNCTION 7: RUN A QUERY AND GET THE FIRST VALUE
This function and the following one are the most used functions of this collection (after ConnectDb), since we always need to extract a singular value or (next function) singular row.

function getScalar($sql,$def="") {
	// execute a $sql query and return the first
	// value, or, if none, the $def value
	$rs = mysql_query($sql) or die(mysql_error().$sql);
	if (mysql_num_rows($rs)) {
		$r = mysql_fetch_row($rs);
		mysql_free_result($rs);
		return $r[0];
	}
	return $def;
}

EXAMPLE:

echo getScalar("select count(*) from users"); // 23

FUNCTION 8: RUN A QUERY AND GET FIRST ROW

function getRow($sql) {
	// execute a $sql query and return the first
	// row, or, if none, return an empty string
	$rs = mysql_query($sql) or die(mysql_error().$sql);
	if (mysql_num_rows($rs)) {
		$r = mysql_fetch_array($rs);
		mysql_free_result($rs);
		return $r;
	}
	mysql_free_result($rs);
	return "";
}

EXAMPLE:

print_r( getRow("select * from users limit 0,1") ); // Array (...);

FUNCTION 9: DUPLICATE A SPECIFIED ROW
This function use the getCommaFields described above and make an insert in a table duplicating a particular row defined by the primary id field and value specified in the parameters.
It’s usefull to add “duplicate” functionality in your administrator backend software.

function duplicateRow($table,$primaryField,$primaryIDvalue) {
	// duplicate one record in a table
	// and return the id
	$fields = getCommaFields($table,$primaryField);
	$sql = "insert into $table ($fields) select $fields from $table where $primaryField='".mysql_real_escape($primaryIDvalue)."' limit 0,1";
	mysql_query($sql) or die(mysql_error().$sql);
	return mysql_insert_id();
}

EXAMPLE:

// duplicate the user with id_user=12 and get the new user id:
$newuser = duplicateRow("users","id_user","12");

FUNCTION 10: CONVERT A RECORDSET TO JSON AND CSV
This function converts a record set returned by a mysql_query function to a JSON encoded string or to a CSV string.
This script could also be improved by adding some configuration for the CSV export.

function convertResult($rs, $type, $jsonmain="") {
	// receive a recordset and convert it to csv
	// or to json based on "type" parameter.
	$jsonArray = array();
	$csvString = "";
	$csvcolumns = "";
	$count = 0;
	while($r = mysql_fetch_row($rs)) {
		for($k = 0; $k < count($r); $k++) {
			$jsonArray[$count][mysql_field_name($rs, $k)] = $r[$k];
			$csvString.=",\"".$r[$k]."\"";
		}
		if (!$csvcolumns) for($k = 0; $k < count($r); $k++) $csvcolumns.=($csvcolumns?",":"").mysql_field_name($rs, $k);
		$csvString.="\n";
		$count++;
	}
	$jsondata = "{\"$jsonmain\":".json_encode($jsonArray)."}";
	$csvdata = str_replace("\n,","\n",$csvcolumns."\n".$csvString);
	return ($type=="csv"?$csvdata:$jsondata);
}

EXAMPLE:

//... connect ...
$sql = "select * from users limit 0,10";
$rs = mysql_query($sql);
$jsonString = convertResult($rs,"json","users"); // json encoded string
$csvString = convertResult($rs,"csv"); // scv output