WDP - Database

EXPLAIN WHAT IS A DATABASE

A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. In one view, databases can be classified according to types of content: bibliographic, full-text, numeric, and images

RELATIONAL DATABASES

A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. 

INTRODUCTION TO SQL

SQL is a standard language for accessing and manipulating databases

• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
• SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
• SQL can create new databases
• SQL can create new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
• SQL can set permissions on tables, procedures, and views

ARCHITECTURES FOR DATABASE ACCESS

MYSQL & PHPMyAdmin

PhpMyAdmin is one of the most popular applications for MySQL databases management. It is a free tool written in PHP. Through this software you can create, alter, drop, delete, import and export MySQL database tables. You can run MySQL queries, optimize, repair and check tables, change collation and execute other database management commands.

HOW TO SET UP  PHPMyAdmin

Once you have doenloaded the zip file and turned off any antivirus or firewall you are ready to start

STEP 1
Step 2

Step 3
STEP 4
Step 5

Step 6
STEP 7
Step 8

Step 9
STEP 10
Step 11

Step 12
Step 13

Step 14

 Open the folder you downloaded to and right click and click Extract All...

Select a destination folder.

I have used the default folder which is the same folder the zip file was downloaded to

Click on Extract

Screen shot below shows file being extracted by Windows 7

Open the location where you extracted the file

Open the folder phpMyAdmin-X.X.X-english (where X is the version number)

The file you find in there will have the same file name.

You need to rename it phpmyadmin

Right click the renamed file and Copy it to clipboard - right click and select Copy from list

Navigate to your web server root (C:\Inetpub\wwwroot) and paste the file there

Open the phpmyadmin folder you pasted and create a new folder and name it config

Now close all open folders

Open your Browser and type http://localhost/phpmyadmin/setup/index.php

This opens the configuration page

Ignore the security warnings as they are intended for server administrators installing phpMyAdmin on an internet server

On the bottom of the page click on New Server

You are required to set up the php configuration for your computer as follows

Check the following settins, some of which are entered by default

Server hostname: localhost

Connection Type: tcp

PHP extension to use: mysql

Authentication type: config

User for config auth: root

Password for config auth: Your MySQL root password

Scroll down and click Save

The phpMyAdmin Overview page opens

Ignore any warnings

Under Servers ensure localhost appears under Name

Now close browser

Navigate to your phpmyadmin folder (C:\Inetpub\wwwroot\phpmyadmin)

Open the config folder

Right click and copy the file config.inc

Paste the config file to phpmyadmin folder (C:\Inetpub\wwwroot\phpmyadmin)

The official instructions instruct you to delete the config folder, however this is not necessary in a local testing environment

Step 15

Close all folders

Open Web Browser and type in http://localhost/phpmyadmin/ to start setting up your databases

Congratulations! - you have now created a PHP development environment.

PHP CODE TO CONNECT TO THE DATABASE

PHP 5 and later can work with a MySQL database using:

• MySQLi extension (the "i" stands for improved)
• PDO (PHP Data Objects)

Example:

<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

ref:

http://searchsqlserver.techtarget.com/definition/database

http://searchsqlserver.techtarget.com/definition/relational-database

http://www.w3schools.com/sql/sql_intro.asp

https://docs.oracle.com/cd/B10501_01/server.920/a96531/ch1_ovea.gif

http://www.windowsecurity.com/whitepapers/software_engineering/An-Overview-of-Common-Database-Architectures.html

https://www.siteground.com/tutorials/phpmyadmin/

http://www.w3schools.com/php/php_mysql_connect.asp

http://www.landpro.com.au/Windows_7_Install_PHPMyAdmin.php