tkv
12-29-2007, 08:23 PM
Overview: In this section you will learn how to make PHP work with databases.
Okay I admit this is what most of you were waiting for creating a database driven site using PHP, Initially we will be working with MySQL database but dont worry its very easy to connect to other databases I will show you how to connect to other databases later on.
Syntax for connecting to a MySQL database
PHP has built in support for connecting to MySQL. The following PHP function call establishes the connection:
mysql_connect();
address: It is the IP address or hostname of the computer on which the MySQL server software is running, if MySQL is running on the same machine on which PHP is installed we use "localhost"
username: As the name suggests is the MySQL user account name.
password: Contains the password of the MySQL database.
The mysql_connect function shown above, returns a number that identifies the connection that has been established. Since we intend to make use of the connection, we should hold this value in a variable, lets assume that our MySQL database is on a localmachine and the username is root and password is pass and we will be storing the connection in a variable $db. Then the mysql_connect function will look something like this
$db = mysql_connect("locahost","root","pass");
Now that we are connected to MySQL database the next step is to select the database we wish to use to select the database we use the function mysql_select_db the syntax of which is given below
mysql_select_db(Database name,database connection string);
Let assume that we will be connecting to mybuddy database, so you code will be
mysql_select_db("mybuddy",$db);
That is it we have connected to MySQL database, see it so simple its just 2 lines of code
$db = mysql_connect("localhost","root","pass");
mysql_select_db("mybuddy",$db);
Before we can start retrieving data from our database we need some tables with some data in our database so that we can work with them!
Retrieving data from a table (Single row)
Before we can retrive data from a database table we need a table with data, I will use a jokes table which will contain some jokes for working with the example (I thought why not make it interesting and fun).
Jokes Table with sample data
# Table structure for table `jokes`
CREATE TABLE jokes (
joke_no smallint(6) NOT NULL default '0',
joke text NOT NULL,
PRIMARY KEY (joke_no)
) TYPE=MyISAM;
# Data for table `jokes`
INSERT INTO jokes VALUES (1, 'Q:) What is the astronaut\'s favorite place on the computer? \r\n\r\nA:) The space bar. \r\n');
INSERT INTO jokes VALUES (2, 'Salesman: This computer will cut your workload by 50%. \r\n\r\nCustomer: That\'s great. I\'ll take two of them! \r\n');
INSERT INTO jokes VALUES (3, 'Q:) What do you get when you cross a computer with an alligator? \r\nA:) A megabite. \r\n');
The above is the dump of table jokes with 3 jokes in it.
Performing SQL Query
In order to retrive data from the table we will have to execute a SQL Query for that we use the mysql_query function the syntax of which is
mysql_query(, );
For example if we were to write a query to retrive joke no 1 from our jokes table we would write the code as
$sql_result = mysql_query("SELECT joke FROM jokes WHERE joke_no=1", $db);
Remember: We had created our database connection and stored it in $db variable, thus I have replaced the connection id with $db, I have also stored the value of mysql_query (resource id) in a variable $sql_result
The above query did not give us the actual data but a resource id, to get the actual data we use the function mysql_fetch_row in case of a single row and mysql_fetch_array in case of multiple rows.
So to retrive the data I use the following code (we have just one row)
$rs = mysql_fetch_row($sql_result);
echo $rs[0];
The above code displays the joke, mysql_fetch_row outputs the data (joke) in an array we have stored that in a variable $rs and in the next line we use the echo construct to display the value in our first array $rs[0] as joke is the first element in our array.
To summarize, here's the complete code of a PHP Web page that will connect to our database, fetch the joke (joke number 1) from the jokes tables in the database
<?php
$db = mysql_connect("localhost","root","pass");
mysql_select_db("mybuddy",$db);
//replace the above values with your actual database values
$sql_result = mysql_query("SELECT joke FROM jokes WHERE joke_no=1", $db);
$rs = mysql_fetch_row($sql_result);
echo $rs[0];
?>
Retrieving multiple rows for data from a table
In the earlier tutorial we have retrieved a single row from a table, In this section we will learn how to retrieve multiple rows of data from a table.
As you have seen earlier we have used the function mysql_fetch_row to retrieve a single rows we use the mysql_fetch_array function to retrieve multiple rows from a table.
Here is an example where we retrieve the entire contents from our jokes table using mysql_fetch_array
<?php
$db = mysql_connect("localhost","root","pass");
mysql_select_db("mybuddy",$db);
//replace the above values with your actual database values
$sql_result = mysql_query("SELECT joke_no, joke FROM jokes", $db);
while($rs = mysql_fetch_array($sql_result))
{
echo "Joke No: $rs[0]<BR>";
echo str_replace("\n","<BR>",$rs[1]);
echo "<P>";
}
?>
There is nothing special here in the above example, except that mysql_fetch_row is called in a while loop so that it will continue the loop until all the records are displays (SQL Query is completed)
The mysql_fetch_array function accepts a result set as a parameter (stored in the $sql_result variable in this case), and returns the next row in the result set as an array this continues until there are no more rows in the result set when there are no more rows left mysql_fetch_array returns false which exits the loop
Okay I admit this is what most of you were waiting for creating a database driven site using PHP, Initially we will be working with MySQL database but dont worry its very easy to connect to other databases I will show you how to connect to other databases later on.
Syntax for connecting to a MySQL database
PHP has built in support for connecting to MySQL. The following PHP function call establishes the connection:
mysql_connect();
address: It is the IP address or hostname of the computer on which the MySQL server software is running, if MySQL is running on the same machine on which PHP is installed we use "localhost"
username: As the name suggests is the MySQL user account name.
password: Contains the password of the MySQL database.
The mysql_connect function shown above, returns a number that identifies the connection that has been established. Since we intend to make use of the connection, we should hold this value in a variable, lets assume that our MySQL database is on a localmachine and the username is root and password is pass and we will be storing the connection in a variable $db. Then the mysql_connect function will look something like this
$db = mysql_connect("locahost","root","pass");
Now that we are connected to MySQL database the next step is to select the database we wish to use to select the database we use the function mysql_select_db the syntax of which is given below
mysql_select_db(Database name,database connection string);
Let assume that we will be connecting to mybuddy database, so you code will be
mysql_select_db("mybuddy",$db);
That is it we have connected to MySQL database, see it so simple its just 2 lines of code
$db = mysql_connect("localhost","root","pass");
mysql_select_db("mybuddy",$db);
Before we can start retrieving data from our database we need some tables with some data in our database so that we can work with them!
Retrieving data from a table (Single row)
Before we can retrive data from a database table we need a table with data, I will use a jokes table which will contain some jokes for working with the example (I thought why not make it interesting and fun).
Jokes Table with sample data
# Table structure for table `jokes`
CREATE TABLE jokes (
joke_no smallint(6) NOT NULL default '0',
joke text NOT NULL,
PRIMARY KEY (joke_no)
) TYPE=MyISAM;
# Data for table `jokes`
INSERT INTO jokes VALUES (1, 'Q:) What is the astronaut\'s favorite place on the computer? \r\n\r\nA:) The space bar. \r\n');
INSERT INTO jokes VALUES (2, 'Salesman: This computer will cut your workload by 50%. \r\n\r\nCustomer: That\'s great. I\'ll take two of them! \r\n');
INSERT INTO jokes VALUES (3, 'Q:) What do you get when you cross a computer with an alligator? \r\nA:) A megabite. \r\n');
The above is the dump of table jokes with 3 jokes in it.
Performing SQL Query
In order to retrive data from the table we will have to execute a SQL Query for that we use the mysql_query function the syntax of which is
mysql_query(, );
For example if we were to write a query to retrive joke no 1 from our jokes table we would write the code as
$sql_result = mysql_query("SELECT joke FROM jokes WHERE joke_no=1", $db);
Remember: We had created our database connection and stored it in $db variable, thus I have replaced the connection id with $db, I have also stored the value of mysql_query (resource id) in a variable $sql_result
The above query did not give us the actual data but a resource id, to get the actual data we use the function mysql_fetch_row in case of a single row and mysql_fetch_array in case of multiple rows.
So to retrive the data I use the following code (we have just one row)
$rs = mysql_fetch_row($sql_result);
echo $rs[0];
The above code displays the joke, mysql_fetch_row outputs the data (joke) in an array we have stored that in a variable $rs and in the next line we use the echo construct to display the value in our first array $rs[0] as joke is the first element in our array.
To summarize, here's the complete code of a PHP Web page that will connect to our database, fetch the joke (joke number 1) from the jokes tables in the database
<?php
$db = mysql_connect("localhost","root","pass");
mysql_select_db("mybuddy",$db);
//replace the above values with your actual database values
$sql_result = mysql_query("SELECT joke FROM jokes WHERE joke_no=1", $db);
$rs = mysql_fetch_row($sql_result);
echo $rs[0];
?>
Retrieving multiple rows for data from a table
In the earlier tutorial we have retrieved a single row from a table, In this section we will learn how to retrieve multiple rows of data from a table.
As you have seen earlier we have used the function mysql_fetch_row to retrieve a single rows we use the mysql_fetch_array function to retrieve multiple rows from a table.
Here is an example where we retrieve the entire contents from our jokes table using mysql_fetch_array
<?php
$db = mysql_connect("localhost","root","pass");
mysql_select_db("mybuddy",$db);
//replace the above values with your actual database values
$sql_result = mysql_query("SELECT joke_no, joke FROM jokes", $db);
while($rs = mysql_fetch_array($sql_result))
{
echo "Joke No: $rs[0]<BR>";
echo str_replace("\n","<BR>",$rs[1]);
echo "<P>";
}
?>
There is nothing special here in the above example, except that mysql_fetch_row is called in a while loop so that it will continue the loop until all the records are displays (SQL Query is completed)
The mysql_fetch_array function accepts a result set as a parameter (stored in the $sql_result variable in this case), and returns the next row in the result set as an array this continues until there are no more rows in the result set when there are no more rows left mysql_fetch_array returns false which exits the loop