Home >> FAQs/Tutorials >> PHP Script Tutorials and Tips >> Index

PHP Script Tips - Working with MySQL Database

By: FYICenter.com

Part:   1  2  3  4   5  6  7 

(Continued from previous part...)

What Is a Result Set Object?

A result set object is a logical representation of data rows returned by mysql_query() function on SELECT statements. Every result set object has an internal pointer used to identify the current row in the result set. Once you get a result set object, you can use the following functions to retrieve detail information:

• mysql_free_result($rs) - Closes this result set object.
• mysql_num_rows($rs) - Returns the number rows in the result set.
• mysql_num_fields($rs) - Returns the number fields in the result set.
• mysql_fetch_row($rs) - Returns an array contains the current row indexed by field position numbers.
• mysql_fetch_assoc($rs) - Returns an array contains the current row indexed by field names.
• mysql_fetch_array($rs) - Returns an array contains the current row with double indexes: field position numbers and filed names.
• mysql_fetch_lengths($rs) - Returns an array contains lengths of all fields in the last row returned.
• mysql_field_name($rs, $i) - Returns the name of the field of the specified index.

How To Query Tables and Loop through the Returning Rows?

The best way to query tables and loop through the returning rows is to run the SELECT statement with the catch the mysql_query() function, catch the returning object as a result set, and loop through the result with the mysql_fetch_assoc() function in a while loop as shown in the following sample PHP script:

<?php
  include "mysql_connection.php";

  $sql = "SELECT id, url, time FROM fyi_links";
  $rs = mysql_query($sql, $con);
  while ($row = mysql_fetch_assoc($rs)) {
    print($row['id'].", ".$row['url'].", ".$row['time']."\n");
  }
  mysql_free_result($rs);

  mysql_close($con); 
?>

Using mysql_fetch_assoc() is better than other fetch functions, because it allows you to access field values by field names. If you run this script, you will see all rows from the fyi_links table are printed on the screen:

101, dev.fyicenter.com, 2006-02-26 22:29:02
102, dba.fyicenter.com, 2006-02-26 22:29:02
1101, dev.fyicenter.com, 2006-02-26 22:29:02
1102, dba.fyicenter.com, 2006-02-26 22:29:02

How To Update an Existing Rows in a Table?

Updating existing rows in a table requires to run the UPDATE statement with a WHERE clause to identify the row. The following sample script updates one row with two new values:

<?php
  include "mysql_connection.php";

  $sql = "UPDATE fyi_links SET notes='Nice site.', counts=8"
    . " WHERE id = 102";
  if (mysql_query($sql, $con)) {
    print(mysql_affected_rows() . " rows updated.\n");
  } else {
    print("SQL statement failed.\n");
  }

  mysql_close($con); 
?>

If you run this script, you will get something like this:

1 rows updated.

How To Delete an Existing Rows in a Table?

If you want to remove a row from a table, you can use the DELETE statement with a WHERE clause to identify the row. The following sample script deletes one row:

<?php
  include "mysql_connection.php";

  $sql = "DELETE FROM fyi_links WHERE id = 1102";
  if (mysql_query($sql, $con)) {
    print(mysql_affected_rows() . " rows deleted.\n");
  } else {
    print("SQL statement failed.\n");
  }

  mysql_close($con); 
?>

If you run this script, you will get something like this:

1 rows deleted.

(Continued on next part...)

Part:   1  2  3  4   5  6  7