note 45573 deleted from function.mysql-query by philip
| From: | philip@php.net | Date: | Fri, 04 Sep 2015 19:18:32 +0000 |
| Subject: | note 45573 deleted from function.mysql-query by philip | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-203641@lists.php.net to get a copy of this message | ||
Note Submitter: me at harveyball dot com
----
Just thought id post this as i couldnt find a nice and simple way of dumping data from a mysql
database and all the functions i found were way overly complicated so i wrote this one and thought
id post it for others to use.
//$link is the link to the database file
//$db_name is the name of the database you want to dump
//$current_time is just a reference of time()
//returns $thesql which is a string of all the insert into statements
function dumpData()
{
global $link,$db_name,$current_time;
$thesql="";
$thesql.="#SQL DATA FOR $mdb_name \n";
$thesql.="#BACK UP DATE ". date("d/m/Y G:i.s",$current_time)." \n";
$result = mysql_list_tables($mdb_name);
while ($row = mysql_fetch_row($result))
{
$getdata=mysql_query("SELECT * FROM $row[0]");
while ($row1=mysql_fetch_array($getdata))
{
$thesql.="INSERT INTO
$row[0] VALUES (";
$getcols = mysql_list_fields($mdb_name,$row[0],$link);
for($c=0;$c<mysql_num_fields($getcols);$c++)
{
if (strstr(mysql_field_type($getdata,$c),'blob')) $row1[$c]=bin2hex($row1[$c]);
//Binary null fix if ever needed
if ($row1[$c]=="0x") $row1[$c]="0x1";
//delimit the apostrophies for mysql compatability
$row1[$c]=str_replace("'","''",$row1[$c]);
if (strstr(mysql_field_type($getdata,$c),'blob'))
$thesql.="0x$row1[$c]";
else
$thesql.="'$row1[$c]'";
if ($c<mysql_num_fields($getcols)-1) $thesql.=",";
}
$thesql.=");;\n";
}
}
return $thesql;
}
Please note the sql statements are terminated with ;; not a ; this is so when you want to do a
multiple query you can tokenise the sql string with a ;; which allows your data to contain a ;
If you want to run the multiple query then use this simple function which i wrote due to not being
able to find a decent way of doing it
//$q is the query string ($thesql returned string)
//$link is the link to the database connection
//returns true or false depending on whether a single query is executed allows you to check to see
if any queries were ran
function multiple_query($q,$link)
{
$tok = strtok($q, ";;\n");
while ($tok)
{
$results=mysql_query("$tok",$link);
$tok = strtok(";;\n");
}
return $results;
}