Home > Software > Introduction to MySQL > Interacting with MySQL Using PHP

Working with MySQL MySQL and PHP Notes and References

Introduction to MySQL: A Tutorial

Interacting with MySQL Using PHP

For developing database driven web applications PHP is often used in conjunction with MySQL. A few useful examples are provided here on using PHP with MySQL. You require some basic knowledge of PHP for trying out these commands.

i. Connecting to Database Server Using PHP

Two most commonly used functions to connect to the MySQL server are:.

  1. mysql_connect();
  2. mysql_select_db();

mysql_connect() function takes three arguments as localhost, username, password and mysql_select_db() takes database name as argument.

These two functions are called by the following statements:

mysql_connect(localhost,username,password);

mysql_select_db(databasename);

mysql_connect function connects to the server and mysql_select_db() selects the database under the username provided in mysql_connect() function.

If any wrong name is given, the server responds with an error.

You can connect to MySQL server through PHP by using the above given hostname, username, password by the way

mysql_connect('localhost','subu','subu');

mysql_select_db('sample_db');

This  function selects the database sample_db.

ii. Executing Queries Using PHP and MySQL

There is a function in PHP called mysql_query(), by using which you can execute every  MySQL query which you are executing from command prompt.

Using the above table "student" you can do the following operation using PHP.

You can fetch records by executing:

$query="select * from student";

$result=mysql_query($query);

or

$result=mysql_query("select * from student");

The above function will return  true if the query executed successfully else returns false, if query is otherthan a SELECT statement.

If the query is a SELECT statement it returns result identifier.

You can test whether the query is ok on not as

if(!$result)

print(mysql_error());

else

print("Query OK");

mysql_error() is a function which returns the error in regard to the corresponding  query.

There are four functions used for fetching data:

  • mysql_fetch_row :Returns row as an enumerated array.
  • mysql_fetch_object :Returns row as an object.
  • mysql_fetch_array :Returns as an associative array.
  • mysql_result:Returns :One cell of data.

mysql_fetch_row() returns an array taking the above $result variable as argument

You can use this function to fetch the table data as:

while($data=mysql_fetch_row($result))

{

    print("$data[0],$data[1],$data[2],$data[3]");

}

The function mysql_fetch_object performs the same task, except the row is returned as an object rather than an array.

suppose $result=mysql_query("selectroll_no,name from student");

Then the procedure to get the  value  roll_no and name is:

while($row=mysql_fetch_object($result))

{

    print("$row->roll_no,$row->name\n");

}

The most useful fetching  function,mysql_fetch_array, offers the choice of results as an associative array or enumerated array.

This means you can refer to outputs by database field name rather than number:

Example: use of mysql_fetch_array():

 $query="SELECT roll_no,name ,specialization FROM student";

$result=mysql_query($query);

while($row=mysql_fetch_array($result))

{

    print("$row[roll_no],$row[name],$row[specialization]");

}

Note:mysql_fetch_array can also be used with numerical identifiers rather than field names as in mysql_fetch_row.

if you want to specify offset or field name rather than  making both available , you can do it like this:

$offset_row=mysql_fetch_array($result,MYSQL_NUM);

or

$associative_row =mysql_fetch_array($result,MYSQL_ASSOC);

 mysql_result function is useful where  you need only one piece of data.

An example of its usage:

$query="SELECT count(*) FROM student";

$result=mysql_query($query);

$data=mysql_result($result,0);

print("$data");

The mysql_result function  takes two  arguments: result identifier , row identifier. You can give another one as an optional field

i.e. mysql_result($result,0,0);

Field can take the value of the field offset  which is stored in variable $offset_row as given above.

You can execute INSERT, UPDATE, DELETE, ALTER statement  as described above by putting the query in mysql_query function.

Working with MySQL MySQL and PHP Notes and References
     Home                                                Copyright 2003-2016 TutorialsWeb.com                                   Disclaimer                                           Sitemap