Jump to content

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


scheols

Recommended Posts

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.
Link to comment
Share on other sites

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.'<br />';
          } else {
              echo 'Could not create table '.$i.'<br />';
          }
          $result->close();
          $i++;
    } while ($mysqli->next_result());
} // end if
$mysqli->close();
?>
[/code]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.'<br />';
          } else {
              echo 'Could not create table '.$i.'<br />';
          }
          $result->close();
          $i++;
    } while ($mysqli->next_result());
} // end if
$mysqli->close();
?>[/code]
[quote=line5]`password` varchar(255) NOT NULL default '',[/quote]
Link to comment
Share on other sites

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.'<br />';
          } else {
              echo 'Could not create table '.$i.'<br />';
          }
          $result->close();
          $i++;
    } while ($mysqli->next_result());
} // end if
$mysqli->close();
?>
[/code]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.'<br />';
          } else {
              echo 'Could not create table '.$i.'<br />';
          }
          $result->close();
          $i++;
    } while ($mysqli->next_result());
} // end if
$mysqli->close();
?>
[/code]
Link to comment
Share on other sites

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.'<br />';
          } else {
               echo 'Could not create table '.$i.'<br />';
          }
          $result->close();
          $i++;
     } while ($mysqli->next_result());
} else {
     die('DB ERROR: '.$mysqli->error);
}
$mysqli->close();
?>
[/code]
Link to comment
Share on other sites

[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
Link to comment
Share on other sites

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.'<br />';
          } else {
              echo 'Could not create table '.$i.'<br />';
          }
          $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.
Link to comment
Share on other sites

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 :)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.