Translating Magento php script into a mysql query

[Freejoy]

Hi,

I’m trying to translate this php script into and mysql query so I can create this table in phpMyadmin because it fails to install with the script.

$listSubscriberTable = $connection->newTable($installer->getTable('mzax_emarketing/newsletter_list_subscriber'))
->addColumn('list_id',        Varien_Db_Ddl_Table::TYPE_SMALLINT, 5,    $req, 'Newsletter list ID')
->addColumn('subscriber_id',  Varien_Db_Ddl_Table::TYPE_INTEGER, null,  $req, 'Newsletter subscriber ID')
->addColumn('changed_at',     Varien_Db_Ddl_Table::TYPE_DATETIME, null, $req, 'Creation Time')
->addColumn('list_status',    Varien_Db_Ddl_Table::TYPE_TINYINT, null,  $int, 'List subscribe status')
->addIndex('PRIMARY', array('list_id', 'subscriber_id'),$primaryIndex );

$installer->addForeignKey($listSubscriberTable, $subscriberTable, ‘subscriber_id’);
$installer->addForeignKey($listSubscriberTable, $listTable, ‘list_id’);

$connection->createTable($listSubscriberTable);

Anyone can help me here would be very much appreciated.

[molona]

I assume that newTable is equivalent to USE TABLE. It could be CREATE TABLE but it looks more like USE TABLE because then it goes addColumn. I assume that addColumn is equivalent to ALTER TABLE ADD

I would have to check PhpMyAdmin, I’m not really sure.

Have you checked MySQL documentation?

[Freejoy]

I think it’s a create table here’s the whole script:

I created the first one from the error but this one I couldn’t do.

<?php

/* @var $installer Mzax_Emarketing_Model_Resource_Setup */
$installer = $this;
$installer->startSetup();

$connection = $installer->getConnection();

$req = array(‘nullable’ => false);
$opt = array(‘nullable’ => true, ‘unsigned’ => true);

$int = array(‘nullable’ => false, ‘unsigned’ => true, ‘default’ => 0);

$uid = array(
‘identity’ => true,
‘unsigned’ => true,
‘nullable’ => false,
‘primary’ => true
);

$varchar = 255;
$text = 65536;

$primaryIndex = array(‘type’ => Varien_Db_Adapter_Interface::INDEX_TYPE_PRIMARY);

$subscriberTable = $installer->getTable(‘newsletter/subscriber’);

/********************************************************************

• Setup Newsletter List Table
  ********************************************************************/
  $listTable = $connection->newTable($installer->getTable(‘mzax_emarketing/newsletter_list’))
  ->addColumn(‘list_id’, Varien_Db_Ddl_Table::TYPE_SMALLINT, 5, $uid, ‘Newsletter list ID’)
  ->addColumn(‘created_at’, Varien_Db_Ddl_Table::TYPE_DATETIME, null, $req, ‘Creation Time’)
  ->addColumn(‘updated_at’, Varien_Db_Ddl_Table::TYPE_DATETIME, null, $req, ‘Update Time’)
  ->addColumn(‘name’, Varien_Db_Ddl_Table::TYPE_TEXT, $varchar, $req, ‘List Name’)
  ->addColumn(‘description’, Varien_Db_Ddl_Table::TYPE_TEXT, $text, $req, ‘List Description’)
  ->addColumn(‘auto_subscribe’, Varien_Db_Ddl_Table::TYPE_BOOLEAN, null, $int, ‘Auto subscribe to List’)
  ->addColumn(‘is_private’, Varien_Db_Ddl_Table::TYPE_BOOLEAN, null, $int, ‘Private List’);

$connection->createTable($listTable);

/********************************************************************

• Setup Newsletter List Subscriber Table
  ********************************************************************/
  $listSubscriberTable = $connection->newTable($installer->getTable(‘mzax_emarketing/newsletter_list_subscriber’))
  ->addColumn(‘list_id’, Varien_Db_Ddl_Table::TYPE_SMALLINT, 5, $req, ‘Newsletter list ID’)
  ->addColumn(‘subscriber_id’, Varien_Db_Ddl_Table::TYPE_INTEGER, null, $req, ‘Newsletter subscriber ID’)
  ->addColumn(‘changed_at’, Varien_Db_Ddl_Table::TYPE_DATETIME, null, $req, ‘Creation Time’)
  ->addColumn(‘list_status’, Varien_Db_Ddl_Table::TYPE_TINYINT, null, $int, ‘List subscribe status’)
  ->addIndex(‘PRIMARY’, array(‘list_id’, ‘subscriber_id’),$primaryIndex );

$installer->addForeignKey($listSubscriberTable, $subscriberTable, ‘subscriber_id’);
$installer->addForeignKey($listSubscriberTable, $listTable, ‘list_id’);

$connection->createTable($listSubscriberTable);

$installer->endSetup();

The first one looked like this and it worked:

CREATE TABLE `mzax_emarketing_newsletter_list_subscriber` (

list_id smallint UNSIGNED NOT NULL auto_increment COMMENT ‘Newsletter list ID’ ,
subscriber_id TYPE_INTEGER NOT NULL COMMENT ‘Creation Time’ ,
updated_at datetime NOT NULL COMMENT ‘Update Time’ ,
name varchar(255) NOT NULL COMMENT ‘List Name’ ,
description text NOT NULL COMMENT ‘List Description’ ,
auto_subscribe bool NOT NULL default ‘0’ COMMENT ‘Auto subscribe to List’ ,
is_private bool NOT NULL default ‘0’ COMMENT ‘Private List’ ,
PRIMARY KEY (list_id)
) COMMENT=‘mzax_emarketing_newsletter_list’ ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci