10 PHP usefull functions for MySQL stuff

Sep 22, 2015

By Giulio Pons

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:

1
2
3
4
5
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.

01
02
03
04
05
06
07
08
09
10
11
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:

1
2
3
4
<?
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?)

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
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:

1
2
3
4
5
6
7
<?
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:

1
2
3
4
5
6
7
8
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:

1
2
3
4
5
6
7
8
9
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:

1
2
3
4
5
6
...
$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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
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.

01
02
03
04
05
06
07
08
09
10
11
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:

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

FUNCTION 8: RUN A QUERY AND GET FIRST ROW

01
02
03
04
05
06
07
08
09
10
11
12
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:

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

1
2
3
4
5
6
7
8
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:

1
2
// 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.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
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:

1
2
3
4
5
//... 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

Top