how to run sql script from php?

[alan123]

I want to create a table from php by calling a sql script file(createdb.sql), how to impliment this? thanks for any help.

 

[Westbury]

What type of database is it? (MySQL, MSSQL, etc...)

Whatever the type, you need to open the file first;

$filename = "/path/to/file/createdb.sql";
$fd = fopen ($filename, "r");
$contents = fread ($fd, filesize ($filename));
fclose ($fd);

Then connect to your database and query it with $contents. Look at the php documentation for how to do that as it depends on the type of database.

http://www.php.net/manual/en/funcref.php

If it aint broke, redesign it!

 

[Westbury]

Just for future reference please read faq581-3339.
Although everyone here is generally more than happy to help, it can get fustrating when people ask questions before doing any research into the matter themselves.

If it aint broke, redesign it!

 

[cLFlaVA]

Or, if you're using a MySQL database, once you get the file opening down, you can check out the faq (faq434-3850) on how to get data from MySQL into PHP.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[alan123]

Thanks for the reply.
Yes the db type is mysql.
I try to run sql script from php code to create table, but it fails.

<?
$dbhost = "localhost";	
$dbusername = "root"; 	
$dbpass = "";	
$dbname	= "dbname"; 	
$connection = mysql_connect($dbhost, $dbusername, $dbpass);
$SelectedDB = mysql_select_db($dbname);
$filename = "createdb.sql";
$fd = fopen ($filename, "r");
$contents = fread ($fd, filesize ($filename));
fclose ($fd);
mysql_query ("$contents");
?>

createdb.sql is:

CREATE TABLE history (
uname varchar(25) NOT NULL default '',
time datetime,  
ip varchar(25),
action varchar(100)
);

I missed something?
Also how can I skip the comment line in create.sql to run it correctly?

Thanks.

 

[Westbury]

It could be failing because your are trying to cerate a column called time. Time is a column type to mysql and so this may be confusing it. Try renaming it.
You also dont need the double quotes into the mysql_query command.

mysql_query($contents);

What comment line in createdb.sql? Any comments (if correctly commented out) will be ignored by mysql.

If that doesn't work, try adding or die command to the end of your mysql commands
eg:

$connection = mysql_connect($dbhost, $dbusername, $dbpass) or die("Connect failed: " . mysql_error());
$SelectedDB = mysql_select_db($dbname) or die("Select failed: " . mysql_error());
$filename = "createdb.sql";
$fd = fopen ($filename, "r");
$contents = fread ($fd, filesize ($filename));
fclose ($fd);
mysql_query ($contents) or die("Query failed: " . mysql_error());

Hope that helps

Westbury

If it aint broke, redesign it!

 

[alan123]

Thanks for the help.
Now I got a issue, in createdb.sql, if I only create one table, the php code can generate it correctly, however if I want to create two tables, php cannot generate and gives error "Query failed: You have an error in your SQL syntax near 'CREATE TABLE profile...", I have check but couldn't find error.
createdb.sql:

CREATE TABLE history (
uname varchar(25) NOT NULL default '',
stime datetime,  
ip varchar(25),
action varchar(100)
)TYPE=MyISAM;

#create table profile
CREATE TABLE profile (
name varchar(20),
address varchar(50)
)TYPE=MyISAM;

I tried to remove comment line but still shows error, what may cause this problem?

 

[cLFlaVA]

You can't run two SQL statements at the same time. You'll have to loop through, running each SQL statement individually.



*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[alan123]

can only run one SQL statement? but I know php can run .sql script which contains multiple statements.

 

[Westbury]

Where have you heard that? If you are referring to something like phpMyAdmin then that works in a different way. I believe it splits in the multiple statements into an array on single statements, then executes each one on it own. (I could be wrong on that, so dont take it as absolute truth)


If it aint broke, redesign it!