Posts Tagged MySQL Workbench

Web Development Tools for Ubuntu OS

A set of good development tools is critical for website development. When I switched from Windows to Ubuntu system, I continuously searched the internet and tried different tools for website development. Now I have a set of trusted tools in hand. I’d like to share my view of the tools with you. I use Apache2 web server and MySQL server to host and store data. I will skip the servers and focus on programming and testing.

A. GEANY for PHP, JavaScript, and HTML writing

First of all, a powerful text editor with language grammar highlight is the most important one. At the very beginning, gEdit was used but it is plain text editor without any help in programming. Then I got PHPEdit and it works ok but slow. After a while I was get sick of it. I want a similar text editor but responding fast. I found GEANY when I read any web article online. The exact address is forgotten. After I downloaded GEANY and tested it. I loved it immediately. Since then, it is the editing tool for me. I have one post to introduce it briefly.
http://www.sunfinedata.com/tips/a-fastest-and-easy-to-use-web-programming-environment-geany/

B. MySQL Workbench for SQL programming

For the database operation and programming, MySQL workbench is the suitable, powerful tool. I have used it for a long time even on Windows system. It provides most functions you need to deal with the database. I have one post to introduce the tool.

http://www.sunfinedata.com/tutorial/use-mysql-workbench-to-develop-data-model-and-stored-procedures/

C. Meld for file comparison and merging

In Windows environment, I use Beyond Comparison to do all sorts of diff and merge operations. In the linux world, Meld is a diff and merge tool for free. You can compare two or three files and edit them in place (diffs update dynamically). You can compare two or three folders and launch file comparisons. You can browse and view a working copy from popular version control systems such such as CVS, Subversion, Bazaar-ng and Mercurial.

D. Google Chrome for web page testing

Whatever you write, at the end, you have to put them to the web server and test the programs and view the outputs. Chrome is a nice tool that can help us view the output in page view and source view. The source view is particularly useful if the page format has problems. By checking the page source, it will be easy to figure out what is wrong in the HTML code level.

Share

Tags: , , , , , , ,

A good PHP editor in Ubuntu Desktop OS

gPHPEdit is a good PHP editor in Ubuntu Desktop environment. The things I like it includes
1) Manage multiple files in tabs
2) Interpret the class implementation under the current folder
3) Highlight the syntex based on language, PHP, CSS, HTML
4) It handles the unicode characters very well

The things that I do not like includes the following:
1) It is very slow when I open a file with a lot of files in the folder
2) The close button in the middle of the toolbar. I accidentally closed my files several times, especially at the begining
3) There is no file browser that like the Windows Explorer has

Except for the dislikes, this is still a very good PHP editor. It becomes one of my web developing tools. By combining with MySQL workbench, it is very easy to develop web applications with database support.

Here is a screenshot of the application.

Share

Tags: , , , ,

Save and retrieve utf-8 characters to MySQL database directly

As the new version of MySQL Workbench (V5.2.31) rolled out, it solved the utf-8 character problem. I can directly insert, query, and edit utf-8 characters. This is first time I use the interface to directly handle Chinese characters in utf-8. In the past I do not know what encoding was used when I loaded utf-8 characters into the MySQL database even though I can use PHP get the characters back and display with no problem. I am still confused about that. But now in the MySQL Workbench, I am able to directly handle utf-8 characters.
However, I got a string of question marks, ie, “??????”, back from the data retrieved from the database by using my old PHP code. I googled and find a nice solution and then incorporate the solution into a nice, useful PHP class. It can handle utf-8 easily.
The idea is the following. For all PHP page, we should have the following meta tag:

<meta http-equiv="content-type" content="text/html; charset=utf-8">

This tells the Web browser that display page content in UTF-8 encoding and take form input text in UTF-8 encoding.
When we inserted data from forms into MySQL data tables, we should set two session control variables:

character_set_client=utf8
character_set_connection=utf8

This tells MySQL server that my SQL statement is encoded as UTF-8 and keep it as UTF-8 when executing the statement.
On the other hand, when retrieving text data from MySQL, I need to set one session control variable:

character_set_results=utf8.

This tells MySQL server that result set must be sent back in UTF-8 encoding.

Here is my PHP class to handle insert and retrieve data from MySQL database. It is generic class. You can modify it and use it in your PHP application.
<?php

/*
Author: Zhanshan Dong at Sunfinedata.com
@version $Id: sql.php 2010-05-18 $
*/

class SQL
{
private $_config;
private $_mysqli;

function __construct($config)
{
$this->_config = $config;
$this->_mysqli = @new mysqli($this->_config['DB_HOST'],
$this->_config['DB_USER'],
$this->_config['DB_PASSWORD'],
$this->_config['DB_NAME']);

// For handling any characters in utf-8 encoding correctly

// When insert utf-8 encoding input data to MySQL database, need to set two session
// control variables: character_set_client=utf8 and character_set_connection=utf8
// when saving input text to the database table. This is to tell MySQL server that
// my SQL statement is encoded as UTF-8 and keep it as UTF-8 when executing the statement
$mysqli->query("SET character_set_client=utf8");
$mysqli->query("SET character_set_connection=utf8");

// When retrieving text data from MySQL, need to set one session control variable:
// character_set_results=utf8. This is to tell MySQL server that result set must
// be sent back in UTF-8 encoding.
$mysqli->query("SET character_set_results=utf8");
}

function __destruct()
{
$this->_mysqli->close();
}

function execute($sql)
{
$res = "";
$res = $this->_mysqli->query($sql);
return $res;
}

function affected_rows()
{
return $this->_mysqli->affected_rows;
}

function escape_str($str)
{
return $this->_mysqli->real_escape_string($str);
}

function getInsertID()
{
return mysqli_insert_id($this->_mysqli);
}

}

?>

Share

Tags: , , , ,

Use MySQL Workbench to develop data model and stored procedures

In the past few years, MySQL Workbench has grown to a full blown MySQL desktop tool that can help MySQL users in various ways. In this article I want to introduce its capability to handle data model and stored procedure. In the past, I used to use Microsoft Access to generate data models because I do not have an appropriate free tool to do it. Now MySQL.org provides MySQL Workbench (I use version 5.2.25 CE). There are three main function blocks:  SQL Development, Data Modeling, and Server Administration. We will focus on its data modeling block that provides convenient yet powerful tools to construct data models and develop stored procedures.  The following screen shot is the home page of the MySQL Workbench, it highlights the three main function blocks. The data modeling is in the middle. You can open existing model and create new model from scratch, existing database, or existing SQL script.

Once you select a way to work on a data model, we can visually work on your new data model in the EER diagram page. Below is a very simple data model for Beyond Event database. There are three tables in the model.They are connected through foreign keys. You can use Workbench to create new tables, add fields and indices, even insert data rows. After a data model is created, you can use forward engineering to create a real database.

Since version 5, MySQL introduced stored procedure functionality. So you can write complex SQL stored procedure to process data and database request. That can help ease internet traffic and accelerate the SQL query.  By using the SQL routine editor in MySQL Workbench in the following screen shot, a series of SQL routines related to your data model can be developed and stored with your data model. Workbench’s routine editor is plain simple. As long as you are familiar with SQL, it is not a big deal.

You can download the latest version MySQL Workbench from mysql.org. The latest version 5.2.27 CE fixed the utf-8 encoding problem. Now we can type non-english characters with no problem. See the screen shot.

Share

Tags: , , , ,