Loop question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Loop question

Post by psurrena »

The code below works EXCEPT it returns each db record 11 times. Could any help by telling what is wrong?

Code: Select all

$cat=$_GET['type'];
if(!filter_var($cat, FILTER_VALIDATE_INT)) {
	echo "<p>Please use a valid category id.</p>";
} else {						
	$query="SELECT project_id,project_name,category_full FROM project,category WHERE project_cat LIKE'%$cat%' ORDER BY project_name ASC";
	$result=mysql_query($query) OR DIE (mysql_error());
	$row=mysql_fetch_assoc($result);
	
	if (mysql_query($query)){
		if (mysql_num_rows($result)> 0){
			echo "<h2>".$row['category_full']."</h2>";
			while ($row=mysql_fetch_assoc($result)){
				echo '<a href="work.php?mode=item&id='.$row['project_id'].'">'.$row['project_name']."</a><br />";
			}
		} else {
			echo "<p>There are no projects in this category.</p>";
		}
		mysql_close();
	} else {
		echo "Could not perform query.";
	}
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Other than running the query twice, it appears your query would be at fault.
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

I asked this question yesterday and thought I understood it. How would I not run the query twice yet get the information need? 1) the title row
2) the loop
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Using a do..while loop.
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

Like so?

Code: Select all

$query="SELECT project_id,project_name,category_full FROM project,category WHERE project_cat LIKE'%$cat%' ORDER BY project_name ASC";
$result=mysql_query($query) OR DIE (mysql_error());
$row=mysql_fetch_assoc($result);
if (mysql_query($query)){
	if ($row=mysql_fetch_assoc($result)){
		echo "<h2>".$row['category_full']."</h2>";
		do {
			echo '<a href="work.php?mode=item&id='.$row['project_id'].'">'.$row['project_name']."</a><br />";
		} while($row=mysql_fetch_assoc($result));
	} else {
		echo "<p>There are no projects in this category.</p>";
	}
	mysql_close();
} else {
	echo "Could not perform query.";
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You're still calling the query twice, but likely, yes. Due to current logic, you eat the first record still as well. Remove the first reference to mysql_fetch_assoc() and the second reference to mysql_query().
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

No, you're still skipping an entire result.

Every time that you run a MySQL fetch function, you get that data, and move on to the next. So, once you fetch a row, if you don't do anything with it before running the fetch function again, you've lost that data completely.

Code: Select all

$row=mysql_fetch_assoc($result); // <--- Here, you fetch a row.
if (mysql_query($query)){
        if ($row=mysql_fetch_assoc($result)){ // <-- Here, you fetch a row as well, but you've done nothing with the row before it.
                echo "<h2>".$row['category_full']."</h2>";
                do {
                        echo '<a href="work.php?mode=item&id='.$row['project_id'].'">'.$row['project_name']."</a><br />";
                } while($row=mysql_fetch_assoc($result));
And, why run the query twice? Instead of if(mysql_query($query)), just check if($result).
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

It works, which is great but why is this better than just using a while loop? I still have to run a fetch twice. Once to get the heading and once to displace all the records...

Sorry to drag this on but it's very interesting and I just want to really understand the decisions being made.

Code: Select all

$query="SELECT project_id,project_name,category_full FROM project,category WHERE project_cat LIKE'%$cat%' ORDER BY project_name ASC";
$result=mysql_query($query) OR DIE (mysql_error());

if ($result){
	if ($row=mysql_fetch_assoc($result)){
		echo "<h2>".$row['category_full']."</h2>"; //<-- HEADING
		do {
			echo '<a href="work.php?mode=item&id='.$row['project_id'].'">'.$row['project_name']."</a><br />"; //<--Records
		} while($row=mysql_fetch_assoc($result));
	} else {
		echo "<p>There are no projects in this category.</p>";
	}
	mysql_close();
} else {
	echo "Could not perform query.";
}
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

The difference between the while loop and the do...while loop is when the condition is checked.

In the do...while loop, you fetch the result before the loop starts, use it, THEN the condition is checked (which fetches the next result).

If you were to use a regular while loop, you wouldn't get to use the result that you checked for in your if statement in the loop.
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

Thanks for all the help feyd / superdezign
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Lets use some logic in what you are trying to do:

Code: Select all

<?php
// Set the query to use
$query = "SELECT project_id, project_name, category_full 
    FROM project,category 
    WHERE project_cat 
    LIKE'%$cat%' 
    ORDER BY project_name ASC";
// Execute the query and fail if error
$result = mysql_query($query) OR die (mysql_error());

// We need to know if we have echoed the title
$echo_title = false;

// If we have rows, show them
if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_array($result)) {
        // Have we shown the title row in this loop yet?
        if (!$echo_title) {
            // Nope, so we show, then turn it off
            echo $row['category_full'] . '<br />';
            $echo_title = true;
        }
        // Now echo our list
        echo '<a href="work.php?mode=item&id='.$row['project_id'].'">'.$row['project_name'].'</a><br />';
    }
} else {
    // There were no rows to show
    echo '<p>There are no projects in this category.</p>';
}
?>
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

Post by psurrena »

wow, thanks. How does this compare to while...do? Which is a better practice?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

While (or while/do) will only execute if the while condition evaluates to true. do/while evaluates at least once because the do runs before checking the while. So if the while is false, the do already did what it does.
djot
Forum Contributor
Posts: 313
Joined: Wed Jan 14, 2004 10:21 am
Location: planet earth
Contact:

Post by djot »

do ... while often misses one result, had trouble with that often. I only use while {} now.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

djot wrote:do ... while often misses one result, had trouble with that often. I only use while {} now.
The only time that happens is if the condition you are checking against is changed within the loop. That is useful for times when you want to omit the last result.
Post Reply