[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

How To Modify This PHP Script To Convert Entire Database?

Hi,

I found a PHP script somewhere and it seemed really useful to me, but the problem is it only converts 1 table at a time, so if I had a few hundreds of tables in my database it will still take a lot of manual work.

Does anyone here knows how to modify the script and make it convert the entire database once?

Thanks!
<?
$outputfile= "OUTPUT.sql";
$DB_HOST="localhost";
$DB_USER="user";
$DB_PASS="pass";
$DB_DBNAME="name";
$DB_TABLE="table";
 
$fp=fopen($outputfile, "w+");
if(!$fp){
echo "Can not open file for writing.\n";
exit;
}
 
$dbcon=mysql_connect($DB_HOST, $DB_USER, $DB_PASS);
mysql_query("SET NAMES latin1", $dbcon);
mysql_select_db($DB_DBNAME, $dbcon);
$sqlstr="select * from $DB_TABLE";
$rs=mysql_query($sqlstr, $dbcon);
$NAMESTR="SET NAMES utf8;\r\n\r\n";
fwrite($fp, $NAMESTR, strlen($NAMESTR));
while($rsrow=mysql_fetch_row($rs)){
$i_sqlstr="INSERT INTO $DB_TABLE VALUES(";
foreach($rsrow as $rid=>$rval){
$rsrow[$rid]=iconv("BIG5", "UTF-8", $rval);
if(strval($rid)=="0")
$i_sqlstr.="'".addslashes($rsrow[$rid])."'";
else
$i_sqlstr.=", '".addslashes($rsrow[$rid])."'";
}
$i_sqlstr.=");\r\n\r\n";
fwrite($fp, $i_sqlstr, strlen($i_sqlstr));
}
mysql_free_result($rs);
mysql_close($dbcon);
fclose($fp);
 
exit;
?>

Open in new window

0
Chiehkai
Asked:
Chiehkai
  • 4
  • 2
  • 2
1 Solution
 
Beverley PortlockCommented:
If your MySQL user has the relevant authorities then you can get a list of the tables as follows

$rs = mysql_query("SHOW TABLES");
if ( $rs ) {
    while ( $rw = mysql_fetch_array( $rs, MYSQL_NUM ) {
        $tableName = $rw[0];

        convertTable( $tableName, $outputfile  );
    }
}


Where convertTable woud contain your existing code
0
 
bombozamaCommented:
Try this:
<?
$outputfile= "OUTPUT.sql";
$DB_HOST="localhost";
$DB_USER="user";
$DB_PASS="pass";
$DB_DBNAME="name";
 
$fp=fopen($outputfile, "w+");
if(!$fp){
echo "Can not open file for writing.\n";
exit;
}
 
$dbcon=mysql_connect($DB_HOST, $DB_USER, $DB_PASS);
mysql_query("SET NAMES latin1", $dbcon);
mysql_select_db($DB_DBNAME, $dbcon);
 
$sqlstr0 = "SHOW TABLES";
$rs0 = mysql_query($sqlstr0, $dbcon) or die(mysql_error());
$row_rs0 = mysql_fetch_assoc($rs0);
do{
 $sqlstr="select * from ".$row_rs0[0];
 $rs=mysql_query($sqlstr, $dbcon);
 $NAMESTR="SET NAMES utf8;\r\n\r\n";
 fwrite($fp, $NAMESTR, strlen($NAMESTR));
 while($rsrow=mysql_fetch_row($rs)){
 $i_sqlstr="INSERT INTO ".$row_rs0[0]." VALUES(";
 foreach($rsrow as $rid=>$rval){
 $rsrow[$rid]=iconv("BIG5", "UTF-8", $rval);
 if(strval($rid)=="0")
 $i_sqlstr.="'".addslashes($rsrow[$rid])."'";
 else
 $i_sqlstr.=", '".addslashes($rsrow[$rid])."'";
 }
 $i_sqlstr.=");\r\n\r\n";
 fwrite($fp, $i_sqlstr, strlen($i_sqlstr));
 }
} while($row_rs0 = mysql_fetch_assoc($rs0));
 
mysql_free_result($rs);
mysql_close($dbcon);
fclose($fp);
 
exit;
?>

Open in new window

0
 
Beverley PortlockCommented:
Typo - missed a bracket

while ( $rw = mysql_fetch_array( $rs, MYSQL_NUM ) {

should be

while ( $rw = mysql_fetch_array( $rs, MYSQL_NUM ) ) {
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
bombozamaCommented:
Two things: I forgot to erase the $DB_DBNAME="name"; line and this will work only if you have at least one table in your database.
0
 
ChiehkaiAuthor Commented:
bombozama :

I erased the $DB_DBNAME="name"; line and tried to run the script, but it displayed the following message :

"No database selected"

Anything wrong?
0
 
bombozamaCommented:
I'm sorry... got a little confused... leave the line.
Hope it helps.
0
 
ChiehkaiAuthor Commented:
Well, I also tried keeping that line but got these errors :

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /path/to/script.php on line 26

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /path/to/script.php on line 40

Any possible solutions?

Thanks.
0
 
bombozamaCommented:
The free result error happened because the instruction was made out of the loop. The other error is an offset array error. Try this:
<?php
$outputfile = "OUTPUT.sql";
$DB_HOST = "localhost";
$DB_USER = "user";
$DB_PASS = "pass";
$DB_DBNAME = "name";
 
$column = 'Tables_in_'.$DB_NAME;
 
$fp = fopen($outputfile, "w+");
if(!$fp){
 echo "Can not open file for writing.\n";
 exit;
}
 
$dbcon = mysql_connect($DB_HOST, $DB_USER, $DB_PASS);
 mysql_query("SET NAMES latin1", $dbcon);
 mysql_select_db($DB_DBNAME, $dbcon);
 
 $sqlstr0 = "SHOW TABLES";
 $rs0 = mysql_query($sqlstr0, $dbcon) or die(mysql_error());
 $row_rs0 = mysql_fetch_assoc($rs0);
 
 do{
  $sqlstr="select * from ".$row_rs0[$column];
  $rs=mysql_query($sqlstr, $dbcon);
  $NAMESTR="SET NAMES utf8;\r\n\r\n";
  fwrite($fp, $NAMESTR, strlen($NAMESTR));
  while($rsrow = mysql_fetch_row($rs)){
   $i_sqlstr = "INSERT INTO ".$row_rs0[$column]." VALUES(";
   foreach($rsrow as $rid => $rval){
    $rsrow[$rid] = iconv("BIG5", "UTF-8", $rval);
    if(strval($rid)=="0")
     $i_sqlstr .= "'".addslashes($rsrow[$rid])."'";
    else
     $i_sqlstr .= ", '".addslashes($rsrow[$rid])."'";
   }
   $i_sqlstr .= ");\r\n\r\n";
   fwrite($fp, $i_sqlstr, strlen($i_sqlstr));
  }
  mysql_free_result($rs);
} while($row_rs0 = mysql_fetch_assoc($rs0));
 
mysql_close($dbcon);
fclose($fp);
 
exit;
?>

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now