mysql_fetch - how to fetch table multiple times

[SteveL714]

I have a routine where I'd like to use elements of one table multiple times. Is there any way to reposition a table back to the beginning so I can use MySQL_fetch_assoc on the table again?

Any help would be appreciated.

Steve

 

[ChrisHirst]

MySQL is not Access or MSSQL, unless you set your query to start at a particular row (LIMIT offset, rowCount) it will return all the rows possible and PHP doesn't use recordsets or cursors that need to be 'moved' or reset before reading the records. That's a M$ Windows only trick.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[ChrisHirst]

However, .....


Why do you need to fetch the table(s) multiple times??

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[vacunita]

Generally if you need to use the results more than once (which would be rare), you would simply dump all your results into an array on your first iteration through the result set.

Then you can access the array whenever you want.

However you can use the mysql_row_seek() function to position the pointer back at the beginning of the result set. mysql_row_seek takes an integer value for the position of the row you want. So setting it to 0 will place the pointer back to the beginning and the next call to mysql_fetch_whatever will start from there.

However Chris makes an excellent point, why would you want to do this?

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[SteveL714]

Thanks for the responses. I guess I might not have phrased my problem properly. I need to run thru the table multiple times - Being a newcomer to MySQL I'm not sure if 'fetch' is the correct term for that, but that's the function I found that lets me move thru the table reading records.

Chris, you're right - I know how to do that in MSSQL, and I was hoping that MySQL had a counterpart. The result set contains a list of variable column headings for use with a second result set. I do like Phil's idea of capturing the data in an array on the first pass and then running thru that multiple times. I think that's probably the way that I'll go. If I have any further issues I'll let you know.

Thanks again.

Steve

 

[vacunita]

As I said you can use mysql_row_seek(0); to reset the pointer. The next call to mysql_fetch_assoc() will then start from the first row.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[ChrisHirst]

Use fetch-assoc, which returns a row set as a two dimensional associative array. (similar to obj.GetRows() in ADO/DAO)

Once you have that there is no need to requery the table, unless of course you are changing the query. With ADO/DAO you are moving through a recordset that needs to have a cursor type and location specified that determines how you can move through the records, with MySQL/MySQLi in PHP, the data is in an array that you can access any way you want without needing keep track of where the 'cursor' is.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[vacunita]

with MySQL/MySQLi in PHP, the data is in an array that you can access any way you want without needing keep track of where the 'cursor' is.

Not quite, MySQL never returns an array. It return a resource.

A resource is a special variable, holding a reference to an external resource. Resources are created and used by special functions.

The fetch_assoc or the other fetch functions are specifically there to obtain the rows of the result as arrays. But the actual value returned by mysql_query is neither an array nor a directly accessible object in its own right.

mysqli returns a mysqli_result object with implemented methods and functions to acquire the returned rows as arrays.

For MYSQL executing fetch_assoc and fetch_array does in fact move a pointer in the resource one row ahead. Which is why when you continue to call it you get subsequent rows instead of the same one.

Using mysql_row_seek relocates the pointer to whichever row you want.





----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[SteveL714]

When I tried using mysql_row_seek() I got an error message about an undefined function. From the online PHP documentation it appears that version 5.5.0 has deprecated a number of procedural style commands to the new mysqli_ construct. It looks like I've got a lot of reengineering to do. Sigh!

Steve

 

[ChrisHirst]

Not quite, MySQL never returns an array. It return a resource.

No, fetch_assoc which is what I was referring to IS an associative array

mysqli_result::fetch_assoc -- mysqli_fetch_assoc — Fetch a result row as an associative array

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum