PHP Sql: the database of PHP

· Dev & Design
Authors

The process of accessing and working with a database connection
from within a PHP script is as follows.

  • First you need to establish a connection to the database server.
  • You should have to validate any user input
  • Then select the database on the server to use.
  • Execute all the desired query against the database.
  • Furter retrieve and process the results.
  • Then create HTML or perform actions based on results.
  • Finally close the database connection.

How to connect to MySQL Database?

The first step here is to connect to the database, This is done via
the mysql_connect() function, whose syntax is as follows:

mysql_connect($server , $username , $password )

<?php
$con = mysql_connect(“localhost”,”henry”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}// some code
?>

The first parameter $server which we have passed, is the address of MySQL server
to connect using the username and password provided by the $username and $password parameters.


How to close a Database Connection

Though not strictly necessary, sometimes it is advantageous to close an open
connection to the database when it is no longer needed, Instead of waiting for an end
of the request, while PHP will do so automatically. The syntax of mysql_close() function
is as follows.

mysql_close($link)

where the parameter $link is the database connection resource returned from the
mysql_connect() function.


How to select the Database to Use?

Once you have set up a database connection, the next step is to select the database
on which you willbe performing queries(This is similar to SQL USE). To do
so, you need the mysql_select_db() function which has the following syntax:

mysql_select_db($database , $link);

<?php
$con = mysql_connect(“localhost”,”henry”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}// some code

mysql_select_db(“my_db”, $con);

mysql_close($con);
?>

Here $my_db is the name of database, and the other parameter $con which is optional
is the database connection resource returned by the mysql_connect() function. This
function attempts to select the specified database and return a Boolean value indicating the
success or failure.


How to perform Query against a Database

To perform a query against the database within PHP, we make use of
mysql_query() function, whose syntax is as follows:

mysql_query($query , $link);

<?php
$con = mysql_connect(“localhost”,”henry”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}// some code

mysql_select_db(“my_db”, $con);
$result = mysql_query(“SELECT * FROM person”);
mysql_close($con);
?>

where $result holds the result of the query executed. Query is written inside the mysql_query
function(without the terminating semi-colon or \g), the optional parameter $con is the value
returned by the function mysql_connect(). And if the database is not closed, then
PHP will use the last opened connection.


How to retrieving a Result Set?

PHP has many different methods to access the data from a result set,
but they all have the same general form. Here we are showing the examle
with the context of mysql_fetch_row() function. Its syntax is as follows:

mysql_query($query , $link);

<?php
$con = mysql_connect(“localhost”,”henry”,”abc123″);
if (!$con)
{
die(‘Could not connect: ‘ . mysql_error());
}// some code

mysql_select_db(“my_db”, $con);
$result = mysql_query(“SELECT * FROM person”);
while($row = mysql_fetch_array($result))
{
echo $row[‘FirstName’] . ” ” . $row[‘LastName’];
echo “<br />”;
}
mysql_close($con);
?>

where $result holds the result returned from a successful query executed using
the function mysql_query(). This function returns a single row from
the result set when called , in the form of enumerated array, where element “0” represents the first
column, element “1” represents the second, and so on. Each subsequent call to the funtion
will return the subsequent row in the result set until no row remains. Then the function
mysql_fetch_row() returns a boolean false. Generally mysql_fetch_row() function is used
in conjunction with the while loop to retrieve the entire array as shown in the above example.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s