how can i make more then one table in one install file?

scheols · July 18, 2006

like as you can see when you create forums u need a install file so its easier on you right? So how can i make a intall.php file that can create more then one table?

wildteen88 · July 18, 2006

Create an array of all the tables needed to be installed. The use foreach loop to create the table eg:
[code=php:0]
$tables = array();

//table 1
$tables[] = "CREATE TABLE table1 (
-- table1 fields here
);";

// table 2
$tables[] = "CREATE TABLE table2 (
-- table2 fields here
);";

// connect to db here

// now loop through our table array to create our tables:
foreach($tables as $table => $query)
{
// perform our query
mysql_query($queryl) or die(mysql_error());
}[/code]

Thats is basically how got about create more than one table automatically.

scheols · July 18, 2006

foreach($tables as $table => $query)

^where is the $table varible can u verify sorry i never made multiple tables or is it a spelling error

willfitch · July 18, 2006

If you have MySQL 4.1.3+ and the MySQLi extension compiled with your PHP config, you can do the following:

[code=php:0]
<?php
$sql = 'CREATE TABLE table1 (
/* FIELDS HERE */
);';

$sql .= 'CREATE TABLE table2 (
/* FIELDS HERE */
);';
$mysqli = new mysqli('host','username','pass','db');
$i=1;

if ($mysqli->multi_query($sql)) {
do {
if ($result = $mysqli->store_result()) {
echo 'Created table '.$i.' ';
} else {
echo 'Could not create table '.$i.' ';
}
$result->close();
$i++;
} while ($mysqli->next_result());
} // end if
$mysqli->close();
?>
[/code]

scheols · July 18, 2006

MySQL version 4.1.19-standard is my mysql version

whats mysqli and how do i get the extention ?

[code]
foreach($tables as $table => $query)
[/code]

can you plz edit your first example?

willfitch · July 18, 2006

First, check to ensure you don't already have it:
[code=php:0]
<?php
if (function_exists('mysqli_connect')) {
echo 'I have it!';
} else {
echo "I don't have it.";
}
?>
[/code]

If you do, great! Start using it! If you don't, and you are on *nix, you will have to compile PHP with:

--with-mysqli=/path/to/mysql/dir/bin/mysql_config

If you don't have it and you are on Windows, simply edit the php.ini file and add the following during the extension loads:

extension=php_mysqli.dll

scheols · July 18, 2006

http://xizionz.vectoredhost.com/mysqltest.php

tehe :)

im guessing i can use your second one right?

willfitch · July 18, 2006

LOL

Looks like you're good to go!

scheols · July 18, 2006

i got a problem mate -_-

[quote]Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /home/scheols/public_html/forumz/dbcreate.php on line 5[/quote]

[code]<?php
$sql = 'CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(30) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`email` varchar(40) NOT NULL default '',
`msn` varchar(250) NOT NULL default 'Not Specified',
`aim` varchar(250) NOT NULL default 'Not Specified',
`posts` int(10) NOT NULL default '0',
`location` varchar(36) NOT NULL default 'Not Specified',
`online` varchar(12) default NULL,
`level` int(1) default '1',
`validated` varchar(50) NOT NULL default '0',
`val_num` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
)';
$sql .='CREATE TABLE `topics` (
`id` int(9) NOT NULL auto_increment,
`timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
`fid` int(4) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`post` longtext NOT NULL,
`username` varchar(32) NOT NULL default '',
`last_post_username` varchar(32) NOT NULL default '',
`replies` int(9) NOT NULL default '0',
`views` int(9) NOT NULL default '0',
PRIMARY KEY (`id`)
)';
$sql .='CREATE TABLE `replies` (
`id` int(4) NOT NULL auto_increment,
`tid` int(9) NOT NULL default '0',
`post` longtext NOT NULL,
`username` varchar(32) NOT NULL default '',
PRIMARY KEY (`id`)
)';
$sql .='CREATE TABLE `pmessages` (
`title` varchar(255) NOT NULL default 'Untitled Message',
`message` text NOT NULL,
`touser` varchar(255) NOT NULL default '',
`from` varchar(255) NOT NULL default '',
`unread` varchar(255) NOT NULL default 'unread',
`date` date NOT NULL default '0000-00-00',
`id` int(15) NOT NULL auto_increment,
`reply` varchar(15) NOT NULL default 'no',
PRIMARY KEY (`id`)
)';
$sql .='CREATE TABLE `forums` (
`id` int(4) NOT NULL auto_increment,
`cid` int(4) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`description` longtext NOT NULL,
`last_post_title` varchar(255) NOT NULL default '',
`last_post_username` varchar(32) NOT NULL default '',
`topics` int(9) NOT NULL default '0',
`replies` int(9) NOT NULL default '0',
PRIMARY KEY (`id`)
)';
$sql .='CREATE TABLE `catagories` (
`id` int(4) NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
`banner` varchar(250) NOT NULL default '',
PRIMARY KEY (`id`)
)';
include "nconfig.php";
$mysqli = new mysqli('localhost','$username','$pass','$db');
$i=1;
if ($mysqli->multi_query($sql)) {
do {
if ($result = $mysqli->store_result()) {
echo 'Created table '.$i.' ';
} else {
echo 'Could not create table '.$i.' ';
}
$result->close();
$i++;
} while ($mysqli->next_result());
} // end if
$mysqli->close();
?>[/code]
[quote=line5]`password` varchar(255) NOT NULL default '',[/quote]

willfitch · July 18, 2006

Yup, you do. On line five, you used '' for default ''. There are actually many parse errors. lol. Do this:

[code=php:0]
<?php
$sql = "CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(30) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`email` varchar(40) NOT NULL default '',
`msn` varchar(250) NOT NULL default 'Not Specified',
`aim` varchar(250) NOT NULL default 'Not Specified',
`posts` int(10) NOT NULL default '0',
`location` varchar(36) NOT NULL default 'Not Specified',
`online` varchar(12) default NULL,
`level` int(1) default '1',
`validated` varchar(50) NOT NULL default '0',
`val_num` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `topics` (
`id` int(9) NOT NULL auto_increment,
`timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
`fid` int(4) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`post` longtext NOT NULL,
`username` varchar(32) NOT NULL default '',
`last_post_username` varchar(32) NOT NULL default '',
`replies` int(9) NOT NULL default '0',
`views` int(9) NOT NULL default '0',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `replies` (
`id` int(4) NOT NULL auto_increment,
`tid` int(9) NOT NULL default '0',
`post` longtext NOT NULL,
`username` varchar(32) NOT NULL default '',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `pmessages` (
`title` varchar(255) NOT NULL default 'Untitled Message',
`message` text NOT NULL,
`touser` varchar(255) NOT NULL default '',
`from` varchar(255) NOT NULL default '',
`unread` varchar(255) NOT NULL default 'unread',
`date` date NOT NULL default '0000-00-00',
`id` int(15) NOT NULL auto_increment,
`reply` varchar(15) NOT NULL default 'no',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `forums` (
`id` int(4) NOT NULL auto_increment,
`cid` int(4) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`description` longtext NOT NULL,
`last_post_title` varchar(255) NOT NULL default '',
`last_post_username` varchar(32) NOT NULL default '',
`topics` int(9) NOT NULL default '0',
`replies` int(9) NOT NULL default '0',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `catagories` (
`id` int(4) NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
`banner` varchar(250) NOT NULL default '',
PRIMARY KEY (`id`)
)";
include "nconfig.php";
$mysqli = new mysqli('localhost','$username','$pass','$db');
$i=1;
if ($mysqli->multi_query($sql)) {
do {
if ($result = $mysqli->store_result()) {
echo 'Created table '.$i.' ';
} else {
echo 'Could not create table '.$i.' ';
}
$result->close();
$i++;
} while ($mysqli->next_result());
} // end if
$mysqli->close();
?>
[/code]

scheols · July 18, 2006

i get a whole new list or errors now

[quote]
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/scheols/public_html/forumz/nconfig.php on line 10

Warning: mysqli::mysqli() [function.mysqli-mysqli]: (28000/1045): Access denied for user '$username'@'localhost' (using password: YES) in /home/scheols/public_html/forumz/dbcreate.php on line 66

Warning: mysqli::multi_query() [function.mysqli-multi-query]: Couldn't fetch mysqli in /home/scheols/public_html/forumz/dbcreate.php on line 68

Warning: mysqli::close() [function.mysqli-close]: Couldn't fetch mysqli in /home/scheols/public_html/forumz/dbcreate.php on line 79[/quote]

[quote=lines in order]
`location` varchar(36) NOT NULL default 'Not Specified',

$mysqli = new mysqli('localhost','$username','$pass','$db');

if ($mysqli->multi_query($sql)) {

$mysqli->close();

[/quote]

i dont understand these errors since i dont know mysqli so maybe you can still help.

willfitch · July 18, 2006

Yes.

You have your variables for the connection:

[CODE]
$mysqli = new mysqli('localhost','$username','$pass','$db');
[/CODE]
inside single quotes.
Use this:

$mysqli = new mysqli('localhost',$username,$pass,$db);

scheols · July 18, 2006

so humm the page is not a blank page? heres what i came up with and nothen happened:

[code]
<?php
$sql = "CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(30) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`email` varchar(40) NOT NULL default '',
`msn` varchar(250) NOT NULL default 'Not Specified',
`aim` varchar(250) NOT NULL default 'Not Specified',
`posts` int(10) NOT NULL default '0',
`location` varchar(36) NOT NULL default 'Not Specified',
`online` varchar(12) default NULL,
`level` int(1) default '1',
`validated` varchar(50) NOT NULL default '0',
`val_num` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `topics` (
`id` int(9) NOT NULL auto_increment,
`timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
`fid` int(4) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`post` longtext NOT NULL,
`username` varchar(32) NOT NULL default '',
`last_post_username` varchar(32) NOT NULL default '',
`replies` int(9) NOT NULL default '0',
`views` int(9) NOT NULL default '0',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `replies` (
`id` int(4) NOT NULL auto_increment,
`tid` int(9) NOT NULL default '0',
`post` longtext NOT NULL,
`username` varchar(32) NOT NULL default '',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `pmessages` (
`title` varchar(255) NOT NULL default 'Untitled Message',
`message` text NOT NULL,
`touser` varchar(255) NOT NULL default '',
`from` varchar(255) NOT NULL default '',
`unread` varchar(255) NOT NULL default 'unread',
`date` date NOT NULL default '0000-00-00',
`id` int(15) NOT NULL auto_increment,
`reply` varchar(15) NOT NULL default 'no',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `forums` (
`id` int(4) NOT NULL auto_increment,
`cid` int(4) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`description` longtext NOT NULL,
`last_post_title` varchar(255) NOT NULL default '',
`last_post_username` varchar(32) NOT NULL default '',
`topics` int(9) NOT NULL default '0',
`replies` int(9) NOT NULL default '0',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `catagories` (
`id` int(4) NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
`banner` varchar(250) NOT NULL default '',
PRIMARY KEY (`id`)
)";
include "nconfig.php";
$mysqli = new mysqli('localhost',$username,$pass,$db);
$i=1;
if ($mysqli->multi_query($sql)) {
do {
if ($result = $mysqli->store_result()) {
echo 'Created table '.$i.' ';
} else {
echo 'Could not create table '.$i.' ';
}
$result->close();
$i++;
} while ($mysqli->next_result());
} // end if
$mysqli->close();
?>
[/code]

willfitch · July 18, 2006

Try this for error handling purposes:
[code=php:0]
<?php
$sql = "CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(30) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`email` varchar(40) NOT NULL default '',
`msn` varchar(250) NOT NULL default 'Not Specified',
`aim` varchar(250) NOT NULL default 'Not Specified',
`posts` int(10) NOT NULL default '0',
`location` varchar(36) NOT NULL default 'Not Specified',
`online` varchar(12) default NULL,
`level` int(1) default '1',
`validated` varchar(50) NOT NULL default '0',
`val_num` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `topics` (
`id` int(9) NOT NULL auto_increment,
`timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
`fid` int(4) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`post` longtext NOT NULL,
`username` varchar(32) NOT NULL default '',
`last_post_username` varchar(32) NOT NULL default '',
`replies` int(9) NOT NULL default '0',
`views` int(9) NOT NULL default '0',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `replies` (
`id` int(4) NOT NULL auto_increment,
`tid` int(9) NOT NULL default '0',
`post` longtext NOT NULL,
`username` varchar(32) NOT NULL default '',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `pmessages` (
`title` varchar(255) NOT NULL default 'Untitled Message',
`message` text NOT NULL,
`touser` varchar(255) NOT NULL default '',
`from` varchar(255) NOT NULL default '',
`unread` varchar(255) NOT NULL default 'unread',
`date` date NOT NULL default '0000-00-00',
`id` int(15) NOT NULL auto_increment,
`reply` varchar(15) NOT NULL default 'no',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `forums` (
`id` int(4) NOT NULL auto_increment,
`cid` int(4) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`description` longtext NOT NULL,
`last_post_title` varchar(255) NOT NULL default '',
`last_post_username` varchar(32) NOT NULL default '',
`topics` int(9) NOT NULL default '0',
`replies` int(9) NOT NULL default '0',
PRIMARY KEY (`id`)
)";
$sql .="CREATE TABLE `catagories` (
`id` int(4) NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
`banner` varchar(250) NOT NULL default '',
PRIMARY KEY (`id`)
)";
include "nconfig.php";
$mysqli = new mysqli('localhost',$username,$pass,$db);
$i=1;
if ($mysqli->multi_query($sql)) {
do {
if ($result = $mysqli->store_result()) {
echo 'Created table '.$i.' ';
} else {
echo 'Could not create table '.$i.' ';
}
$result->close();
$i++;
} while ($mysqli->next_result());
} else {
die('DB ERROR: '.$mysqli->error);
}
$mysqli->close();
?>
[/code]

scheols · July 18, 2006

[quote]
DB ERROR: 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 'CREATE TABLE `topics` ( `id` int(9) NOT NULL auto_increment, `timestamp` tim' at line 15
[/quote]
thats the error not sure cant wait to finnaly fix this problem thanks will

willfitch · July 18, 2006

Ok, I see your issues. Place a semi-colon after each "CREATE TABLE" statement:

[code=php:0]
<?php
$sql = "CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(30) NOT NULL default '',
`password` varchar(255) NOT NULL default '',
`email` varchar(40) NOT NULL default '',
`msn` varchar(250) NOT NULL default 'Not Specified',
`aim` varchar(250) NOT NULL default 'Not Specified',
`posts` int(10) NOT NULL default '0',
`location` varchar(36) NOT NULL default 'Not Specified',
`online` varchar(12) default NULL,
`level` int(1) default '1',
`validated` varchar(50) NOT NULL default '0',
`val_num` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
);";
$sql .="CREATE TABLE `topics` (
`id` int(9) NOT NULL auto_increment,
`timestamp` timestamp NOT NULL default '0000-00-00 00:00:00',
`fid` int(4) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`post` longtext NOT NULL,
`username` varchar(32) NOT NULL default '',
`last_post_username` varchar(32) NOT NULL default '',
`replies` int(9) NOT NULL default '0',
`views` int(9) NOT NULL default '0',
PRIMARY KEY (`id`)
);";
$sql .="CREATE TABLE `replies` (
`id` int(4) NOT NULL auto_increment,
`tid` int(9) NOT NULL default '0',
`post` longtext NOT NULL,
`username` varchar(32) NOT NULL default '',
PRIMARY KEY (`id`)
);";
$sql .="CREATE TABLE `pmessages` (
`title` varchar(255) NOT NULL default 'Untitled Message',
`message` text NOT NULL,
`touser` varchar(255) NOT NULL default '',
`from` varchar(255) NOT NULL default '',
`unread` varchar(255) NOT NULL default 'unread',
`date` date NOT NULL default '0000-00-00',
`id` int(15) NOT NULL auto_increment,
`reply` varchar(15) NOT NULL default 'no',
PRIMARY KEY (`id`)
);";
$sql .="CREATE TABLE `forums` (
`id` int(4) NOT NULL auto_increment,
`cid` int(4) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`description` longtext NOT NULL,
`last_post_title` varchar(255) NOT NULL default '',
`last_post_username` varchar(32) NOT NULL default '',
`topics` int(9) NOT NULL default '0',
`replies` int(9) NOT NULL default '0',
PRIMARY KEY (`id`)
);";
$sql .="CREATE TABLE `catagories` (
`id` int(4) NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`description` varchar(255) NOT NULL default '',
`banner` varchar(250) NOT NULL default '',
PRIMARY KEY (`id`)
);";
include "nconfig.php";
$mysqli = new mysqli('localhost',$username,$pass,$db);
$i=1;
if ($mysqli->multi_query($sql)) {
do {
if ($result = $mysqli->store_result()) {
echo 'Created table '.$i.' ';
} else {
echo 'Could not create table '.$i.' ';
}
$result->close();
$i++;
} while ($mysqli->next_result());
} else {
die('DB ERROR: '.$mysqli->error);
}
$mysqli->close();
?>
[/code]

When using the multi_query method, you must delimit each query.

scheols · July 18, 2006

although it creates tables i still get this error

[quote]Could not create table 1

Fatal error: Call to a member function close() on a non-object in /home/scheols/public_html/forumz/install.php on line 75[/quote]

$result->close();

also Thank you man once this lil bug is fixed you saved my life :)

scheols · July 18, 2006

bump sorry for bumping

willfitch · July 18, 2006

Glad to see it works.

Ok, take out the $result->close() portion, and you will be good to go. :)

Sign In

how can i make more then one table in one install file?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information