dbx_query

Description

object dbx_query ( object link_identifier, string sql_statement [, int flags])

dbx_query() returns an object or 1 on success, and 0 on failure_ The result object is returned only if the query given in sql_statement produces a result set (i_e_ a SELECT query, even if the result set is empty)_

Ejemplo 1_ How to handle the returned value

<?php
$link   = dbx_connect(DBX_ODBC, "", "db", "username", "password")
    or die("Could not connect");

$result = dbx_query($link, 'SELECT id, parentid, description FROM table');

if ( is_object($result) ) {
    // ___ do some stuff here, see detailed examples below ___
    // first, print out field names and types 
    // then, draw a table filled with the returned field values
}
else {
    exit("Query failed");
}

dbx_close($link);
?>

The flags parameter is used to control the amount of information that is returned_ It may be any combination of the following constants with the bitwise OR operator (|)_ The DBX_COLNAMES_* flags override the dbx_colnames_case setting from php_ini_

DBX_RESULT_INDEX

It is always set, that is, the returned object has a data property which is a 2 dimensional array indexed numerically_ For example, in the expression data[2][3] 2 stands for the row (or record) number and 3 stands for the column (or field) number_ The first row and column are indexed at 0_

If DBX_RESULT_ASSOC is also specified, the returning object contains the information related to DBX_RESULT_INFO too, even if it was not specified_

DBX_RESULT_INFO

It provides info about columns, such as field names and field types_

DBX_RESULT_ASSOC

It effects that the field values can be accessed with the respective column names used as keys to the returned object's data property_

Associated results are actually references to the numerically indexed data, so modifying data[0][0] causes that data[0]['field_name_for_first_column'] is modified as well_

DBX_RESULT_UNBUFFERED (CVS only)

This flag will not create the data property, and the rows property will initially be 0_ Use this flag for large datasets, and use dbx_fetch_row() to retrieve the results row by row_

The dbx_fetch_row() function will return rows that are conformant to the flags set with this query_ Incidentally, it will also update the rows each time it is called_

DBX_COLNAMES_UNCHANGED (available from PHP 4_3_0)

The case of the returned column names will not be changed_

DBX_COLNAMES_UPPERCASE (available from PHP 4_3_0)

The case of the returned column names will be changed to uppercase_

DBX_COLNAMES_LOWERCASE (available from PHP 4_3_0)

The case of the returned column names will be changed to lowercase_

Note that DBX_RESULT_INDEX is always used, regardless of the actual value of flags parameter_ This means that only the following combinations are effective:

DBX_RESULT_INDEX
DBX_RESULT_INDEX | DBX_RESULT_INFO
DBX_RESULT_INDEX | DBX_RESULT_INFO | DBX_RESULT_ASSOC _ this is the default, if flags is not specified_

The returned object has four or five properties depending on flags:

handle

It is a valid handle for the connected database, and as such it can be used in module specific functions (if required)_

<?php
$result = dbx_query ($link, "SELECT id FROM table");
mysql_field_len ($result_>handle, 0);
?>

cols and rows

These contain the number of columns (or fields) and rows (or records) respectively_

<?php
$result = dbx_query ($link, 'SELECT id FROM table');
echo $result_>rows; // number of records
echo $result_>cols; // number of fields 
?>

info (optional)

It is returned only if either DBX_RESULT_INFO or DBX_RESULT_ASSOC is specified in the flags parameter_ It is a 2 dimensional array, that has two named rows (name and type) to retrieve column information_

Ejemplo 2_ lists each field's name and type

<?php
$result = dbx_query ($link, 'SELECT id FROM table',
                     DBX_RESULT_INDEX | DBX_RESULT_INFO);

for ($i = 0; $i < $result_>cols; $i++ ) {
    echo $result_>info['name'][$i] _ "\n";
    echo $result_>info['type'][$i] _ "\n";  
}
?>

data

This property contains the actual resulting data, possibly associated with column names as well depending on flags_ If DBX_RESULT_ASSOC is set, it is possible to use $result_>data[2]["field_name"]_

Ejemplo 3_ outputs the content of data property into HTML table

<?php
$result = dbx_query ($link, 'SELECT id, parentid, description FROM table');

echo "<table>\n";
foreach ( $result_>data as $row ) {
    echo "<tr>\n";
    foreach ( $row as $field ) {
        echo "<td>$field</td>";
    }
    echo "</tr>\n";
}
echo "</table>\n";
?>

Ejemplo 4_ How to handle UNBUFFERED queries

$result = dbx_query ($link, 'SELECT id, parentid, description FROM table', DBX_RESULT_UNBUFFERED);

echo "<table>\n";
while ( $row = dbx_fetch_row($result) ) {
    echo "<tr>\n";
    foreach ( $row as $field ) {
        echo "<td>$field</td>";
    }
    echo "</tr>\n";
}
echo "</table>\n";

Nota: Always refer to the module_specific documentation as well_
Column names for queries on an Oracle database are returned in lowercase_