mq16

Here's how to connect a website to a MySQL database.

It's all very well using MySQL Workbench for all our examples, but you might be wondering how to create a website with MySQL? After all, MySQL is used by more websites than any other database management system.

Querying a MySQL database from your website is not much different to querying it from MySQL Workbench — you use the same SQL statements for both. But of course, I'm sure you don't have a little gremlin sitting inside your website just waiting around for an SQL query to type every time someone visits your website? After all, you'd need to find a gremline with a good knowledge of SQL — not easy to find these days!

A better solution would be to do what most other websites do — use a server-side programming language to interface between the website and the MySQL server.

The server-side script would first connect to MySQL, then it can run any query that it needs to.

MySQL Workbench PHP Generator

MySQL Workbench includes a PHP generator. Well, it's not a full-blown PHP generator, but it will generate enough PHP code for you to paste into your website so that it can connect to MySQL and run your queries.

You can access it from the Tools | Utilities menu:

Copy as PHP Code (Connect to Server)

This option generates PHP code that connects using the parameters for the connection that you're currently using. You can always replace the parameters with another connection if required.

Clicking on this option simply copies the code to your clipboard.

Here's an example of the code that this option produces:

$host="localhost";
$port=3306;
$socket="";
$user="root";
$password="";
$dbname="fruitshop";

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

//$con->close();

Simply paste this code into the PHP document that needs to connect to MySQL and update any fields as required.

This code uses the MySQLi driver, which driver is used by PHP to provide an interface with MySQL databases.

The code also provides an error message in case the connection fails.

Copy as PHP Code (Iterate SELECT Results)

This option allows you to generate PHP code that embeds the SQL statement into a PHP script. It also generates the PHP code to iterate through the result set (so that the results can be displayed to the website user).

Again, clicking on this option copies the code to your clipboard.

Here's an example of using this option.

1. Run the Query

   Run the query that you will be using from the website.

2. Copy the PHP Code

   

   Select Tools | Utilities | Copy as PHP Code (Iterate SELECT Results)

3. Paste the Code

   The code will be pasted to your clipboard. Open your PHP document and paste the code. Here's what the above query turned out like once converted to PHP

   $query = "SELECT FruitId, FruitName FROM Fruit";
   
   
           if ($stmt = $con->prepare($query)) {
               $stmt->execute();
               $stmt->bind_result($FruitId, $FruitName);
               while ($stmt->fetch()) {
                   //printf("%s, %s\n", $FruitId, $FruitName);
               }
               $stmt->close();
           }

So you would need to combine this code with the first code. The first code opens the connection, the second code runs the query.