whats with all the quirks I am finding? Obviously the

ASP

  methodology and the

PHP

  methodology are less than compatible...

stripped down to the junk that does it. should only update 5 rows, but oh no, its gotta update like 30 (actually its random). And its all because of the sleep(60).
I assume the connection is resetting, but I don't get why it wouldn't just blow up? Why does it just go ahead and start processing more rows in the database?!?!

Dude I don't understand what you're trying to accomplish... you've put a re-query inside of a "while you're getting rows" loop... this code is guaranteed to fubar you nasty.

If what you are trying to do is re-query for 5 more every 60 seconds, then your loop should look more like:

while (my master work statement is true)
{
mysql_query(for the 5 rows)
while(mysql_fetch_object/assoc/row)
{
do code
do code
}
sleep 60 seconds
}

or something like that... gotta run for the night, more tomorrow. In case you're confused, you only do the query once... you fetch until it comes back false, then you know you've exhausted the set returned by the query.

/p

<edit>
Looking a second time it's clear... you're using the same database connection that should be returning rows to do the update. You'll need two database connections to do this, or the way I do it is collect my 5 rows into an array, then do my 5 updates - that's lighter and faster. You can't use a database connection to do two things at once... if you're mid-query and returning rows, you can't execute an update with it, it'll fish your dataset cursor.
</edit>

This is quite interesting - thats something that I would have attempted myself. The only reason I can see for it not working, is that you're updating the same data that could be stored in your result set, which I guess could lead to problems - although I'm not sure this explains the random results.

I'm suprised the DB connection can't be reused, as ultimately the query has taken place and you're just looping a resultset resource which I'd have thought was already dragged into memory within the while statement.

DM

The query function returns a handle to a MySQL result set, aka a cursor. As you fetch, you are literally requesting another row from that result set. This makes sense because if you have a result set of 10MM rows you would not want to wait for the result set to all DL before you could start processing. This behavior is the same as Oracle's.

If you retask <your connection> to the db, you are releasing the result set and all that would be left is a cached random mess of the original query. If you choose to update while connected, then the best way is multiple DB connections - otherwise, as I mentioned and if you know your result set is going to be small enough, the lightest way to go is to grab all rows into an array and then act on them.

(It's all much clearer when I am awake  )

thats what I figured, and i also had worked out that you would probably say that... so...


it seeems to be the sleep(30).
obviously this is overly simplified, and you can guess what I am actually doing, but this oversimplification has the same problem. So this is the barebones example of what is going on.

This new version dumps the results into an array, then does the processing.

I understand what the issue is when I run a loop inside a loop, processing records from a record set, while it is "open", updating those records. It causes the original query to become invalid, when the next fetch is run, so it rebuilds it, and gets another 5 rows. The reason its random is because the script is timing out, and sometimes the queries run fast, others it runs slow, resulting in different numbers of records being processed.

DUMB

But i understand.

ASP

  was sheltering me from this via ADO. I could open a recordset and just walk around in it, willy nilly, making changes to it while doing so. Each record would commit the changes once i did a .update, but I would still have the same records in the recordset, regardless of the changes I make.

In

php

 /mysql, you apparently don't actually grab a whole recordset. you only grab the record for which your current cursor is located. So when you do a mysql_fetch_assoc, it increments the cursor counter by 1, and reruns the query from scratch, which means that if you have changed any of the criteria of the records from the prior result set which causes them to no longer be included in the result, you no longer get that record, and as such, you get a brand new one in this limit 5 scenario.

So you mean to tell me mysql_fetch_assoc runs the query 5 times against the database when I do
select * from table limit 5
while ($row = mysql_fetch_assoc($rs))
{
  echo 'wtf<br>';
}

I get it, makes sense. dumb, but makes sense.
So then answer me this sports fans...
Why the hell does it do it still when I have moved on, using a single loop to dump it all into an array. The new example, does just this. should process 5, but it instead processes about 15 give  or take.

Some quickies:

• No latency on a row variable - it is a straight-ahead array reference that will be killed when you reuse it


• The sleep 30 is probably a problem because of MySQL thinking you have died. I believe that the TTL parameter is setable somewhere, but if you're putting this stuff on multiple servers where you don't have access to the MySQL configs then this would become problematic over time.

As an efficiency measure, you could do your loop as tightly as this:

$set = mysql_query("select phrase, phraseid from phrases limit 5");
while ($phrases[] = mysql_fetch_array($set));

and the phrases array will have name=>value for each of the 5 rows.


Doesn't run the query 5 times... just like Oracle, there is a temp set created for you (it's actually a temp table of pointers to records, but it is under the hood) and as you move the cursor you retrieve another record based on the pointer... so the query is done once and the cursor is bound to your connection to MySQL... when you die, the cursor becomes invalid and the set goes away. It's actually wicked fast.



If you are receiving more than 5 rows from a query where you explicitly state you want 5 then I am clueless, unless you're getting the entire table and there's some way to inhibit/unconfigure the LIMIT directive in MySQL which would be fantastically stupid.

Where are you getting the "15ish?"

NBs - new posts up, as we discussed in

PHP

  repository and

PHP

  discussions.

or maybe not... LOL
still doing it!?!?!? dammit im gonna test more.

<>per our offline conversation.

the problem seems to be the connection to the database timing out, but

PHP

  continuing along its merry way. What must be happening is that

php

 , after 30 seconds, tries to do the next record, but Mysql is like "WTF are you talking about?"

PHP

  must respond, "this recordset: select * from table limit 5" and mysql returns that queryset, which of course is now an updated recordset. Thats why i was getting an increase in processed records.

this is purely a "i used to be an

ASP

  guy so i get my head stuck in the stairway railing" kind of thing.

again. move along, nothing to see here. other than how not to do something.
The solution is to open and close the connection each pass. Close before the sleep, and open when you loop again.