This is the second part of this tutorial. The first part explained all the items on this function and now you are going to see them together.

<?php

$_SESSION[’doThisTables’] = array(); # When the database is too big, you will need a array to hold all tables that can break the script in timeout and execute them later.

function dumpdb($dbserver = ”, $dbuser = ”, $dbpass = ”, $dbdatabase = ”,
$pathToSaveFile = ”, $filename = ”, $printAtScreen = false,
$printScreenOnly = false, $doThisTable = ”){

if(is_string($dbserver) && strlen($dbserver) > 0) {
$server = mysql_escape_string($dbserver);
}
else {
$server = DB_SERVER;
}
if(is_string($dbuser) && strlen($dbuser) > 0) {
$user = mysql_escape_string($dbuser);
}
else {
$user = DB_USERNAME;
}
if(is_string($dbpass) && strlen($dbpass) > 0) {
$pass = mysql_escape_string($dbpass);
}
else {
$pass = DB_PASSWORD;
}
if(is_string($dbdatabase) && strlen($dbdatabase) > 0) {
$db = mysql_escape_string($dbdatabase);
}
else {
$db = DB_DATABASE;
}
if(is_string($pathToSaveFile) && strlen($pathToSaveFile) > 0) {
$pathToSave = mysql_escape_string($pathToSaveFile);
if(is_string($filename) && strlen($filename) > 0) {
$nameToBeUsed = mysql_escape_string($filename);
if(!stristr($nameToBeUsed,’.')) {
$nameToBeUsed .= ‘.sql’;
}
$path = $pathToSave.$nameToBeUsed;
}
else {
$path = $pathToSave.”$db”.”_MySQLDumpAt_”.time().”.sql”;
}
}
else {
if(is_string($filename) && strlen($filename) > 0) {
$nameToBeUsed = mysql_escape_string($filename);
if(!stristr($nameToBeUsed,’.')) {
$nameToBeUsed .= ‘.sql’;
}
$path = SITE_ROOT.”/backups/”.$nameToBeUsed;
}
else {
$path = SITE_ROOT.”/backups/$db”.”_MySQLDumpAt_”.time().”.sql”;
}
}
$byPassTableQuery = false;
if(is_string($doThisTable) && strlen($doThisTable) > 0) {
$tablesToDo = array($doThisTable);
$byPassTableQuery = true;
}
mysql_connect($server, $user, $pass);
mysql_select_db($db);
$tables = false;
if($byPassTableQuery === false) {
$stmt = “SHOW TABLES FROM `$db`”;
$tables = mysql_query($stmt);
}
$sql_dump = “”;
if($tables || $byPassTableQuery === true) {
# $tables = mysql_list_tables($db); <– Deprecated. Using SQL SHOW TABLES instead
if($printScreenOnly === false) {
if(is_file($path) && filesize($path) > 0) {
$filePointer = fopen($path,’a+’); # Opening file to save backup pointing the row for the end of the file
}
else {
$filePointer = fopen($path,’a+’); # Opening file to save backup pointing the row for the beggining of the file
}
}
$tables_array = array(); # for performance and avoiding the script to die in big databases keep the tables in a separate array to control
if($byPassTableQuery === false) {
while($td = mysql_fetch_array($tables)){
$tables_array[] = $td[0];
}
}
else {
$tables_array = $tablesToDo;
}
$tablesSize = sizeof($tables_array);
for($index = 0; $index < $tablesSize; $index++) {
# while($td = mysql_fetch_array($tables)) {
# For each table, create a show create table statement and dump all inserts
$table = $tables_array[$index]; # Getting current table
$showCreates = mysql_query(”SHOW CREATE TABLE `$table`”); # Create table statement
if($showCreates){
$insert_sql = “”; # Cleaning the insert sql string
$d = mysql_fetch_array($showCreates); # retriving the table structure
$d[1] .= “;”; # adding a end statement at the create structure
$createTable = $d[1]; # create statement string
$sql_dump .= $createTable; # for printing at screen
if($printScreenOnly === false) {
fwrite($filePointer,$createTable); # adding create statement in the file
fwrite($filePointer,”\n”); # adding a new line after the create statement
}
$table_query = mysql_query(”SELECT * FROM `$table`”);# getting all rows from the table
$num_fields = mysql_num_fields($table_query); # getting all fields from the table
$num_records = mysql_num_rows($table_query); # checking if the number of rows is too big
if($num_records > 10000 && !in_array($table,$_SESSION[’doThisTables’])) {
$_SESSION[’doThisTables’][] = $table;
}
else {
while($fetch_row = mysql_fetch_array($table_query)){
$insert_sql .= “INSERT INTO $table VALUES(”;
for ($n=1;$n<=$num_fields;$n++){
$m = $n - 1;
$insert_sql .= “‘”.mysql_real_escape_string($fetch_row[$m]).”‘, “;
}
$insert_sql = substr($insert_sql,0,-2);
$insert_sql .= “);\n”; # creating the insert statement for that
}
if ($insert_sql!= “”){
$sql_dump .= $insert_sql; # adding the insert statement to the sql dump string
if($printScreenOnly === false) {
fwrite($filePointer,$insert_sql); # writing the insert statement at file
}
}
}
}
}
if($printScreenOnly === false) {
fclose($filePointer); # closing the file
}
}
if($printAtScreen === true) {
return $sql_dump;
}
else {
return true;
}
}
$pathToSaveFile = SITE_ROOT.’/backups/’;
$filename = DB_DATABASE.’_’.date(’m-d-Y-H-i-s’).’.sql’;
$sql = array();

# If you are testing and needs to print the script on the page uncomment the following lines
/*
********************************************************************************
$dbdatabase = DB_DATABASE;
$dbserver = DB_SERVER;
$dbuser = DB_USERNAME;
$dbpass = DB_PASSWORD;
mysql_connect($dbserver,$dbuser,$dbpass);
mysql_select_db($dbdatabase);
$stmt = “SHOW TABLE STATUS FROM `$dbdatabase`”;
$sqlHandler = mysql_query($stmt);
$rows = 0;
while($result = mysql_fetch_assoc($sqlHandler)) {
$rows += (int) $result[’Rows’];
}
if($rows > 200000) {
$maxTime = ini_get(’max_execution_time’);
if($maxTime <= 120) {
$timeExec = (int) floor($rows/500);
ini_set(’max_execution_time’,$timeExec); # Just in case it needs more time
ini_set(’memory_limit’,'96M’);
}
}
***********************************************************************************
*/
$dump = dumpdb($dbserver = ”, $dbuser = ”, $dbpass = ”, $dbdatabase = ”, $pathToSaveFile, $filename, $printAtScreen = false,
$printScreenOnly = false, $doThisTable = ”);
$sql[] = $dump;
$sizeof = sizeof($_SESSION[’doThisTables’]);
if($sizeof > 0) {
for($index = 0; $index < $sizeof; $index++) {
$table = $_SESSION[’doThisTables’][$index];
$dump = dumpdb($dbserver = ”, $dbuser = ”, $dbpass = ”, $dbdatabase = ”, $pathToSaveFile,
$filename, $printAtScreen = false, $printScreenOnly = false, $table);
$sql[] = $dump;
}
}
unset($_SESSION[’doThisTables’]);
?>

These are some considerations to help you a little bit.

1. They hold a lot of information about the module, module table and join tables on the relationship table.
2. The crm creates a unique id for the table id. No matter what table you are working with the primary id field must be named id and cannot be auto increment. If I’m not wrong the sugarbean will be responsible to generate this value wich looks like a hash: d6ee4529-56f0-3bc8-19b8-47cdbe4f2305
3. The user table works as exception from the previous statement. The primary key field is named id, but the key is composed by username followed by a dash and id. Ex. chris_id.
4. The user table also works as a group table. It has on the end a is_group flag.
5. Calendar is saved on the iCalendar format
   1. Example: BEGIN:VCALENDAR
      VERSION:2.0
      PRODID:-//SugarCRM//SugarCRM Calendar//EN
      BEGIN:VFREEBUSY
      ORGANIZER;CN= Administrator:
      DTSTART:20080303T212800Z
      DTEND:20080503T212800Z
      DTSTAMP:20080304T212800Z
      END:VFREEBUSY
      END:VCALENDAR
6. Module tables should be named as module name followed by submodule or action. Everything in lower case.
   1. Example: project; project_task; project_task_audit;
7. email_addr_bean_rel holds a bean_id that is the id of the table and a bean_module that is the table where it came from
8. Just like vTiger it have custom fields
9. No matter what module you are going to associate with, if there’s a user assigned to, the field will be name assigned_user_id. The exception is when the module table works with more than one module, so it will have to hold a bean (module, table) and a bean_id (the module, table, id). The same is true for holding the information for a user that modifies the record. In this case the field will be named modified_user_id.
10. In some tables date_modified will be the same as date_created

PHP has a whole bunch of pre-built functions to escape strings and most of them for MySQL, but only one does not require the mysql link identifier to work and all of them give the same result.

mysql_real_escape_string, mysqli_real_escape_string and mysqli_escape_string requires the link identifier to work, otherwise will throw an error, but the mysql_escape_string does not.

All of the functions will escape quotes, double quotes, slashes and so on. The real escape ones, does more escaping to better work with MySql, and all of them are more than necessary for avoiding sql injection attacks or user input errors.

For more info go to PHP.net

There’s no function at MySQL that can do the same thing as IsNumeric() from SQL Server or the is_numeric() from PHP, but there’s a way to work the way around.

When I started researching for this particular function, I needed to check for any accounts that had a bad format type for the phone. In this case the phone had to be all numbers only.

The trick to check for this on MySQL is simple, just convert the field to a integer type, but there’s a catch it will only work for integer numbers.

This is how you would do the query.

SELECT * FROM the_table
WHERE 1
AND CONVERT(some_field, SIGNED INTEGER) IS NOT NULL

This will return all rows that have the field correctly set to a number format.

During the study period for the Zend Certification, I have read a lot about the MySQLi library from PHP and all Zend Certification for PHP5 is based on this library. Considering this, I have decided to take a practical look on the library and it’s really amazing what they have done with the new lib.

For making a long story short, this is what - so far - this is what I can say about the new lib.

- Good Points:
* No more need to use pear libraries or any kind of library for doing muti-queries or procedures, this new library has built in features for that.

* The library consider the connection and statements as two different objects.

* You can set the charset, user, server from the object itself.

* It gives support to Master and Slaves servers.

* It will only execute the query after it verify that the statement is correct and you have bind the statement.

* It has a lot of info available on the object that a Db Administrator can use. Information like Server info, protocol, client info, client version, host info, host version and so on.

- Annoying point

* As far as I know you have to specify the columns as vars when you are binding the statement.

The next step is creating a benchmark between this improved library and the old library to see how fast both work.

Coding Horror did a nice job of creating a SQL join visual explanation using Venn Diagrams.

http://www.codinghorror.com/blog/archives/000976.html

I was looking for some functions for using in MySQL and I found this great article about optimizing the server that runs MySQL. It shows useful information including what type of API you should use for developing with the database.

Optimizing MySQL

One of the things that the article references is the virtual links for disks on MySQL. I did a small research and I found this other article that talks about this.

http://dev.mysql.com/doc/refman/5.1/en/symbolic-links.html

Save 5 minutes to read this, have a lot of useful information for the daily basis.

File: Presentation about Optimizing MySQL