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.

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.

ModuleInfo = DefineModule(name= "CodeUtils", author= "Oracle Corp.", version="1.0")

2. the individual plugin names and identifiers (or just comment them out) and maybe the function names.

@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

Author: Mike Lischke

Team Lead MySQL Workbench View all posts by Mike Lischke