PHP with MYSQL

For orignial post kindly visit www.apepoint.com

When we work with MySQL we need to execute a number of query .We can execute almost all these queries by using PHP. For this PHP have provided some of inbuild function In the below list i have provided details of some of the inbuild functions.

mysql_connect()

This function is use to establish a connection with PHP SCRIPT and MySQL.

mysql_select_db()

This function is used to select database on which we will execute query

mysql_query()

This function execute a mysql query in selected database

mysql_fetch_row()

This function return a row of data from result as a numeric array

mysql_num_row()

This return number of rows in the result set.

mysql_error()

This function let us know the exact error in our mysql query if any.

mysql_close()

This function close the mysql connection

Now we are going to make a connection with our database (MySQL) and execute a query

This process is done in four steps

Step 1: Establishing connection
Step 2: Selecting database
Step 3: Executing a query
Step 4: Closing connection

As earlier I have explained the inbuild function lets take an example for excuting a query of create table in database "apepoint".


mysql_connect("hostname","username","password");
mysql_select_db("apepoint");
mysql_query("create table visitors(id int(4), ipaddress varchar(20))");
mysql_close($link);
?>


This is enough and the above query will execute and a table visitors will create in database apepoint. In some case there may be some error for example error in query so for this we need a proper error handling this is quite simple the below example add this also

mysql_connect("hostname","username","password") or die(mysql_error());
mysql_select_db("apepoint")or die(mysql_error());
mysql_query("create table visitors(id int(4), ipaddress varchar(20))")or die(mysql_error());
mysql_close();
?>


In this example we use

mysql_connect("hostname","username","password") this function is used to connect with our database this take 3 parameters and establish a connection with our database.

In case you have any problem in executing above script we suggest you to execute the below script

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

This tell you where the connection is established or not.


mysql_select_db("database_name")

This function is used to select database name a in mysql there may be so many database so we must provide the database name before executing any query.

mysql_query($sql)

This function is used to execute a mysql query in our database we can pass any query in this function

Some time we may want to know the total no. of affected row so we can get by using

int mysql_affected_row($result);

The below is an example of this

mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db('mydb');
mysql_query('DELETE FROM mytable WHERE id < 10');
printf("Records deleted: %d\n", mysql_affected_rows());
?>
mysql_close()

After completing the process we mysql close a mysql connecion this can be done by this function

In the previous example we study how to execute a query

But what about the cases when we execute query which return some result for example when we want to display result on web page.This process is little complex as it requre some extra effort

Consider we have a table which contents
visitors
ipaddress ID
localhost 122.0.0
google.com 209.85.227.147
apepoint.com 216.151.164.66




for this we will repeat first 3 steps

step 1 extablish connection
step 2 select database
step 3-a execute query
step 3-b process result
step 4 close connection

here you can under stand that step 3 has been expended

mysql_connect("hostname","username","password") or die(mysql_error());
mysql_select_db("apepoint")or die(mysql_error());
$result=mysql_query("select*from visitors")or die(mysql_error());

while($row=mysql_fetch_row($result))
{
echo "ID :";
echo $row[0];
echo "IP Address :";
echo $row[1];
echo "\n":
}

mysql_close();
?>


In this we use function mysql_fetch_row($result) this function return result as an enumerated array.

In some case we may need to save the result in array

we can do it by useing array_push() function below is the implementation of the same

$id= array;
$ip=array;
mysql_connect("hostname","username","password") or die(mysql_error());
mysql_select_db("apepoint")or die(mysql_error());
$result=mysql_query("select*from visitors")or die(mysql_error());

while($row=mysql_fetch_row($result))
{
array_push($id,$row[0]);
array_push($ip,$row[1]);
}

mysql_close();
?>


the above example store resulted data in arrays of id and ip address.

Apart from these there are so many more functions are available some of these are explained by below examples


mysql_connect("hostname", "user", "password");
mysql_select_db("mydb");
$result = mysql_query("select * from mytable");
while ($row = mysql_fetch_object($result)) {
echo $row->user_id;
echo $row->fullname;
}
mysql_free_result($result);
?>

In this example we use mysql_fetch_object function this function fetch results in the form of object



mysql_connect("hostname","username","password") or die(mysql_error());
mysql_select_db("apepoint")or die(mysql_error());
$result=mysql_query("select*from visitors")or die(mysql_error());

while($row=mysql_fetch_assoc($result))
{
echo "ID :";
echo $row->id;
echo "IP Address :";
echo $row->ipaddress;
echo "\n":
}

mysql_close();
?>


In the above example we use mysql_fetch_assoc() function this fetch the result is in the form of an associative array


For more example kindly click on the link script tute from main menu.
In this i explained about the visitors tracking by using php mysql