how to pocess a whole dump at a time?

sid666

ok... following problem...

im writing my first bigger php/mysql script and need to do the following:

after uploading the files, the script checks if the db is already set up - if NOT (till here it works), then the mysql dump should be "applied" (for the lack of a better word) to the database.

so i pretty much have a dump in a string and want the whole thing to be processed...

the thing is, i got it like that now:

<?php

$link = mysql_connect($dbhost, $dbuser, $dbpw);

$initquery = "CREATE TABLE " . "$prefix" . "starxtest ( id tinyint(4) NOT NULL auto_increment, testname tinytext NOT NULL, PRIMARY KEY (id)) TYPE=MyISAM;";

if (mysql_db_query($dbname, $initquery, $link)) {
mysql_close($testlink);
header("Location:$PHP_SELF");
}
else {
print "database tables could not be created...";
mysql_close($link);
exit();
}

ok - so far so good - this actually DOES work... but now have a closer look at the $initquery variable which holds the query:
as long as it got ONE statement as here it works fine. the thing is: i want it to hold a whole DUMP which - of course consists of a whole lot more than just one statement.

so how can i process this? is there a way to have it split up in array elements and have it processed one by one, or whats the best way to do that...?

what i need is pretty much what f.ex. phpmyadmin does, when you "Run SQL query/queries" on a database

thanks a lot - every help is greatly appreciated...

thanks a lot - sid

sid666

does noone have an idea? thats like the last thing i need for this script to be published... grrrrrrr

ccranium

I'm not sure if I understand correctly, but as I read it, you want to be able to run a large number of queries to create the tables on your database, all in one statement. Is that correct?

If that's the case, try adding "GO" between each of your sql commands. So something like :

sqlstring = "create table foo ( field1, field2) GO "
sqlstring += "create table bar ( field1) GO"

That will turn each of your sql statements into a discrete command to be run on the database.

sid666

yeah... thats pretty much what i need... ill give it a shot and let you know if it works...

thanks a lot man!

sid666

ok... i tried this:

$initquery .= "CREATE TABLE " . "$prefix" . "starxtest ( id tinyint(4) NOT NULL auto_increment, testname tinytext NOT NULL, PRIMARY KEY (id)) TYPE=MyISAM; GO ";
$initquery .= "INSERT INTO " . "$prefix" . "starxtest VALUES (1, 'The Big starXtest-Test') GO ";

but still the same result...

ccranium

It could be a problem with the concatination.

What are the contents of $initquery after you load it all up? (do an echo $initquery😉

What does mysql_error(); return?

sid666

...ok... this is a bit... umm... weird... but it works:

<?php

require "config.php";

//initialize database: $initquery[] = array holding the queries

$initquery[] = "CREATE TABLE " . "$prefix" . "starxtest (
id tinyint(4) NOT NULL auto_increment,
testname tinytext NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;";

$initquery[] = "INSERT INTO " . "$prefix" . "starxtest VALUES (1, 'The Big starXtest-Test');";

//more queries here...

// execute query-array elements

for ($i = 0; $i < count($initquery); $i++) {

	if (mysql_db_query($dbname, $initquery[$i], $link)) {
	}
	else {
			print "
			<CENTER>
			<FONT COLOR=red>database tables could not be created...<BR>
			<BR>
			please check the settings for the database variables in <B>config.php</B>!</FONT>
			";
			mysql_close($link);
			exit();
	}

}

header("Location:$PHP_SELF");
mysql_close($link);

i just have a n array with elements consisting of the statements - then i just run through it and execute one statement array after the other...

thanks again rob... you gave me the clue!

sid666

$initquery included the whole dump (all statements in one long string)- with the new way it is an array where each element holds exactly one statement.

i didnt rum mysql_error() yet - ill try that later!

sid666

thats what i needed the code for - lemme know what you are thinking!

http://www.stargraphx.com/scripts/php/starxtest.php

sid