while loop question
Moderator: General Moderators
-
fbachofner
- Forum Newbie
- Posts: 18
- Joined: Sun Feb 28, 2010 6:29 am
while loop question
I am writing my first non-trivial PHP pages and having quite some success.
I am at a stumbling block, however. I have a query that returns exactly 4 rows (and will ALWAYS return 4 rows).
I need to display values from the first two rows, throw some other stuff onto the page and then display values from the last two rows.
This is simple to do with 2 queries and limiting the result set, but that's inelegant and probably not as fast.
can't I just do something similar to:
" while ($row['website_ID_PK'] < 3 ) { "
[clarification: 'website_ID_PK' is one of the fieldnames returned by the query and the construct $row['website_ID_PK'] returns appropriate data in other tests]
More info--
the query returns into a variable:
" $CurrentShowQuery_result = mysql_query($CurrentShowQuery); "
and if I use code like
" while ($row = mysql_fetch_assoc($CurrentShowQuery_result)) { "
I can easily get ALL the results . . . but I want just the first 2 and then later the last 2
Thanks in advance for your insights
I am at a stumbling block, however. I have a query that returns exactly 4 rows (and will ALWAYS return 4 rows).
I need to display values from the first two rows, throw some other stuff onto the page and then display values from the last two rows.
This is simple to do with 2 queries and limiting the result set, but that's inelegant and probably not as fast.
can't I just do something similar to:
" while ($row['website_ID_PK'] < 3 ) { "
[clarification: 'website_ID_PK' is one of the fieldnames returned by the query and the construct $row['website_ID_PK'] returns appropriate data in other tests]
More info--
the query returns into a variable:
" $CurrentShowQuery_result = mysql_query($CurrentShowQuery); "
and if I use code like
" while ($row = mysql_fetch_assoc($CurrentShowQuery_result)) { "
I can easily get ALL the results . . . but I want just the first 2 and then later the last 2
Thanks in advance for your insights
Re: while loop question
one solution:
load the records that you get in your while loop in an array first... after that you can use the array values and place them where you want.
Miko
load the records that you get in your while loop in an array first... after that you can use the array values and place them where you want.
Miko
-
fbachofner
- Forum Newbie
- Posts: 18
- Joined: Sun Feb 28, 2010 6:29 am
Re: while loop question
That's what I thought I was doingmikosiko wrote: load the records that you get in your while loop in an array first... after that you can use the array values and place them where you want.
" mysql_fetch_assoc " is data being pulled into a php array, right?
Here's the relevant annotated code:
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);
//temp testing line below
$row = mysql_fetch_assoc($ExhibitsQuery_result)
?>
later on the page where I am trying to display:
Code: Select all
Now exhibiting:<br/><br/>
<?php
//iterate through 2 rows and pull field values from each
// ORIGINAL: while ($row = mysql_fetch_assoc($ExhibitsQuery_result)) -- will return all results from Query
// NEW: see below - page stops working entirely -- wait, wait, wait, then timeout
while ($row['website_ID_PK'] <= 4 )
{
echo '<div class="show_list">';
echo '<B>' . $row['Artist_Name'] .'</B><br/>';
echo '<i>' . $row['Exhibit_Title'] .'</i><br/>';
echo '<a href=Artist_Details.php?artist_ID_PK=' . $row['artist_ID_FK'] . '>Preview Work</a>' .'<br/>';
echo '<a href=Press_Release.php?exhibit_ID_PK=' . $row['exhibit_ID_PK'] . '>Press Release</a>' .'<br/>';
echo '</div>';
//do some other stuff then
//something similar to
//while ($row['website_ID_PK'] > 5 )
Thanks for any insights!
Re: while loop question
Hi,
Looks to me like the loop is not loading more rows - the while will loop forever.
Why not try something like:
HTH,
Dave.
Looks to me like the loop is not loading more rows - the while will loop forever.
Why not try something like:
Code: Select all
Now exhibiting:<br/><br/>
<?php
//iterate through 2 rows and pull field values from each
// ORIGINAL: while ($row = mysql_fetch_assoc($ExhibitsQuery_result)) -- will return all results from Query
// NEW: see below - page stops working entirely -- wait, wait, wait, then timeout
// Assuming query line was $ExhibitsQuery_result=mysql_query() and has worked to return your 4 rows in the correct order
$count=0;
while ( ($row=mysql_fetch_assoc($ExhibitsQuery_result)) && ($count<2) )
{
echo '<div class="show_list">';
echo '<B>' . $row['Artist_Name'] .'</B><br/>';
echo '<i>' . $row['Exhibit_Title'] .'</i><br/>';
echo '<a href=Artist_Details.php?artist_ID_PK=' . $row['artist_ID_FK'] . '>Preview Work</a>' .'<br/>';
echo '<a href=Press_Release.php?exhibit_ID_PK=' . $row['exhibit_ID_PK'] . '>Press Release</a>' .'<br/>';
echo '</div>';
$count++;
} // will get the first two rows
//do some other stuff then
//reset $count=0 and do the exact same while loop - will fetch the next 2 rows
Dave.
Re: while loop question
Code: Select all
That's what I thought I was doing
" mysql_fetch_assoc " is data being pulled into a php array, right?this were the goal that you posted first:
The mysql_fetch_assoc effectively generate a recordset which coincidentally is an array... but what I'm suggesting you based in your goals is use that information to load a DIFFERENT array inside the while loop... in that way you can use the values loaded in the suggested array in ANY place in your code an not only inside your while loop... remember that every time your while loop is executed the values of your recordset are changed (a new different row every time)... right?"I need to display values from the first two rows, throw some other stuff onto the page and then display values from the last two rows"
Miko
-
fbachofner
- Forum Newbie
- Posts: 18
- Joined: Sun Feb 28, 2010 6:29 am
Re: while loop question
Hi Dave:davex wrote:Looks to me like the loop is not loading more rows - the while will loop forever.
Why not try something like:
<snip>
Thanks for the response.
Unfortunately,
Code: Select all
$count=0;
while ( ($row=mysql_fetch_assoc($ExhibitsQuery_result)) && ($count<2) )Even worse, for some reason this method results in the LAST three records being returned rather than the first three.
This is very puzzling to me.
Any more ideas? Thanks in advance.
-
fbachofner
- Forum Newbie
- Posts: 18
- Joined: Sun Feb 28, 2010 6:29 am
Re: while loop question
Hi Miko:mikosiko wrote:The mysql_fetch_assoc effectively generate a recordset which coincidentally is an array... but what I'm suggesting you based in your goals is use that information to load a DIFFERENT array inside the while loop... in that way you can use the values loaded in the suggested array in ANY place in your code an not only inside your while loop... remember that every time your while loop is executed the values of your recordset are changed (a new different row every time)... right?
I suppose I understand what you are saying in concept but have absolutely no idea how to put it into practice. Is there an example to which you could point me?
Alternatively, since the data is already in a recordset, is there a "direct" way to pull the first 2 rows and then later the next 2 rows?
I am happy to do experimenting and have been reading the PHP docs but I barely know where to start. Should I be using mssql_fetch_array instead?
Please remember this is my first attempt at anything "complex" in PHP.
Thanks!
Re: while loop question
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).davex wrote:Hi,
Looks to me like the loop is not loading more rows - the while will loop forever.
Why not try something like:HTH,Code: Select all
Now exhibiting:<br/><br/> <?php ...... //do some other stuff then //reset $count=0 and do the exact same while loop - will fetch the next 2 rows
Dave.
Re: while loop question
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)fbachofner wrote:Hi Dave:davex wrote:Looks to me like the loop is not loading more rows - the while will loop forever.
Why not try something like:
<snip>
Thanks for the response.
Unfortunately,returns 3 results (even when I change the value of ($count<1) to, say, "1" )Code: Select all
$count=0; while ( ($row=mysql_fetch_assoc($ExhibitsQuery_result)) && ($count<2) )
Even worse, for some reason this method results in the LAST three records being returned rather than the first three.
This is very puzzling to me.
Any more ideas? Thanks in advance.
Last edited by mikosiko on Sun Feb 28, 2010 4:55 pm, edited 1 time in total.
Re: while loop question
Hi,
*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.
For clarity I think copying it all into an array as suggested by others may well be the best bet - not only can you use/reuse the data anywhere but you can also see what's inside the array with a print_r for debug purposes.
So I propse something like the following:
Perform the query:
Then load everything into a single array:
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:
Then do whatever else you want and have a second for loop of the form:
If this still gives problems then uncomment the print_r line and see what the raw array data looks like - my bet would be the query is returning the wrong number of rows and/or the wrong order.
Regards,
Dave.
*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.
For clarity I think copying it all into an array as suggested by others may well be the best bet - not only can you use/reuse the data anywhere but you can also see what's inside the array with a print_r for debug purposes.
So I propse something like the following:
Perform the query:
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);
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);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>';
}
Code: Select all
for ($i=2; $i<4; $i++)
{
// same old display code or whatever you like
}Regards,
Dave.
-
fbachofner
- Forum Newbie
- Posts: 18
- Joined: Sun Feb 28, 2010 6:29 am
Re: while loop question
OK, so from another problem I had sometime ago, I know I can fix that issue by resetting the count of "extracted" rows like this: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
mysql_data_seek($ExhibitsQuery_result,0);
but my other problem remains --
while ( ($row=mysql_fetch_assoc($ExhibitsQuery_result)) && ($count<1) )
does NOT respect the <1 or <2
ALL remaining rows are returned (were back up to 4, the complete result set . . .)
Thanks for any input!
Re: while loop question
EXACTLY what I said in my first answer!!!davex wrote:Hi,
Then load everything into a single array:
Dave.
FBACHOFNER : I did edit my previous post regarding to the usage of POST-INCREMENT instead of PRE-
check that again
Re: while loop question
mikosiko - absolutely your way was best!
Not that it really matters but I'm still a bit confounded as to why three rows displayed, not two.
The first $row=mysql... would load record 0.
However it wasn't outputted anywhere - the loop then should go through twice on each occasion performing another $row=mysql...
So I would have thought the first row (0) would simply be lost when row(1) was loaded and displayed then row(2) was loaded and displayed in the while loop so you would see rows 1 and 2 but only 2 rows in total.
Ho-de-hum. No matter - let's just hope the array clears thing up.
Regards,
Dave.
Not that it really matters but I'm still a bit confounded as to why three rows displayed, not two.
The first $row=mysql... would load record 0.
However it wasn't outputted anywhere - the loop then should go through twice on each occasion performing another $row=mysql...
So I would have thought the first row (0) would simply be lost when row(1) was loaded and displayed then row(2) was loaded and displayed in the while loop so you would see rows 1 and 2 but only 2 rows in total.
Ho-de-hum. No matter - let's just hope the array clears thing up.
Regards,
Dave.
Re: while loop question
.... $count++ is the culprit.... ++$count is the winnerdavex wrote:mikosiko - absolutely your way was best!
Not that it really matters but I'm still a bit confounded as to why three rows displayed, not two.
The first $row=mysql... would load record 0.
Regards,
Dave.
Re: while loop question
Code: Select all
$count=0;
while ( $count<2 )
{
echo $count."<br />";
$count++
}I think maybe my $count++ line was missed out at the end of the while loop - I should have just done while ( $count++<2 ).
I'd have thought that:
Code: Select all
$count=0;
while (++$count<2)
{
echo $count."<br />";
}Meh. Maybe it's too late in the night for my addled brain to cope
Cheers,
Dave.