php mysqli fetch row
mysqli_result::fetch_array
Описание
Помимо хранения данных в числовых индексах массива результатов, функция также может сохранять данные в ассоциативных индексах, используя имена полей набора результатов в качестве ключей.
Замечание: Имена полей, возвращаемые этой функцией являются зависимыми от регистра.
Замечание: Эта функция устанавливает NULL-поля в значение null PHP.
Если два столбца или более имеют одинаковые имена, данные последнего столбца будут перезаписывать данные предыдущих. В таких ситуациях для доступа к данным всех столбцов с одинаковыми именами лучше пользоваться обычными массивами, индексированными номерами столбцов.
Список параметров
Возвращаемые значения
Примеры
Пример #1 Пример использования mysqli_result::fetch_array()
Результат выполнения данных примеров:
Смотрите также
User Contributed Notes 4 notes
Putting multiple rows into an array:
Note that the array returned contains only strings.
E.g. when a MySQL field is an INT you may expect the field to be returned as an integer, however all fields are simply returned as strings.
What this means: use double-equals not triple equals when comparing numbers.
Please note that under PHP 5.x there appears to be a globally defined variable MYSQL_ASSOC, MYSQL_NUM, or MYSQL_BOTH which is the equivalent of MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH. Yet under PHP 7.x this is NOT the case and will cause a failure in trying to retrieve the result set!
This can cause severe headaches when trying to find out why you are getting the error:
— mysqli_result::fetch_array() expects parameter 1 to be integer, string given in ‘Filename’ on line ‘XX’
Here is a function to return an associative array with multiple columns as keys to the array.
Given a simple mySQL table:
mysql> select * from city;
+—————-+—————-+——————+————+
| country | region | city | hemisphere |
+—————-+—————-+——————+————+
| South Africa | KwaZulu-Natal | Durban | South |
| South Africa | Gauteng | Johannesburg | South |
| South Africa | Gauteng | Tshwane | South |
| South Africa | KwaZulu-Natal | Pietermaritzburg | South |
| United Kingdom | Greater London | City of London | North |
| United Kingdom | Greater London | Wimbledon | North |
| United Kingdom | Lancashire | Liverpool | North |
| United Kingdom | Lancashire | Manchester | North |
+—————-+—————-+——————+————+
$assoc = array(); // The array we’re going to be returning
mysql_fetch_row
mysql_fetch_row — Обрабатывает ряд результата запроса и возвращает массив с числовыми индексами
Данное расширение устарело, начиная с версии PHP 5.5.0, и будет удалено в будущем. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API и соответствующий FAQ для получения более подробной информации. Альтернативы для данной функции:
Описание
Возвращает массив с числовыми индексами, содержащий данные обработанного ряда, и сдвигает внутренний указатель результата вперед.
Список параметров
Возвращаемые значения
mysql_fetch_row() обрабатывает один ряд результата, на который ссылается переданный указатель. Ряд возвращается в виде массива. Каждая колонка располагается в следующей ячейке массива, начиная с нулевого индекса
Примеры
Пример #1 Получение одного ряда с помощью mysql_fetch_row()
Примечания
Замечание: Эта функция устанавливает NULL-поля в значение NULL PHP.
Смотрите также
Коментарии
to print an array, simply use print_r(array name)
like this:
$myrow = mysql_fetch_row($result);
echo «»;
this will output the array in a readable form, with the index, too. Don’t forget the ‘pre’ tags or the output will be on a single line.
The following function to read all data out of a mysql-resultset, is may be faster than Rafaels solution:
$query = «SELECT * FROM table»;
$result = mysql_query($query);
$row = mysql_fetch_row($result);
sry 🙂 note now fixed:
Creates table from all db info:
require ‘prhlavicka.php’ ;
pis_hlavicku ( ‘Vypis článků’ );
mysql_fetch_array
mysql_fetch_array — Обрабатывает ряд результата запроса, возвращая ассоциативный массив, численный массив или оба
Данный модуль устарел, начиная с версии PHP 5.5.0, и удалён в PHP 7.0.0. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API. Альтернативы для данной функции:
Описание
Возвращает массив, соответствующий обработанному ряду результата запроса и сдвигает внутренний указатель данных вперёд.
Список параметров
Возвращаемые значения
Примеры
Пример #1 Запрос с применением псевдонимов для дублирующихся имён колонок
Пример #2 mysql_fetch_array() с MYSQL_NUM
$result = mysql_query ( «SELECT id, name FROM mytable» );
Пример #3 mysql_fetch_array() с MYSQL_ASSOC
$result = mysql_query ( «SELECT id, name FROM mytable» );
Пример #4 mysql_fetch_array() с MYSQL_BOTH
$result = mysql_query ( «SELECT id, name FROM mytable» );
Примечания
Замечание: Производительность
Замечание: Имена полей, возвращаемые этой функцией являются зависимыми от регистра.
Замечание: Эта функция устанавливает NULL-поля в значение null PHP.
Смотрите также
User Contributed Notes 31 notes
Benchmark on a table with 38567 rows:
mysql_fetch_array
MYSQL_BOTH: 6.01940000057 secs
MYSQL_NUM: 3.22173595428 secs
MYSQL_ASSOC: 3.92950594425 secs
mysql_fetch_row: 2.35096800327 secs
mysql_fetch_assoc: 2.92349803448 secs
As you can see, it’s twice as effecient to fetch either an array or a hash, rather than getting both. it’s even faster to use fetch_row rather than passing fetch_array MYSQL_NUM, or fetch_assoc rather than fetch_array MYSQL_ASSOC. Don’t fetch BOTH unless you really need them, and most of the time you don’t.
I have found a way to put all results from the select query in an array in one line.
// Read records
$result = mysql_query(«SELECT * FROM table;») or die(mysql_error());
// Delete last empty one
array_pop($array);
You need to delete the last one because this will always be empty.
By this you can easily read the entire table to an array and preserve the keys of the table columns. Very handy.
For all of you having problems accessing duplicated field names in queries with their table alias i have implemented the following quick solution:
The usage of this function will be pretty similar to calling mysql_fetch_array:
= mysql_query ( «select * from student s inner join contact c on c.fID = s.frContactID» );
Please be aware that by using this function, you have to access all fields with their alias name (e.g. s.Name, s.Birhtday) even if they are not duplicated.
If you have questions, just send me a mail.
Best regards,
Mehdi Haresi
die-webdesigner.at
Regarding duplicated field names in queries, I wanted some way to retrieve rows without having to use alias, so I wrote this class that returns rows as 2d-arrays
function fetch()
<
if ($row = mysql_fetch_row($this->results))
<
$drow = array();
The class is initialized with a mysql_query result:
I hope others find this useful as it has been to me.
Simple way to put table in an array.
//= Query ========================//
$sql = mysql_query ( «select * from table1» );
One of the most common mistakes that people make with this function, when using it multiple times in one script, is that they forget to use the mysql_data_seek() function to reset the internal data pointer.
When iterating through an array of MySQL results, e.g.
If, for some reason, you wanted to interate through the array a second time, perhaps grabbing a different piece of data from the same result set, you would have to make sure you call
for the problem with fields containing null values in an associated array, feel free to use this function. i’ve got no more problems with it, just drop it in your script:
$fval = mysql_fetch_row ($result);
if ($fval === false) return false;
Just another workaround for columns with duplicate names.
Modify your SQL to use the AS keyword.
Instead of:
$sql = «SELECT t1.cA, t2.cA FROM t1, t2 WHERE t1.cA = t2.cA»;
Try:
$sql = «SELECT t1.cA AS foo1, t2.cA AS foo2 FROM t1, t2 WHERE t1.cA = t2.cA»;
Here’s a quicker way to clone a record. Only 3 lines of code instead of 4. But the table must have an auto-incremented id.
I took the code from Tim and altered it. Props to Tim.
// set the auto-incremented id’s value to blank. If you forget this step, nothing will work because we can’t have two records with the same id
$entity [ «id» ] = «» ;
// if you want the auto-generated id of the new cloned record, do the following
$newid = mysql_insert_id ();
?>
There you go.
I did find ‘jb at stormvision’s’ code useful above, but instead of the number of rows you need the number of fields; otherwise you get an error.
So, it should read like the following:
$result=mysql_query(«select * from mydata order by ‘id'»)or die(‘died’);
$num_fields = mysql_num_fields($result);
$j=0;
$x=1;
while($row=mysql_fetch_array($result)) <
for($j=0;$j
In the note entered by Typer85, concerning the use of mysql_data_seek(), it should be noted that there are two parameters, both of which are required.
If you have already iterated through a result array (for instance, using mysql_fetch_array()), and have a need to start from the top, the proper syntax is:
EG:
mysql_data_seek($result,0)
(«0» represents the first record in the array.)
This will reset your result to the top of the array so that you can then re-process with
while($row = mysql_fetch_array($result)) or other array processing.
Little improvement to the previous function.
my main purpose was to show the fetched array into a table, showing the results side by side instead of underneath each other, and heres what I’ve come up with.
any kind of improvements on this would be awesome!
I ran into troubles with MySQL NULL values when I generated dynamic queries and then had to figure out whether my resultset contained a specific field.
First instict was to use isset() and is_null(), but these function will not behave as you probably expect.
I ended up using array_key_exists, as it was the only function that could tell me whether the key actually existed or not.
It’s kind of similar to Daogen, which was suggested in one of the comments above, but simpler and easier to use.
Php Object Generator generates the Php Classes for your Php Objects. It also provides the database class so you can focus on more important aspects of your project. Hope this helps.
As opposite of mysql_fetch_array:
Here is a suggestion to workaround the problem of NULL values:
// get associative array, with NULL values set
$record = mysql_fetch_array($queryID,MYSQL_ASSOC);
mob AT stag DOT ru has a nice function for getting simple arrays from MySQL but it has a serious bug. The MySQL link being set as an argument is NULL when no link is supplied meaning that you’re passing NULL to the mysql funcctions as a link, which is wrong. I am not using multitple connections so I removed the link and using the global link. If you want to support multiple links check to see if its set first.
Here’s a quick way to duplicate or clone a record to the same table using only 4 lines of code:
$id_max = mysql_result(mysql_query(«SELECT MAX(id) FROM table_name»),0,0) or die(«Could not execute query»);
$entity = mysql_fetch_array(mysql_query(«SELECT * FROM table.» WHERE ),MYSQL_ASSOC) or die(«Could not select original record»); // MYSQL_ASSOC forces a purely associative array and blocks twin key dupes, vitally, it brings the keys out so they can be used in line 4
$entity[«id»]=$id_max+1;
mysql_query(«INSERT INTO it_pages («.implode(«, «,array_keys($Entity)).») VALUES (‘».implode(«‘, ‘»,array_values($Entity)).»‘)»);
This is very useful when the following query is used:
Different versions of MySQL give different responses to this.
Therefore, it is better to use mysql_fetch_array() because the numeric references given my mysql_fetch_row() give very different results.
If you use implode() with the return value by mysql_fetch_array, if you use MYSQL_BOTH on parameter 2, the result is not really what you’re expecting.
For example :
my sql database contains «Amine, Sarah, Mohamed»;
the result is : Amine-Amine-Sarah-Sarah-Mohamed-Mohamed
and we expect just : Amine-Sarah-Mohamed
You must use MYSQL_NUM or MYSQL_ASSOC on parameter 2 to resolve the problem.
I never had so much trouble with null fields but it’s to my understanding that extract only works as expected when using an associative array only, which is the case with mysql_fetch_assoc() as used in the previous note.
However a mysql_fetch_array will return field values with both the numerical and associative keys, the numerical ones being those extract() can’t handle very well.
You can prevent that by calling mysql_fetch_array($result,MYSQL_ASSOC) which will return the same result as mysql_fetch_assoc and is extract() friendly.
I wrote some utility functions to improve usability and readability, and use them everywhere in my code. I suppose they can help.
Example use:
if(is_array($rows=mysql_fetch_all(«select * from sometable»,$MySQL))) <
//do something
>else <
if(!is_null($rows)) die(«Query failed!»);
>
If you think MySQL (or other) database
handling is difficult and requires lot’s of
code, I recommend that you try http://titaniclinux.net/daogen/
DaoGen is a program source code generator
that supports PHP and Java. It makes database
programming quick and easy. Generated sources
are released under GPL.
An example with mysql_fetch_array():
array ([0] => «John», [‘name’] => «John»)
Then you can access to the results:
If you perform a SELECT query which returns different columns with duplicate names, like this:
———
$sql_statement = «SELECT tbl1.colA, tbl2.colA FROM tbl1 LEFT JOIN tbl2 ON tbl1.colC = tbl2.colC»;
Moral of the story: You must use the numerical index on the result row arrays if column names are not unique, even if they come from different tables within a JOIN. This would render mysql_fetch_assoc() useless.
Please be advised that the resource result that you pass to this function can be thought of as being passed by reference because a resource is simply a pointer to a memory location.
Because of this, you can not loop through a resource result twice in the same script before resetting the pointer back to the start position.
—————-
// Assume We Already Queried Our Database.
// Loop Through Result Set.
// We looped through the resource result already so the
// the pointer is no longer pointing at any rows.
// If we decide to loop through the same resource result
// again, the function will always return false because it
// will assume there are no more rows.
// So the following code, if executed after the previous code
// segment will not work.
The only solution to this is to reset the pointer to make it point at the first row again before the second code segment, so now the complete code will look as follows:
—————-
// Assume We Already Queried Our Database.
// Loop Through Result Set.
Of course you would have to do extra checks to make sure that the number of rows in the result is not 0 or else mysql_data_seek itself will return false and an error will be raised.
Also please note that this applies to all functions that fetch result sets, including mysql_fetch_row, mysql_fetch_assos, and mysql_fetch_array.
mysqli_result::fetch_assoc
Описание
Замечание: Имена полей, возвращаемые этой функцией являются зависимыми от регистра.
Замечание: Эта функция устанавливает NULL-поля в значение null PHP.
Список параметров
Возвращаемые значения
Если два или более столбца в выборке имеют одинаковое название полей, то приоритет отдаётся последнему столбцу. Для доступа к другому столбцу с таким же именем вам необходимо произвести нумерованную выборку с помощью функцию mysqli_fetch_row() или добавить псевдонимы.
Примеры
Пример #1 Пример использования mysqli_result::fetch_assoc()
$query = «SELECT Name, CountryCode FROM City ORDER BY ID DESC» ;
$query = «SELECT Name, CountryCode FROM City ORDER BY ID DESC» ;
Результатом выполнения данных примеров будет что-то подобное:
Пример #2 Сравнение использования mysqli_result iterator и mysqli_result::fetch_assoc()
mysqli_result можно повторить с помощью foreach. Результирующий набор всегда будет повторяться с первой строки, независимо от текущей позиции.
$query = ‘SELECT Name, CountryCode FROM City ORDER BY ID DESC’ ;
Результатом выполнения данного примера будет что-то подобное:
Смотрите также
User Contributed Notes 5 notes
I often like to have my results sent elsewhere in the format of an array (although keep in mind that if you just plan on traversing through the array in another part of the script, this extra step is just a waste of time).
If you were used to using code like this:
The official example given here breaks a cardinal rule, and should be rectified.
. breaks the rule of «assignment in condition».
. is the correct syntax.
Conditional statements should always check for a boolean
Be careful when using fetch_assoc instead of fetch_row. If two columns of the result have the same column name, even if they are prefixed with different table names in the query, only one of them will be retained in the result. This is because the prefix is dropped (either by mysql or by this function)
For example if the query is
select p1.name, p2.name
from person p1, friend, person p2
where p1.id = friend.person1 and p2.id = friend.person2
the arrays returned by fetch_assoc will be of the form
and not (as expected)
/*it will exceed the first
id&user_name value that
you have use in the first fetch*/
mysql_fetch_object
mysql_fetch_object — Обрабатывает ряд результата запроса и возвращает объект
Данный модуль устарел, начиная с версии PHP 5.5.0, и удалён в PHP 7.0.0. Используйте вместо него MySQLi или PDO_MySQL. Смотрите также инструкцию MySQL: выбор API. Альтернативы для данной функции:
Описание
Возвращает объект со свойствами, соответствующими колонкам в обработанном ряду и сдвигает внутренний указатель результата вперёд.
Список параметров
Возвращаемые значения
Примеры
Пример #1 Пример использования mysql_fetch_object()
Пример #2 Пример использования mysql_fetch_object()
Примечания
Замечание: Производительность
В плане скорости эта функция аналогична mysql_fetch_array() и почти также быстра, как mysql_fetch_row() (разница незначительна).
Замечание: Имена полей, возвращаемые этой функцией являются зависимыми от регистра.
Замечание: Эта функция устанавливает NULL-поля в значение null PHP.
Смотрите также
User Contributed Notes 24 notes
When working with a stdClass object returned from a database query, specifically:
You may run into assignment problems if you have a field with a ‘.’ in the name.
e.g.: `user.id`
To remedy this situation, you can use curly braces and single quotes during assignment.
This little function stores the content of a mysql_fetch_object result into an object by using MySQL request field names as object parameters :
In reply to rodolphe dot bodeau at free dot fr :
So if you want to fetch a row in a class you can:
Be aware how you write code in your methods: in this case, classes are used for centralize the code and they are not
really safe because you can have an additional Information
( with a Join Query for example ) without methods to access to them.
So classes need get and set method generalized.
( for extra see PHP 5 Manual O’Rielly on the use of generalized methods get and set )
Since PHP 5.2.1 it seems like this function doesn’t like queries that return columns with empty names, like:
select » from `table`
PHP exits and mysql_error() does not return an error.
This method offers a nice way to fetch objects from databases. As Federico at Pomi dot net mentioned it doesn’t work native as the type of the object fetched isn’t the right one, but with a small typecast it works flawlessly.
$o = new MyClass;
$o->_load(1);
I found the above code to be buggy, not adding all the records to the array. This is the code I used instead:
in __construct() id: 2 go: second
outside ________ id: 2 go: 4845bd99ca2fd
It means that __construct() is invoked after filling fields with data from database, eg. it can be used to change strings into integers.
@Simon Paridon and others concerning SQL to php getting results via mysql_fetch_object:
Every query that would fail in a database frontend, such as MySQLs «Query Browser» and only will work by using the `-marks will probably give results hardly accessible in PHP especially if you have column names with «-» or » » in it.
Using the example of Simon Paridon: it is not possible to execute a query like:
SELECT id, user-id FROM unlucky_naming
SELECT id, `user-id` FROM unlucky_naming
so either be a bit wiser when naming the colums (e.g. user_id)
SELECT id, `user-id` AS user_id FROM unlucky_naming
(i have not tested it in PHP yet, but i guess this will fail as well, if you have a query like «SELECT `foo name` FROM `unlucky naming 2`»)
Somewhat down «amackenz at cs dot uml dot edu» mentioned to name sum, count etc. this may be a good hint for newbies: increase the speed of your php applications by using (my)sql native functions and save data transfer as well as processing time
In reviewing Eskil Kvalnes’s comments (04-Mar-2003 11:59
When using table joins in a query you obviously need to name all the fields to make it work right with mysql_fetch_object()) I was left asking and, as a newbie, the reason why I’m here. I have a 28 field table. Ran SELECT * with a LEFT JOIN, etc and it appears to have worked on my test server without issue.
On further reading, MYSQL.COM has the following:
* It is not allowed to use a column alias in a WHERE clause, because the column value may not yet be determined when the WHERE clause is executed. See section A.5.4 Problems with alias.
* The FROM table_references clause indicates the tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see section 6.4.1.1 JOIN Syntax. For each table specified, you may optionally specify an alias.
Aware of the fact there’s a difference between tables and fields there appears to be confusion here somewhere.
Be carefull:
the object returned will be a new/fresh object.
You can’t use this function to replace some attributes of an existing object keeping the old ones.
function print()
<
print($name.» «.$surname);
>
function get_from_db()
<
$res=query(«select name, surname from ppl where. limit 1»);
$this=mysql-fetch-object($res);
>
This is a very very elegant (and costless) way to fetch an enterie query to every single field name from a «wide» table:
A way saving time with the same result:
Some clarifications about previous notes concerning duplicate field names in a result set.
Consider the following relations:
TABLE_A(id, name)
TABLE_B(id, name, id_A)
Where TABLE_B.id_A references TABLE_A.id.
Now, if we join these tables like this: «SELECT * FROM TABLE_A, TABLE_B WHERE TABLE_A.id = TABLE_B.id_A», the result set looks like this: (id, name, id, name, id_A).
The behaviour of mysql_fetch_object on a result like this isn’t documented here, but it seems obvious that some data will be lost because of the duplicate field names.
I created a table, with 5 INT columns, and 1000 rows of random ints.
I did 100 selects:
SELECT * FROM bench. (mysql_fetch_object)
Query time: 5.40725040436
Fetching time: 16.2730708122 (avg: 1.32130565643E-5)
Total time: 21.6803212166
SELECT * FROM bench. (mysql_fetch_array)
Query time: 5.37693023682
Fetching time: 10.3851644993 (avg: 7.48886537552E-6)
Total time: 15.7620947361
SELECT * FROM bench. (mysql_fetch_assoc)
Query time: 5.345921278
Fetching time: 10.6170959473 (avg: 7.64049530029E-6)
Total time: 15.9630172253
«Note: Performance Speed-wise, the function is identical to mysql_fetch_array(), and almost as quick as mysql_fetch_row() (the difference is insignificant).»
And I am a penguin 🙂
@Jezze : This wil also work. Also works with static methods.