Page 2 of 3
Re: while loop question
Posted: Sun Feb 28, 2010 5:28 pm
by mikosiko
Dave... maybe my brain is f a r t i n g too
fbachofner:
Don't want to mess with your head...

but other way to do the same without an additional array is using the clause LIMIT in your select... but I still thinking that using an array is better if you want to use the same data several times.
Code: Select all
ExhibitsQuery = "SELECT website_ID_PK, exhibit_ID_FK, exhibit_ID_PK, Exhibit_Title, Artist_Name, artist_ID_FK, DATE_FORMAT(Exhibit_Date_Start, '%d %M, %Y') as Exhibit_Date_Start, DATE_FORMAT(Exhibit_Date_End, '%d %M, %Y') as Exhibit_Date_End, DATE_FORMAT(Reception_Date, '%W, %d %M, %Y') as Reception_Date, Reception_Time
FROM website_config, exhibit
WHERE website_ID_PK BETWEEN 3 AND 6 AND exhibit_ID_PK = exhibit_ID_FK
ORDER BY website_ID_PK ASC";
$ExhibitsQuery_result = mysql_query($ExhibitsQuery.' LIMIT 0,2')
<<< do what ever you want with firts 2 rows >>>
$ExhibitsQuery_result_2 = mysql_query($ExhibitsQuery.' LIMIT 2,2')
<<< do what ever you want with rows 3 and 4 >>>
Re: while loop question
Posted: Sun Feb 28, 2010 5:31 pm
by fbachofner
mikosiko wrote:this happens for the same reason that I did explain in the post to Dave.... you already extracted the first record in your first sentence $row=etc..etc... therefore the first while will show records 2 and 3... but notice also the usage or POST-INCREMENT for the var $count ($count++).. meaning... that you will get 3 records instead of 2 in your while.... to return only 2 records PRE-INCREMENT should be used (++$count)
Hi Miko:
Thanks for your ongoing help. Try to imagine, for a moment, not knowing much about programming and reread your post.
I can conceptually understand that $count INCREMENTS each time PHP loops through the while loop [until $count=1 since it is limited to $count<2]
. . . but now you are throwing a *seemingly* completely different construct at me [i.e. (++$count) ]
Dave didn't have any "++" anywhere in the suggested code until later in the thread and that's not in context. Now my head is spinning!
Where would this go? Or did Dave in his example NOT mean "&& ($count<1)" but rather "++ ($count<1)" ?! That wouldn't make semantic sense to me (granted I barely understand PHP).
Thanks again!
Re: while loop question
Posted: Sun Feb 28, 2010 5:38 pm
by fbachofner
Hi Dave:
davex wrote:*Update: just noticed the first mysql_fetch_assoc() in the original query post and mentioned it below which you posted while I was writing mikosiko, thanks for the heads up*
Hmm... do you still have that first debug mysql_fetch_assoc() in the first part of your code? That will pull down the first row. As for the $count I can't see how/why it would return 3 - $count=0 first iteration, $count=1 second interation at the end of which $count=2 and the while loop should quit.
Frustrating . . . I thought that mysql_fetch_assoc() loads ALL the data into the array right away for future use . . . It reads only the first record and then more as data is demanded of it?
Thanks for any clarification you could give on this topic.
davex wrote:Then load everything into a single array:
Code: Select all
$data_array=array();
while ($row=mysql_fetch_assoc($ExhibitsQuery_result))
{
$data_array[]=$row;
}
mysql_free_result($ExhibitsQuery_result);
// Now for debug purposes let's check:
echo "Number of rows: ".count($data_array)."<br />"; // should be 4 if your query has worked as intended
// We can also optionally output the contents of the array at this point if it hasn't to see what's up with the data:
// print_r($data_array);
Uncommenting these lines shows everything works great! But then:
davex wrote:Ok so now we should have $data_array containing 4 elements each an associative array of the relevant row from the database.
The following code will output the first two elements/rows:
Code: Select all
for ($i=0; $i<2; $i++)
{
echo '<div class="show_list">';
echo '<B>' . $data_array[$i]['Artist_Name'] .'</B><br/>';
echo '<i>' . $data_array[$i]['Exhibit_Title'] .'</i><br/>';
echo '<a href=Artist_Details.php?artist_ID_PK=' . $data_array[$i]['artist_ID_FK'] . '>Preview Work</a>' .'<br/>';
echo '<a href=Press_Release.php?exhibit_ID_PK=' . $data_array[$i]['exhibit_ID_PK'] . '>Press Release</a>' .'<br/>';
echo '</div>';
}
Somehow the $data_array variable has not taken the field names. WAIT, it seems I somehow missed the [$i]'s I will go back to try that!
Thanks for all your help!
Re: while loop question
Posted: Sun Feb 28, 2010 5:42 pm
by fbachofner
Hi Dave:
fbachofner wrote:Somehow the $data_array variable has not taken the field names. WAIT, it seems I somehow missed the [$i]'s I will go back to try that!
Can't believe I missed that.
Yep, obviously the [$i] needs to be there in each echoed element. Makes sense -- you have to tell what the count is to pull the right element out of the array!
Starting to make sense . . .
Thanks again!
Re: while loop question
Posted: Sun Feb 28, 2010 5:48 pm
by mikosiko
fbachofner wrote:
Thanks for your ongoing help. Try to imagine, for a moment, not knowing much about programming and reread your post.
Thanks again!
I understand completely your point and I apologize for not be clear enough...
Also disregard my comment about the pre-post increment... that didn't apply in this case... but just for your knowledge ++$count (++ in the front) is called pre-increment and $count++ is called post-increment... the difference?... read it here:
http://php.net/manual/en/language.opera ... rement.php
hope this help
Re: while loop question
Posted: Sun Feb 28, 2010 5:49 pm
by fbachofner
Hi Miko:
mikosiko wrote:Dave... maybe my brain is f a r t i n g too
No way, you guys ROCK!
mikosiko wrote:fbachofner:
Don't want to mess with your head...

but other way to do the same without an additional array is using the clause LIMIT in your select... but I still thinking that using an array is better if you want to use the same data several times.
<snip>
Code: Select all
$ExhibitsQuery_result = mysql_query($ExhibitsQuery.' LIMIT 0,2')
<<< do what ever you want with firts 2 rows >>>
$ExhibitsQuery_result_2 = mysql_query($ExhibitsQuery.' LIMIT 2,2')
<<< do what ever you want with rows 3 and 4 >>>
On this page I need the data only once, so LIMIT could be perfect.
HOWEVER, the reason I started this topic is that I only want one query of the database. If I use LIMIT as you have suggested, doesn't this effectively generate two queries (or is this like an "internal" PHP query of the $ExhibitsQuery_result)?
This would be really helpful to understand. I would always like the least amount of stress on the database server since that connection is relatively "expensive" (in terms of latency, etc.).
Thanks!
Re: while loop question
Posted: Sun Feb 28, 2010 5:53 pm
by mikosiko
fbachofner wrote:Hi Miko:
mikosiko wrote:Dave... maybe my brain is f a r t i n g too
No way, you guys ROCK!
mikosiko wrote:fbachofner:
Don't want to mess with your head...

but other way to do the same without an additional array is using the clause LIMIT in your select... but I still thinking that using an array is better if you want to use the same data several times.
<snip>
Code: Select all
$ExhibitsQuery_result = mysql_query($ExhibitsQuery.' LIMIT 0,2')
<<< do what ever you want with firts 2 rows >>>
$ExhibitsQuery_result_2 = mysql_query($ExhibitsQuery.' LIMIT 2,2')
<<< do what ever you want with rows 3 and 4 >>>
On this page I need the data only once, so LIMIT could be perfect.
HOWEVER, the reason I started this topic is that I only want one query of the database. If I use LIMIT as you have suggested, doesn't this effectively generate two queries (or is this like an "internal" PHP query of the $ExhibitsQuery_result)?
This would be really helpful to understand. I would always like the least amount of stress on the database server since that connection is relatively "expensive" (in terms of latency, etc.).
Thanks!
yes.. 2 queries.... but that should no put much stress in you DB or connection... but if that is an issue... use the option with array.
Re: while loop question
Posted: Sun Feb 28, 2010 5:58 pm
by fbachofner
Hi Miko and Dave:
mikosiko wrote:I understand completely your point and I apologize for not be clear enough...
Also disregard my comment about the pre-post increment... that didn't apply in this case... but just for your knowledge ++$count (++ in the front) is called pre-increment and $count++ is called post-increment... the difference?... read it here:
http://php.net/manual/en/language.opera ... rement.php
MEA CULPA -- My fault!
I reread Dave's 1st post just now. Somehow I missed seeing AND missed copying line 18: " $count++; " I suppose I thought @count would increment (as a default) without the ++ operator which I hadn't seen.
Dave, this probably also explains why 3 records were returned!
I'm going to retry the first example to see whether it works (omitting the mistake of my first pulled row).
Thanks!
Felix
Re: while loop question
Posted: Sun Feb 28, 2010 6:07 pm
by fbachofner
davex wrote:I think maybe my $count++ line was missed out at the end of the while loop - I should have just done while ( $count++<2 ).
Now I'm laughing at myself. YES, your $count++ was missed ENTIRELY! (see my post above)
I've redone your first example and it works, although in the second "pass" I'm getting only the 4th record (the 3rd is skipped)
Now I have to go back to read Miko's anticipation of that issue . . .
Thanks again!
Re: while loop question
Posted: Sun Feb 28, 2010 6:53 pm
by fbachofner
Hi Miko:
mikosiko wrote:Dave... only problem with your approach is that in the first while loop the mysql_fetch_assoc will fetch a row and moves the internal data pointer ahead, therefore the second while loop will miss the record # 3 and will show record 4 only (assuming table have only 4 records).
So short of putting everything into an array or using LIMIT (the two other options we've discussed), would there be a solution analogous to Dave's first solution which would NOT miss record #3?
It seems there should be a way to increment the counter so that the next record is not retrieved before the second while loop.
I have now tried BOTH pre and post-increment and the 3rd record is missed either way . . .
Thanks.
Felix
Re: while loop question
Posted: Sun Feb 28, 2010 11:53 pm
by davex
Hi,
Just remove the first mysql_fetch_assoc() immediately after the query.
Then the first while loop will fetch rows 0 and 1 (the first two)
The second while loop will fetch rows 2 and 3 (the second two)
As for your query about mysql_fetch_assoc() this only fetches a single row of the database so you need to call it once for every row you wish to get.
Cheers,
Dave.
Re: while loop question
Posted: Mon Mar 01, 2010 1:53 pm
by fbachofner
Hi Dave:
davex wrote:Just remove the first mysql_fetch_assoc() immediately after the query.
Then the first while loop will fetch rows 0 and 1 (the first two)
The second while loop will fetch rows 2 and 3 (the second two)
I've done that. The result no matter what I try (pre and post-increment) is that records 1+2 are returned for the first group and record 4 ONLY for the second group.
Miko made it sound like this third record was going to "disappear" anyway as the mysql_fetch_assoc() executes one more time before getting to the count condition. Perhaps there is a different way of writing the while clause?
would
Code: Select all
while ( ($++count<2) && ($row=mysql_fetch_assoc($ExhibitsQuery_result)))
work? It seems this might "evaluate" the "count" before pulling another row. Since the count would exceed the limit on the third pass, maybe mysql_fech_assoc would not execute?
davex wrote:As for your query about mysql_fetch_assoc() this only fetches a single row of the database so you need to call it once for every row you wish to get.
I guess I'll need to "make peace" with this concept. I thought a construct like
Code: Select all
$ExhibitsQuery_result = mysql_query($ExhibitsQuery)
was what "fetched" the data and that that fetch_assoc put it ALL into an array.
Thanks again!
Re: while loop question
Posted: Mon Mar 01, 2010 2:32 pm
by davex
Hi,
Ok I think I was a bit of an idiot - change BOTH while loops so they have:
Code: Select all
$count=0;
while ( ($count++<2) && ($row=mysql_fetch_assoc($ExhibitsQuery_result)) )
{
// do the output
}
This should load rows 1 and 2 in the first while then 3 and 4 in the second.
Absolutely my mistake in the order of the while - of course the $row would work as it was on the left side of the &&.
Try that.
If it doesn't work post the code again in entirety as it's gone through many many changes.
Cheers,
Dave.
Re: while loop question
Posted: Mon Mar 01, 2010 7:08 pm
by mikosiko
fbachofner wrote:
Miko made it sound like this third record was going to "disappear" anyway as the mysql_fetch_assoc() executes one more time before getting to the count condition.
Thanks again!
Felix... what I did say was simply that every time you make a call to mysql_fetch_assoc() it "read" one record from your recordset and
move the internal pointer to your next record... in your code you were doing an initial call to mysql_fetch_record, therefore your while loop will not start in the first record but in the second.
What I do suggest you is to take a look to the code that Dave gave to you several posts ago using an array (as I did suggest in my first post)... that is the simples way to solve (no the only one)... your solution is there... anything else is going in circles... why you don't take that code.. implement it and ask anything that you do not understand there?
will be easy...

Miko
Re: while loop question
Posted: Wed Mar 03, 2010 1:43 pm
by fbachofner
Hi Dave:
davex wrote:Try that.
I did, as I anticipated in the immediately preceding post.

Reversing the order of $count and $row so that $count comes FIRST works like a charm.
I came up with that idea when I realized that as a scripting language PHP almost certainly evaluates everything in order, left-to-right. Simple, right?
Anyway, it worked! Moreover, I discovered the incrementer (i.e. the ++) can be put elsewhere in the code as you suggested in your first example.
I should add, that this reversion (to your original suggestion) was only for learning. It is clear that adding the query results into an array is the more powerful solution and that approach is what the final code will use.
This has been a great(!) learning experience and I would like to thank you and Miko profusely for all your insights! When next you find yourselves in Monterey, CA, give me a call and I'll invite you to dinner!
Felix