Bulk Insert => MYSQL

[d22552000]

Since people keep ignoring me... Ill post the question in a different way, without the source.

 

How do I instert multiple rows of SQL into a database from a php file?

Is there a way to have a PHP file read a MYSQL DUMP (.sql) file and run it?

 

==

 

Please reply this time.. -,-!

[cooldude832]

php can read any file type, you just have to know its file structure to turn it into what you need.

[d22552000]

.... and that's what im asking..  How to do it.

 

I tried something LIKE fopen with fread and explode,

but I got errors in it, and instead made it a variable...

 

but as you can see in the "Error in line 13618" post in this forum...

that didnt work to well either.

[cooldude832]

read up on sql dumps info or try phpmyadmin

[dbo]

Look into LOAD DATA INFILE or using a shell/exec command to use the mysqlimport utility.

[d22552000]

see now,. ive looked up everythign like this, but havent treid LOAD DATA INFILE im gonna look taht up now.

 

The problem with the insert is that its DYNAMIC.  I need to insert this from a PHP file.  It is about 3.5MB of queries, all of the queries are static wihtout anythign in them that change, but I have to be able to import them using a php script becasue they need to be imported into multiple databases.

 

I will look up LOAD DATA INFILE but in the mean time, other suggestions are welcome.

[dbo]

Both of the ways I mentioned can be done programatically through PHP.

[d22552000]

so is the syntax:

$DO = "LOAD DATA INFILE C:\*\*\*\f.sql";
mysql_query($DO) or die(mysql_error());

??

[d22552000]

I cant do that.

 

LOAD DATA INFILE only loads into ONE TABLE AT A TIME.

 

I can't do that cuase I have over 890 tables to insert.

[dbo]

The other idea I gave was better anyways. Can you paste a subsection of the file in here? I'm sure it's a generic syntax.

[d22552000]

what do you mean by shell/exec.

[dbo]

its a php function to let you execute an external program.

[d22552000]

ok... so do...

 

exec("mysql.exe /p paswordmysql /e source f.sql");

 

or somethign like that..

[dbo]

That's the right idea but you need to use mysqlimport.exe not mysql.exe. Google mysqlimport for example usage.

[d22552000]

well.. I could sitll run the command through mysqld.exe couldnt I..

 

can you please give me an exampe..É

[dbo]

Well you've not pasted me a section of the file yet.

[d22552000]

uh..

 

dammit I cant post it

 

my comptuer replaces left bracket with ^^ and right with çç

 

ugh, ill restart my computer and post the code.

[dbo]

You don't need to post the whole file just a handful of lines. Even if it replaces it you should be able to manually modify it for a few lines.

[d22552000]

I can post it, but it wont be in code tags, w1e im posting...

 

 

VBCREATE.PHP

 

<?PHP

 

$time = microtime();

$time = explode(" ", $time);

$time = $time[1] + $time[0];

$start = $time;

 

function dircopy($srcdir, $dstdir, $verbose = false) {

$num = 0;

if(!is_dir($dstdir)) mkdir($dstdir);

if($curdir = opendir($srcdir)) {

while($file = readdir($curdir)) {

if($file != '.' && $file != '..') {

$srcfile = $srcdir . '\\' . $file;

$dstfile = $dstdir . '\\' . $file;

if(is_file($srcfile)) {

if(is_file($dstfile)) $ow = filemtime($srcfile) - filemtime($dstfile); else $ow = 1;

if($ow > 0) {

if($verbose) echo "Copying '$srcfile' to '$dstfile'...";

if(copy($srcfile, $dstfile)) {

touch($dstfile, filemtime($srcfile)); $num++;

if($verbose) echo "OK\n";

}

else echo "Error: File '$srcfile' could not be copied!\n";

}

}

else if(is_dir($srcfile)) {

$num += dircopy($srcfile, $dstfile, $verbose);

}

}

}

closedir($curdir);

}

return $num;

}

 

/////////////////////////////////////////////////////////////////////////////////////

 

error_reporting(E_ALL);

ini_set("display_errors", 1);

set_time_limit(0);

 

echo "Please be patient, this is long step..<br /><br />";

 

$n = $_POST['n'];

$p = $_POST['p'];

 

    $g_link = mysql_connect( '127.0.0.1', 'root', '') or die('Could not connect to server.' );

    mysql_query('CREATE DATABASE ' . $n . ';');

    mysql_select_db($n, $g_link) or die('Could not select database.');

 

$num = dircopy('C:/Inetpub/wwwroot/VB3.6.7/forum', 'C:/Inetpub/wwwroot/'.$p, 0);

 

echo $num . " Copied.<br /><br />Writing Config..<br /><br />";

 

$filename='C:/Inetpub/wwwroot/'.$p.'/includes/config.php';

 

$somecontent = "\$config['Database']['dbname'] = '" . $n . "';\$config['Database']['tableprefix'] = '';";

$somecontent = $somecontent . "\$config['Database']['technicalemail'] = '';\$config['Database']['force_sql_mode'] = true;";

$somecontent = $somecontent . "\$config['MasterServer']['servername'] = 'localhost';\$config['MasterServer']['port'] = 3306;";

$somecontent = $somecontent . "\$config['MasterServer']['username'] = 'root';\$config['MasterServer']['password'] = '';";

$somecontent = $somecontent . "\$config['MasterServer']['usepconnect'] = 0;\$config['Misc']['admincpdir'] = 'admincp';";

$somecontent = $somecontent . "\$config['Misc']['modcpdir'] = 'modcp';\$config['Misc']['cookieprefix'] = 'bb';";

$somecontent = $somecontent . "\$config['Misc']['forumpath'] = '';\$config['SpecialUsers']['canviewadminlog'] = '1,2';";

$somecontent = $somecontent . "\$config['SpecialUsers']['canpruneadminlog'] = '1';\$config['SpecialUsers']['canrunqueries'] = '';";

$somecontent = $somecontent . "\$config['SpecialUsers']['undeletableusers'] = '';\$config['SpecialUsers']['superadministrators'] = '1,2';";

$somecontent = $somecontent . "\$config['Misc']['maxwidth'] = 2592;\$config['Misc']['maxheight'] = 1944;";

 

 

if (!$handle = fopen($filename, 'a')) {

echo "<br /><br /><strong>Cannot open file</strong> ($filename)";

exit;

}

 

if (fwrite($handle, $somecontent) === FALSE) {

echo "Cannot write to file ($filename)";

exit;

}

 

echo "Success";

fclose($handle);

 

require('C:/Inetpub/wwwroot/VB3.6.7/sql.php');

sql($n);

 

echo '<br /><br /><br /><br /><a href="http://24.86.150.207/' . $p . '/admincp/index.php">Login to AdminCP</a>';

 

 

$time = microtime();

$time = explode(" ", $time);

$time = $time[1] + $time[0];

$finish = $time;

$totaltime = ($finish - $start);

printf ("This page took %f seconds to load.", $totaltime);

 

?>

 

WORKS as far as I know... but the problem is in sql.php

 

CONTENT OF SQL.php

 

<?PHP

 

function sql($DB) {

    $g_link = mysql_connect( '127.0.0.1', 'root', '') or die('Could not connect to server.' );

    mysql_select_db($DB, $g_link) or die('Could not select database.');

 

read = èè;

mysql_query($read, $g_link) or die('Could not perform SQL.' . mysql_error()); echo 'End of SQL.';

 

mysql_close($g_link);

}

 

?>

[dbo]

You're killing me. I need to see your datafile not your code.

[d22552000]

the sql can be seen at:

 

http://24.86.150.207/sql.sql.sql.sql

 

sql four times was on purpose.

[dbo]

*sigh* All I wanted you to do was paste me a handful of lines. Hold on.

[d22552000]

sorry about that, its just.. uh.. big.

[dbo]

mysqlimport will do exactly what you want.

 

If you don't want to do that your file has a generic enough syntax that you could easily parse it. Here's the pseudo code for that process.

 

Upload file

Connect to mysql

Open file

    Start looping through each line

        Concat the string until it ends with ; when it does execute the query and reset the variable

    end loop

Close File

Disconnect from mysql

[d22552000]

I am REALLY bad at mysql and php so if you could give me information on how to use EITHER of them, even on how to use the mysql import.. it would help me.