Page 1 of 3

while loop question

Posted: Sun Feb 28, 2010 7:04 am
by fbachofner
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

Re: while loop question

Posted: Sun Feb 28, 2010 8:49 am
by mikosiko
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

Re: while loop question

Posted: Sun Feb 28, 2010 3:34 pm
by fbachofner
mikosiko 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.
That's what I thought I was doing ;-)

" 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

Posted: Sun Feb 28, 2010 3:44 pm
by davex
Hi,

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
 
HTH,

Dave.

Re: while loop question

Posted: Sun Feb 28, 2010 4:10 pm
by mikosiko

Code: Select all

That's what I thought I was doing 
 
" mysql_fetch_assoc " is data being pulled into a php array, right?
No.. you are not doing what I was suggesting...

this were the goal that you posted first:
"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"
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?

Miko

Re: while loop question

Posted: Sun Feb 28, 2010 4:29 pm
by fbachofner
davex wrote:Looks to me like the loop is not loading more rows - the while will loop forever.
Why not try something like:
<snip>
Hi Dave:

Thanks for the response.

Unfortunately,

Code: Select all

$count=0;
while ( ($row=mysql_fetch_assoc($ExhibitsQuery_result)) && ($count<2) )
returns 3 results (even when I change the value of ($count<1) to, say, "1" )

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.

Re: while loop question

Posted: Sun Feb 28, 2010 4:42 pm
by fbachofner
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?
Hi Miko:

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

Posted: Sun Feb 28, 2010 4:46 pm
by mikosiko
davex wrote:Hi,

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
......
//do some other stuff then
//reset $count=0 and do the exact same while loop - will fetch the next 2 rows
 
HTH,

Dave.
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).

Re: while loop question

Posted: Sun Feb 28, 2010 4:49 pm
by mikosiko
fbachofner wrote:
davex wrote:Looks to me like the loop is not loading more rows - the while will loop forever.
Why not try something like:
<snip>
Hi Dave:

Thanks for the response.

Unfortunately,

Code: Select all

$count=0;
while ( ($row=mysql_fetch_assoc($ExhibitsQuery_result)) && ($count<2) )
returns 3 results (even when I change the value of ($count<1) to, say, "1" )

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.
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)

Re: while loop question

Posted: Sun Feb 28, 2010 4:52 pm
by davex
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:

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);
 
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);
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>';
 }
 
Then do whatever else you want and have a second for loop of the form:

Code: Select all

for ($i=2; $i<4; $i++)
 {
 // same old display code or whatever you like
 }
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.

Re: while loop question

Posted: Sun Feb 28, 2010 4:57 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
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:

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

Posted: Sun Feb 28, 2010 5:01 pm
by mikosiko
davex wrote:Hi,

Then load everything into a single array:



Dave.
EXACTLY what I said in my first answer!!! :) thanks Dave for show him the code :wink:

FBACHOFNER : I did edit my previous post regarding to the usage of POST-INCREMENT instead of PRE-

check that again

Re: while loop question

Posted: Sun Feb 28, 2010 5:07 pm
by davex
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.

Re: while loop question

Posted: Sun Feb 28, 2010 5:13 pm
by mikosiko
davex 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.
.... $count++ is the culprit.... ++$count is the winner :)

Re: while loop question

Posted: Sun Feb 28, 2010 5:22 pm
by davex

Code: Select all

$count=0;
while ( $count<2 )
 {
 echo $count."<br />";
 $count++
 }
Should output 0 then 1? Two iterations.

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 />";
 }
Would just output 1 - being 1 on the first iteration then 2 and hence quitting the while on the second?

Meh. Maybe it's too late in the night for my addled brain to cope :P

Cheers,

Dave.