8.1.11.2 Generating PHP Code

MySQL Workbench can be used to generate PHP code with the bundled PHP plugin, by using the Tools, Utilities, Copy as PHP Code menu option.

The example scenario that follows demonstrates how to create PHP code. It is a SELECT statement, and optionally uses SET to set variables.

SQL @variables generate PHP variables in the code that then bind to the statement before execution.

1. Generate or type in the desired SQL query into the SQL editor. This example will use the sakila database, with the query being:

   SET @last_update = '2006-02-14';
   
   SELECT  actor_id, first_name, last_name, last_update
     FROM  actor
     WHERE last_update > @last_update;

2. While in the SQL editor, choose Tools, Utilities, Copy as PHP Code (Iterate SELECT Results) from the main menu. This will copy PHP code to the clipboard.

3. Paste the code to the desired location.

Additionally, PHP code that connects to the MySQL database can also be generated by choosing Tools, Utilities, Copy as PHP Code (Connect to Server).

After combining the two, the generated PHP code will look like this:

<?php

$host     = "localhost";
$port     = 3306;
$socket   = "";
$user     = "nobody";
$password = "";
$dbname   = "sakila";

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

//$con->close();

$query = "SELECT actor_id, first_name, last_name, last_update
          FROM   actor
          WHERE  last_update > ?";
$last_update = '';

$stmt->bind_param('s', $last_update);

if ($stmt = $con->prepare($query)) {

    $stmt->execute();
    $stmt->bind_result($actor_id, $first_name, $last_name, $last_update);

    while ($stmt->fetch()) {
        // printf("%s, %s, %s, %s\n",
        //   $actor_id, $first_name, $last_name, $last_update);
    }

    $stmt->close();
}

?>

Note

The generated PHP code uses the mysqli PHP extension for MySQL. This extension must be enabled in your PHP distribution for this code to work. For additional details about this PHP extension, see MySQL and PHP.