Programming a Website with MySQL DataBase Access for Localhost

Overview

Introduction

This article covers a series of topics leading up to programming an interactive webpage with MySQL database access. This tutorial will cover setting up the website to accept user queries such as the one illustrated in the following screenshot:

Accepting user input to perform basic editing functions on the database will also be covered. The path followed by this article involves six chapters and four programming languages: HTML/CSS is used to build the webpages; Javascript is used to render the pages dynamic and interact with the user; A packages known as "AJAX" is used to call PHP scripts which activate the MySQL queries to the database.

It is also possible, in principle, to setup a user interface which performs queries on the database without using Javascript/AJAX. The path which includes Javascript is taken here since this allows for dynamic web pages which display using the full graphical styling available from HTML/CSS. Furthermore using Javascript allows any editing and processing of the user input prior to its submission as a query to the database to be done by the client's system thus conserving server resources.

The first five chapters described below build, in steps, to the techniques used in the sixth chapter to access the database. For a rapid introduction it is possible to skip to the final chapter.

Chapters/Links

The following topics, which can be accessed by clicking on the button to the left of the discription, are covered:

Basic MySQL

This includes how to create a new database and table and how to execute four basic operations on the database: read, delete, write and edit.

II.

PHP and JavaScript

The basic JavaScript and PHP elements are covered including, basic commands for variables, loops, conditional statements and data structures. This section presupposes some knowledge of programming in a C++ like language.

III.

Accessing MySQL with PHP

This section covers executing the four basic operations: read, delete, write, and edit on the database using PHP scripts to send MySQL queries/commands.

IV.

Interactive Webpages with JavaScript

This section covers using HTML to call JavaScript and using JavaScript to rewrite HTML elements leading to an interactive display of page content.

Calling PHP from JavaScript

This section covers using a package called "Asynchronous JavaScript and XML (AJAX)" to call PHP scripts from JavaScript functions.

VI.

Interactive Database Access

This final section covers setting up an interactive webpage that can perform four basic operations that require data to be sent from HTML through JavaScript to PHP to query MySQL and back again. These four operations performed on the database are: read, delete, write, and edit.

The code has been tested on a Ubuntu local server. For a discussion of installing the software necessary to run the server see: "Setting up a Local Linux Server with MySQL Support".

Interactive Symbols

The following buttons appear interspersed in the text and at the end of the sections. These icons open up additional information:

Describes bugs, difficulties, or other anomalies that might be encountered.

Contains additional comments regarding the commands executed.

Contains links to web resources that either were the original source of the command sequence executed or contain additional information on the commands.

Disclaimer

The code covered on this site is functional; however as it is written it could potentially be exploited to attack the server. This site does not currently discuss securing a server or the code running there on. It is intended to cover only the basics of creating a website which interfaces with MySQL.