Custom Web Applications to Grow Your Business

(256) 429-8145

Adding and modifying MYSQL Tables in any CAKEPHP controller - NO PHPMyAdmin or Shell Access

There are those moments when as a web developer you will not have access to PHPMyAdmin or access to shell (to build SCHEMA), but critical modification to the database structure and table is needed. Here are some examples of adding and modifying database tables in CAKEPHP by adding a few lines of code in any controller and running the view in the browser. Do remember to add models for new mysql tables.

// CREATE MYSQL POST TABLE IN CONTROLLER
App::import('Model', 'ConnectionManager');
$con = new ConnectionManager;
$cn = $con->getDataSource('default');
$cn->query("CREATE table posts (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VarChar(100),
title_slug VarChar(110),
desctiption text
)");

// ADD COLUMN TO TABLE
App::import('Model', 'ConnectionManager');
$con = new ConnectionManager;
$cn = $con->getDataSource('default');
$cn->query("ALTER TABLE posts
ADD category_id int");
)");

// MOVE THE POSITION OF COLUMN IN TABLE
App::import('Model', 'ConnectionManager');
$con = new ConnectionManager;
$cn = $con->getDataSource('default');
$cn->query("ALTER TABLE posts MODIFY category_id int AFTER id");
)");