Php mysqli fetch columns

mysqli_result::fetch_column

Fetches one row of data from the result set and returns the 0-indexed column. Each subsequent call to this function will return the value from the next row within the result set, or false if there are no more rows.

Note: This function sets NULL fields to the PHP null value.

Parameters

0-indexed number of the column you wish to retrieve from the row. If no value is supplied, the first column will be returned.

Return Values

Returns a single column from the next row of a result set or false if there are no more rows.

There is no way to return another column from the same row if you use this function to retrieve data.

Examples

Example #1 mysqli_result::fetch_column() example

 mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $mysqli = new mysqli("localhost", "my_user", "my_password", "world"); $query = "SELECT CountryCode, Name FROM City ORDER BY ID DESC LIMIT 5"; $result = $mysqli->query($query); /* fetch a single value from the second column */ while ($Name = $result->fetch_column(1)) < printf("%s\n", $Name); >

 mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $mysqli = mysqli_connect("localhost", "my_user", "my_password", "world"); $query = "SELECT CountryCode, Name FROM City ORDER BY ID DESC LIMIT 5"; $result = mysqli_query($mysqli, $query); /* fetch a single value from the second column */ while ($Name = mysqli_fetch_column($result, 1)) < printf("%s\n", $Name); >

The above examples will output something similar to:

Rafah Nablus Jabaliya Hebron Khan Yunis

See Also

• mysqli_fetch_all() — Fetch all result rows as an associative array, a numeric array, or both
• mysqli_fetch_array() — Fetch the next row of a result set as an associative, a numeric array, or both
• mysqli_fetch_assoc() — Fetch the next row of a result set as an associative array
• mysqli_fetch_object() — Fetch the next row of a result set as an object
• mysqli_fetch_row() — Fetch the next row of a result set as an enumerated array
• mysqli_data_seek() — Adjusts the result pointer to an arbitrary row in the result

PHP 8.2

(PHP 5, 7, 8) mysqli_result::fetch_array mysqli_fetch_array the next row of set as an associative, numeric or both Object-oriented style Procedural style

(PHP 5, 7, 8) mysqli_result::fetch_assoc mysqli_fetch_assoc the next row of set an associative array Object-oriented style Procedural style Fetches one

(PHP 5, 7, 8) mysqli_result::fetch_field mysqli_fetch_field Returns the next in set Object-oriented style Procedural style Returns the definition of one

(PHP 5, 7, 8) mysqli_result::fetch_field_direct mysqli_fetch_field_direct meta-data for single Object-oriented style Procedural style Returns an object

Источник

The mysqli_result class

Represents the result set obtained from a query against the database.

Class synopsis

public fetch_object ( string $class = «stdClass» , array $constructor_args = [] ): object | null | false

Properties

Stores whether the result is buffered or unbuffered as an int ( MYSQLI_STORE_RESULT or MYSQLI_USE_RESULT , respectively).

Changelog

Version	Description
8.0.0	mysqli_result implements IteratorAggregate now. Previously, Traversable was implemented instead.

Table of Contents

• mysqli_result::__construct — Constructs a mysqli_result object
• mysqli_result::$current_field — Get current field offset of a result pointer
• mysqli_result::data_seek — Adjusts the result pointer to an arbitrary row in the result
• mysqli_result::fetch_all — Fetch all result rows as an associative array, a numeric array, or both
• mysqli_result::fetch_array — Fetch the next row of a result set as an associative, a numeric array, or both
• mysqli_result::fetch_assoc — Fetch the next row of a result set as an associative array
• mysqli_result::fetch_column — Fetch a single column from the next row of a result set
• mysqli_result::fetch_field_direct — Fetch meta-data for a single field
• mysqli_result::fetch_field — Returns the next field in the result set
• mysqli_result::fetch_fields — Returns an array of objects representing the fields in a result set
• mysqli_result::fetch_object — Fetch the next row of a result set as an object
• mysqli_result::fetch_row — Fetch the next row of a result set as an enumerated array
• mysqli_result::$field_count — Gets the number of fields in the result set
• mysqli_result::field_seek — Set result pointer to a specified field offset
• mysqli_result::free — Frees the memory associated with a result
• mysqli_result::getIterator — Retrieve an external iterator
• mysqli_result::$lengths — Returns the lengths of the columns of the current row in the result set
• mysqli_result::$num_rows — Gets the number of rows in the result set

User Contributed Notes 7 notes

Converting an old project from using the mysql extension to the mysqli extension, I found the most annoying change to be the lack of a corresponding mysql_result function in mysqli. While mysql_result is a generally terrible function, it was useful for fetching a single result field *value* from a result set (for example, if looking up a user’s ID).

The behavior of mysql_result is approximated here, though you may want to name it something other than mysqli_result so as to avoid thinking it’s an actual, built-in function.

function mysqli_result ( $res , $row , $field = 0 ) <
$res -> data_seek ( $row );
$datarow = $res -> fetch_array ();
return $datarow [ $field ];
>
?>

Implementing it via the OO interface is left as an exercise to the reader.

Switching from Php5 to Php7, especially if you have worked on an ongoing, long term project, it is unfortunate that there is no mysqli_result function.

So, this may be helpfull and you can call this function as you wish. I assume you do restricted search (searching for single row or few rows only).

function mysqli_result($search, $row, $field)$i=0; while($results=mysqli_fetch_array($search))if ($i==$row)
$i++;>
return $result;>

$search=mysqli_query($connection, «select name from table_name where «);
$name=mysqli_result($search, 0, «id»);

An «mysqli_result» function where $field can be like table_name.field_name with alias or not.
function mysqli_result ( $result , $row , $field = 0 ) if ( $result === false ) return false ;
if ( $row >= mysqli_num_rows ( $result )) return false ;
if ( is_string ( $field ) && !( strpos ( $field , «.» )=== false )) $t_field = explode ( «.» , $field );
$field =- 1 ;
$t_fields = mysqli_fetch_fields ( $result );
for ( $id = 0 ; $id < mysqli_num_fields ( $result ); $id ++) if ( $t_fields [ $id ]-> table == $t_field [ 0 ] && $t_fields [ $id ]-> name == $t_field [ 1 ]) $field = $id ;
break;
>
>
if ( $field ==- 1 ) return false ;
>
mysqli_data_seek ( $result , $row );
$line = mysqli_fetch_array ( $result );
return isset( $line [ $field ])? $line [ $field ]: false ;
>
?>

Generally, it appears Mysqli OO vs Procedural style has no significant difference in speed, at least with the more generally used functions and methods (connect, close, query, free, etc).

With the fetch_* family of functions and methods dealing with result rows, however, Procedural wins out. Averaging over a hundred or so tests with a result set of 180,000 records, and using mysqli_fetch_*() functions vs. their mysqli_result::fetch_*() counterpart object methods to read and iterate over all records, all of the mysqli_fetch_*() functions win by ~0.1 seconds less.

This is interesting considering we’re dealing with the same result object in both styles.

This was using Vistax64, PHP5.3.2, Mysql 5.1.45, using a bit of this code:

// procedural — takes 0.1 seconds less than OO here
$stopwatch = microtime ( true );
while( $row = mysqli_fetch_assoc ( $result )) ++ $z ;
>
echo microtime ( true ) — $stopwatch ;

// OO
$stopwatch = microtime ( true );
while( $row = $result -> fetch_assoc ()) ++ $z ;
>
echo microtime ( true ) — $stopwatch ;

Источник

mysqli_fetch_column

Fetches one row of data from the result set and returns the 0-indexed column. Each subsequent call to this function will return the value from the next row within the result set, or false if there are no more rows.

Note: This function sets NULL fields to the PHP null value.

Parameters

0-indexed number of the column you wish to retrieve from the row. If no value is supplied, the first column will be returned.

Return Values

Returns a single column from the next row of a result set or false if there are no more rows.

There is no way to return another column from the same row if you use this function to retrieve data.

Examples

Example #1 mysqli_result::fetch_column() example

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );

$query = «SELECT CountryCode, Name FROM City ORDER BY ID DESC LIMIT 5» ;

$result = $mysqli -> query ( $query );

/* fetch a single value from the second column */
while ( $Name = $result -> fetch_column ( 1 )) printf ( «%s\n» , $Name );
>

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );

$query = «SELECT CountryCode, Name FROM City ORDER BY ID DESC LIMIT 5» ;

$result = mysqli_query ( $mysqli , $query );

/* fetch a single value from the second column */
while ( $Name = mysqli_fetch_column ( $result , 1 )) printf ( «%s\n» , $Name );
>

The above examples will output something similar to:

Rafah Nablus Jabaliya Hebron Khan Yunis

See Also

• mysqli_fetch_all() — Fetch all result rows as an associative array, a numeric array, or both
• mysqli_fetch_array() — Fetch the next row of a result set as an associative, a numeric array, or both
• mysqli_fetch_assoc() — Fetch the next row of a result set as an associative array
• mysqli_fetch_object() — Fetch the next row of a result set as an object
• mysqli_fetch_row() — Fetch the next row of a result set as an enumerated array
• mysqli_data_seek() — Adjusts the result pointer to an arbitrary row in the result

User Contributed Notes

Источник

PHP RFC: Add fetch_column method to mysqli

There are 3 methods in mysqli that return arrays from result set, one that returns stdClass object, and one that returns an array of arrays. However, mysqli doesn’t have a method to return a scalar value.

Proposal

As of now, there is no easy way to fetch a scalar value in mysqli. If the SQL statement can return no rows then the simplest way to fetch scalars is to fetch an array and using the null-coalescing operator default the value to false. This is due to all the fetch methods returning false if no more rows are present in the result set.

$result = $mysqli->query('SELECT username FROM users WHERE >); echo $result->fetch_row()[0] ?? false;

This RFC borrows the idea from PDO to add another method to mysqli_result class. The method would be called fetch_column to keep with the existing mysqli naming convention.

The above example can then be simplified to a single method call:

$result = $mysqli->query('SELECT username FROM users WHERE >); echo $result->fetch_column();

The new method will also accept an optional integer parameter to specify which column to fetch from the current row. The index is 0-based just like in PDO.

Just like with other fetch_* methods this one will also move the internal result pointer to the next row when called.

Difference between PDO and mysqli

There will be only two differences from PDO: the name of the method, and the fact that MySQL doesn’t have boolean types thus this method can never return a boolean. However, the method can still return false, which indicates that no row could be fetched from the result.

Источник