Retrieving data from a database

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.

<?php
  # Connect using the default user ID and password
  $conn = sasql_connect( "UID=DBA;PWD=sql" );
  if( ! $conn ) {
      echo "sasql_connect failed\n";
  } else {
      echo "Connected successfully\n";
      # Execute a SELECT statement
      $result = sasql_query( $conn, "SELECT * FROM Customers" );
      if( ! $result ) {
          echo "sasql_query failed!";
      } else {
          echo "query completed successfully\n";
          # Generate HTML from the result set
          sasql_result_all( $result );
          sasql_free_result( $result );
      }
      sasql_close( $conn );
  }
?>

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

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.

<?php
  # Connect using the default user ID and password
  $conn = sasql_connect( "UID=DBA;PWD=sql" );
  if( ! $conn ) {
      die ("Connection failed");
  } else {
      # Connected successfully.
  }
  # Execute a SELECT statement
  $result = sasql_query( $conn, "SELECT * FROM Customers" );
  if( ! $result ) {
      echo "sasql_query failed!";
      return 0;
  } else {
      echo "query completed successfully\n";
  }
  # Retrieve meta information about the results
  $num_cols = sasql_num_fields( $result );
  $num_rows = sasql_num_rows( $result );
  echo "Num of rows = $num_rows\n";
  echo "Num of cols = $num_cols\n";
  while( ($field = sasql_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 = sasql_fetch_row( $result )) ) {
      $curr_row++;
      $curr_col = 0;
      while( $curr_col < $num_cols ) {
          echo "$row[$curr_col]\t|"; 
          $curr_col++;
      }
      echo "\n";
  }
  # Clean up.
  sasql_free_result( $result );
  sasql_disconnect( $conn );
?>

The sasql_fetch_array function returns a single row from the table. The data can be retrieved by column names and column indexes.

The sasql_fetch_assoc function returns a single row from the table as an associative array. The data can be retrieved by using the column names as indexes. The following is an example.

<?php
  # Connect using the default user ID and password
  $conn = sasql_connect("UID=DBA;PWD=sql");
  
  /* check connection */
  if( sasql_errorcode() ) {
      printf("Connect failed: %s\n", sasql_error());
      exit();
  }
  
  $query = "SELECT Surname, Phone FROM Employees ORDER by EmployeeID";
  
  if( $result = sasql_query($conn, $query) ) {
  
      /* fetch associative array */
      while( $row = sasql_fetch_assoc($result) ) {
          printf ("%s (%s)\n", $row["Surname"], $row["Phone"]);
      }
  
      /* free result set */
      sasql_free_result($result);
  }
  
  /* close connection */
  sasql_close($conn);
?>

Two other similar methods are provided in the PHP interface: sasql_fetch_row returns a row that can be searched by column indexes only, while sasql_fetch_object returns a row that can be searched by column names only.

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

When a SELECT statement is sent to the database, a result set is returned. The sasql_fetch_row and sasql_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.

<?php
  # Connect using the default user ID and password
  $conn = sasql_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 = sasql_query( $conn, $query1 );
  if( $result ) {
    $num_rows = sasql_num_rows( $result );
    echo "Returned : $num_rows <BR>\n";
    $I = 1;
    while( ($row = sasql_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 = sasql_query( $conn, $query2 );
      if( $result2 ) {
     while(($detailed = sasql_fetch_array($result2))) {
         echo " $detailed[column_name]";
     }
     sasql_free_result( $result2 );
      } else {
     echo "******FAILED********";
      }
      echo "<br>\n";
      $I++;
    }
  }
  echo "<BR>\n";
  sasql_disconnect( $conn );
?>

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

Retrieving data from a database

Simple select query

Fetching by column name

Nested result sets