Uploading database structure with PHP to a MYSQL DB [Archive]

View Full Version : Uploading database structure with PHP to a MYSQL DB

blasto333

02-13-2003, 08:41 PM

I did a mysql dump and I want to make it so my applications uploads the structure of the tables to the database. here is what I got now. This makes the database perfectly and the settings file but it does not upload the structure:(

<?
$company=$_POST["company"];
$address=$_POST["address"];
$phone=$_POST["phone"];
$email=$_POST["email"];
$server=$_POST["server"];
$database=$_POST["database"];
$username=$_POST["username"];
$password=$_POST["password"];

if(!$server or !$database or !$username or !$password or !$company or!$phone)
{
echo "You did not provide all the required fields";

}
else
{
$info="<?
\$company=\"$company\";
\$address=\"$address\";
\$phone=\"$phone\";
\$email=\"$email\";
\$server=\"$server\";
\$database=\"$database\";
\$username=\"$username\";
\$password=\"$password\";
?>";
$open = fopen( "../settings.php", "w" ) or die ( "Operation Failed!" );
fputs( $open, "$info " );
fclose( $open );

include ("../settings.php");
$db = mysql_connect("$server", "$username", "$password");
mysql_create_db($database);
mysql_select_db("$database",$db);

$MAKETABLES="
# Dump of table brands
# ------------------------------

DROP TABLE IF EXISTS brands;

CREATE TABLE `brands` (
`Brand` varchar(30) NOT NULL default '10',
`ID` int(8) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

# Dump of table categories
# ------------------------------

DROP TABLE IF EXISTS categories;

CREATE TABLE `categories` (
`Category` varchar(30) NOT NULL default '10',
`ID` int(8) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

# Dump of table customers
# ------------------------------

DROP TABLE IF EXISTS customers;

CREATE TABLE `customers` (
`FirstN` varchar(25) default NULL,
`LastN` varchar(25) default NULL,
`AccountNum` varchar(10) default NULL,
`PhoneNum` varchar(25) default NULL,
`Comments` varchar(255) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM COMMENT='Customer Info.';

# Dump of table items
# ------------------------------

DROP TABLE IF EXISTS items;

CREATE TABLE `items` (
`ItemName` varchar(30) default NULL,
`ItemID` varchar(15) default NULL,
`Description` varchar(255) default NULL,
`Brand` varchar(150) default NULL,
`Category` varchar(60) default NULL,
`Price` varchar(30) default NULL,
`Tax` varchar(30) default NULL,
`TotalCost` varchar(100) default NULL,
`Quantity` int(6) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM COMMENT='Item Info';

# Dump of table sales
# ------------------------------

DROP TABLE IF EXISTS sales;

CREATE TABLE `sales` (
`Date` date default '0000-00-00',
`Customer` varchar(60) default NULL,
`Items` varchar(30) default NULL,
`Price` varchar(12) default NULL,
`TotalCost` varchar(30) default NULL,
`Quantity` varchar(4) NOT NULL default '',
`Tax` varchar(10) default NULL,
`TaxType` varchar(6) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

";

$result=mysql_query($MAKETABLES, $db);
}
?>

Studio64

02-13-2003, 10:51 PM

add this to the next line

echo mysql_error();

See if it outputs anything... I'd probably guess yes and your code generated an SQL error.

blasto333

02-14-2003, 07:31 AM

I did not echo anything. Hmm. What else do you think the problem could be?

luxline

02-14-2003, 10:14 AM

Does this user have permission to create the database?

Thats my best offer, now if we start clutching at straws, here are a couple of "I doubt it, but maybe..."

That date format in the sales table, double check default date formats for your OS/System

Also ensure set globals is off otherwise you'll get a conflict setting the $customer etc...

If the database actually gets created, then Im a bit lost for a solution on this one.

luxline

02-14-2003, 10:17 AM

ok script ran okay, I can only suggest its a problem with user permissions.

Good luck, hope you find a solution!

sasha

02-14-2003, 10:22 AM

Try puting mysql_error() on a few places and some echo so we see where your code breaks.

just after :
mysql_create_db($database) ;
add:
echo "CREATING DB:" . mysql_error() . " " ;

just after :
mysql_select_db("$database",$db);
add:
echo "SELECTING DB:" . mysql_error() . " " ;

just after :
$result=mysql_query($MAKETABLES, $db);
add :
echo "MAKING TABLES: " . mysql_error() . " " ;

What output you get after this ?

blasto333

02-14-2003, 03:42 PM

No errors reported using this code

<?
$company=$_POST["company"];
$address=$_POST["address"];
$phone=$_POST["phone"];
$email=$_POST["email"];
$server=$_POST["server"];
$database=$_POST["database"];
$username=$_POST["username"];
$password=$_POST["password"];

if(!$server or !$database or !$username or !$password or !$company or!$phone)
{
echo "You did not provide all the required fields";

}
else
{
$info="<?
\$company=\"$company\";
\$address=\"$address\";
\$phone=\"$phone\";
\$email=\"$email\";
\$server=\"$server\";
\$database=\"$database\";
\$username=\"$username\";
\$password=\"$password\";
?>";
$open = fopen( "../settings.php", "w" ) or die ( "Operation Failed!" );
fputs( $open, "$info " );
fclose( $open );

include ("../settings.php");
$db = mysql_connect("$server", "$username", "$password");
mysql_create_db($database);
echo "CREATING DB:" . mysql_error() . " " ;
mysql_select_db("$database",$db);
echo "SELECTING DB:" . mysql_error() . " " ;

$MAKETABLES="
# Dump of table brands
# ------------------------------

DROP TABLE IF EXISTS brands;

CREATE TABLE `brands` (
`Brand` varchar(30) NOT NULL default '10',
`ID` int(8) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

# Dump of table categories
# ------------------------------

DROP TABLE IF EXISTS categories;

CREATE TABLE `categories` (
`Category` varchar(30) NOT NULL default '10',
`ID` int(8) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

# Dump of table customers
# ------------------------------

DROP TABLE IF EXISTS customers;

CREATE TABLE `customers` (
`FirstN` varchar(25) default NULL,
`LastN` varchar(25) default NULL,
`AccountNum` varchar(10) default NULL,
`PhoneNum` varchar(25) default NULL,
`Comments` varchar(255) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM COMMENT='Customer Info.';

# Dump of table items
# ------------------------------

DROP TABLE IF EXISTS items;

CREATE TABLE `items` (
`ItemName` varchar(30) default NULL,
`ItemID` varchar(15) default NULL,
`Description` varchar(255) default NULL,
`Brand` varchar(150) default NULL,
`Category` varchar(60) default NULL,
`Price` varchar(30) default NULL,
`Tax` varchar(30) default NULL,
`TotalCost` varchar(100) default NULL,
`Quantity` int(6) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM COMMENT='Item Info';

# Dump of table sales
# ------------------------------

DROP TABLE IF EXISTS sales;

CREATE TABLE `sales` (
`Date` date default '0000-00-00',
`Customer` varchar(60) default NULL,
`Items` varchar(30) default NULL,
`Price` varchar(12) default NULL,
`TotalCost` varchar(30) default NULL,
`Quantity` varchar(4) NOT NULL default '',
`Tax` varchar(10) default NULL,
`TaxType` varchar(6) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

";

$result=mysql_query($MAKETABLES, $db);
echo "MAKING TABLES: " . mysql_error() . " " ;

}

?>

I have all permissions with my user, it is the only one and it is the root user

luxline

02-14-2003, 04:10 PM

Youre going to have to split that $MAKETABLES line at each semi-colon, and run multiple mysql queries, basically one command per query.

Testing it on Windows and Unix servers, the mysql_query function doenst like multiple commands.

luxline

02-14-2003, 04:16 PM

Or in practical terms, change the line
$result=mysql_query($MAKETABLES, $db);

to:

$array = explode( ';', $MAKETABLES );
foreach( $array as $single_query ){
$result = mysql_query( $single_query,$db );
}

You owe me a beer!

blasto333

02-14-2003, 04:45 PM

This code

<?
$company=$_POST["company"];
$address=$_POST["address"];
$phone=$_POST["phone"];
$email=$_POST["email"];
$server=$_POST["server"];
$database=$_POST["database"];
$username=$_POST["username"];
$password=$_POST["password"];

if(!$server or !$database or !$username or !$password or !$company or!$phone)
{
echo "You did not provide all the required fields";

}
else
{
$info="<?
\$company=\"$company\";
\$address=\"$address\";
\$phone=\"$phone\";
\$email=\"$email\";
\$server=\"$server\";
\$database=\"$database\";
\$username=\"$username\";
\$password=\"$password\";
?>";
$open = fopen( "../settings.php", "w" ) or die ( "Operation Failed!" );
fputs( $open, "$info " );
fclose( $open );

include ("../settings.php");
$db = mysql_connect("$server", "$username", "$password");
mysql_create_db($database);
mysql_select_db("$database",$db);

$MAKETABLES="
DROP TABLE IF EXISTS brands;

CREATE TABLE `brands` (
`Brand` varchar(30) NOT NULL default '10',
`ID` int(8) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS categories;

CREATE TABLE `categories` (
`Category` varchar(30) NOT NULL default '10',
`ID` int(8) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS customers;

CREATE TABLE `customers` (
`FirstN` varchar(25) default NULL,
`LastN` varchar(25) default NULL,
`AccountNum` varchar(10) default NULL,
`PhoneNum` varchar(25) default NULL,
`Comments` varchar(255) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM COMMENT='Customer Info.';

DROP TABLE IF EXISTS items;

CREATE TABLE `items` (
`ItemName` varchar(30) default NULL,
`ItemID` varchar(15) default NULL,
`Description` varchar(255) default NULL,
`Brand` varchar(150) default NULL,
`Category` varchar(60) default NULL,
`Price` varchar(30) default NULL,
`Tax` varchar(30) default NULL,
`TotalCost` varchar(100) default NULL,
`Quantity` int(6) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM COMMENT='Item Info';

DROP TABLE IF EXISTS sales;

CREATE TABLE `sales` (
`Date` date default '0000-00-00',
`Customer` varchar(60) default NULL,
`Items` varchar(30) default NULL,
`Price` varchar(12) default NULL,
`TotalCost` varchar(30) default NULL,
`Quantity` varchar(4) NOT NULL default '',
`Tax` varchar(10) default NULL,
`TaxType` varchar(6) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

";

$array = explode( ';', $MAKETABLES );
foreach( $array as $single_query )
{
$result = mysql_query( $single_query,$db );
}

?>

Gets me the error: Parse error: parse error, unexpected $ in /Library/WebServer/Documents/TEST/Install/makeinstall.php on line 74

Studio64

02-14-2003, 04:57 PM

Which line is line 74?

luxline

02-14-2003, 05:10 PM

Either you're running out of memory on that array, or the php file is being uploaded in binary format.

It runs fine here, and on a couple of shared hosting servers. Bit stuck for an answer.

ONLY the last line with:
) TYPE=MyISAM;

remove the semi-colon, as the explode function will just produce an empty query string for the last pass.

blasto333

02-14-2003, 05:46 PM

THANK you thank you thank you! It was just a spacing issue or something here is what works!

<?
//Gets the info that was typed in on the form.
$company=$_POST["company"];
$address=$_POST["address"];
$phone=$_POST["phone"];
$email=$_POST["email"];
$server=$_POST["server"];
$database=$_POST["database"];
$username=$_POST["username"];
$password=$_POST["password"];

//Checks to make sure the required fields were filled out.
if(!$server or !$database or !$username or !$password or !$company or!$phone)
{
echo "You did not provide all the required fields";

}
else
{
/*Writes the info to a settings file which the program needs for all database connections
and displaying info about the company.
*/
$info="<?
\$company=\"$company\";
\$address=\"$address\";
\$phone=\"$phone\";
\$email=\"$email\";
\$server=\"$server\";
\$database=\"$database\";
\$username=\"$username\";
\$password=\"$password\";
?>";
$open = fopen( "../settings.php", "w" ) or die ( "Operation Failed!" );
fputs( $open, "$info " );
fclose( $open );

//Creates the Database the user wants
include ("../settings.php");
$db = mysql_connect("$server", "$username", "$password");
mysql_create_db($database);
mysql_select_db("$database",$db);

//Puts the correct table structure in the database, so the user can begin to use the program!
$MAKETABLES="

DROP TABLE IF EXISTS brands;

CREATE TABLE `brands` (
`Brand` varchar(30) NOT NULL default '10',
`ID` int(8) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS categories;

CREATE TABLE `categories` (
`Category` varchar(30) NOT NULL default '10',
`ID` int(8) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;

DROP TABLE IF EXISTS customers;

CREATE TABLE `customers` (
`FirstN` varchar(25) default NULL,
`LastN` varchar(25) default NULL,
`AccountNum` varchar(10) default NULL,
`PhoneNum` varchar(25) default NULL,
`Comments` varchar(255) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM COMMENT='Customer Info.';

DROP TABLE IF EXISTS items;

CREATE TABLE `items` (
`ItemName` varchar(30) default NULL,
`ItemID` varchar(15) default NULL,
`Description` varchar(255) default NULL,
`Brand` varchar(150) default NULL,
`Category` varchar(60) default NULL,
`Price` varchar(30) default NULL,
`Tax` varchar(30) default NULL,
`TotalCost` varchar(100) default NULL,
`Quantity` int(6) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM COMMENT='Item Info';

DROP TABLE IF EXISTS sales;

CREATE TABLE `sales` (
`Date` date default '0000-00-00',
`Customer` varchar(60) default NULL,
`Items` varchar(30) default NULL,
`Price` varchar(12) default NULL,
`TotalCost` varchar(30) default NULL,
`Quantity` varchar(4) NOT NULL default '',
`Tax` varchar(10) default NULL,
`TaxType` varchar(6) default NULL,
`ID` int(6) NOT NULL auto_increment,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;";

//Does the query to put it in the database.
$array =explode (';' ,$MAKETABLES );
foreach( $array as $single_query )
{
$result =mysql_query ($single_query ,$db );
}

}

?>