[SOLVED] PHP Creat tables in MySQL

[Emzi]

Hey all,

I'm trying to create a script for users to go to an install page and click "install" and it will create tables in their database (which they've already provided information for).

At the moment in the PHP code I've written it so it will create two database tables. The problem is it will only create one of them. And bizzarly it installs the table that comes second ('tblAPPLIED').

Here's my code:

PHP: Select all

<?
@mysql_select_db($database_ukjobsite) or die( "Unable to select database");
 
$query = "CREATE TABLE `tblADMIN` (
  `admin_id` int(11) NOT NULL auto_increment,
  `admin_username` varchar(25) collate utf8_unicode_ci NOT NULL,
  `admin_password` varchar(25) collate utf8_unicode_ci NOT NULL,
  `admin_level` int(11) NOT NULL default '0',
  PRIMARY KEY  (`admin_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2";
 
$query = "CREATE TABLE `tblAPPLIED` (
  `applied_id` int(11) NOT NULL auto_increment,
  `first_name` varchar(50) character set utf8 collate utf8_unicode_ci NOT NULL,
  `last_name` varchar(50) character set utf8 collate utf8_unicode_ci NOT NULL,
  `region_id` smallint(5) NOT NULL,
  `telephone` varchar(25) character set utf8 collate utf8_unicode_ci NOT NULL,
  `covernote` text character set utf8 collate utf8_unicode_ci NOT NULL,
  `cv_filename` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `date_applied` int(11) NOT NULL default '0',
  PRIMARY KEY  (`applied_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";
 
mysql_query($query);
mysql_close();
?>

Does anyone know where I'm going wrong? Thanks in advance!

[Rakuli]

It's because you are resetting the query.... You set the query text and then overwrite it.

add a semi colon to the end of the first query and then start the second query with the concatenation operator.

....etc etc AUTO_INCREMENT=2;";

$query .= 'etc';

[Emzi]

Hi, thanks for that, but it still didn't work.

This is what I have now:

PHP: Select all

$query = "CREATE TABLE `tblADMIN` (
  `admin_id` int(11) NOT NULL auto_increment,
  `admin_username` varchar(25) collate utf8_unicode_ci NOT NULL,
  `admin_password` varchar(25) collate utf8_unicode_ci NOT NULL,
  `admin_level` int(11) NOT NULL default '0',
  PRIMARY KEY  (`admin_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2;";
 
$query .= "CREATE TABLE `tblAPPLIED` (
  `applied_id` int(11) NOT NULL auto_increment,
  `first_name` varchar(50) character set utf8 collate utf8_unicode_ci NOT NULL,
  `last_name` varchar(50) character set utf8 collate utf8_unicode_ci NOT NULL,
  `region_id` smallint(5) NOT NULL,
  `telephone` varchar(25) character set utf8 collate utf8_unicode_ci NOT NULL,
  `covernote` text character set utf8 collate utf8_unicode_ci NOT NULL,
  `cv_filename` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `date_applied` int(11) NOT NULL default '0',
  PRIMARY KEY  (`applied_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";
 
 
mysql_query($query);
mysql_close(); 

Any ideas what I did wrong? Thanks

[Rakuli]

Why don't you just run it as two queries?

PHP: Select all

$query = "CREATE TABLE `tblADMIN` (

  `admin_id` int(11) NOT NULL auto_increment,

  `admin_username` varchar(25) collate utf8_unicode_ci NOT NULL,

  `admin_password` varchar(25) collate utf8_unicode_ci NOT NULL,

  `admin_level` int(11) NOT NULL default '0',

  PRIMARY KEY  (`admin_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2";



 mysql_query($query);





$query = "CREATE TABLE `tblAPPLIED` (

  `applied_id` int(11) NOT NULL auto_increment,

  `first_name` varchar(50) character set utf8 collate utf8_unicode_ci NOT NULL,

  `last_name` varchar(50) character set utf8 collate utf8_unicode_ci NOT NULL,

  `region_id` smallint(5) NOT NULL,

  `telephone` varchar(25) character set utf8 collate utf8_unicode_ci NOT NULL,

  `covernote` text character set utf8 collate utf8_unicode_ci NOT NULL,

  `cv_filename` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,

  `date_applied` int(11) NOT NULL default '0',

  PRIMARY KEY  (`applied_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1";

 

 

mysql_query($query);

mysql_close(); 

[Emzi]

To be honest, I'm still not 100% at this and I seem to be ok at the more complex stuff, but the simple stuff is beyond me

But thanks, what you suggested worked So thanks!