Jump to content

Archived

This topic is now archived and is closed to further replies.

scheols

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

Recommended Posts

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?

Share this post


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

Share this post


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

Share this post


Link to post
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]

Share this post


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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites
http://xizionz.vectoredhost.com/mysqltest.php

tehe :)

im guessing i can use your second one right?

Share this post


Link to post
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]

Share this post


Link to post
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]

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
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);

Share this post


Link to post
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]

Share this post


Link to post
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]

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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 :)

Share this post


Link to post
Share on other sites
Glad to see it works.

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

Share this post


Link to post
Share on other sites

×

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.