intall mysql database

[php wanna bee]

Hi All,

I just started playing around with php and mysql databases a few months ago. I must say its very addicting and fun, what a nerd i am :)

Anyhow let me get back on track


Im trying to figure out how i can add this code to my database without having to go into myphpadmin


[code]CREATE TABLE forumbodies (
  id int(10) DEFAULT '0' NOT NULL,
  body blob NOT NULL,
  PRIMARY KEY (id),
  KEY id (id)
);
CREATE TABLE forumthreads (
  id int(10) DEFAULT '0' NOT NULL auto_increment,
  date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  mainthread int(10) DEFAULT '0' NOT NULL,
  parent int(10) DEFAULT '0' NOT NULL,
  author char(50) DEFAULT '' NOT NULL,
  subject char(50) DEFAULT '' NOT NULL,
  host char(50),
  forum int(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY id (id),
  KEY date (date),
  KEY mainthread (mainthread),
  KEY parent (parent),
  KEY author (author),
  KEY subject (subject),
  KEY forum (forum)
);[/code]



It would be nice to be able to let the user only have to run a php file on the server to install the tables.


Can someone guide me in the correct direction?

[printf]

The easy part is doing the insert, the harder part is making sure you have the correct permissions setup so users can on play around with their database. A query is just query, so adding tables, altering tables and dropping tables is all done using a normal query call, which depends on the scripting language you are using. So for your database it might look like this.

// PHP

[code]<?php

// the array of database updates we will be doing

$new_tables = array (
"DROP TABLE IF EXISTS forumbodies;",
"CREATE TABLE forumbodies (
  id int(10) DEFAULT '0' NOT NULL,
  body blob NOT NULL,
  PRIMARY KEY (id),
  KEY id (id)
) ENGINE=MyISAM;",
"DROP TABLE IF EXISTS forumthreads;",
"CREATE TABLE forumthreads (
  id int(10) DEFAULT '0' NOT NULL auto_increment,
  date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  mainthread int(10) DEFAULT '0' NOT NULL,
  parent int(10) DEFAULT '0' NOT NULL,
  author char(50) DEFAULT '' NOT NULL,
  subject char(50) DEFAULT '' NOT NULL,
  host char(50),
  forum int(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY id (id),
  KEY date (date),
  KEY mainthread (mainthread),
  KEY parent (parent),
  KEY author (author),
  KEY subject (subject),
  KEY forum (forum)
) ENGINE=MyISAM;"
);


// the database server your connecting to

mysql_connect ( 'localhost', 'user', 'pass' ) or die ( 'Connection error: ' . mysql_error () );

// the database your going to (drop if they exist), and add your tables to

mysql_select_db ( 'my_database' ) or die ( 'Select error: ' . mysql_error () );

// now lets just do what needs to be done

foreach ( $new_tables AS $item )
{
// run each query

if ( ! mysql_query ( $item ) )
{
// if we have an error, show it here

echo mysql_error () . '<br />';
}
}

?>[/code]

// Perl

[code]use DBI;
use strict;

# the array of database updates we will be doing

my @new_tables = (
"DROP TABLE IF EXISTS forumbodies;",
"CREATE TABLE forumbodies (
  id int(10) DEFAULT '0' NOT NULL,
  body blob NOT NULL,
  PRIMARY KEY (id),
  KEY id (id)
) ENGINE=MyISAM;",
"DROP TABLE IF EXISTS forumthreads;",
"CREATE TABLE forumthreads (
  id int(10) DEFAULT '0' NOT NULL auto_increment,
  date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  mainthread int(10) DEFAULT '0' NOT NULL,
  parent int(10) DEFAULT '0' NOT NULL,
  author char(50) DEFAULT '' NOT NULL,
  subject char(50) DEFAULT '' NOT NULL,
  host char(50),
  forum int(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY id (id),
  KEY date (date),
  KEY mainthread (mainthread),
  KEY parent (parent),
  KEY author (author),
  KEY subject (subject),
  KEY forum (forum)
) ENGINE=MyISAM;"
);

# the database connection string

my $my_dbs = 'localhost;3306;my_database;user;pass'; # server;port;database;user;pass

# convert the database string into an array of values

my @ms_db = split(/;/, $my_dbs);

# call the mysql connection and load the object

my $tcc = &make_connection;

# now lets just do what needs to be done

foreach my $query (@new_tables)
{
$tcq = $tcc->prepare($query);

$tcq->execute;
}

# done with the database

$tcq->finish;

# release the object, close the connection

$tcc->disconnect();

# the connection function

sub make_connection
{
return DBI->connect("dbi:mysql:" . $ms_db[2] . ":" . $ms_db[0] . ":" . $ms_db[1], $ms_db[3], $ms_db[4]);
}

exit(0);
[/code]

Need a different scripting language example, just ask me...


It doesn't matter what scripting language you are using, all of them give you access to the database in some form.


printf

[php wanna bee]

Its printing this

Invalid default value for 'id'


Anyideas?

Thanks for the response!!!!

[quote]<?php

// the array of database updates we will be doing

$new_tables = array (
"DROP TABLE IF EXISTS forumbodies;",
"CREATE TABLE forumbodies (
  id int(10) DEFAULT '0' NOT NULL,
  body blob NOT NULL,
  PRIMARY KEY (id),
  KEY id (id)
) ENGINE=MyISAM;",
"DROP TABLE IF EXISTS forumthreads;",
"CREATE TABLE forumthreads (
  id int(10) DEFAULT '0' NOT NULL auto_increment,
  date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  mainthread int(10) DEFAULT '0' NOT NULL,
  parent int(10) DEFAULT '0' NOT NULL,
  author char(50) DEFAULT '' NOT NULL,
  subject char(50) DEFAULT '' NOT NULL,
  host char(50),
  forum int(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY id (id),
  KEY date (date),
  KEY mainthread (mainthread),
  KEY parent (parent),
  KEY author (author),
  KEY subject (subject),
  KEY forum (forum)
) ENGINE=MyISAM;"
);


// the database server your connecting to

mysql_connect ( 'localhost', 'user', 'pass' ) or die ( 'Connection error: ' . mysql_error () );

// the database your going to (drop if they exist), and add your tables to

mysql_select_db ( 'my_database' ) or die ( 'Select error: ' . mysql_error () );

// now lets just do what needs to be done

foreach ( $new_tables AS $item )
{
// run each query

if ( ! mysql_query ( $item ) )
{
// if we have an error, show it here

echo mysql_error () . '<br />';
}
}

?>[/quote]

[printf]

Ya, if have an auto_increment, you can't assign a default value, error in (CREATE TABLE forumthreads...).

So this is wrong...

[code]  id int(10) DEFAULT '0' NOT NULL auto_increment,[/code]


Just change the array code to what is below

[code]$new_tables = array (
"DROP TABLE IF EXISTS forumbodies;",
"CREATE TABLE forumbodies (
  id int(10) DEFAULT '0' NOT NULL,
  body blob NOT NULL,
  PRIMARY KEY (id),
  KEY id (id)
) ENGINE=MyISAM;",
"DROP TABLE IF EXISTS forumthreads;",
"CREATE TABLE forumthreads (
  id int(10) unsigned NOT NULL auto_increment,
  date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  mainthread int(10) DEFAULT '0' NOT NULL,
  parent int(10) DEFAULT '0' NOT NULL,
  author char(50) DEFAULT '' NOT NULL,
  subject char(50) DEFAULT '' NOT NULL,
  host char(50),
  forum int(4) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY id (id),
  KEY date (date),
  KEY mainthread (mainthread),
  KEY parent (parent),
  KEY author (author),
  KEY subject (subject),
  KEY forum (forum)
) ENGINE=MyISAM;"
);[/code]


printf

[php wanna bee]

Boy do i have ton to learn!!! Thanks

That did the trick!