PHP Database Access (PDA)


PDA:   SOLD       



Storing data in regular files has many limitations. The larger the file size, the more difficult it is to search for data. Regular files also do not have the ability to process data, such as calculating total values, averages, and so on.

And more importantly, there are limitations to controlling access to data. We cannot determine who can and who cannot access data. Who can read and who cannot write, and so on.

Therefore, now we will use a more sophisticated data storage media, namely a database. Data operations in a database generally follow the same pattern, namely through a series of steps as follows:

  • Opening a connection with the database server.
  • Select and open the desired database
  • Send commands to retrieve/modify/delete data.
  • Accessing data retrieval results.
  • Ending the connection.

We will discuss each of these steps one by one. 

For example, we will enable the login facility in the Contact Manager application. The user will be asked to enter a username and password. If the data entered is the same as that in the database, the user will be forwarded to the welcome page. If wrong, an error message will appear. 

Additionally, we will create a page that allows users to change their password. 

Unregistered users can register on the page that will be provided. Here is a screenshot of the required web page: 

login.html

register.htm

A. SQL Query Overview

SQL (Structured Query Language) is a language specifically used to operate databases. To facilitate learning, SQL queries will be grouped into three:

  • Queries to manage databases
  • Query to access data in one table
  • Queries involving more than one table.

Database management queries. Included in this group are queries that aim to: 

  • Creating a database
  • Delete database
  • Create a table
  • Modifying tables
  • Deleting a table
  • Adding a user
  • Setting permissions
  • Delete user.

Creating a database is done with the following command: 

CREATE DATABASE

For example: for the Content Management application, we will create a ContentManager database with the following query: 

CREATE DATABASE ContactManager

Deleting a database is done with the command:

DROP DATABASE

Example : 

DROP DATABASE ContactManager

Creating a table is done by giving the following command:

CREATE TABLE <nama tabel> (
  <nama kolom> <tipe data>,
  <nama kolom> <tipe data>,
  ... ,
  <nama kolom> <tipe data>
);

for example:

CREATE TABLE userTable(
  UserId INT (3),
  UserName VARCHAR (50),
  Password VARCHAR (50),
  NamaLengkap VARCHAR (50)
);

Deleting a table is done using a query

DROP TABLE;

Example :

DROP TABLE userTable;

Single table query 

Single table queries are used to manage data in a single table. 

Some things that can be done on one table are:

|Tujuan                      |Query |
|-----------------------------|--------|
| Memasukkan data             | INSERT |
| Memodifikasi data           | UPDATE |
| Mengambil data              | SELECT |
| Menghapus data              | DELETE |
| Menghitung banyaknya data   | COUNT  |
| Menghitung penjumlahan data | SUM    |
| Menghitung nilai minimal    | MIN    |
| Menghitung nilai maksimal   | MAX    |
| Menghitung nilai rata-rata  | AVG    |

To insert data into a database, we use the INSERT command. The writing rules are:

INSERT INTO <nama tabel> (<nama kolom 1>, < nama kolom
1>, ..)
VALUES (<isi kolom 1>, <isi kolom 2>, ..);

Example :

INSERT INTO userTable VALUES (
  1,
  'endy',
  'inipaswod',
  'Endy Muhardin'
);

To change data in a database, use the UPDATE command, with the following writing rules:

UPDATE <nama tabel>
SET <nama kolom>=<isi kolom> WHERE <kriteria>

Example :

UPDATE userTable
SET password='test'
WHERE UserName='endy'

To display data in a table, use the SELECT command. The writing rules are as follows:

SELECT <nama kolom>
FROM <nama tabel>  WHERE <kriteria>

Example :

SELECT *
FROM UserTable
WHERE UserId=1

To delete data from a table, use the DELETE command. The writing rules are: 

DELETE FROM <nama tabel> WHERE <kriteria>

Example :

DELETE FROM UserTable
WHERE NamaLengkap='Endy Muhardin'

Query multiple tables 

Multi-table queries are used to connect data across more than one database. The discussion of joins is outside the PHP Programming material and is in the discussion of relational databases.

B. Creating a connection to the database 

Before we start, we will first create a database. We need a user table to store the following data:

| Nama Field  | Tipe Data   |
|-------------|-------------|
| UserId      | int(3)      |
| UserName    | varchar(50) |
| Password    | varchar(50) |
| NamaLengkap | varchar(50) |

The table is created by providing the following query:

CREATE TABLE userTable(
  UserId INT (3) AUTO_INCREMENT PRIMARY KEY,
  UserName VARCHAR (50),
  Password VARCHAR (50),
  NamaLengkap VARCHAR (50) );

The table is ready to use. We will start with the register page, since we don't have any users yet. The register.php file will handle the registration of new users. Its job is to enter the data filled in the form into the database. Next, we will do the steps as mentioned above.

Opening a connection with the database server 

To open a connection, we need the server address and the username and password for the database. After that, we open the connection with the following command: 

$dbServer = "localhost";
$dbUser = "endy";
$dbPass = "test";

$dbConn = mysql_pconnect($dbServer, $dbUser, $dbPass);

The connection to the database is stored in the $dbConn variable for use in subsequent steps.

Select and open the desired database 

Next, we will use the tutorial database, which was previously created using PHPMyAdmin. Use the following code to select and open the phpTutorial database.  

$dbName = "phpTutorial"; mysql_select_db($dbName);

Now the database is ready to use. We can move on to the next stage, which is entering data.

Send commands to retrieve/modify/delete data. 

The command to enter data is created using SQL rules, explained in more detail in the next section. The query (command) to enter data is: 

INSERT INTO userTable VALUES (
  1,
  'endy',
  'inipaswod',
  'Endy Muhardin'
);

The command is executed in the PHP script as follows:

$query = "
INSERT INTO userTable VALUES (
  1,
  'endy',
  'inipaswod',
  'Endy Muhardin'
);
";

$hasil = mysql_query($query); if($hasil){
  echo(mysql_affected_rows()." orang ditambahkan ke dalam sistem");
}

Our script must take data from the HTML Form (register.htm) and insert it into the database. To do this, we will create a register.php file that contains the following code:

<?
$dbServer = "localhost";
$dbUser = "endy";
$dbPass = "test";

$dbConn = mysql_pconnect($dbServer, $dbUser, $dbPass);

$dbName = "phpTutorial"; mysql_select_db($dbName);

$query = "
INSERT INTO userTable
(UserName, Password, NamaLengkap)
VALUES (
  '$user',
  '$pass',
  '$namaLengkap'
);
";

$hasil = mysql_query($query); if($hasil){
  echo(mysql_affected_rows()." orang ditambahkan ke dalam sistem");
}

?>

C. Accessing query results 

After register.htm is executed successfully, we have one user in the database. Thus, we can test the login page that we have created. To do this, we need to access the data in the database and match it with the input given by the user.

The steps to access data in a database are: 

  • Creating a database connection
  • Selecting a database
  • Creating a query
  • Running a query
  • Taking the results
  • Processing results.

The query that will be used is:

SELECT * FROM UserTable WHERE userName='input dari user'

This query will be put into the script as: 

$query = SELECT * FROM UserTable WHERE userName='$login'

And executed with the code: 

$hasil = mysql_query($query);

If the userName entered by the user does not exist in the database, the query will return 0 (zero) rows of results. If there are, the query will produce an array containing the data in each column in the database.

To find out the number of results obtained from a query, use the following code: 

$jumlahHasil = mysql_num_rows($hasil);

If the result is not zero (meaning the username has been registered in the system), we will retrieve the password data. For that, we use the command:

$data = mysql_fetch_array($hasil);

Password data is in an array, accessed by: 

$passDb = $data["Password"];

Thus, the entire login.php script will become:

<?

// variabel yang diperlukan untuk akses database
$user = "endy";
$pass = "test";
$db = "ContentManager";
$server = "localhost";

// membuat koneksi
$koneksi = mysql_connect($server, $user, $pass);

// memeriksa koneksi if(!$koneksi){
  echo("Koneksi ke database gagal");   exit;
}

// membuka database mysql_select_db($db);

// membuat query
$query = "SELECT * FROM userTable WHERE userName='".$login."'";

// mengeksekusi query
$hasil = mysql_query($query);

// mengakses hasil
$jumlahHasil = mysql_num_rows($hasil); if($jumlahHasil < 1){
  echo("User $login tidak ada dalam database <br>");
}else{
  echo("User $login ada dalam database<br>");
  $data = mysql_fetch_array($hasil);
  $passwordAsli = $data["password"];

  if($password == $passwordAsli){
    echo("Password untuk user $login benar<br>");
  }else{
    echo("Password untuk user $login salah<br>");
  }
}

?>

Post a Comment

Previous Next

نموذج الاتصال