Database Programming with mySQL and PHP

This tutorial is mainly intend to the audience who already has a little background on databases and want to do database programming in mySQL and PHP. If you are in a real hurry I suppose you can rush to the summery straight away.

Database Programming with mySQL and PHP.

When I wrote the first draft, it was really long so, I'm making this little shorter. I'm also assuming that you have installed both mySQL and PHP properly plus you have privileges (mostly to create, alter and drop databases and tables) to mySQL.
Okay, for any database related programming you just keep in mind the following steps in order.

  1. Create a connection.
  2. Select a database.
  3. Execute Queries.
  4. Get The Results.
  5. [Close the connection]. Not necessary, because the connection get closed, when the web page is sent from the server.

Now, lets go and investigate above steps.

First, we will create a database according to the following details.
Database name: AddressBook
Table name: Addresses
Table fields: ID, Name, DOB, HouseNumber, Street, City, Country, Telephone, Fax, Email, Remarks.

Well, you can just use the following SQL script for creating the database and the table. 

CREATE DATABASE AddressBook;
USE AddressBook;
CREATE TABLE Addresses (
    ID SMALLINT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(60) NOT NULL,
    DOB DATE NOT NULL,
    HouseNumber VARCHAR(5) NOT NULL,
    Street VARCHAR(30) NOT NULL,
    City VARCHAR(15) NOT NULL,
    Country VARCHAR(30) NOT NULL,
    Telephone VARCHAR(15) NOT NULL,
    Fax VARCHAR(15) NOT NULL,
    Email VARCHAR(30) NOT NULL,
    Remarks TEXT,
    PRIMARY KEY(ID)
);
INSERT INTO Addresses VALUES ('3','Tom','1966-05-03','No 31','Paradise Street,','Paradise City,','Haven.','666-666','999-999','tom@paradise.haven','Good Old Tommy');
 

The last line is just inserting an entry. By the way, please remember the username, password and the server (localhost usually) of the database that you have created. (For an example I've use root, with no password on localhost).

Finally, lets do some coding.

/*First, Lets make a connection*/
$db=mysql_connect("localhost","root");

/*Parameters are: mysql_connect([server],[username],[password]) and will return a link identifier on success otherwise a FALSE. i.e. you can use $db as the connection when you do the subsequence steps. */

/*Now, Lets Select the AddressBook database. */
mysql_select_db("AddressBook",$db);

/* Parameters are: mysql_select_db(database name,[link identifier]) and will return TRUE on success and FALSE otherwise. */

/*Ooo right, Lets execute a query. */
$results=mysql_query("SELECT * FROM Addresses",$db);

/*Parameters are: mysql_select_db(SQL Query,[link identifier]) and will return a resource identifier on success otherwise a FALSE. */

/*This is where things get little complicated. Well, piece of cake really. */
/*First lets see whether the query has returned any rows. */
if(mysql_num_rows($result)){
/*basically if any rows are returned, they will be checked.*/

/*Depending upon your requirement, you could use many ways to retrieve your results. For example lets say that you want to see the name of the person in the first row (if you know how many rows are returned), you can simply use some thing like this
echo $mysql_result($result,0,"Name");
The parameters are basically the resource identifier (from the above query), row number, and Field Name.
Or you can just go and fetch rows one by one till there ain't no more. Like this */
while($myresult=mysql_fetch_row($result)){

/*Now if you wanna display the name */
echo $myresult[1];

/*The thing that you should know under which element the 'Name' field will be located. That's not hard since you know about the table structure. So zero will be ID, one is Name, Two is DOB and likewise. This is not terribly hard, but if you want to use the field name instead, you could either use
while($myresult=mysql_fetch_array($result, MYSQL_ASSOC)){
or
while($myresult=mysql_fetch_assoc($result)){

in both case you can use like this,
echo $myresult['Name'];
*/
}
}

/*Closing the connection is not necessary as I've explained earlier, anyway I'll include it. */
mysql_close($db);
 

Note:
That's about it. For INSERT, UPDATE, DELETE or REPLACE use mysql_affected_rows([resource identifier]) to check whether how many rows are affected and for SELECT, DESCRIBE, SHOW or EXPLAIN use mysql_num_rows([resource identifier]).

Summary.

  • You just have to remember the following order
  1. Create a connection.
  2. Select a database.
  3. Execute Queries.
  4. Get The Results.
  5. [Close the connection]. Not needed, coz' anyway the connection get closed when the web page is sent from the server.
  • Following mySQL functions
  1. resource mysql_connect ( [string server], [string username], [string password])
  2. bool mysql_select_db ( string database_name, [resource link_identifier])
  3. resource mysql_query ( string query, [resource link_identifier])
  4. int mysql_num_rows ( resource result)
  5. int mysql_affected_rows ( [resource link_identifier])
  6. mixed mysql_result ( resource result, int row, [mixed field])
  7. array mysql_fetch_row ( resource result)
  8. array mysql_fetch_array ( resource result, [int result_type])
    result_type :
    a. MYSQL_ASSOC i.e. Associative array.
    b. MYSQL_NUM i.e. Array elements are indexed in numeric.
    c. MYSQL_BOTH. i.e. Mixed of both.
  9. array mysql_fetch_assoc ( resource result)
  10. bool mysql_close ( [resource link_identifier])

That's all folks.
Bye Bye.

Okay for those who want more, I'll write a small program to create and display Addresses.

Architecture:

  1. Search by Name: Use a file called search.php, sends details to address.php i.e name=xx&type=display
  2. Display Details: address.php and parameters name=xx&type=display
  3. Insert Records: address.php and parameters type=insert
  4. Modify Records: address.php and parameters name=xx&type=modify
  5. Delete Records: address.php and parameters name=xx&type=delete
  6. Have a Simple Navigator to move from one record to another: Have a different file and include it when ever needed.  

Folks, I've written this in a hurry, so it might be little confusing, but try to understand the concept. That's all you need. Well good luck and mail me (comments, suggestions, love, hate or any sort of mails).

download AddressBook.zip

Thanks

Janaka.

Comments

Hey Janaka Wickremasinghe

August 10, 2009 by Kunal A.

hey.

your tutorial really helped me. I have some areas where I am still struggling. Where and how could I contact you?

You must me logged in to write a comment.