article

Basics of Online Relational Database Development with PHP 4 and MysQL

Email
Submitted on: 1/1/2015 2:46:00 PM
By: Faisal Arif (from psc cd)  
Level: Beginner
User Rating: By 5 Users
Compatibility: PHP 4.0
Views: 2360
 
     Basics of Online Relational Database Development with PHP 4 and MysQL

 
				
  • 1. Introduction
  • 2. MySQL/PHPMyAdmin Introduction
    • i - What is a database?
    • ii - Relational?
    • iii - Table Schematics
    • iv - Creating your MySQL table with appropriate datatypes
  • 3. Query By Example (QBE) and Basic SQL Statements
    • i - SELECT, WHERE, ORDER BY
    • ii - Working Code Examples
    • iii - QBE
  • 4. The Role of PHP4
    • i - Performing a simply query using mysql_fetch_array()
    • ii - phpMyAdmin - The secret weapon in internet db design ;)
    • iii - Useful links for PHP and MySQL
  • 5. Greets/Bibliography


Introduction:

This article will assume working knowledge of SQL and PHP4 of some kind. I will not
be assuming you've not used PHP4 in the past and simply reading this article in order
to grasp internet databases will not really work in practice.

If you have absolutely no idea of SQL or PHP4, please visit www.php.net and download
their PHP4 manual and download "Teach yourself SQL in 21 days" (search on google...its free ;-)  ). After reading both of those you may find you understand this article a lot
better.

2 - i - What is a database?


The word "database" is one that is used overly in the world of information technology.
Personally, I do not think enough people are aware of what a database actually is. Sure,
many many people I know are excellent C coders, they can effectively use UNIX and Linux,
perform all kinds of exploits on various systems yet... cannot administer a simple
database. This I find very strange.

We can define a database as a collection of data stored in some structural way
which can be "groped" in order to extract information from it.

Databases are used and accessed every day. For those of you who are not too confident
with databases, Yahoo! is one huge database of links... and this is the same for all
directory search engines. Meta searches are something completely different and not
to be confused and not something I will divulge into due to the constraints of this
article.


2 - ii - Relational?


One database server can have many databases to serve, of which those databases can have
many fields, those fields can have many attributes.

In order for me to explain how a relational database works, take a look at the previous
paragraph. We can see that a database server and a singular database would have what
we would refer to as a "One to Many" relationship. There can be "one" database server
to "many" databases to be served. If this is still unclear, bare with me.

Consider the following two tables, which I will use for the purposes of this paper
and is in fact a database I am currently constructing.

We shall go through the various stages of building a links database to serve to the
public via the web.


linkssectionname

linkidsection
url
description
sectionid<---------------------------------- sectionid


Above we can see two tables, 'links' and 'sectionname'. First of all, let us define
the content which shall be held in the two named tables.

The links table shall hold the main data. Every link shall be entered into this table
manually via using phpMyAdmin or 'hand entered' via a shell or command prompt (win32).

There are four fields present in links: linkid, url, description and sectionid.
There are only two fields present in sectionname: section and sectionid.

sectionid is an identical field to the sectionid field which was held in the previous table.
The sectionid here would also directly relate to section field in row order. We can assume
the following record layout within the sectionname table:


2. iii - Table Schematics

 ____________________________
|section | sectionid |
|-----------------------------|
|Security | 1 |
|Fun Links| 2 |
|Misc Stuff| 3 |
_______________________________



Above we see the table schematics for the sectionname table. We see security has an id of
1, fun links has an id of 2 and finally misc stuff has an id of 3. Each one of these is
going to be related to many many records in the links table. So, if we look at both tables
in turn:

Links and Sectionname

we can understand that the relationship between the links table the sectionname table is
said to be "many to one", as mentioned. There is MANY links to ONE section.

By understanding this basic database concept, we can start to grasp the fundamentals
concerned with database design and relational web development.

note: when entering data manually into the links table, the sectionid


2. iv - Creating your MySQL table with appropriate datatypes


Those of you who have programmed before, will be more than adapted to datatypes.
SQL is like C, in that it requires each field (or variable in C) to be given a datatype.
This is where the SQL and PHP4 programming language differs from most other programming
languages, it does not require variables to be given a signed data type.
For instance $foo = 1.523 will be automatically assigned a doublevar by PHP (float in C).
However datatypes can be forced upon variables in PHP if the need arises.

So, this section is going to be written as if the user was at a mysql> prompt. I'm writing
this way because administering a MySQL database from phpMyAdmin is extremely simple.
Ok so here goes. The create table function is used then the fields to be used, table
name and datatypes for fields shall be passed to the create table function as arguements
in order to setup the table. Here we go:

mysql> CREATE TABLE links (

linkid BIGINT(20) not null AUTO_INCREMENT,
url TINYTEXT not null,
description LONGTEXT not null,
PRIMARY KEY (linkid));


Above we can see the SQL used to create a table with the name of links, and add 4 very
important fields to this table.

Firstly we notice:

linkid BIGINT(20) not null AUTO_INCREMENT,

This is to set the linkid field which is going to be used for the primary key,
I'll mention why later. The bigint datatype of size 20 has been set to linkid with
the AUTO_INCREMENT tag on it. This means that whenever entering data into the database,
there is no need to add any data into the linkid field. It will simply be incremented by
1, for instance (linkid++ for you C programmers) everytime a new record is added to
the database. We've assigned bigint because it can handle extrememly large numbers.
Of course one wouldn't expect there to be more than 12,000 links in
a database. If you're creating databases so large, you seriously shouldn't be
reading this article. The NOT NULL flag has been added to this field, and many others.
NOT NULL means simply what it says, this field cannot be left
with a NULL entry, meaning that something must be entered into that field in order for
the record to be added
to the database table.


Secondly we can notice the remanining two fields:

url TINYTEXT not null,
description LONGTEXT not null,


The field 'url' and 'description' have been assigned TINYTEXT and LONGTEXT datatypes.
We wouldn't expect a mass amount of data to be entered into url where as we would
expect a fair amount of data to be placed into description. For example the description
for an url could be:


"This is a really really cool site. It has loads of stuff on it blah blah blah" and
this kinda' thing could go on for maybe 500 or more chars which exceeds the TINYTEXT
length boundaries. LONGTEXT would suffice this kind of data entry and therefore is a
much better data type to assign.


Lastly we can notice:

PRIMARY KEY (linkid));

This is the final statement in the SQL code which assigns a valid primary key to the
table based on one of the previous fields that have been defined in the CREATE TABLE()
function. As mentioned, the primary key is a unique way of identifying records within a
database with the means to not have duplicate data.

Note: if you're wondering where there's 2 closing brackets (parantheses), the trailing )
comes to close the CREATE TABLE() funtion. The semi-colon ; is the character used to
terminate any SQL statement to a database server.


3. i - SELECT, WHERE, ORDER BY

We must consider what commands we're going to use in order to select which data we
want from the database, on what condition we want this data to be included in the
results of our query and lastly... how we want the data to be returned and sorted
in the query.

We can simply answer all three of these questions by looking at three commands

SELECT: This is used in order to manually SELECT what fields we want to display

WHERE: This is used as a condition statement with SELECT to take records from

fields within the SELECT section which match the results of the WHERE
assignment.

ORDER BY: We use ORDER BY to sort the data, usually by field in ascending or descending
order.


3. ii - Working Code Examples


SELECT:

mysql> SELECT * FROM SECTIONNAME;

The following basic SELECT statement uses a * (wildcard) in order to select all fields
from the table name in the format SELECT [fields_to_be_selected] FROM
[the_table_you_want_to_select_fields_from];


security 1
fun stuff 2
misc links 3


WHERE and ORDER BY:

Well, supposing we only wanted to select all urls from the links table which were to do
with security? By using relational database development and linking the two tables,
as shown earlier, it's very easy to use WHERE statement as follows:

mysql> SELECT * FROM links
WHERE sectionid = 1
ORDER BY url;

Above, we can see how we have selected all fields from the links table but we've added a
condition to which records we want outputting in our query. We have effectively used
the WHERE statement to show that the only records we want outputting in our query are
the ones that have a sectionid of 1. If we go back and look at the sectionname table as
shown:

 ____________________________
|section | sectionid |
|-----------------------------|
|Security | 1 |
|Fun Links| 2 |
|Misc Stuff| 3 |
_______________________________


we can see that the record with a sectionid of 1 is security. All links concerned with
security would have been previously entered with a '1' in the sectionid field on the
links table. The database would not know this otherwise.

The ORDER BY statement used in this way would output the query and sort ascending on
the 'url' field.


3. iii - QBE (Query By Example)


QBE is a function that can be used with a web application called phpMyAdmin.
It's an extremely quick way of performing a query on a database by giving the GUI
an example of what you'd like to search for, how you want to search for it and how
you want the results displaying. This will be discussed briefly in the next
chapter.


4. The Role of PHP4

i - Performing a simply query using mysql_fetch_array()



This code is HEAVILY commented, basically I've went on writing the article while
coding at the same time it maybe advisable to take out the comments to leave just
the source code and save as query.php or something then have another code
version with comments, whichever you decide ;)


//removethesecomments
//PHP4 Query on MySQL Database


//Variables to open database socket/connection with

$user = "username";
$pass = "password";
$db = "database_name";

//Database Socket
$socket = mysql_connect('localhost', $user, $pass);
if ( ! $socket)
die ("Could not connect to MySql Server");

//Database selection function, with previous 2 variables passed as args to function
mysql_select_db($db, $socket)
or die ("Could not connect to database: $db".mysql_error() );

//The use of the "or die" statement is the error message that will be returned if the
//socket or database name is null
//The error code will also be returned for fault finding with the mysql_error() function


//This is the query that is being sent to the mysql database and is returned to the
//variable $result
//This is a static query and is not suitable for a search engine, as only one specific
//query can be sent not the results of a text box on a php form etc.

$result = mysql_query("SELECT * FROM LINKS");

//We can easily assign the results of mysql_num_rows to return the amount of results
//found by the
//query and assign it to a differntly named variable $num_rows and pass the $result
//var which holds
//our query to it as arguements


$num_rows = mysql_num_rows($result);

//notice how a simple table structure has been used to store the output of the query
//on the database :)

echo "<table = \"100%\">";


//The $rows variable holds the array contents and "steps through the array" row by row

while ($rows = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>";
echo "$rows[linkid]";
echo "</td>";
echo "<td>";
echo "<a href=\"$rows[url\">$rows[url]</a>";
echo "</td><td>";
echo "$rows[description]";
echo "</td>";
echo "</tr>";
}

echo "</table>";

//Here we use our previously declared $num_rows variable in order to find the number of
//returned results

echo "<small>Your query on database $db returned $num_rows result(s)</small>";

//Above we can see we used the function mysql_fetch_array(). This returns the
//contents of the query in an array and therefore we would access such results as we
//would any normal array. We could return them as objects or even
//by using mysql_fetch_rows() but that isn't deemed as the more modern way to use
//PHP4 and was used a lot more in the PHP3 days.

//removethesecomments?>
4. ii - phpMyAdmin : The secret weapon in internet db design ;)

phpMyAdmin is an excellent program which can be used for the administration of small or
large MySQL databases. It is a complete web interface developed with php4 and allows
logins to the database on a given host. The latest copy of phpMyAdmin can be
found at http://www.phpwizard.net and deserved a special mention because it is an
excellent tool for beginners who'd like to get into database design. It does however
look very much like MS Access but, oh well ;)


4.iii - Useful links for PHP and MySQL

http://www.php.net Official PHP website
http://www.mysql.org Official MySQL website
http://www.phpwizard.net Developers of phpMyAdmin
http://www.zend.com Home of Zend Engine

Recommended Books:

Teach Yourself PHP4 in 24 Hours
PHP4 Bible
MySQL and PHP From Scratch
Web Application Development with PHP
O'Reilly's MySQL and mSQL

Check those books out at: http://www.amazon.com


5. Greets/Biliography

Nothing I referred to was more than maybe 1 or 2 lines of information and wasn't copied,
so I dont think anyone else or any other piece of information or website deserves
recognition for this paper.


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.