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.
