Page 1 of 1

how to create mysql tables using php

Posted: Wed Sep 14, 2011 8:32 am
by Tassadduq
Hi dears
i have a code that create the mysql tables in my defined database. It is working very fine on my localhost but when i test is on my web server it does nothing :(
i want to create the database tables automatically through running a php script. here is my code.
please help me. Thanks in Advance

Code: Select all

<?php
	$h = $_REQUEST['dbhost'];
	$d = $_REQUEST['dbname'];
	$u = $_REQUEST['dbuname'];
	$p = $_REQUEST['dbpass'];			
	$connection = mysql_connect($h, $u, $p) or die ("Unable to connect to web server, invalid username or password!");
	mysql_select_db($d) or die ("Unable to select database!");
	
if (mysql_num_rows(mysql_query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '". $d ."'"))) {
	mysql_select_db("$d");
  mysql_query("CREATE TABLE table (...)");
$vlsSql		 =  "

CREATE TABLE `banners` (
  `bannerid` int(11) NOT NULL AUTO_INCREMENT,
  `bannertitle` varchar(200) DEFAULT NULL,
  `bannerimage` varchar(250) DEFAULT NULL,
  `bannerlink` varchar(250) DEFAULT NULL,
  `published` int(11) DEFAULT NULL,
  `aadedon` varchar(80) DEFAULT NULL,
  `updatedon` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`bannerid`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO banners VALUES ('1', null, 'uploads/banners/Untitled_2.png', '#', '1', null, null);

CREATE TABLE `pagesetting` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) DEFAULT NULL,
  `description` text,
  `active` int(11) DEFAULT NULL,
  `updatedon` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
INSERT INTO pagesetting VALUES ('1', 'Meta Page Title', 'My Landing Page', '1', '2011/09/08 16:51:34');
INSERT INTO pagesetting VALUES ('2', 'Meta Page Keywords', 'landing, page, seo, sem, search, engine, marketing, search, engine, optimization, page, ranking, back, links, advertisement', '1', '2011/09/08 16:51:34');
INSERT INTO pagesetting VALUES ('3', 'Meta Page Description', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.', '1', '2011/09/08 16:51:34');
INSERT INTO pagesetting VALUES ('4', 'Logo', 'uploads/logo/logo.jpg', '1', null);
INSERT INTO pagesetting VALUES ('5', 'Slogan', 'My website slogan lorem ipsum', '1', '2011/09/08 17:44:28');
INSERT INTO pagesetting VALUES ('6', 'Page Background', 'uploads/pagebg/jean_victor_balin_dove_clip_art_22251.jpg', '1', null);
INSERT INTO pagesetting VALUES ('7', 'Inner Container Background', 'uploads/innerbg/grey.jpg', '1', null);
INSERT INTO pagesetting VALUES ('8', 'Banner Status', 'Banner setting (Show/Hide)', '1', null);
INSERT INTO pagesetting VALUES ('9', 'Call Us', '21212-432456', '1', '2011/09/08 17:59:13');
INSERT INTO pagesetting VALUES ('10', 'Footer', 'Copyrights © All rights reserved', '1', '2011/09/09 17:41:48');
INSERT INTO pagesetting VALUES ('11', 'Open Close', 'Mon-Fri 9:30am - 5:30pm | Sat 11:00am – 2:00pm', '1', '2011/09/09 18:12:01');

CREATE TABLE `users` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(150) DEFAULT NULL,
  `loginid` varchar(15) NOT NULL DEFAULT '',
  `password` varchar(12) DEFAULT NULL,
  `securitylevel` int(2) DEFAULT '10',
  `active` int(2) DEFAULT '-1',
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=7863 DEFAULT CHARSET=latin1;
INSERT INTO users VALUES ('7862', 'Admin', 'admin', '".$p."', '1', '1');
";
			$vlaSql		 =  explode (";\r", $vlsSql);
			foreach ($vlaSql as $vlsSql)
			{
				if ($vlsSql != "")
					$vlsResult	 =  mysql_query ($vlsSql);
			}
			
			header('location:create_config_file.php?status=success');
}
else {
			echo "Database ($d) not found on your server, Please first create the database and user.";
}
?>

Re: how to create mysql tables using php

Posted: Wed Sep 14, 2011 9:33 am
by AbraCadaver
After the query do mysql_error(). It's possible that the mysql user that you are using doesn't have create permissions for that database.

Re: how to create mysql tables using php

Posted: Wed Sep 14, 2011 10:19 am
by Tassadduq
now getting the following error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"' at line 1

Re: how to create mysql tables using php

Posted: Wed Sep 14, 2011 11:55 am
by social_experiment
After which query did you place the function AbraCadaver suggested? Try breaking the long sql query into pieces and testing them individually. The problem is a syntax error in one of the queries.

Re: how to create mysql tables using php

Posted: Thu Sep 15, 2011 11:38 am
by phphelpme
I have know certain servers to only accept a certain length of MySQL query. I had this issue with GoDaddy servers. I had to split the query up into the create section, then the insert section and everything worked fine.

Best wishes

Re: how to create mysql tables using php

Posted: Thu Sep 15, 2011 8:02 pm
by Deborah226
$sql = "
create table table_name (
id int(11) not null auto_increment primary key,
user varchar(255) ,
name varchar(255)
)
";
mysql_query($sql);


<?php
$link = mysql_connect("127.0.0.1","root","123456");
mysql_select_db("test");

$sql = "
create table table_name (
id int(11) not null auto_increment primary key,
user varchar(255) ,
name varchar(255) charset gbk
)
";
mysql_query($sql) or die(mysql_error());
?>
ipad 2 case

Re: how to create mysql tables using php

Posted: Thu Sep 29, 2011 4:45 am
by Tassadduq
Hi Thanks to everyone. Finally i coded the solution. Here is my complete method that is working very perfectly for those who having the same problem.

First i crate a php script to only create database tables , here is create_tables.php file script which connects to db and create database tables.

Code: Select all

 $h = $_REQUEST['dbhost'];
$d = $_REQUEST['dbname'];
$u = $_REQUEST['dbuname'];
$p = $_REQUEST['dbpass'];	
$connection = mysql_connect($h, $u, $p) or die ("Unable to connect to web server, invalid username or password!");
 if (mysql_num_rows(mysql_query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '". $d ."'"))) {
mysql_select_db("$d");
	
$sql = "
CREATE TABLE table1(
  id int(11) NOT NULL AUTO_INCREMENT,
  title varchar(200) DEFAULT NULL,
  image varchar(250) DEFAULT NULL,
  link varchar(250) DEFAULT NULL,
  published int(11) DEFAULT NULL,
  aadedon varchar(80) DEFAULT NULL,
  updatedon varchar(80) DEFAULT NULL,
  PRIMARY KEY (id)
)
";
mysql_query($sql)or die (sprintf ("Erro [%d]: %s", mysql_errno (), mysql_error ()));

$sql = "
CREATE TABLE table2(
  id int(11) NOT NULL AUTO_INCREMENT,
  title varchar(180) DEFAULT NULL,
  bgfile text,
  status int(11) DEFAULT 0,
  addedon varchar(80) DEFAULT NULL,
  updatedon varchar(80) DEFAULT NULL,
  PRIMARY KEY (id)
)
";
header('location:insert_tables_data.php?h='.$h.'&d='.$d.'&u='.$u.'&p='.$p);
}
else {
echo "Database ($d) not found on your server, Please first create the database and user.";
}
above code will connect to your database server and select your given database and create the tables. if all done successfully it will redirect to the next page which will insert data to tables.
now here is the insert_tables_data.php script which will insert data into tables

Code: Select all

 $h = $_REQUEST['h'];
$d = $_REQUEST['d'];
$u = $_REQUEST['u'];
$p = $_REQUEST['p'];	
$connection = mysql_connect($h, $u, $p) or die ("Unable to connect to web server, invalid username or password!");
 if (mysql_num_rows(mysql_query("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '". $d ."'"))) {
mysql_select_db("$d");
$sql = "
INSERT INTO table1 VALUES ('1',NULL,'uploads/images/images.jpg','link.html',1,NULL,NULL);
";
mysql_query($sql)or die (sprintf ("Erro [%d]: %s", mysql_errno (), mysql_error ()));

$sql = "
INSERT INTO table2 VALUES (1,NULL,'uploads/innerbg/blue.jpg',1,'2011/09/09 15:20:08','2011/09/13 10:17:24');
";
mysql_query($sql)or die (sprintf ("Erro [%d]: %s", mysql_errno (), mysql_error ()));
$sql = "
INSERT INTO table2 VALUES (2,NULL,'uploads/innerbg/grey.jpg',0,'2011/09/09 15:20:13','2011/09/13 10:17:24');
";
mysql_query($sql)or die (sprintf ("Erro [%d]: %s", mysql_errno (), mysql_error ()));
header('location:successfull.php);

IMPORTANT :
The Insert query should be separated on by one to insert the data into table rows. like above.

Re: how to create mysql tables using php

Posted: Thu Sep 29, 2011 4:38 pm
by phphelpme
I thought seperating the creation and insertions would do the trick.

Glad you got it sorted and thanks for sharing.

Best wishes