How to use Forge Class to create a database schema?

I'm using CodeIgniter 3.1.0 to develop an app. In order to improve installation, I've written an Install_Controller, and an Install_Model.
I want to use Database Forge class to manage the database schema, but it's not very clear in user guide and nothing on Google helps.
I need to use $this->dbforge->create_database, because the user knows nothing about database, so all he will do is MySQL "Next, next, install" and then run a Windows batch file that run PHP as web server, so from Chrome he can use URL to install the app.

User guide says: In order to initialize the Forge class, your database driver must already be running, since the forge class relies on it.

So I have setup the config/database.php with user, pwd, dbname and so on... Even because I need it to use in app. That's working fine in app.

After I delete my database to start over from a fresh install and try to load the Install URL, give me the error: Message: mysqli::real_connect(): (HY000/1049): Unknown database 'test'

So, how can I use Forge Class to create database schema, if I need to have it first?

Some code...

Code:

$db['default'] = array(
   'dsn' => '',
   'hostname' => 'localhost',
   'username' => 'root',
   'password' => 'root',
   'database' => 'test',
   'dbdriver' => 'mysqli'
);

$autoload['libraries'] = array('database');

class Install extends CI_Controller
{
   public function __construct()
   {
       parent::__construct();
   }

   public function index()
   {
       $this->load->model('install_model');
       $this->install_model->createDabase();
   }
}

class Install_model extends CI_Model {
   function __construct() {
       parent::__construct();
   }

   function createDabase() {
       $this->load->dbforge();

       if ($this->dbforge->create_database('test'))
           echo 'Database created!';
       else
           echo 'Database error!';
   }
}

I made a very dirty workaround, I hope someone give me a nice answer...
1. Remove database from autoloader.
2. Use multiple databases, the default one is the same, but I created a second one having empty database name.
3. Use the second database with Forge Class, instead of the default until create schema, then I back to the default to create tables.
4. Load manually the default database in others Controllers

Based on my experience it is best that the database exist prior to installing the software (I'm not saying it can't be done) even if the database is empty. This is assuming that the software is being installed on a hosted account.

Which brings me to my question...where is this software being installed?

Based on my experience it is best that the database exist prior to installing the software (I'm not saying it can't be done) even if the database is empty.  This is assuming that the software is being installed on a hosted account.  

Which brings me to my question...where is this software being installed?

It is a kiosk computer, the support team is based on hardware only, so all they know about software is basic installations.

The first version of install was a guide that describe each step, but the team said that is not their work setting things like create database, change IP from text file and so on... Even because it is a very limited computer, all they will have to edit is Notepad, which is not a user frindely, and they need to do it for each kiosk, what take hours to do it, for each event (they have at least 2 events per month).

Now I did a page like "Next next install" as I posted as workaround, but I think that if I need the database already created to use create_database command, that is a bug, once this command became useless, and I could done the same thing with query function.

I hope that is a clean solution, because since I can't change dynamically the database name, at least the Forge Class should ignore the database name if it is not exists, or the simple fact to load the database should not throw error, unless you try to use it.

A sample of the dirty solution I did:

PHP Code:

$autoload['libraries'] = array();

$db['default'] = array(
        'dsn'    => '',
        'hostname' => 'localhost',
        'username' => 'root',
        'password' => 'root',
        'database' => 'test',
        'dbdriver' => 'mysqli',
);

$db['install'] = array(
        'hostname' => 'localhost',
        'username' => 'root',
        'password' => 'root',
        'new_database' => 'test',
        'dbdriver' => 'mysqli',
);

private function createSchema( $renew ) {
        $this->load->database('install');
        $this->load->dbforge();

        if( !$this->dbforge->create_database($this->db->new_database) ) {
            if( $renew ) {
                if( !$this->dbforge->drop_database($this->db->new_database) )
                    return $this->db->error();

                if( !$this->dbforge->create_database($this->db->new_database) )
                    return $error = $this->db->error();
            } else
                return $error = $this->db->error();
        }

        return 0;
    }

    private function createSchedule() {
        $this->load->database( 'default', FALSE, TRUE );
        $this->load->dbforge();
        $fields = array(
                'idEvent' => array(
                        'type' => 'INT',
                        'constraint' => '10',
                        'unsigned' => TRUE,
                        'null' => FALSE,
                        'auto_increment' => FALSE,
                ),
                '0000' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '0100' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '0200' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '0300' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '0400' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '0500' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '0600' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '0700' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '0800' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '0900' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '1000' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '1100' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '1200' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '1300' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '1400' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '1500' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '1600' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '1700' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '1800' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '1900' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '2000' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '2100' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '2200' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                '2300' => array(
                        'type' => 'BIT',
                        'constraint' => '1',
                        'null' => FALSE,
                        'default' => 0,
                ),
                'dtLastUpdate DATETIME DEFAULT CURRENT_TIMESTAMP'
        );

        if( !$this->dbforge->add_field( $fields ) )
            return $this->db->error();

        if( !$this->dbforge->add_key( 'idEvent', TRUE ) )
            return $this->db->error();

        if( !$this->dbforge->create_table( 'schedule', TRUE ) )
            return $this->db->error();

        return 0;
    } 


You can use the native MySQLi calls to create anything that you need for the database.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

You can use the native MySQLi calls to create anything that you need for the database.

How? I try to load only the driver, but I found nothing in the guide or Google.

Can you send dome code?

You would not be able to use the CodeIgniter database settings at the start.

Code to create the database:

PHP Code:

<?php
$serverName = "localhost";
$userName   = "username";
$password   = "password";

// Create the Database Connection
$connId = mysqli_connect($serverName, $userName, $password);

// Check the Database Connection
if ($connId === false)
{
    exit("ERROR: Could not connect. " . mysqli_connect_error());
} 

// Create the Database
// You have to supply the database name below!
$sql = "CREATE DATABASE yourDatabaseName";

// Check that the Database was created
if (mysqli_query($connId, $sql))
{
    echo "Database created successfully";
}
else
{
    echo "ERROR: Creating Database. " . mysqli_error($connId);
}

// Close the Connection
mysqli_close($connId);

?>

You would then need a way to edit and update the CodeIgniter Database settings with the new information,
Once that is done then you would be able to use the CodeIgniter Database.

Here is pure code to create database table:

PHP Code:

<?php
$serverName = "localhost";
$userName   = "username";
$password   = "password";

// Attempt MySQL server connection. Assuming you are running MySQL server
$connId = mysqli_connect($serverName, $userName, $password);
 
// Check connection
if ($connId === false)
{
    exit("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Attempt create table query execution
$sql = "CREATE TABLE persons(person_id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT, first_name CHAR(30) NOT NULL, last_name CHAR(30) NOT NULL, email_address VARCHAR(50))";

if (mysqli_query($connId, $sql))
{
    echo "Table persons created successfully";
}
else
{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($connId);
}
 
// Close connection
mysqli_close($connId);
?>

Hope that gets you started.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

You can use the native MySQLi calls to create anything that you need for the database.

How? I try to load only the driver, but I found nothing in the guide or Google.

Can you send dome code?

Good advice from @InsiteFX

If you need to use user inputs for db connection, (or need the info over more than one method/call) you can look over how I do it in Open Blog. https://github.com/enlivenapp/Open-Blog-...taller.php

Saving the connection info to $this->session... then using that info to populate the database connection info.

You can also get an idea on how to use a .sql file to populate the db info all at once.

if the application is on a standalone client (kiosk) is there a need to access the database simultaneously by multiple processes?

If not, you could create an sqlite db file and use that as a database and pack that with your program.

You would not be able to use the CodeIgniter database settings at the start.

Code to create the database:

PHP Code:

<?php
$serverName = "localhost";
$userName   = "username";
$password   = "password";

// Create the Database Connection
$connId = mysqli_connect($serverName, $userName, $password);

// Check the Database Connection
if ($connId === false)
{
    exit("ERROR: Could not connect. " . mysqli_connect_error());
} 

// Create the Database
// You have to supply the database name below!
$sql = "CREATE DATABASE yourDatabaseName";

// Check that the Database was created
if (mysqli_query($connId, $sql))
{
    echo "Database created successfully";
}
else
{
    echo "ERROR: Creating Database. " . mysqli_error($connId);
}

// Close the Connection
mysqli_close($connId);

?>

You would then need a way to edit and update the CodeIgniter Database settings with the new information,
Once that is done then you would be able to use the CodeIgniter Database.

Here is pure code to create database table:

PHP Code:

<?php
$serverName = "localhost";
$userName   = "username";
$password   = "password";

// Attempt MySQL server connection. Assuming you are running MySQL server
$connId = mysqli_connect($serverName, $userName, $password);
 
// Check connection
if ($connId === false)
{
    exit("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Attempt create table query execution
$sql = "CREATE TABLE persons(person_id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT, first_name CHAR(30) NOT NULL, last_name CHAR(30) NOT NULL, email_address VARCHAR(50))";

if (mysqli_query($connId, $sql))
{
    echo "Table persons created successfully";
}
else
{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($connId);
}
 
// Close connection
mysqli_close($connId);
?>

Hope that gets you started.

Thanks for the code!

I thought that could be a way to do it in pure CodeIgniter, but seems your solution is the best performance way to do it.

I will keep my solution, because even today is a MySQL application, if in future it changes, using Forge Class will make it compatible without any change.

You can use the native MySQLi calls to create anything that you need for the database.

How? I try to load only the driver, but I found nothing in the guide or Google.

Can you send dome code?

Good advice from @InsiteFX

If you need to use user inputs for db connection, (or need the info over more than one method/call) you can look over how I do it in Open Blog. https://github.com/enlivenapp/Open-Blog-...taller.php

Saving the connection info to $this->session... then using that info to populate the database connection info.

You can also get an idea on how to use a .sql file to populate the db info all at once.

Wow! That is definitely the best approach using pure CodeIgniter solution, but I did not saw the database creation it self, the problem I asked for a better solution.

Just to retribuite the information, basically all the work done in PHP with forms, could be done with HTML 5 DOM Validation, which make it easy to keep and sustain.

Thanks for the help.