How to connect to MySQL database using PHP

How to connect to MySQL database using PHP

To be able to connect to a MySQL database, you will need the following MySQL Database Connection Credentials:

  • Database Name
  • Host Name
  • User Name
  • Password

Before you can get content out of your MySQL database, you must know how to establish a connection to MySQL from inside a PHP script. To perform basic queries from within MySQL is very easy. This article will show you how to get up and running.

Let’s get started. The first thing to do is connect to the database.The function to connect to MySQL is called mysql_connect. This function returns a resource which is a pointer to the database connection. It’s also called a database handle, and we’ll use it in later functions. Don’t forget to replace your connection details.

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

// Create connection
$dbhandle = new mysqli($servername, $username, $password);

// Check connection
if ($dbhandle->connect_error) {
    die("Connection failed: " . $dbhandle->connect_error);
} 
echo "Connected to MySQL successfully";
?>

All going well, you should see “Connected to MySQL successfully” when you run this script. If you can’t connect to the server, make sure your password, username and hostname are correct.

Introduction to Structured Query Language

Introduction to Structured Query Language

This page is a introductory tutorial of the Structured Query Language (also known as SQL) and is a pioneering effort on the World Wide Web, as this is the first comprehensive SQL tutorial available on the Internet. SQL allows users to access data in relationalf database management systems, such as Oracle, Sybase, Informix, Microsoft SQL Server, Access, and others, by allowing users to describe the data the user wishes to see. SQL also allows users to define the data in a database, and manipulate that data. This page will describe how to use SQL, and give examples. The SQL used in this document is “ANSI”, or standard SQL, and no SQL features of specific database management systems will be discussed until the “Nonstandard SQL” section. It is recommended that you print this page, so that you can easily refer back to previous examples.

Table Components

Table-name and Column-name

Where table-name and column-name are the names used and offered by you for the table and its columns, type stands for the kind of data to be stored in each column or field and size gives the maximum size of each field in terms of characters wide.

There is a wide range of data types available for use with Relational Database Management System (RDBMS). A few of the most commonly used data types and those that you will learn to use in this unit follow.

NOT NULL

This is optional and can be omitted. A column can be defined as NOT NULL. Use of this option means that the data must include a value for that column. If a column is defined as NOT NULL, and no value is given when inserting data, then an error will occur. The row will not be stored. Primary key columns (see below) must be defined as NOT NULL. For further information, see the discussion on null values later in this section.

PRIMARY KEY

This part of the command specifies the column, or columns, that make up the primary key. In a relational database all tables must have one, and only one, primary key (PK) defined. Should a table have more than one column that makes up the primary key then a comma separates them.

For example: PRIMARY KEY (student_number, unit_number).

FOREIGN KEY

This part of the command specifies the column, or columns, that make up the foreign key (FK). In a relational database a foreign key is the primary key of the parent table that links or joins tables together and enforces referential integrity between tables. To illustrate how and why we define foreign keys in database we are going to use the department and employee tables; see below.

Foreign key
Database Foreign key

Foreign Key Constraint

The foreign key constraint:

  • will reject an insert or update of a value if a corresponding value does not currently exist in the master table
  • will reject a delete if it would invalidate a references constraint; for example, a department may not be removed from the department table if there are any employees from the employee table who belong to that department
  • must reference a primary key column or group of columns in the master table
  • requires that the data type of the foreign key column(s) and constraint column(s) match.

Using the Create Table Command

CREATE TABLE table_name
(
column_name1 data_type size,

column_name2 data_type size,

column_name3 data_type size,
....
);

The column_name parameters specify the names of the columns of the table.

The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).

The size parameter specifies the maximum length of the column of the table.

The column_name parameters specify the names of the columns of the table.

The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).

The size parameter specifies the maximum length of the column of the table.

Rules for table and column names:

  • Can include letters and numbers but must begin with a letter.
  • Cannot include spaces. However, if you wish to represent a space use an underscore (_). For example: employee_number.
  • Cannot be a reserved word used in SQL, e.g. TABLE.
  • Cannot be the name of an SQL command, e.g. CREATE.
How to copy a database in phpMyAdmin

How to copy a database in phpMyAdmin

This tutorial will demonstrate how to copy or duplicate a database in phpMyAdmin.

Begin by accessing phpMyAdmin

  1. Select the database you wish to copy (by clicking on the database from the phpMyAdmin home screen).
  2. Once inside the database, select the Operations tab.
  3. Scroll down to the section where it says “Copy database to:”
  4. Type in the name of the new database.
  5. Select “structure and data” to copy everything.  Alternately, you can select “Structure only” if you want the columns but not the data.
  6. Check the box “CREATE DATABASE before copying” to create a new database.
  7. Check the box “Add AUTO_INCREMENT value.”
  8. Click on the Go button to proceed.

If you click on the home icon, you will notice your new database listed in the left column.  If you go inside, you will see the database is a copy of the previous database

Restoring The Database via SSH/Telnet

Restoring The Database via SSH/Telnet

In order to restore your database via SSH or Telnet you will require 2 things:

  1. SSH or Telnet access to your site. You will need to check with your hosting company to see if this is available.
  2. An SSH/Telnet Client, such as PuTTy.

Note:

If your database backup resides on your home computer, you will first have to upload it via FTP to your website

Open your SSH/Telnet client and log into your website. The command line prompt you will see will vary by OS. For most hosting companies, this will bring you into the FTP root folder.

You can either change directoties to wherever the backup is located and type in the following:

mysql -u dbusername -p databasename < backupname.sql

Or if you do not want to change directories and you know the path to where the backup is located, type in the following:

mysql -u dbusername -p databasename < /path/to/backupname.sql

You will be prompted for the database password. Enter it and the database will backup.

If your hosting company has you on a remote MySQL server, such as mysql.yourhost.com, you will need to add the servername to the command line. The servername will be the same as in your config.php. The command line will be:

mysql -h servername -u dbusername -p databasename < backupname.sql

Or:

mysql -h servername -u dbusername -p databasename < /path/to/backupname.sql
Change MySQL root password in WampServer

Change MySQL root password in WampServer

In this tutorial we are now going to see how to open the phpMyAdmin from the WampServer icon from the system tray and changing the root password.

Steps to change root password:

WampServer MySQL

  1. Open the MySQL console by selecting the menu shown above.
  2.  

  3. After clicking on the “MySQL console” following console window will be opened where default password is empty so we hit one enter key to continue without typing –  
    MySQL
  4.  

  5. Now in order to change the default password we need to type following query in the MySQL console (we are setting password as – “
    SET PASSWORD FOR root@localhost=PASSWORD('jordan');
  6.  

  7. In the older versions of the phpmyadmin we need to manually change the password in the phpmyadmin config file but in the current version we can skip this step.
  8.  

Updating the password of root or any user :

We can use following query in the mysql console for updating the query –

mysql> UPDATE mysql.user
SET Password = PASSWORD("AMMAN")
WHERE User = "root";

In the above example we are setting password “AMMAN” to the user “root”.

Backing Up The Database via SSH/Telnet

Backing Up The Database via SSH/Telnet

In order to back up your database via SSH or Telnet you will require 2 things:

  1. SSH or Telnet access to your site. You will need to check with your hosting company to see if this is available.
  2.  

  3. An SSH/Telnet Client, such as PuTTy.

Open your SSH/Telnet client and log into your website. The command line prompt you will see will vary by OS.
For most hosting companies, this will bring you into the FTP root folder.

Type in the following to create a backup in the current directory:

mysqldump --opt -Q -u dbusername -p databasename > backupname.sql

Or to create a backup in a separate directory (signified by /path/to/) type:

mysqldump --opt -Q -u dbusername -p databasename > /path/to/backupname.sql

You will be prompted for the database password. Enter it and the database will backup.

If your hosting company has you on a remote MySQL server, such as mysql.yourhost.com, you will need to add the servername to the command line. The servername will be the same as in your config.php. The command line will be:

Current directory:

mysqldump --opt -Q -h servername -u dbusername -p databasename > backupname.sql

Separate directory:

mysqldump --opt -Q -h servername -u dbusername -p databasename > /path/to/backupname.sql

You can then, if you wish, download the backup to your home computer.

Database Normalization

Database Normalization

Database Normalization is a technique for designing relational database tables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies. For example, when multiple instances of a given piece of information occur in a table, the possibility exists that these instances will not be kept consistent when the data within the table is updated, leading to a loss of data integrity. A table that is sufficiently normalized is less vulnerable to problems of this kind, because its structure reflects the basic assumptions for when multiple instances of the same information should be represented by a single instance only.

Higher degrees of normalization typically involve more tables and create the need for a larger number of joins, which can reduce performance. Accordingly, more highly normalized tables are typically used in database applications involving many isolated transactions (e.g. an Automated teller machine), while less normalized tables tend to be used in database applications that do not need to map complex relationships between data entities and data attributes (e.g. a reporting application, or a full-text search application).

Database theory describes a table’s degree of normalization in terms of normal forms of successively higher degrees of strictness. A table in third normal form (3NF), for example, is consequently in second normal form (2NF) as well; but the reverse is not always the case.

Although the normal forms are often defined informally in terms of the characteristics of tables, rigorous definitions of the normal forms are concerned with the characteristics of mathematical constructs known as relations. Whenever information is represented relationally, it is meaningful to consider the extent to which the representation is normalized.

A table that is not sufficiently normalized can suffer from logical inconsistencies of various types, and from anomalies involving data operations. In such a table:

  • The same information can be expressed on multiple records; therefore updates to the table may result in logical inconsistencies. For example, each record in an “Employees’ Skills” table might contain an Employee ID, Employee Address, and Skill; thus a change of address for a particular employee will potentially need to be applied to multiple records (one for each of his skills). If the update is not carried through successfully—if, that is, the employee’s address is updated on some records but not others—then the table is left in an inconsistent state. Specifically, the table provides conflicting answers to the question of what this particular employee’s address is. This phenomenon is known as an update anomaly.
  • There are circumstances in which certain facts cannot be recorded at all. For example, each record in a “Faculty and Their Courses” table might contain a Faculty ID, Faculty Name, Faculty Hire Date, and Course Code—thus we can record the details of any faculty member who teaches at least one course, but we cannot record the details of a newly-hired faculty member who has not yet been assigned to teach any courses. This phenomenon (fact/ event) is known as an insertion anomaly.
  • There are circumstances in which the deletion of data representing certain facts necessitates the deletion of data representing completely different facts. The “Faculty and Their Courses” table described in the previous example suffers from this type of anomaly, for if a faculty member temporarily ceases to be assigned to any courses, we must delete the last of the records on which that faculty member appears. This phenomenon is known as a deletion anomaly.

Ideally, a relational database table should be designed in such a way as to exclude the possibility of update, insertion, and deletion anomalies. The normal forms of relational database theory provide guidelines for deciding whether a particular design will be vulnerable to such anomalies. It is possible to correct an unnormalized design so as to make it adhere to the demands of the normal forms: this is called normalization.

Normalization typically involves decomposing an unnormalized table into two or more tables that, were they to be combined (joined), would convey exactly the same information as the original table.

TermDefinition
Relation

A table-like structure of rows and columns, where the rows store data about an entity and the columns store data about the attributes of that entity.  Each column has a unique name, and all values in a column are for the same attribute.  The cells in the table can only hold a single value.  The order of the rows doesn’t matter, and neither does the order of the columns.  The data in each row as a whole must be unique.

Functional dependency

A relationship between attributes in a relation where the value (or values) of one (or more) attributes determines the value (or values) of another attribute (or set of attributes).  For example, the functional dependency A Arrow Right B expresses the fact that if we know a value of A we will always know the corresponding value of B.    

Determinant

The attribute or set of attributes that functionally determine the value of another attribute or set of attributes.  The “left-hand side” of a functional dependency. For example, in the functional dependency A Arrow Right B, A is the determinant.

Candidate key

An attribute or set of attributes that uniquely determines the values of all the other attributes for a row in a relation.

Composite key

A key that contains two or more attributes.

Primary key

The candidate key selected to be the “official” key of a relationship.

Surrogate key

A column of artificial data added to a relation to serve as the primary key.

Foreign key

The attribute (or set of attributes) that is a primary key in one table that is then placed in a second table to form a relationship to the first table by storing linking values.  The term foreign key refers to the attribute or set of attributes in the second table.

Referential integrity constraint

A value constraint of a foreign key that states that no value can be placed in the foreign key unless it already exists as a primary key value in the linked table.

Normal form

One category in a set of categories (“normal forms”) used to describe relations according to the type of anomalies that can occur in the relations.

Multivalued dependency

An anomaly that occurs in a relation when a determinant is matched to a set of values rather than a single value.  For example, the multivalued dependency Person Arrow RightArrow Right Sibling means that a person may have more than one sibling. This is not a problem if the multivalued dependency exists in a separate table, but can cause a problem if the multivalued dependency exists within a table with other attributes.

For a table to be a relation:

  • The rows must contain data about an entity
  • The columns must contain data about the attributes of the entity instances
  • All entries in a column must be the same data type
  • Each column must have a unique name
  • Each cell in the table must hold only a single value
  • The order of the columns must be unimportant
  • The order of the rows must be unimportant
  • The data in each row must be unique – no two rows may contain identical data