Php mysqli fetch column

Класс mysqli_result

Представляет результирующий набор, полученный из запроса в базу данных.

Обзор классов

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

Свойства

Сохраняет буферизованный или небуферизованный результат в виде целого числа ( int ) ( MYSQLI_STORE_RESULT или MYSQLI_USE_RESULT соответственно).

Список изменений

Версия	Описание
8.0.0	Класс mysqli_result теперь реализует интерфейс IteratorAggregate . Ранее вместо него был реализован интерфейс Traversable .

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.

Источник

PHP 8.1: MySQLi: fetch_column function

Another suggestion made by Kamil Tekiela was included in the PHP 8.1 release.
Starting with this release, one more method has become available in the mysqli class — the fetch_column() method previously implemented in the PDO library. Lets create simple table for our tests:

create table persons ( id int primary key, name varchar(64), age tinyint ); insert into persons values (10, 'Alice', 18), (20, 'Bob', 22); +====+=======+=====+ | id | name | age | +====+=======+=====+ | 10 | Alice | 18 | | 20 | Bob | 22 | +----+-------+-----+ 

$result = $mysqli->query("SELECT name FROM persons WHERE echo $result->fetch_column(); 

You can test the above code here If the query returns several columns, the fetch_column(n) function can return the value from the column whose number is passed as a function parameter (column numbering starts from 0).

$result = $mysqli->query("SELECT name, age FROM persons WHERE echo $result->fetch_column(1); 

Just like with other fetch_* methods this one will also move the internal result pointer to the next row when called. So you can not use fetch_column(i) for loop thought one record columns.
Next example explain this error:

query("SELECT name, age FROM persons"); $name = $result->fetch_column(0); $age = $result->fetch_column(1); // Returns Alice name with Bob's age printf("Name: %s, age: %d", $name, $age); 

Источник

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

Источник