2013-10-22_101755

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 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);
}

?>

5 comments

  1. Samuel

    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.

  2. ExplodingCabbage

    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.