Skip to content

Category Archives: MySQL

Database Admin Controller for Code Igniter and Ext JS 4

Friday, February 3, 2012

I have been using Code Igniter and Ext JS 4 for several months.  In that time, I have created a couple basic templates to display MySQL tables and perform CRUD on those tables.  In addition, I have wanted to create some basic admin screens for the Core Igniter configured databases.  In my configuration, I have multiple databases defined, using Code Igniter’s built-in support for multiple database access.  So, given all of that, I have created a controller that will display all Code Igniter configured databases, list the contained tables, and allow you to display and edit those tables.

Right now the basic template supports simple pagination, as well as Adding, Deleting, and Updating records.  While fairly full-featured, it does not yet handle remote sorting.  In addition, I am sure you will find some minor display issues with data fields that are not your standard integer or varchar.  I will look to address these later.  As an added bonus, the template references Ext JS remotely, so you do not need to install Ext JS 4 for these admin screens to work.  If you are interested in giving Ext JS 4 a try, this is a great way to explore with it.

One feature of this controller is that the panel for the table admin is dynamically generated.  This will allow you to save the created JS file, and modify it to suit your needs.  You can easily create very custom screens from this generated template.

If you would like to see updates, or have any issues, leave me a message here and I will try to address them.

You can download the zip file here.

To install, just add the enclosed files to your controllers and views directory and make sure you have a valid database configured.

Also filed in Programming, Software | Tagged , , , , , , | Comments (0)

MySQL – How to drop all Foreign Keys on a table

Tuesday, March 2, 2010

I recently was working with a 3rd party DB that was designed well and used lots of foreign keys to enforce referential integrity. Unfortunately, the application that used the DB structure was very poorly written and caused all sorts of problems when the foreign keys were applied. I had to quickly remove all the foreign keys from about 160 tables. The code below will quickly create a script that removes all Foreign Keys from a database.

select concat(‘alter table ‘,table_schema,’.',table_name,’ DROP FOREIGN KEY ‘,constraint_name,’;') from information_schema.table_constraints
where constraint_type=’FOREIGN KEY’;

You can limit by schema by adding the line : AND table_schema rlike ‘Schema Name’

Remember, Foreign keys are only valid when using Innodb, MyISAM does not support foreign keys.

Thanks to Prodromus for providing some of this information.  They have a great site that lists a ton of MySQL tips and tricks.  And, if you are a non-profit, they even perform pro-bono MySQL DBA work.  Look them up.

Also filed in | Tagged , , , , , , , , , , , , , | Comments (0)