IST366: MySQL API Support in PHP

MySQL API Support in PHP

Most important functions

mysql_connect --- creates a connection to a MySQL server.
```
 int mysql_connect(string hostname[:port], string username, string password);
 
```
All arguments are optional; if you skip an argument the default value is used. Here is a short description of the arguments:
- hostname --- the name of the host running the database server.
 Default value: 'localhost'
- :port --- the port that database server is using for listening to requests. Only needed if your setup uses a port different than the default for MySQL
 Default value: ':3306'
- username --- the name of the user allowed to connect to the database server.
 Default value: the user than owns the web server process.
- password --- the password for the user.
 Default value: empty string.
The function returns a link identifier (a positive number which references the connection) on success, or false on error. This link identifier will be used in all the function calls,which send requests to the MySQL server.
Example:
```
 $connection = mysql_connect("ist.linux-site.net", "carter68", "mypasswd");
 if( $connection )
 print "Connection was established ";
 else
 print "Error: failed to connect to the server ";
 
```
mysql_pconnect --- creates a persistant connection to a MySQL server.
```
  int mysql_connect(string hostname[:port], string username, string password);
 
```
The function arguments and the return value are same as those for mysql_connect(). The difference between these two functions is that the connection created with mysql_pconnect() is not closed when the PHP script exits or mysql_close() call is made. The PHP interpreter maintains the connection with the MySQL server. When a mysql_pconnect() is made, the PHP interpreter first finds out if there is an existing open connection with the same function arguments. If it finds one then the link identifier of the existing connection is returned, instead of creating a new one.
The mysql_pconnect() function should be used in PHP applications where, over a short period of time, a large number of connections will be made to the MySQL server using the same username and password. This function saves the overhead of creating and closing a connection.
mysql_close --- closes the connection to the MySQL server.
```
 int mysql_close(int link_identifier);
 
```
The only parameter this function needs is the link_identifier returned by the mysql_connect() function. This particular connection will be closed. If you openned only one connection in your PHP script you don't have to provide this argument.
The mysql_close() retunrs true on the success, or false on error.
Example:
```
 $connection = mysql_connect("ist.linux-site.net", "carter68", "mypasswd");
 if( $connection )
 mysql_close();
 else
 print "Error: failed to connect to the server ";
 
```
mysql_select_db --- selects a database to work with.
```
 int mysql_select_db(string database_name, int [link_identifier]);
 
```
The database_name is the name of the database which is to become the active database. This parameter is required. The link_identifier references the connection, on wich the request will be sent to the server. If you have only one connection you don't have to provide this parameter. The function returns true on success, or false on error.
Example:
```
 $connection = mysql_connect("localhost", "bob", "test");
 if( $connection ){
 if( mysql_select_db("university") )
 print "Connection estableshed, database selected ";
 else
 print "Error: can not select database "; 
 }
 
```
mysql_query --- sends the SQL statement to the MySQL server for execution.
```
 int mysql_query(string query, int [link_identifier]);
 
```
The query parameter, whish is the SQL command to be sent, is required, though the link_identifier as usual is optional. The function returns a result identifier (positive integer) on success, or false on error. The result identifier contains the result of the execution of the SQL statement:
- In the case of create, alter, drop statements, the result identifier will indicate success or failure.
- In the case of delete, insert, or update statements, the result identifier can be used to find out the number of affected rows by using mysql_affected_rows()
- With the select statement, the result identifier will be an integer that corresponds to a pointer to the result set. This can be used to find out the result of the select statement with a mysql_result() call with the result identifier as an argument.
Example:
```
 $connection = mysql_pconnect("localhost", "bob", "test") or
 die("Error: can not estableshed connection to the server </html>");
 if( mysql_select_db("bob") ){
 $query = "create table php_test ( id char(3) not null )";
	$resid = mysql_query($query, $connection);
	if( $resid ) 
	 print "Query <pre>$query</pre> has been successfully executed ";
	else
	 print "Error: can not run SQL statement: <pre>$query</pre>";
 }
 
```

mysql_error --- returns any error message resulting from the last MySQL operation.

  string mysql_error(int [link_identifier]);

And again if you have only one open connection you don't have to send any parameters to the function.
Example:

 ...
 $query = "select myid from php_test";
 $resid = mysql_query($query, $connection) or 
          die("Error '".mysql_error($connection)."' in the statement: <pre>$query</pre>");
 ...

mysql_affected_rows --- retrieves the number of rows affected by a SQL query.
```
  int mysql_affected_rows(int [link_identifier]);
 
```
The call should be used to find out the number of rows inserted, updated, or deleted by the previous SQL (insert, delete, replace, or update) query. sent to the server. If the last query was a delete without a where clause (thus removing all records from a given table), the function will return zero. The number of rows returned by the select statement should be found with mysql_num_rows() function rather than with mysql_affected_rows().
mysql_result --- gets the data from the result identifier.
```
 mixed mysql_result(int result_identifier, int row, mixed [field]);
 
```
The result_identifier argument is a required argument, the value of the argument is usually obtained as the result of one of the functions: mysql_query(), mysql_db_query(), mysql_list_tables(), or mysql_list_dbs(). The row argument is the number of row from which to retrieve the data. The optional argument field determines the field in the row from which to retrieve the data. A value of this argument can be the column offset, column name, or column name in the format table_name.column_name. If the row argument is not specified then the next field of the row is returned. The type of the row argument depends on the value you are using. The type of the result returning by the mysql_result() function depends on the the column data type.
Example:
```
 ...
 $query = "select * from Department";
 $resid = mysql_query($query, $connection) or 
 die("Error '".mysql_error($connection)."' in the statement: <pre>$query</pre>");
 $value = mysql_result($resid, 0, 0);
 print "Row 0, column 0: $value ";
 $value = mysql_result($resid, 12, 'bad_column_name');
 if( empty($value) )
 print "Error: there is no such column or row in the table ";
 ...
 
```

Here there is an example that uses almost all describe functions.

Other functions to retrieve results of `select` query

mysql_num_rows --- returns the number of rows in the result identifier.
```
  int mysql_num_rows(int result_identifier);
 
```
The result_identifier parameter is required, and this function is used when the query performed corresponded to a select statement.

mysql_num_fields --- returns the number of fields in a result set.

   int mysql_num_fields(int result_identifier);

The result_identifier parameter is required.
Example:

 ...
 $resid = mysql_query("select * from Employee", $connection);
 if( ! $resid ){
    print "Error: can not run the query. Reason: " . mysql_error($connection);
    exit();
 }
 for($i=0;$i<mysql_num_rows($resid);$i++){
    print "Row $i:";
    for($j=0;$j<mysql_num_fields($resid);$j++)
       print "<li> Field #$j: " . mysql_result($resid, $i, $j);
    print "<br>";
 }
 ...

mysql_field_name --- retrieves the name of the field in the table.
```
  string mysql_field_name(int result_identifier, int field_index);
 
```
Both parameters are required. The function returns the name of the field with index field_index in the result identifier.
mysql_field_type --- returns the data type of a given field.
```
  string mysql_field_type(int result_identifier, int field_index);
 
```
Both parameters are required. The function returns the type of the field with index field_index in the result identifier.

mysql_field_len --- returns the length of the specified field.

  int mysqlfield_len(int result_identifier, int field_index);

Both parameters are required. The function returns the length of the field with index field_index in the result identifier.

Example:

 ...
 $query = "select * from Department";
 $resid = mysql_query($query) or die(mysql_error());
 print "<table border=1>\n <tr> ";
 for($i=0;$i<mysql_num_fields($resid);$i++){
 	print " <th satyle='width:". mysql_field_len($resid, $i) ."'>" . 
	      mysql_field_name($resid, $i) . 
		  " (" . 
		  mysql_field_type($resid, $i) . 
		  ")</th>";
 }
 print "</tr>\n";
 ...

See the complete example here.

mysql_fetch_row --- retrieves the next row from the result identifier as enumerated array.
```
 array mysql_fetch_row(int result_identifier);
 
```
The function returns an array (corresponding to the current row), or false if there are no more rows. mysql_fetch_row() increments the internal row pointer field of the result_identifier. So each subsequent call of this function will return the next row from the result.
Example:
```
 ...
 if( $resid = mysql_query($query) )
 while( $row = mysql_fetch_row($resid) ){
 print " <tr>";
 for($i=0;$i<mysql_num_fields($resid);$i++)
 print "<td>$row[$i]</td>\n";
 print " </tr>\n";
 } 
 ...
 
```
mysql_fetch_array --- fetches the next row as an associative array.
```
  array mysql_fetch_array(int result_identifier, int [result_type]);
 
```
The result_identifier parameter is required. The second optional argument result_type can have the following values:
- MYSQL_NUM: will cause the returned array to contain numeric indecies only (in this case mysql_fetch_array() works like mysql_fetch_row() function).
- MYSQL_ASSOC: will cause the returned array to contain associative indecies only.
- MYSQL_BOTH: will cause the returned array to contain both numeric and associative indecies.
The default value of the second argument is MYSQL_BOTH. Each subsequent call of mysql_fetch_array() will return an array corresponding to the next row, or false if there are no more rows.
Example:
```
   ...
   $query = "SELECT E.name, D.depname FROM Employee E, Department D WHERE E.depid=D.depid";
   $resid = mysql_query($query, $connection);
   while( $row = mysql_fetch_array($resid, MYSQL_ASSOC) ){
      print $row["name"];
	  print $row["depname"];
   }
   ...
  
```
Please note that the name of the table is not included in the index. We use E.name in the SQL statement, but only name as an index. Due to this fact, there is a limitation for the associative array: if there is a duplication in the field names, the last one will take precedence and you will need to use to use the numerical index to extract the other fields with the same name, or use aliasing of the result fields. For example, if the SQL query is
```
   SELECT table1.id, table2.id
     FROM table1, table2;
  
```
And you used
```
   $resid = mysql_query("SELCT ...");
   $row = mysql_fetch_array($resid);
  
```
then, referring to $row["id"] will return the content of table2.id.
mysql_fetch_field --- retrieves the column information from the result set.
```
 object mysql_fetch_field(int result_identifier, int [field_index])
 
```
The function returns an object describing the field with the given index field_index in the result_identifier. If the second argument is not specified, then the next field that was not retrieved with mysql_fetch_field() is returned. The returned object has the following properties:
- name: column name
- table: name of the table the column belongs to
- max_length: maximum length of the column
- not_null: 1, if the column can not be null
- primary_key: 1, if the column is a primary key
- unique_key: 1, if the column is a unique key
- multiple_key: 1, if the column is a non-unique key
- numeric: 1, if the column is numeric
- blob: 1, if the column is a BLOB
- type: the type of the column
- unsigned: 1, if the column is unsigned
- zerofill: 1, if the column is zero-filled
Example:
```
 ...
 if( $resid = mysql_query("select * from Employee E, Department D where D.depid=E.depid") ){
 $col_info = mysql_fetch_field($resid, 4);
 print "Coulmn " . $col_info->name . " belongs to the table " . $col_info->table . " ";	
 }
 ...
 
```

You will find the complete example here.

MySQL API Support in PHP

Most important functions

Other functions to retrieve results of select query

Other functions to retrieve results of `select` query