how to create mysql tables using php

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Tassadduq
Forum Commoner
Posts: 60
Joined: Wed Dec 03, 2008 2:53 pm

how to create mysql tables using php

Post 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.";
}
?>
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: how to create mysql tables using php

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
Tassadduq
Forum Commoner
Posts: 60
Joined: Wed Dec 03, 2008 2:53 pm

Re: how to create mysql tables using php

Post 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
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: how to create mysql tables using php

Post 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.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
phphelpme
Forum Contributor
Posts: 261
Joined: Sun Nov 21, 2010 3:32 pm

Re: how to create mysql tables using php

Post 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
Deborah226
Forum Newbie
Posts: 1
Joined: Thu Sep 08, 2011 4:10 am

Re: how to create mysql tables using php

Post 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
User avatar
Tassadduq
Forum Commoner
Posts: 60
Joined: Wed Dec 03, 2008 2:53 pm

Re: how to create mysql tables using php

Post 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.
phphelpme
Forum Contributor
Posts: 261
Joined: Sun Nov 21, 2010 3:32 pm

Re: how to create mysql tables using php

Post by phphelpme »

I thought seperating the creation and insertions would do the trick.

Glad you got it sorted and thanks for sharing.

Best wishes
Post Reply