Category Archives: Database

How to take backup dump of MySQL database with PHP script

Here is a simple script to take backup of MySQL database, using a PHP script.

<?php
backup_tables(‘localhost’,'username’,'password’,'dbname’);

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$dbname,$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);
}

//cycle through
foreach($tables as $table)
{
$result = mysql_query(‘SELECT * FROM ‘.$table);
$num_fields = mysql_num_fields($result);

$return.= ‘DROP TABLE IF EXISTS ‘.$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”;
}

//save file
$handle = fopen(‘./db/db-backup-’.time().’-’.(md5(implode(‘,’,$tables))).’.sql’,'w+’);
fwrite($handle,$return);
fclose($handle);
}

?>

How to upload a file in PHP

The code to upload file in PHP is very simple, but we need to understand the flow which is a below.

Browse the file from a local system
Upload to server
Server keeps it on a temporary path
Copy from temporary to permanent path

Create a file upload form

<html>

<body>

<form action=”upload-file.php” method=”post” enctype=”multipart/form-data”>

<label for=”file”>Filename:</label>

<input type=”file” name=”file” id=”file” />

<br />

<input type=”submit” name=”submit” value=”Submit” />

</form>

</body>

</html>

Note:

An enctype attribute of the <form> tag has been specified. This attribute specifies which content-type to use when submitting the form
We have used “multipart/form-data” to upload binary data, like the contents of a file, to be uploaded
If proper enctype is not provided, upload will not work.
File upload is a huge security risk so you must check what type of files are being uploade

Create a file upload script (upload-file.php)

<?php

$my_folder = “./uploads/”;

copy($_FILES["file"]["tmp_name"],$my_folder.$_FILES["file"]["name"]);

echo “File uploaded.”;

?>

This will upload the file to the specified path.

Note:

The default file upload size using a browser is usually 2MB so files larger than this size may not upload. You will have to alter the file upload setting on the server.
You need to set write permission to the folder where file needs to be upload. In our case, the “uploads” folder needs to have a 777 permission on a linux/unix server.

How to select and display MySQL records data in PHP

This is the simplest code to select and display records from MySQL database table and display in PHP.

$cn=mysql_connect($db_host,$db_user,$db_password) or die(mysql_error());
mysql_select_db($db_name,$cn) or die(mysql_error());

$sql = “SELECT field_name FROM table_name”;
$rs = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_array($rs)){

echo $field_name = $row["field_name"];
echo “</br>”;

}mysql_free_result($rs);