3. 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:.
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 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
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("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
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:
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:
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";
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:
mysql_result function is useful where you need only one piece of data.
An example of its usage:
$query="SELECT count(*) FROM student";
The mysql_result function takes two arguments: result identifier , row identifier. You can give another one as an optional field
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.