Tag: mysql

Use PHP to Backup your MySQL Database

If you don’t backup your databases regularly, shame on you. You should!

The code below will allow you to generate a backup as often as you’d like. It only makes sense to run this via a CRON job. This is pretty easy to do and you can set the frequency as you wish.

The backup files will be stored in the same directory as this script.

Please leave feedback and let me know if this works for you!

Here’s the PHP code:

Select Code
backup_tables('localhost','username','password','blog');


// Backup the entire database or just a specific table.  
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
    
    $link = mysql_connect($host,$user,$pass);
    mysql_select_db($name,$link);
    
    //get all of the tables
    if($tables == '*')
    {
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result))
        {
            $tables[] = $row[0];
        }
    }
    else
    {
        $tables = is_array($tables) ? $tables : explode(',',$tables);
    }
    
    //This method is completed for each table
    foreach($tables as $table)
    {
        $result = mysql_query('SELECT * FROM '.$table);
        $num_fields = mysql_num_fields($result);
        
        $return.= 'DROP TABLE '.$table.';';
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
        $return.= "\n\n".$row2[1].";\n\n";
        
        for ($i = 0; $i < $num_fields; $i++) 
        {
            while($row = mysql_fetch_row($result))
            {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j<$num_fields; $j++) 
                {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                    if ($j<($num_fields-1)) { $return.= ','; }
                }
                $return.= ");\n";
            }
        }
        $return.="\n\n\n";
    }
    
    //Now, we'll save the file
    $handle = fopen('backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}

Return Random Record via MySQL

MySQL
There are many practical and frequently used methods requiring a random record to be called. For instance, when you see “featured profiles” or random customer comments on a website, they are most likely using a function to call a random record.

In addition, this function can allow you to display data in a random order.

Here’s how it works:

Select Code
SELECT field_1, field_2, field_3, field_4
FROM table_name
WHERE parameter = 1
ORDER BY RAND()
LIMIT 1

It’s that simple. Please post your feedback!

PHP/AJAX: Call PHP function by clicking a link

This tutorial demonstrates how to execute an external PHP function by clicking a simple link within HTML. The method uses AJAX so that the page doesn’t refresh and doesn’t require a form submission.

1) Paste the following code into a .js file. For demonstration purposes, we have named it “ajax_link.js”.

Select Code
/*
* ajax_link.js
* chriscook.me
*/

function loadurl(dest) {

try {
// Moz supports XMLHttpRequest. IE uses ActiveX.
// browser detction is bad. object detection works for any browser
xmlhttp = window.XMLHttpRequest?new XMLHttpRequest(): new ActiveXObject("Microsoft.XMLHTTP");
} catch (e) {
// browser doesn't support ajax. handle however you want
}

// the xmlhttp object triggers an event everytime the status changes
// triggered() function handles the events
xmlhttp.onreadystatechange = triggered;

// open takes in the HTTP method and url.
xmlhttp.open("GET", dest);

// send the request. if this is a POST request we would have
// sent post variables: send("name=aleem gender=male)
// Moz is fine with just send(); but
// IE expects a value here, hence we do send(null);
xmlhttp.send("null");
}

function triggered() {
if ((xmlhttp.readyState == 4) (xmlhttp.status == 200)) {

document.getElementById("ajaxlink").innerHTML = xmlhttp.responseText;
}
}

2) Next, add the following code in the section of your HTML file.

Select Code
<script src="ajax_link.js" type="text/javascript"></script>

3) The following code should be placed in the HTML body of a PHP file.

Select Code
<div id="ajaxlink" onclick="loadurl('ajax_function.php')">Click Here</div>

Replace ‘ajax_function.php’ with the correct file you want to execute. For instance, if you want your users to “click here” in order to send themselves a copy of their monthly invoice, you’d code the mailer function in ‘ajax_function.php’.

4) That’s it. Ensure that you keep the <div> id as “ajaxlink”. Once you click the link, it will disappear (until the page is refreshed).

Feedback: I’d love to hear how you’ve used this tool. Please feel free to post a comment on my blog.

Disclaimer: This tutorial is provided to demonstrate how to perform the function. Please ensure that you review the code and add security measures before using this in a production environment.