PHP and Database

• Its tym 2 add some dynamic content 2 ur website. The best choice for ease-of-use, price and support is the combination of PHP and MySQL.
• MySQL is currently the most popular open source database server in existence.

• So what is a database actually??

• A database is a structure that comes in two flavors: a flat database and a relational database.

• Flat database is a simple database design consisting of one large table instead of several interconnected tables. Called ‘flat’ because of its only two dimensional (data fields and records) structure, these database cannot represent complex data relationships. Also called flat file database or flatform database.

• Whereas, a relational database takes this "flat file" approach several logical steps further, allowing the user to specify information about multiple tables and the relationships between those tables, and often allowing much more declarative control over what rules the data in those tables must obey (constraints). These use language like SQL.

• In a relational structured database there are tables that store data. The columns define which kinds of information will be stored in the table. An individual column must be created for each type of data you wish to store (i.e. Age, Weight, Height).

• On the other hand, a row contains the actual values for these specified columns. Each row will have 1 value for each and every column. For example a table with columns (Name, Age, Weight-lbs) could have a row with the values (Bob, 65, 165).


• Why use database???

• Databases are most useful when it comes to storing information that fits into logical categories. For example, say that you wanted to store information of all the employees in a company. With a database you can group different parts of your business into separate tables to help store your information logically. Example tables might be: Employees, Supervisors, and Customers. Each table would then contain columns specific to these three areas.

• Example of a table ‘Persons’:

Last_name           First_name           Address            City

Dua                     Sakshi                  Xyz                   Panipat

Sharma               Rupani                  Abc                   Kkr

Sharma               Raghu                    Pqr                   Ambala

• The table above contains three records (one for each person) and four columns (LastName, FirstName, Address, and City).

• Queries:

• A query is a question or a request.

• With MySQL, we can query a database for specific information and have a recordset returned.

• Look at the following query:

• Select First_name from Persons:

• The query above selects all the data in the "First_name" column from the "Persons" table, and will return a recordset like this:

First_name

Sakshi

Rupani

Raghu

• If u r using WAMP5 den it is easy 2 manage the database with phpMyAdmin.


• phpMyAdmin is a free software tool written in PHP intended to handle the administration of MySQL over the World Wide Web. phpMyAdmin supports a wide range of operations with MySQL. The most frequently used operations are supported by the user interface (managing databases, tables, fields, relations, indexes, users, permissions, etc), while you still have the ability to directly execute any SQL statement.

• Setting a SQL password in phpMyAdmin:

• Start your browser and type in the Address:

http://localhost/phpMyAdmin/ after starting the server.

• Select your forum database by clicking on its name on the left tab -> Privileges -> Locate the user from the table and click on the icon under "action" tab(check the screen below for clearer orientation) -> On the new page that opens, scroll down a bit and you should see the fields under "Change Password" -> Enter your new password twice and click on the "Go" button.

• Otherwise install MySQL server on ur PC.



Creating a connection to a MySQL Database:

• Before you can access data in a database, you must create a connection to the database.

• In PHP, this is done with the mysql_connect() function.

• Syntax: mysql_connect(servername,username,password);

• Servername: Optional. It is the name of MySQL server. When your webserver is on the same machine with the MySQL server you can use ‘localhost’ or 127.0.0.1 as the value of this variable. Sometimes a web host will require you to specify the MySQL server name and port number. For example if the MySQL server name is db.php-mysql-tutorial.com and the port number is 3306 (the default port number for MySQL) then u will use 'db.php-mysql-tutorial.com:3306'

• Username: Optional. Specifies the username to log in with. Default value is the name of the user that owns the server process i.e. ‘root’.

• Password: Optional. Specifies the password to log in with. Default is " ".

• Example:

• In the following example we store the connection in a variable ($con) for later use in the script. The "die" part will be executed if the connection fails:

<?php

$con = mysql_connect("localhost","root","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

// some code

?>


Closing a Connection:

• The connection will be closed automatically when the script ends. To close the connection before, use the mysql_close() function:

<?php

$con = mysql_connect("localhost","root","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

// some code

mysql_close($con);

?>

• The die() function prints a message and exits the current script.

• Syntax: die(message);

• The mysql_error() function returns the error text from the last MySQL function. Errors coming back from the MySQL database backend no longer issue warnings. Instead, use mysql_error() to retrieve the error text. Note that this function only returns the error text from the most recently executed MySQL function.

                                  to be continued...