2

I'm learning currently php/mysql and I'm confused about this bit.

After some heads scratching I have figured out that mysql_fetch_array remembers which row it last accessed and accesses the next one. (I was originally trying to work out how the code was communicating this to it in example code)

so for database:

parent | job
-------+-------------
mom    | receptionist
-------+-------------
dad    | taxi driver

the code

mysql_fetch_array($result)[job]

returns 'receptionist' the first time and 'taxi driver' the second.

  • Where/how is it keeping track of this?
  • What happens if I don't want to access them in order?

thanks

Improve this question

7 Answers 7

Reset to default
2
  • internal implementation in PHP. Don't try to figure it out ;)
  • if you want a different order, then specify it in your database query.
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thanks. It's a bit difficult working out what to try and figure out and what to just take as a given.
Yes, I know. Back when I was learning PHP, I initially found the database data fetching somewhat unintuitive and inflexible too ;). However, it's just a first impression, you'll see that after you've got used to working with it :).
2

Where/how is it keeping track of this?

The mySQL server has an internal result pointer. In some databases / wrappers / libraries you can rewind that pointer, but as far as I know, this is not possible in the mysql_* library of functions.

What happens if I don't want to access them in order?

You have to access them in some order. The only alternative to that is to fetch all rows into an array: Then you can access each row randomly.

If you want to change the order of records, do that in the query using the ORDER clause.

Some database wrappers like PDO have a fetchAll() method that does exactly that. For large result sets, this can be memory intensive and break the script's memory limit, which is why it's usually not done this way.

Improve this answer

6 Comments

I seem to recall that the newer version libraries (PDO/MySQLi) use a result pointer on the server, while the old mysql library pulls it all over straight away. Or possibly that was just the way that the old mysql_num_rows works.
Thanks. I'm thinking I should read the results into a 2 dimensional array with this function, then access them as I want. Is there anything wrong with this approach?
@nethy there's nothing wrong with it as such, no. It can be memory intensive with large queries but if that is not a problem (and specifically targeting the rows you need directly in the query is not an option), why not.
@nethy, perhaps if you explained how you wanted to use the data we could better direct you. To me, reading into an array seems like extra work; there's nothing really wrong with it, but is there any point to doing it?
Honestly, I'm at an early stage in learning to program. I'm completely new to mysql & databases. I think the problem is I'm thinking in arrays and globals and I just got confused that this works differently. I'm building an app for practice (a todo list app) and I want to grab the data from a table and use different parts of it in different places. The way I'm approaching it is reading the whole table (at this point there is only one user) into the array and basically querying the array.
|
1

There is another way to attack this question.

If you want to know how YOU TOO can make functions that do what this one does. Here is how:

<?php
function count_off()
{
static $count = 1;
echo $count++;
}

count_off();
count_off();
count_off();
count_off();
count_off();
?>

the above will output 12345

I should mention. You shouldn't do this without a very good reason. It is SUPER hard to trace when debugging.

Improve this answer

2 Comments

Does mysql_fetch_array($result)[job] have a count_off like component to it. Is it possible to interact with it somehow (eg change count directly)?
No, it doesn't. From what I understand that is part of mysql itself. (see the other answers) I was just giving you some guidance in-case you wanted to know this so you could do the same in your own functions.
0

If you want to access them in a different order, use an ORDER BY clause in your SQL to change the order that the results are retrieved from the database.

Improve this answer

2 Comments

So.. If (for example) I want a list of parents, then I want the first parent's job, I should use two queries?
No, you would SELECT * FROM parents and in each row you would already have the parent and the job. thirtydot's example shows how you can access these. You could read it into an array if you really wanted to; but that's just more work, it's already right there.
0

The result of mysql_fetch_array is always the next result/row of the query (first the first row off course) Intern it will keep a pointer how for it has fetched.

If you want to get them in an alternate order, you have to define it in the query. Like said the result will always be in the order specified by the query (implicit or explicit)

Improve this answer

Comments

0

If you wrote typical looking code like this:

$result = mysql_query("SELECT parent, job FROM table");

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo $row['parent'] . ' - ' . $row['job'];
}

Each time mysql_fetch_array() is called, if there is another row in the result, it will return the row as an associative array into the $row variable. Otherwise, it will return false, and the execution of the while loop will end.

Also, because you didn't specify an ORDER BY clause, it defaults to returning rows in the order they were inserted into the table.

Improve this answer

2 Comments

The default ordering might as well be random; it's not just the order inserted, but possibly also the last time updated, depending on the table engine in MySQL.
I was thinking of MyISAM, where I've subjectively seen it pretty much stick to insertion order.
0

The mysql_fetch_array() function grabs one row from the database, in the order that MySQL returns it from the query you gave.

To obtain all the rows, you can put the function in a loop.

while($row = mysql_fetch_array($result)) {
    echo $row["job"];
}

This will output:

receptionist
taxi driver

You can change the order by using the sql term order by, which can alphabetically or numerically order your results by a certain column

select * from parent order by job

The above query will order the results alphabetically by the parent job field (results closer to A will come first in the mysql_fetch_*

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.