Help with Database Creation Script.

I've put together a pretty cool template/based website, that requires a Database/MySql to run, as it stores information into the db's and then displays it on the page, so that Novices can run their own websites, update, edit the content, etc...

But, there is one problem - my Self Installing .php Create Databases Script ... doesn't do anything.

It echos the results, like it's supposed to; but It doesn't Create any tables in my database - I'm sure it's something simple, as i'm just above Novice status myself, when it comes to PHP coding; Im sure this is one of those posts that can not only help me, but will help other folks seeking to learn php to deliver dynamic results on the web!

Any help with this would be much appreciated.

<?php
//Connect to the database 
$hostname_sitewidecms = "mysql.localhost.com";
$database_sitewidecms = "sitewidecms";
$username_sitewidecms = "username";
$password_sitewidecms = "password";
mysql_connect($hostname_sitewidecms, $username_sitewidecms, $password_sitewidecms) or trigger_error(mysql_error(),E_USER_ERROR);

//Install the video table so that you can include videos in your site.

// phpMyAdmin SQL Dump
// version 2.8.2.4
// http://www.phpmyadmin.net
// Host: mysql.YourMMSite.com
// Table structure for table `bannersdb`

$sql = 'CREATE TABLE `sitewide`.`bannersdb` (
  `banner_id` int(100) NOT NULL auto_increment,
  `banner_name` varchar(500) NOT NULL,
  `banner_href` varchar(500) NOT NULL,
  `banner_alt` varchar(200) default NULL,
  PRIMARY KEY  (`banner_id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 AUTO_INCREMENT=0' ;
 echo 'Creating table: \'bannersdb\'....';
 

// Table structure for table `sitewidecms`


$sql = 'CREATE TABLE `sitewide`.`sitewidecms` (
  `home_banner` varchar(250) NOT NULL,
  `home_photo` varchar(250) NOT NULL,
  `home_footer` blob NOT NULL,
  `home_links` varchar(1000) NOT NULL,
  `tour_banner` varchar(250) NOT NULL,
  `tour_footer` blob NOT NULL,
  `tour_header` varchar(3000) NOT NULL,
  `tour_links` varchar(1000) NOT NULL,
  `mem_banner` varchar(250) NOT NULL,
  `mem_footer` blob NOT NULL,
  `mem_header` varchar(2000) NOT NULL,
  `mem_links` varchar(1000) NOT NULL,
  `vid_banner` varchar(250) NOT NULL,
  `vid_header` varchar(2000) NOT NULL,
  `vid_footer` blob NOT NULL,
  `vid_links` varchar(2000) NOT NULL,
  `extraslot1` varchar(1000) default NULL,
  `extraslot2` varchar(2000) default NULL,
  `extraslot3` varchar(3000) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8';
echo 'Creating table: \'sitewidecms\'....';


// Table structure for table `updatehome`

$sql = 'CREATE TABLE `sitewide`.`updatehome` (
  `update_id` int(10) NOT NULL auto_increment,
  `modelname` varchar(50) NOT NULL,
  `setdescrip` varchar(1000) NOT NULL,
  `picslot1` varchar(200) NOT NULL,
  `picslot2` varchar(200) NOT NULL,
  `picslot3` varchar(200) NOT NULL,
  `picslot4` varchar(200) NOT NULL,
  `setname` varchar(150) NOT NULL,
  `totalpics` varchar(200) NOT NULL,
  `date` varchar(250) NOT NULL,
  `extraslot1` varchar(500) default NULL,
  `extraslot2` varchar(500) default NULL,
  `extraslot3` varchar(1000) default NULL,
  `extraslot4` varchar(1000) default NULL,
  `extraslot5` varchar(5000) default NULL,
  PRIMARY KEY  (`update_id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 AUTO_INCREMENT=0' ;
echo 'Creating table: \'updatehome\'....';

// Table structure for table `updatemembers`


$sql = 'CREATE TABLE `sitewide`.`updatemembers` (
  `set_id` int(10) NOT NULL auto_increment,
  `date_input` varchar(100) NOT NULL,
  `model_nm` varchar(200) NOT NULL,
  `set_name` varchar(250) NOT NULL,
  `set_descrip` varchar(1000) NOT NULL,
  `thumb` varchar(250) NOT NULL,
  `zipfile` varchar(250) default NULL,
  `folder` varchar(250) NOT NULL,
  `total_pics` varchar(200) NOT NULL,
  `short_descrip` varchar(200) default NULL,
  `extraslot1` varchar(200) default NULL,
  `extraslot2` varchar(200) default NULL,
  `extraslot3` varchar(500) default NULL,
  `extraslot4` varchar(500) default NULL,
  `extraslot5` varchar(1000) default NULL,
  PRIMARY KEY  (`set_id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ';
echo 'Creating table: \'updatemembers\'....';

// Table structure for table `updatemodels`


$sql = 'CREATE TABLE `sitewide`.`updatemodels` (
  `model_id` int(100) NOT NULL auto_increment,
  `model_name` varchar(200) NOT NULL,
  `model_photo` varchar(200) NOT NULL,
  `model_extra1` varchar(1000) default NULL,
  `model_extra2` varchar(1500) default NULL,
  PRIMARY KEY  (`model_id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ';
echo 'Creating table: \'updatemodels\'....';

// Table structure for table `videodb`

$sql = 'CREATE TABLE `sitewide`.`videodb` (
  `vid_id` int(100) NOT NULL auto_increment,
  `vid_name` varchar(250) NOT NULL,
  `vid_model` varchar(250) NOT NULL,
  `vid_descrip` varchar(350) NOT NULL,
  `vid_date` varchar(100) NOT NULL,
  `vid_duration` varchar(200) NOT NULL,
  `vid_file` varchar(150) NOT NULL,
  `extra1` varchar(150) default NULL,
  `extra2` varchar(150) default NULL,
  `extra3` varchar(150) default NULL,
  PRIMARY KEY  (`vid_id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ';
echo 'Creating table: \'videodb\'....';


?>

<?php
mysql_query($sql);
?>

PHP:

 

Well, since you don't have any sort of loop or anything, you need to have a mysql_query($sql); statement after each $sql = statement. The way the script is currently written is that only your last table will ever be placed into the database.

Also, check that you are using valid SQL syntax. If the syntax is incorrect you won't get an error and it will never execute in the database. Try pasting your SQL syntax into PHPMyAdmin and see if you get an error. If you do, correct the statements until you no longer get an error, then put in the program.

BMR777

 

ok so, let me see if I have you correct.

Instead of what I have for the sql statment; I need something like This.

$sql = 'CREATE TABLE `sitewide`.`bannersdb` (
  `banner_id` int(100) NOT NULL auto_increment,
  `banner_name` varchar(500) NOT NULL,
  `banner_href` varchar(500) NOT NULL,
  `banner_alt` varchar(200) default NULL,
  PRIMARY KEY  (`banner_id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 AUTO_INCREMENT=0' ;
mysql_query($sql);
echo 'Creating table: \'bannersdb\'....';

PHP:

I'm going to give this a try now to see what happens.

Also as far as the SQL Syntax, I am getting an error when I try to run it in my phpAdmin - however, I have no clue what is out of order; My guess is that the syntax didn't look right to you, which is why you made mention of it.

Any suggestions to help me figure out which part of this is out of order? Here is the error I get.

MySQL said: Documentation
#1064 - 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 '$sql = 'CREATE TABLE `sitewide`.`bannersdb` (
`banner_id` int(100) NOT NULL a' at line 1

I'll try checking the manual... but till then, any help with this would be great.

thanks

 

$sql = is not part of the query, its the variable containing the query
paste it starting from CREATE

 

paste what starting from CREATE ...?

So far, nothing is working - I'm sure it's because I'm not understanding what goes where.

I want this script to create the database & the tables within that db - if no one wants to help, could you at least point me to where I can figure out the proper Syntax to do such a thing??

thanks

 

You're running mysql_query() in its own - you've not got any way of checking to see if anything is failing. If something does fail and you've got a long list of these you won't know which one(s) have gone wrong without checking each query against the database to see what's missing.

There are various ways of going about this and the ultimate way would be checking everything as it was being created - if one of them failed delete those that hadn't and report which one failed - just so another attempt can be made without having to tidy up first.

$success=mysql_query("insert your query here");

Code (markup):

You can then check $success:

echo ($success ? 'Worked!' : 'Failed!');

Code (markup):

Or you can have an if() with each one:

if (mysql_query("insert your query here")) {echo 'Worked';} else {echo 'Failed';}

Code (markup):

That's just a couple ideas to get you started.

 

I've not tested this, it's all off the top of my head but it gives you the basic idea and would probably need a little tweaking. Just replace the "create ????" inside the mysql_query() functions with your table definitions:

<?php
  $allOK=true;
  $arrTables=array('bannersdb' => false,'sitewidecms' => false,'updatehome' => false,'updatemembers' => false,'updatemodels' => false,'updatevideodb' => false);
  if (mysql_query("create banners")) {$arrTables['bannersdb']=true;}
  if (mysql_query("create sitewide")) {$arrTables['sitewidecms']=true;}
  if (mysql_query("create updatehome")) {$arrTables['updatehome']=true;}
  if (mysql_query("create updatemembers")) {$arrTables['updatemembers']=true;}
  if (mysql_query("create updatemodels")) {$arrTables['updatemodels']=true;}
  if (mysql_query("create videodb")) {$arrTables['updatevideodb']=true;}
  for ($arrTables as $myTable => $mySuccess) {
    if (!$mySuccess) {
      mysql_query("DROP TABLE `".$myTable."`");
      $allOK=false;
    }
  }
  if ($allOK) {
    echo 'All tables created successfully';
  } else {
    echo 'Failed with: ';
    for ($arrTables as $myTable => $mySuccess) {
      if (!$mySuccess) {echo $myTable.' ';}
    }
  }
?>

Code (markup):

 

well...bmr777 told you to paste the query in phpmyadmin, you said you did it and it gave you this error
#1064 - 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 '$sql = 'CREATE TABLE `sitewide`.`bannersdb` (
`banner_id` int(100) NOT NULL a' at line 1
so im telling you to try starting with CREATE instead of starting with $sql =

you are not making this any easier dude...

 

I'll give this a try tomorrow; Thanks Yesideez for these suggestions. Thank you as well Sisko; My apologies if I didn't know what you meant - Im sure I'm not making this easy - besides, if I knew what I was doing, I would not be here asking for help from those much more experienced with php than I am.

So, I do thank you for your suggestions, and now that I know what you mean, I'll give that a try.