Create a Website with MySQL

•  Import Data
• Sample DB 

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. It's outside the scope of this MySQL tutorial to go into the details of creating PHP websites. However, if you're interested, I've written a PHP tutorial that covers the basics of PHP (including database driven websites).

ColdFusion Database Connections

ColdFusion is another server-side programming environment that enables you to connect to a MySQL server. ColdFusion makes it extremely easy to query a database and output the results.

ColdFusion Query & Output

Here's an example of using CFML to query a database and then loop through, and output, the results of that query:

<cfquery datasource="dsnFruitShop" name="getFruit">

 SELECT FruitName, DateUpdated

 FROM FruitShop;

</cfquery>

<cfloop query="getFruit">

  #FruitName#

</cfloop>

ColdFusion Data Source Connection

The above code assumes that a data source connection called dsnFruitShop has been previously set up. This provides the connection to the database.

ColdFusion allows you to connect to a database via its administration interface (ColdFusion Administrator). Here, you create a "data source" connection which you can refer to within your code (as we did in the above example). Once you've set up the data source, it can be used on any ColdFusion page on that ColdFusion server.

Here are the steps for configuring a data source in ColdFusion:

1. Open ColdFusion Administrator

   Log in to the ColdFusion Administrator (usually located at /cfide/administrator/) using your username and password.

2. Open the Data Sources Option

   Click Data Sources in the left menu (under DATA & SERVICES).

3. Add a New Configuration

   Under Add New Data Source, enter the name you'd like to call your data source, and the driver.

   In this case, I select MySQL 5.

   Click Add.

   See below if you receive an error.

4. Configure and Create the Datasource

   Enter the details of your MySQL server and the name of the database you'd like this connection to be used for.

   This is the name of the database on the MySQL server. In our case, this FruitShop.

    

   Click Submit.

Data Source Error?

When you tried to add your data source you may have received the following error:

The standalone MySQL JDBC driver is no longer shipped with ColdFusion. Please download it, put it in cf_root/lib folder and restart ColdFusion.

This is because the driver wasn't included with ColdFusion when it shipped.

If you receive this error, do the following:

1. Download the driver from the MySQL website
2. Extract the file (i.e. .zip file or .tar.gz)
3. Move the .jar file to your cf_root/lib folder. Here, cf_root is your ColdFusion root directory. So for example, on my Mac, I dropped the file into /Applications/ColdFusion11/cfusion/lib/
4. Restart ColdFusion

Now you should be able to add the driver via the ColdFusion Administrator as shown in the previous steps.

The steps listed on this page assume that you at least know how to make a basic website using the scripting language involved. If you'd like to learn more about ColdFusion, see my ColdFusion tutorial which explains how to use ColdFusion (starting with the basics) and covers database connections and plenty more.