MySQL Workbench: PHP development helper plugins

In the new MySQL Workbench 5.2.35, a plugin that will be of interest to PHP developers, both experienced and newbies, has been added.
The plugin contains a couple of functions that allows you to create PHP code straight out of your current work in the Workbench SQL Editor, ready to be pasted to your PHP program.

Screenshot of SQL Editor with PHP Plugins

Copy as PHP Code (Connect to Server) This first plugin will take the parameters from your currently open connection to MySQL and create PHP code to connect to it.

$host="p:localhost";
$port=3306;
$socket="/var/mysql/mysql.sock";
$user="root";
$password="";
$dbname="";

$con = new mysqli($host, $user, $password, $dbname, $port, $socket)
	or die ('Could not connect to the database server' . mysqli_connect_error());

//$con->close();

Not a big deal, but saves some typing for getting something going quickly.

Copy as PHP Code (Iterate SELECT Results) This one will get your query and generate code to execute it and then iterates through the results. It will also parse the SQL and substitute any SQL @variables you use in it with PHP variables that will be bound to the statement before execution. Resultset rows will be bound to PHP variables with the same name as the field (or alias, if your query specified one).

So for the following query:

set @before_date = '1990-01-01';
set @after_date = '1980-01-01';

SELECT 
    emp_no, first_name, last_name, hire_date
FROM
    `employees`.`employees`
WHERE
    `hire_date` < @before_date AND `hire_date` > @after_date;

you would get this back:

$query = "SELECT      emp_no, first_name, last_name, hire_date FROM     `employees`.`employees` WHERE     `hire_date` < ? AND `hire_date` > ?";
$before_date = '';
$after_date = '';

$stmt->bind_param('ss', $before_date, $after_date); //FIXME: param types: s- string, i- integer, d- double, b- blob

if ($stmt = $con->prepare($query)) {
    $stmt->execute();
    $stmt->bind_result($emp_no, $first_name, $last_name, $hire_date);
    while ($stmt->fetch()) {
        //printf("%s, %s, %s, %s\n", $emp_no, $first_name, $last_name, $hire_date);
    }
    $stmt->close();
}

This should be enough for letting you quickly create a PHP program for doing something with the results of a parameterized query, straight out from your normal SQL development workflow and as a bonus, and be safe from injection bugs as a bonus.

Adding your own plugins

The plugins are simple, but more along these lines will be added in the future. And, more importantly, you can modify it to support your own needs. Here’s how:

First of all, find where’s the plugin file. The filename is code_utils_grt.py and you should be able to find it searching in the WB installation folder. To have your own version, rename it to something else like my_code_utils_grt.py, change a few identifiers so it won’t collide with the original built-in plugin as described below and use Scripting -> Install Plugin/Module… to install it to the correct place.

You can use the plugins there as a starting point for your own or modify them to match your coding style, choice of PHP driver etc

The important things you need to change in the plugin copy before installing are:

  1. the plugin name from CodeUtils to something else.
  2. ModuleInfo = DefineModule(name= "CodeUtils", author= "Oracle Corp.", version="1.0")
    
  3. the individual plugin names and identifiers (or just comment them out) and maybe the function names.
  4. @ModuleInfo.plugin("wb.sqlide.copyAsPHPConnect", caption= "Copy as PHP Code (Connect to Server)", input= [wbinputs.currentSQLEditor()], pluginMenu= "SQL/Utilities")
    @ModuleInfo.export(grt.INT, grt.classes.db_query_Editor)
    def copyAsPHPConnect(editor):
    

    The first parameter in @ModuleInfo.plugin is the plugin name and the 2nd is the caption. You can leave everything else, especially the metadata about the input parameters etc.

Here’s a sample plugin that you can use as a template. It’s stripped to the basics for easier understanding:

@ModuleInfo.plugin("wb.sqlide.copyAsPHPQuery", caption= "Copy as PHP Code (Run Query)", input= [wbinputs.currentQueryBuffer()], pluginMenu= "SQL/Utilities")
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
def copyAsPHPQuery(qbuffer):
    sql= qbuffer.selectedText or qbuffer.script

    text = 'print "the query is %s\n"' % sql.replace('"', r'\"')

    mforms.Utilities.set_clipboard_text(text)

    mforms.App.get().set_status_text("Copied PHP code to clipboard")
    return 0

13 thoughts on “MySQL Workbench: PHP development helper plugins”

  1. What am I missing? I can’t find where to download plugins and there are no plugins in my plugin menu. What am I doing wrong?

  2. Adam.. there’s no additional installation needed, the plugin is included starting from Workbench 5.2.35. This article is just about how to use it, not installing.

  3. Hello. I’m new to mySQL workbench and mySQL.

    I made a database. I can connect to it.
    But when i click on “Copy as PHP Code ..”, nothing seems to happen.
    May be the php code has been generated, but then i want to know, where is the php program?

    Please is there anyone who can explain this to me or know a link where it is expained?

    Thanks in advance.

  4. I installed the Mac version of MySQL 5.2.40. I have existing databases on localhost and had no problem selecting a database and tables in it to work on.

    When I click on the Plugings Dropdown the “Utilities” on has 3 choices

    Obfuscate object names etc.
    Put Indexes on columns etc…
    Change the storage engine on all tables

    NONE of the PHP utilities appear.

    I thought that all files in the Plugins folder in the Application / MySQLWorkbench.app were supposed to be installed.

    I see code_utils_grt.py in the Plugins folder but the “Scripting” dropdown “Install Plugin/Module” will not let me drill past the Application.

    When I look at “Plugin Manager” it is empty.

    I am connected to a server host.

    What is going on?

    How do I check to make sure that all the Plugins that are sitting in the Plugins folder are activated?

    If I want to use the PHP plugin exactly as it is written in the Application why must I copy it and modify it simply to install its functionality in the first place?

  5. Hello guys,

    Is there a plugin system for accessing EER models? I would love to have a “create ORM object hierarchy from EER model” plugin for my favorite language (Delphi.)

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.