PHP is a server-side scripting language, and is ideal for operating a database on a server.
The database may be accessed directly and manually edited. However, a script on a website needs a server-side scripting language to perform these tasks remotely. PHP therefore works beautifully with MySQL to access, update and edit the information in the database.
Database design needs to be done well if the power of PHP is to come to the fore. Well-structured and normalised tables, logically interconnected through primary and foreign keys, ensure an open-ended and sustainable structure to the database that can operate at any scale.
Relational Database Management refers to the type of relationships between data in separate tables. In the example, a bookshop wishes to create a database of its stock. It could create a single table, listing all the information about a book in each row (record). The rectangles represent table names, and the ovals column names. The lines between the table names indicate whether the relationships are one-to-one or many-to-one.
However, what if someone asked if there were any other books by the same author? Or the accounts department needed all the books from a single publisher, or date of publication. Or perhaps only the ISBN number.
The relationships within and between tables are indexed by keys. There are two types of key needed to make a relational database function:
The primary key is how the data in a table is primarily ordered. This is ideally an integer, since MySQL is far more efficient with integers as indexes. Table columns which are intended for primary keys can be set to 'auto-increment', so that the table is filled sequentially, starting at '1'. However, primary keys could also be any of the data types that are suitable.
Rules: a primary key must comply to three requirements:
The foreign key is a column in one table that refers to a primary key of another table. For example, the
auth_id in the example of the bookstore inventory database is a foreign key for the titles table. The FK links a unique title to its author, not by her name, but the id number she has in the author's table. This helps to avoid ambiguity of shared names.
In the example, the
authors has a one-to-many relationship with the
titles table. In reality, a book may have more than one author, but for the sake of the exercise, we are assuming the bookstore catalogues books by the first author name only. Each author is unique, and each title has a direct, single relationship to one author. However, an author may have more than one title. The primary key in
authors is the
auth_id, and this is used in the
titles table as the foreign key. FKs can therefore be repeated in a table (an author's ID appears for each title she has published).
Normalization refers to the manner in which data is organised. Relational databases work by the relationships between the data in different tables.
First normalization means that a data of a similar type is uniquely listed in a single table. In the
authors table each author appears once, even though she may have written more than one book. A non-normalized database would have the author's full details in the same table as a book title and details. This would oblige there to be either multiple repetitions of either book title etc. columns, or multiple occurrences of the author's details.
Instead, all of the author's details are listed once in a table dedicated to that purpose, and any requirement for information from that table in other tables is achieved by a single, unique primary key reference (
Not only does this make data retrieval more efficient, it permits data, such as an author's telephone number, to be updated at any point in time with a single action, with no risk of redundant data creating conflicts throughout a complex database.
A third normalization procedure would ensure that no data in a table is interdependent. This means that every column would be directly dependent on the primary key. In practice, this may be excessively devisive, creating many tables which in themselves becomes difficult to keep track of. To what degree a database is normalized depends on how large and complex the data set is, and how the information is to be retrieved and edited.
There are several different table type, or storage engine, in the MySQL database application. They have varying characteristics, and perform differently for different purposes.
The most common storage engine is
MyISAM. This table type is very useful and flexible, with the singular drawback of not being usable for transactions, for which the alternative
InnoDB may be used.
InnoDB requires more disk space and is generally slower than
MyISAM. In addition, it does not support
A typical interface between PHP and MySQL is accessing a database hosted on a server, running a query, and placing the records found in a useful format, such as a single or multi-dimensional array.
The first step is to access the database. For this we need to know the access data for the database:
Since this is for-your-eyes-only data, it should be kept in a file called something like
mysqli_connect.php outside the root of the domain on the server. This prevents anyone else finding the file and exploiting the data for nefarious purposes.
The call to this file can be made ad-hoc when needed, but to ensure access to the mysqli_connect file from anywhere on the site, you can create a config.php file to store its URL, as well as useful constants, and include the config file on each page, whether the database is needed or not.
Once the database has been successfully accessed, a query may be run to retrieve data from a table or a number of tables.
A good way to store the data returned by the query is to create an array, which is structured to make the data useful and logically ordered.
A database stores the date and time in a format which needs to be converted for use on a website. To instruct the computer to insert today's date in a column
This returns the stored date value from the data base and converts it from a string to time format in the variable
timestamp, which can then be displayed in any way desirable.
Be careful of the American order of days and months!
Content © Renewable.Media. All rights reserved. Created : September 19, 2014 Last updated :February 14, 2016
The most recent article is:
View this item in the topic:
and many more articles in the subject:
1687 - 1759
Nicolaus Bernoulli (I) was the first Nicolaus in the illustrious family dynasty of Bernoulli mathematicians in Basel, Switzerland, in the 17th and 18th centuries.
Relationships are a lot like algebra: have you never looked at your X and wondered Y?
Website © renewable-media.com | Designed by: Andrew Bone