Two while statements not working together?

[gc40]

I have two while statements in a php script. However, the second one is not outputting the way it should.
When I test them individually, both work. Can anyone have a look at the code and tell me what I must edit to get both working together...


<?php
// Database Connection
include 'dbconnect.php';

// If current page number, use it
// if not, set one!

if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}

// Define the number of results per page
$max_results = 10;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);

// Perform MySQL query on only the current page number's results

$sql = mysql_query("SELECT * FROM Portfolio where `Website` = 1 ORDER BY `Title` LIMIT $from, $max_results");

while($onehome = mysql_fetch_array($sql))
{


//start of Tabs//
/////////////////

//Defining the Tab Labels; LinkName, ScreenShots, Additional Info
echo "<p align='left'></p><ul id='";

//Giving maintab a unique name by assinging PortfolioID infront of it.
echo $onehome['PortfolioID']."maintab' class='shadetabs'>";

//Defining the Link Tab's Uniqueness
echo "<li class='selected'><a href='#' rel='".$onehome['PortfolioID']."link'>".$onehome['Title']."</a></li>";

//Defining Uniqueness for ScreenShots Tab
echo "<li class=''><a href='#' rel='".$onehome['PortfolioID']."screenshot'>Screenshots</a></li>";

//Defing Uniqueness for Additional Info Tab
echo "<li class=''><a href='#' rel='".$onehome['PortfolioID']."status'>Additional Info.</a></li>";
echo "</ul>";
/////////////
//End of Tabs


}

//Javascript for Tab Content//
echo "<script type='text/javascript'>";
//Initialize every single tab contnet//
echo "initializetabcontent('";

while($onehome = mysql_fetch_array($sql))

{
echo $onehome['PortfolioID']."maintab', ";

}
echo ")";
echo "</script>";
//End of Javascript//

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM Portfolio"),0);

// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);

// Build Page Number Hyperlinks
echo "<center><div class='pagination'>Page $page of $total_pages</div>";

// Build Previous Link
if($page > 1){
$prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\" class='pagination'><< Previous&nbsp;&nbsp;</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "$i ";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\" class='pagination'>&nbsp;$i&nbsp;</a> ";
}
}

// Build Next Link
if($page < $total_pages){
$next = ($page + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\" class='pagination'>&nbsp;&nbsp;Next >></a>";
}
echo "</center><br>";
?>

[NogDog]

Since both while statements are executing a mysql_fetch_array() on the same query result id ("$sql"), each will be moving the result row pointer of the same result set independently of the other. Therefore, the inner loop is going to start on the 2nd result row, then cycle through all the remaining result rows, at which point there will be none left for the outer loop, so it will not have any more results to process. You will therefore need to rethink your logic, or do something with mysql_data_seek() to keep moving the row pointer around (which could get kind of messy).

[gc40]

NogDog, I have always loved the fact that you are willing to reply and be helpful. Its great. However, have you ever thought that people come to these forums because they don't know what they are doing and they come for help. Sure what you offered is help, but help to people at an advanced level. What am I suppose to do with what you typed? There is not even a phrase I can google dude! Can someone offer me something a little more newb-friendly?

e.g. The actual code to what two while statements may look like?

[Znupi]

Before the second while redo the first SQL query.

echo "initializetabcontent('";

$sql = mysql_query("SELECT * FROM Portfolio where `Website` = 1 ORDER BY `Title` LIMIT $from, $max_results");

while($onehome = mysql_fetch_array($sql))

{ // ...

That should help. It's as newb-ish as possible :p.

[gc40]

Just for the record, Znupi, I figured it out last night, but I did it different, your above code is not going to work.

[Znupi]

Why not?...

[NogDog]

If you're going to repeat the query for the inner loop, then assign the result of the mysql_query() to a different variable than the one used for the original query, otherwise you're overwriting the first query's result. Simplified:

$query = "blah blah blah";
$resultOuter = mysql_query($query);
while($row = mysql_fetch_array($resultOuter))
{
// do stuff, then...
$resultInner = mysql_query($query);
while($row = mysql_fetch_array($resultInner))
{
// do other stuff
}
}

However, this could get a bit time-consuming if there are going to be many result rows. If that's the case, it's probably time to look at the overall program design and find a more efficient way to do it.

[Znupi]

Yeah, sorry, I didn't realize the second while was inside the first one. In that case NogDog's solution should work. For a faster solution, you could first add all the rows in a multidimensional array, and work with that. But that isn't newb-ish enough, I guess.

[jasonahoule]

In the original post the loops were not nested. I would suggest doing what Znupi just said (unless you actually can nest the loops) and populate an array with the values from your query. For example...

$tempArray = array();
while($onehome = mysql_fetch_array($sql))
{
array_push($tempArray, $onehome);
...

Then your second loop would use the array you created

foreach($tempArray as $value) {
echo $value['PortfolioID']."maintab', ";
...
}

[NogDog]

In the original post the loops were not nested. ...
Looks like I might have been confused by the way the code was indented.

If that's the case, then all that is needed (at least as far as the query result being reused in a second loop) would be a mysql_data_seek($sql, 0); to "rewind" the result set before doing the second loop.

[gc40]

Arg, I did it a bit different when I was playing around last night and I just changed some stuff from Nog's example:

$resultOuter = mysql_query($sql);
while($onehome = mysql_fetch_array($resultOuter))
{



}

//Javascript for Tab Content//
echo "<script type='text/javascript'>\n";
//Initialize every single tab contnet//
echo "initializetabcontent(";

$resultInner = mysql_query($sql);
$tab_params = '';
while($onetab = mysql_fetch_array($resultInner))
{
$tab_params .= '"' . $onetab['PortfolioID'] . 'maintab", ';
}
$tab_params = substr($tab_params, 0, -2); //strip the last comma and single white space
echo $tab_params.")\n</script>\n";
//End of Javascript//


It seems to work great though, unless you guys see a problem with the code.

Two questions:
1. Is my code above suspetible to SQL Injection?
2. Is there a problem with the way I coded the above while loops?

[MrCoder]

Arg, I did it a bit different when I was playing around last night and I just changed some stuff from Nog's example:

$resultOuter = mysql_query($sql);
while($onehome = mysql_fetch_array($resultOuter))
{



}

//Javascript for Tab Content//
echo "<script type='text/javascript'>\n";
//Initialize every single tab contnet//
echo "initializetabcontent(";

$resultInner = mysql_query($sql);
$tab_params = '';
while($onetab = mysql_fetch_array($resultInner))
{
$tab_params .= '"' . $onetab['PortfolioID'] . 'maintab", ';
}
$tab_params = substr($tab_params, 0, -2); //strip the last comma and single white space
echo $tab_params.")\n</script>\n";
//End of Javascript//


It seems to work great though, unless you guys see a problem with the code.

Two questions:
1. Is my code above suspetible to SQL Injection?
2. Is there a problem with the way I coded the above while loops?


1. How can we tell since you never posted the SQL?
2. Why is there an empty while loop?