Database Programming with mySQL and PHP
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.
- Create a connection.
- Select a database.
- Execute Queries.
- Get The Results.
- [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
- Create a connection.
- Select a database.
- Execute Queries.
- Get The Results.
- [Close the connection]. Not needed, coz' anyway the connection get closed when the web page is sent from the server.
- Following mySQL functions
- resource mysql_connect ( [string server], [string username], [string password])
- bool mysql_select_db ( string database_name, [resource link_identifier])
- resource mysql_query ( string query, [resource link_identifier])
- int mysql_num_rows ( resource result)
- int mysql_affected_rows ( [resource link_identifier])
- mixed mysql_result ( resource result, int row, [mixed field])
- array mysql_fetch_row ( resource result)
- 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.- array mysql_fetch_assoc ( resource result)
- 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:
- Search by Name: Use a file called search.php, sends details to address.php i.e name=xx&type=display
- Display Details: address.php and parameters name=xx&type=display
- Insert Records: address.php and parameters type=insert
- Modify Records: address.php and parameters name=xx&type=modify
- Delete Records: address.php and parameters name=xx&type=delete
- 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).
Thanks
Janaka.
Comments
Hey Janaka Wickremasinghe
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.