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…

January 25, 2010

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

Author

I'm a software engineer, an everyday web developer and a maker. I usually build sites with PHP, within or without WordPress. I build Internet of Things with Arduino and ESP8266. I'm the founder of Rockit.it and Dailybest.it and I'm actually the Chief Technical Officer of Better Days web agency.

Comments on “10 PHP usefull functions for MySQL stuff”

10 thoughts

  1. Stfr says:

    Using mysql_query() is obsolete.
    Use PDO, or mysqli ..but don’t use mysql…

  2. admin says:

    Mmm ok, in the future I will use mysqli, that’s better and I have to study it. :)
    But why PDO? PDO should be used when you plan to change db, or when you have different dbs to connect to.

  3. Stfr says:

    Sure, but you can use it in order to create ORM or just in order to use Object syntax for your Db.
    It depends on the use you do and the importance of the project.

  4. The Same says:

    Function 4 in MySql is the same as:

    SELECT GROUP_CONCAT(”,COLUMN_NAME) as campos
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA=’$BBDD’ AND TABLE_NAME=’$TABLE’ AND COLUMN_NAME NOT IN (‘$EXPECTS’)
    GROUP BY TABLE_NAME;

  5. MarkSpizer says:

    great post as usual!

  6. Excellent article. I just cannot go along with all, still you have some exciting ideas.

Comments are closed

Recommended

Optimize WordPress, a long list of tips

In the above image you can see your WordPress before reading this post, and after the optimizations you will make…

September 15, 2015

How to remove custom fields from WordPress

Ok. You’ve added custom fields in wordpress, but how can you remove them? How to delete custom fields wordpress Two…

September 29, 2013

13 mysql tips that you don’t know

Here is a list of thirteen tips that can be usefull for your queries. If you know more tips send…

October 19, 2012

10 PHP usefull functions for MySQLi stuff (mysql improved)

I’ve modified the previous post to use mysqli instead of mysql. Was it necessary? I think it was good step…

January 29, 2010

Execute Scalar and Execute Row for Php

A couple of simple functions thar returns the first value and the first row of a result set. Very usefull.…

November 9, 2009

Highlight text for search results in PHP

Useful code to highlight text occurences in search results or in a text. How to highlight text in a string…

September 2, 2016