Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - Programming » SQL Anywhere PHP API » Writing PHP scripts

Connecting to a database Next Page

Retrieving data from a database


One use of PHP scripts in web pages is to retrieve and display information contained in a database. The following examples demonstrate some useful techniques.

Simple select query

The following PHP code demonstrates a convenient way to include the result set of a SELECT statement in a web page. This sample is designed to connect to the SQL Anywhere sample database and return a list of customers.

This code can be embedded in a web page, provided your web server is configured to execute PHP scripts.

The source code for this sample is contained in your SQL Anywhere installation in a file called query.php.

<?
  # Connect using the default user ID and password
  $conn = sqlanywhere_connect( "UID=DBA;PWD=sql" );
  if( ! $conn ) {
      die ("Connection failed");
  } else {
      # Connected successfully.
  }
  # Execute a SELECT statement
  $result = sqlanywhere_query( $conn, "SELECT * FROM Customers" );
  if( ! $result ) {
      echo "sqlanywhere_query failed!";
      return 0;
  } else {
      echo "query completed successfully\n";
  }
  # Generate HTML from the result set
  sqlanywhere_result_all( $result );
  sqlanywhere_free_result( $result );
  # Disconnect
  sqlanywhere_disconnect( $conn );
?>

The sqlanywhere_result_all function fetches all the rows of the result set and generates an HTML output table to display them. The sqlanywhere_free_result function releases the resources used to store the result set.

Fetching by column name

In certain cases, you may not want to display all the data from a result set, or you may want to display the data in a different manner. The following sample illustrates how you can exercise greater control over the output format of the result set. PHP allows you to display as much information as you want in whatever manner you choose.

The source code for this sample is contained in your SQL Anywhere installation in a file called fetch.php.

<?
  # Connect using the default user ID and password
  $conn = sqlanywhere_connect( "UID=DBA;PWD=sql" );
  if( ! $conn ) {
      die ("Connection failed");
  } else {
      # Connected successfully.
  }
  # Execute a SELECT statement
  $result = sqlanywhere_query( $conn, "SELECT * FROM Customers" );
  if( ! $result ) {
      echo "sqlanywhere_query failed!";
      return 0;
  } else {
      echo "query completed successfully\n";
  }
  # Retrieve meta information about the results
  $num_cols = sqlanywhere_num_fields( $result );
  $num_rows = sqlanywhere_num_rows( $result );
  echo "Num of rows = $num_rows\n";
  echo "Num of cols = $num_cols\n";
  while( ($field = sqlanywhere_fetch_field( $result ) ) ) {
      echo "Field # : $field->ID \n";  
      echo "\tname   : $field->name \n";  
      echo "\tlength : $field->length \n";   
      echo "\ttype   : $field->type \n";  
  }
  # Fetch all the rows
  $curr_row = 0;
  while( ($row = sqlanywhere_fetch_row( $result ))) {
      $curr_row++;
      $curr_col = 0;
      while( $curr_col < $num_cols ) {
          echo "$row[$curr_col]\t|"; 
          $curr_col++;
      }
      echo "\n";
  }
  # Clean up.
  sqlanywhere_free_result( $result );
  sqlanywhere_disconnect( $conn );
?>

The sqlanywhere_fetch_array function returns a single row from the table. The data can be retrieved by column names and column indexes. Two other similar methods are provided in the PHP interface: sqlanywhere_fetch_row returns a row that can be searched by column indexes only, while sqlanywhere_fetch_object returns a row that can be searched by column names only.

For an example of the sqlanywhere_fetch_object function, see the fetch_object.php example script.

Nested result sets

When a SELECT statement is sent to the database, a result set is returned. The sqlanywhere_fetch_row and sqlanywhere_fetch_array functions retrieve data from the individual rows of a result set, returning each row as an array of columns that can be queried further.

The source code for this sample is contained in your SQL Anywhere installation in a file called nested.php.

<?
  # Connect using the default user ID and password
  $conn = sqlanywhere_connect( "UID=DBA;PWD=sql" );
  if( ! $conn ) {
      die ("Connection failed");
  } else {
      # Connected successfully.
  }
  # Retrieve the data and output HTML
  echo "<BR>\n";
  $query1 = "SELECT table_id, table_name FROM SYSTAB";
  $result = sqlanywhere_query( $conn, $query1 );
  if( $result ) {
    $num_rows = sqlanywhere_num_rows( $result );
    echo "Returned : $num_rows <BR>\n";
    $I = 1;
    while( ($row = sqlanywhere_fetch_array( $result ))) {
      echo "$I:  table_id:$row[table_id]" .
           " --- table_name:$row[table_name] <br>\n";
      $query2 = "SELECT table_id, column_name " .
      "FROM SYSTABCOL" . 
      "WHERE table_id = '$row[table_id]'";
      echo " $query2 <br>\n";
      echo "  Columns: ";
      $result2 = sqlanywhere_query( $conn, $query2 );
      if( $result2 ) {
     while(($detailed = sqlanywhere_fetch_array($result2))) {
         echo " $detailed[column_name]";
     }
     sqlanywhere_free_result( $result2 );
      } else {
     echo "******FAILED********";
      }
      echo "<br>\n";
      $I++;
    }
  }
  echo "<BR>\n";
  sqlanywhere_disconnect( $conn );
?>

In the above sample, the SQL statement selects the table ID and name for each table from SYSTAB. The sqlanywhere_query function returns an array of rows. The script iterates through the rows using the sqlanywhere_fetch_array function to retrieve the rows from an array. An inner iteration goes through the columns of each row and prints their values.