7.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.

Below is an example scenario for how to create PHP code. It is a SELECT statement, and optionally uses SET to set variables.

SQL @variables will generate PHP variables in the code, which will then be bounded 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 that the 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 Improved Extension (Mysqli).