|
|
Posted by Dan Trainor on 08/22/05 00:35
Evan Priestley wrote:
> Dan,
>
> When you run the second query ("$query1"), its results overwrite the
> results of your first query ("$query0"). `mysql_fetch_assoc()' returns
> results from the _most recent_ query, so after the first iteration, the
> call to `mysql_fetch_assoc()' is returning results from `query1', not
> from `query0'.
>
> To solve this, retrieve all data from `query0' before iterating over
> it. Sample code in /painstakingly exact/ PHP which I have tested
> extensively:
>
> <?"php"
>
> run query0
>
> while( rows exist )
> $rows[] = get next row
>
> foreach $rows
> for i = 0; i >= 0; i-- {
> build query1
> run query1
> }
> ?>
>
> Excellent description of your problem, by the way; this one's tricky
> and definitely had me for a while when I first ran into it.
>
> Evan
>
> On Aug 21, 2005, at 7:24 AM, Dan Trainor wrote:
>
>> Hello, all -
>>
>> As a pet project of mine, I've decided to write a bit of code. This
>> is what I have, and it's not working as expected:
>>
>> if ($action == "prepareforupdate") {
>> @unlink("UPDATES/".$id."/9.jpg");
>> $query0 = "SELECT * FROM updates WHERE id=".$id."";
>> if (!$dbdata = mysql_query($query0)) {
>> echo "Can't run query: ".mysql_error();
>> die;
>> };
>>
>> for ($i = 9; $i >= 0; $i--) {
>> $j = $i - 1;
>>
>> echo "Getting ready to rename UPDATES/$id/$j.jpg to
>> UPDATES/$id/$i.jpg || <br />";
>>
>> @rename("UPDATES/$id/$j.jpg","UPDATES/$id/$i.jpg");
>>
>> $returned = mysql_fetch_assoc($dbdata);
>>
>> $query1 = "UPDATE updates SET " . $i . "d = '"
>> .$returned[$j."d"]. "' WHERE id='" . $id . "'";
>>
>> if (!mysql_query($query1)) {
>> echo "MySQL Error: ".mysql_error();
>> };
>>
>> }
>> }
>>
>>
>> I have a database in the following format. Let's say that "d" stands
>> for date, and "t" stands for times, and I have ten days' worth of
>> archives going on:
>>
>> |id|1d|1t|2d|2t|3d|3t|.......|8d|8t|9d|9t|
>>
>> I'm trying to "move" the contents of the previous field to the
>> logical next field, so the value of field "8d" becomes "9d", the
>> value of "7t" becomes the value of "8t", and so on.
>>
>> The problem that I'm having here is that only the first iteration of
>> the for loop work properly. Everything after that, is not being
>> pulled up properly, I suspect. This is what MySQL shows:
>>
>> 174 Query SELECT * FROM updates WHERE id=5
>> 174 Query UPDATE updates SET 9d = '2005-08-21' WHERE id='5'
>> 174 Query UPDATE updates SET 8d = '' WHERE id='5'
>> ....
>> 174 Query UPDATE updates SET 1d = '' WHERE id='5'
>> 174 Query UPDATE updates SET 0d = '' WHERE id='5'
>>
>> So all in all, I think I might be a bit out of my league here, but I
>> am eager to learn. I think of this as more of a dynamic approach to
>> a situation that I'm trying to adapt to.
>>
>> As always, any feedback, or flames for that matter, would be greatly
>> appreciated.
>>
>> Thanks
>> -dant
>>
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>
>
>
Hello, Evan, and thanks for your reply -
I was under the impression that, since I specified $dbdata as the
argument to mysql_fetch_assoc(), that this would work exclusively with
query0. I'm a bit confused as to how query0's return is getting
overwritten, since I've tried to keep query0 and query1 seperate.
I'm going to drop this whole script, since I've found a much better way
of keeping records of updates involving one big-ass database, but I'd at
least like to know what I did wrong in this instance, so that I at least
learn something. Your explanation was very helpful, but please clarify,
if you would be so kind.
Thanks
-dant
Navigation:
[Reply to this message]
|