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

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

Below here there are all the functions in just one big block of code, without any examples (since you can adapt the ones in the original mysql post).

List of the functions:
FUNCTION 1: CONNECT TO A DB
FUNCTION 2: RUN SCRIPT FOR REPAIR AND OPTIMIZE TABLES
FUNCTION 3: CONVERT A RECORDSET TO HTML TABLE
FUNCTION 4: MAKE A STRING WITH FIELD NAMES
FUNCTION 5: MAKE A STRING WITH COMMA SEPARATED VALUES
FUNCTION 6: GET VALUES OF A ENUM/SET FIELD
FUNCTION 7: RUN A QUERY AND GET THE FIRST VALUE
FUNCTION 8: RUN A QUERY AND GET FIRST ROW
FUNCTION 9: DUPLICATE A SPECIFIED ROW
FUNCTION 10: CONVERT A RECORDSET TO JSON AND CSV

<?

define("WEBDOMAIN", "your host");
define("DEFDBNAME", "your db name");
define("DEFUSERNAME", "your user");
define("DEFDBPWD",  "your pwd");

if(!$conn = connectDb()) {die("err db");} else {$conn->query("SET NAMES 'utf8';");}

function connectDb() {
	if ($conn = @new mysqli(WEBDOMAIN, DEFUSERNAME, DEFDBPWD, DEFDBNAME)) return $conn; else return false;
}

function fixTables($conn,$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.
	global $conn;
	if ( $rs = $conn->query("SHOW TABLES FROM $dbname") ) {
		while ( $r = $rs->fetch_array() ) {
			$conn->query("REPAIR TABLE {$r[0]}");
			$conn->query("OPTIMIZE TABLE {$r[0]}");
		}
		$rs->free();
	}
}

function getHtmlTable($rs){
	// receive a record set and print
	// it into an html table
	$out = '<table>';
	while ($field = $rs->fetch_field()) $out .= "<th>".$field->name."</th>";
	while ($linea = $rs->fetch_assoc()) {
		$out .= "<tr>";
		foreach ($linea as $valor_col) $out .= '<td>'.$valor_col.'</td>';
		$out .= "</tr>";
	}
	$out .= "</table>";
	return $out;
}

function getCommaFields($conn, $table, $excepts = ""){
	// get a string with the names of the fields of the $table,
	// except the onews listed in '$excepts' param
	$out = "";
	if ($rs = $conn->query( "SHOW COLUMNS FROM `$table`" )) {
		while($r = $rs->fetch_array()) if ( !stristr(",".$r['Field']."," ,  $excepts) ) $out.= ($out?",":"").$r['Field'];
	} else die($conn->error);
	return $out ;
}

function getCommaValues($conn,$sql) {
	// execute a $sql query and return
	// all the first value of the rows in
	// a comma separated string
	$out = "";
	if ($rs = $conn->query($sql)) while($r=$rs->fetch_row()) $out.=($out?",":"").$r[0];
		else die($conn->error);
	return $out;
}

function getScalar($conn,$sql,$def="") {
	if ( $rs = $conn->query($sql) ) {
		$r = $rs->fetch_array();
		$rs->free();
		return $r[0];
	}
	return $def;
}

function getRow($conn,$sql) {
	if ( $rs = $conn->query($sql) ) {
		$r = $rs->fetch_array();
		$rs->free();
		return $r;
	}
	return "";
}

function duplicateRow($conn, $table,$primaryField,$primaryIDvalue) {
	// duplicate one record in a table
	// and return the id
	$fields = getCommaFields($conn, $table,$primaryField);
	$sql = "insert into $table ($fields) select $fields from $table where $primaryField='".$conn->escape_string($primaryIDvalue)."' limit 0,1";
	if (!$conn->query($sql)) die($conn->error().$sql);
	return $conn->insert_id;
}

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 = $rs->fetch_row()) {
		for($k = 0; $k < count($r); $k++) {
			$rs->field_seek($k);
			$finfo = $rs->fetch_field();
			$jsonArray[$count][$finfo->name] = $r[$k];
			$csvString.=",\"".$r[$k]."\"";
			if (!$count) $csvcolumns.=($csvcolumns?",":"").$finfo->name;
		}
		$csvString.="\n";
		$count++;
	}
	$jsondata = "{\"$jsonmain\":".json_encode($jsonArray)."}";
	$csvdata = str_replace("\n,","\n",$csvcolumns."\n".$csvString);
	return ($type=="csv"?$csvdata:$jsondata);
}

?>

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 MySQLi stuff (mysql improved)”

5 thoughts

  1. Victoria says:

    Hi! Useful is spelt(spelled) with one l. :)

  2. Giulio Pons says:

    Sooorry! :-)

  3. Samuel says:

    Thanks for this! Im switching to mysqli and your post provided valuable insight into how to use the new (to me) command syntax. Greatly appreciate your work and am envious of your ability to write clear and concise code.

  4. ExplodingCabbage says:

    Careful! You’re not HTML-escaping the stuff you pull out of the database in `getHtmlTable`. If any of that stuff contains HTML special chars, like ”, you’re liable to end up with mangled HTML. Worse, if you’re pulling stuff from a table that users are able to insert into (for example, showing names of your users), you’re vulnerable to XSS attacks (e.g. somebody could shove a tag into their username.

    I haven’t looked at any of the other functions here.

  5. Giulio Pons says:

    Sure, I know. If you are handling any external data you have to check everything, but it’s not this case, this are only examples.

Comments are closed

Recommended

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 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

MYSQL add counter in a query

Sometimes I’ve needed to add a counter column in a query and I don’t want to add the column with…

January 21, 2010

How to add rel=”nofollow” to links with preg_replace()

Adding rel="nofollow" to external link is a good SEO practice.

September 22, 2015

Social buttons: the fastest way for WordPress, without plugins

NOTE: the code in this post is written for WordPress but you can easily translate it in any language. You’re here…

September 15, 2015