Trying to create 2 MySQL tables at the same time

Using WordPress and MySQL 4

[doodlebee]

Okay, so I'm writing a plugin for WordPress (2.7), and I'm not having luck creating code to create 2 database tables simultaneously. I can create one, no problem. But it would seem that I cannot create *two*.

I found one obscure reference that, with a certain version of MySQL (5, I believe) you have to use the "mysqli" command to create multiple tables, but with MySQL 4 (which I am currently running) you should be able to create more than one table.

Would anyone mind #1 clarifying the above for me, and #2 taking a peek at my code and telling me why this won't work? I'm going to clean out the column names and such, because they aren't really needed for this example - I already know the syntax is correct because when I do them separately, they are perfectly implanted. it's just when I try to do them *together* only one of them gets written.

function install_tables () {
global $wpdb;

$sales = $wpdb->prefix . "sales";
$settings = $wpdb->prefix . "settings";

if($wpdb->get_var("show tables like '$sales'") != $sales) {

$sql = "CREATE TABLE " . $sales . " (
// blah blah whole bunch of info here
);
CREATE TABLE " . $settings . " (
// blah blah second table info here
); ";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);
}
}

register_activation_hook(__FILE__,'install_tables');

[Demaestro]

Try it in a transaction

Something like this

START TRANSACTION;
create table 1 ();
create table 2;
COMMIT;

[dev.mysql.com...]

[doodlebee]

Gah. Thank you, but that didn't work. I figure I'll just do a second file and have it work that way. I was hoping to have it just work within one call.

Thanks though!

[doodlebee]

Well, that didn't work either. So I just "borrowed" from another plugin that does the same thing I am, but it's not doing what needs to be done. maybe I need another set of eyes on this. The code is working fine for the other guy - so I don't quite get why it's not working for me. Could someone take a peek - maybe I'm just missing something for having looked at for too long?

My Install file, with the SQL queries

function csb_installDB () {
 global $wpdb;

 
 require_once(ABSPATH . 'wp-admin/upgrade-functions.php');

 
 // add charset & collate like wp core
$charset_collate = '';

 
if ( version_compare(mysql_get_server_info(), '4.1.0', '>=') ) {
if ( ! empty($wpdb->charset) )
$charset_collate = "DEFAULT CHARACTER SET $wpdb->charset";
if ( ! empty($wpdb->collate) )
$charset_collate .= " COLLATE $wpdb->collate";
}

 
 $sales = $wpdb->prefix . "sales";
 $options = $wpdb->prefix . "options";

 
 if($wpdb->get_var("show tables like '$sales'") != $sales) {

 
 $sql = "CREATE TABLE " . $sales . " (
 id int(10) unsigned DEFAULT '0' NOT NULL,
 username varchar(8), 
 ip varchar(16),
 date varchar(18),
 method varchar(18),
 cart text,
 discount decimal(13,2) default '0.00',
 subtotal decimal(13,2) default '0.00',
 shipping decimal(13,2) default '0.00',
 tax decimal(13,2) default '0.00',
 total decimal(13,2) default '0.00',
 shipping_zone tinyint(10) unsigned default 0,
 inv_name varchar(50),
 inv_company varchar(40),
 inv_addr1 varchar(50),
 inv_addr2 varchar(50),
 inv_state varchar(25),
 inv_zip varchar(15),
 inv_country varchar(15),
 del_name varchar(50),
 del_addr1 varchar(50),
 del_addr2 varchar(50),
 del_state varchar(25),
 del_zip varchar(15),
 del_country varchar(15),
 tel varchar(20),
 fax varchar(20),
 email varchar(50),
 currency tinyint(10) unsigned default 0,
 message text,
 edata varchar(240),
 sd text,
 PRIMARY KEY (id)
 ) $charset_collate;"; 

 
 dbDelta($sql);

 
 }

 
 if($wpdb->get_var("show tables like '$options'") != $options) {
  
 $sql = "CREATE TABLE " . $options . " (
 option_id bigint(20) AUTO_INCREMENT NOT NULL,
 option_name varchar(64), 
 option_value longtext,
 autoload varchar(20) DEAFULT 'yes',
 PRIMARY KEY (option_id)
 ) $charset_collate;";

 
 dbDelta($sql);

 
 }

 
}

Index of the plugin, with the call to action

 
// prevent loading of this page form outside WordPress
if(preg_match('#' . basename(__FILE__) . '#', $_SERVER['PHP_SELF'])) { die('You are not allowed to call this page directly.'); }

 
//start
global $wpdb;

 
 ini_set('display_errors', '1');
 ini_set('error_reporting', E_ALL);

 
$csb_db_version = "1.0";
update_option('csb_options', $csb_db_version);

 
define('CSB_FOLDER', WP_CONTENT_URL . '/plugins/' . plugin_basename( dirname(__FILE__)) );

 
// init tables in wp-database if plugin is activated
function csb_install() {
csb_installDB();
}

 
// create database tables
register_activation_hook(CSB_FOLDER . '/cakeshop-install.php','csb_install');

 
require('cakeshop-admin.php');
require('cakeshop-functions.php');
require('cakeshop-admin-functions.php');

I get absolutely no errors at all - but the database tables do not get inserted into the database. I feel like I'm just overlooking some... thing. I hate that.

Any help would be appreciated :)