Tag Archives: table

How to Rename a Table using phpMyAdmin Software & Sites 27 APR 2012

It is not immediately apparent how you go about changing the name of a MySQL database table via the phpMyAdmin interface, so I thought I would drop a quick note here to remind me how to do it in the future.

To rename a table in phpMyAdmin, select the targeted table from the database table list in the left hand column.

You’ll see that the breadcrumb navigation will now indicate that you are in the desired tabled (server -> database -> table), and you will now have to click on the Operations tab (near the right hand side of the screen).

In the Table Options fieldset you will find a text box with the label reading “Rename table to”. Simply enter the new name that you wish to rename the table to, and hit the Go button at the bottom of the fieldset.

As easy as that.

How to Duplicate a Table in FlameRobin CodeUnit 03 OCT 2011

FlameRobin is a lightweight, cross-platform database administration tool for Firebird databases. It is also NOT one of my favourite tools to use because of its very lightweight nature! Nevertheless, there are times I must use it, and use it I shall, and besides, other people seem to like it, so it’s worth a post or two. Today I’ll quickly jot down a note on how to duplicate a table within FlameRobin.

First, connect to the database instance by selecting it from the explorer window and double clicking on it. Once the table list is loaded, select the table that you wish to duplicate. Click on it to launch a new information window which happens to have the following headers at the top: Summary, Constraints, Triggers, Indices, Privileges, Dependencies and DDL.

Click on the DDL link.

Copy the resulting SQL statement.

Now right click on the Tables menu option in the main FlameRobin window and select the “Create new…” menu option.

Paste the copied SQL into the window, change the table name sections to the duplicate’s desired name, and click on the Run (Play icon) button. Pressing F4 will also cause the statement to execute. If you’re happy with the report, click on the green tick button to commit your transaction, and smile as the table list in the main window magically gets updated with your new table!

Nifty.

MySQL: How to Check if a Table Exists with SQL CodeUnit 09 FEB 2011

To check if a table exists with SQL in a MySQL database is pretty easy, thanks to the nifty SHOW TABLES command.

To find if a specific table exists, we simply extend the standard SHOW TABLES statement with a LIKE parameter, which allows us to search for a specific string or for a partial string using the standard % modifier. If the statement returns a non-empty result set, we know the table exists.

For example:

SHOW TABLES LIKE 'activity-log';

will return a empty result set if your database doesn’t have a table named ‘activity-log’ in it. Similarly,

SHOW TABLES LIKE '%activity-log';

will return a result set containing all tables that end with ‘activity-log’, e.g. ‘sms-activity-log’ and ’email-activity-log’ as well as plain old ‘activity-log’ of course!

Nifty.

Highlight a Table Row using jQuery CodeUnit 26 JAN 2010

Using jQuery to highlight a table row on mouse over is pretty simple to achieve, and today I’ll quickly demonstrate how you can achieve this neat effect using the addClass and removeClass jQuery functionalities.

First, you need to define your CSS style to be applied to the row you are hovering over, picking out some CSS attributes that you wish to manipulate. In our case, we’re going to be changing the background-color property and thus our CSS style entry looks like this:

.datahighlight {
        background-color: #ffdc87 !important;
}

Next, we force all the rows in our table to be a member of a specific class:

Text Row to be highlighted

Finally, we apply the jQuery code:

      $(document).ready(function(){
		$('.simplehighlight').hover(function(){
			$(this).children().addClass('datahighlight');
		},function(){
			$(this).children().removeClass('datahighlight');
		});
      });

As you can see, all the jQuery magic is doing on mouse over of any element with the class ‘simplehighlight’ applied is adding a class ‘datahighlight’ to all the child elements of that selected element (the one being hovered above), in our case those being the actual cells of the row. Seeing as the !important rule is applied to the simplehighlight definition, those cell backgrounds are forced to the new color definition, giving the appearance of a row highlight.

On mouse out, the datahighlight class is removed, forcing all of the child elements (once again our cells) to return to their normal background color schemes.

It’s simple, easy to implement, and highly effective, as shown by the example below:

MySQL: How to Duplicate a Table Tips, Tricks and Tutorials 10 SEP 2009

There exists in this world a nice little SQL statement know as the SELECT INTO statement, one that works beautifully well in most database systems for when you want to create a backup of an existing table. In MySQL however this doesn’t work straight out of the box, which of course is a pain in the ass.

So how does one duplicate this functionality in MySQL then?

Well funnily enough, it’s actually pretty damn simple. The CREATE TABLE IF NOT EXISTS phrase is key here and combining this with a select statement will in fact create a copy of your existing table, taking all the data from your source table and dumping it into your newly created clone table. (Note that you can control what data gets copied into the new table by modifying the SELECT statement with an appropriate WHERE statement as well as the columns that get created by specifying column names in the first part of the SELECT statement in place of the asterisk).

So for example: “CREATE TABLE IF NOT EXISTS `my_backup_table` SELECT * FROM `my_table`”

…will create the `my_backup_table` if it doesn’t exist and copy over all data currently contained in `my_table`.

Pretty useful, no?

It is however important to note that this process does come with a few drawbacks. Firstly, attribute data like primary keys, comments, etc. gets lost in the process. Also, don’t expect any associated triggers to make the trip either. Finally, certain default values like CURRENT_TIMESTAMP gets converted to 0000-00-00 00:00:00 as well, just to add insult to injury.

But then, if this backup table really is just about keeping the existing data safe, then I guess this really shouldn’t matter all that much to you in the first place! :)

a nest of wooden tables