Page 1 of 1
Loop question
Posted: Thu May 31, 2007 7:32 am
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.";
}
}
Posted: Thu May 31, 2007 7:37 am
by feyd
Other than running the query twice, it appears your query would be at fault.
Posted: Thu May 31, 2007 7:44 am
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
Posted: Thu May 31, 2007 7:45 am
by feyd
Using a do..while loop.
Posted: Thu May 31, 2007 8:10 am
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.";
}
Posted: Thu May 31, 2007 8:23 am
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().
Posted: Thu May 31, 2007 8:24 am
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).
Posted: Thu May 31, 2007 8:50 am
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.";
}
Posted: Thu May 31, 2007 9:03 am
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.
Posted: Thu May 31, 2007 9:08 am
by psurrena
Thanks for all the help feyd / superdezign
Posted: Thu May 31, 2007 11:58 am
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>';
}
?>
Posted: Thu May 31, 2007 3:12 pm
by psurrena
wow, thanks. How does this compare to while...do? Which is a better practice?
Posted: Thu May 31, 2007 3:17 pm
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.
Posted: Fri Jun 01, 2007 2:11 am
by djot
do ... while often misses one result, had trouble with that often. I only use while {} now.
Posted: Fri Jun 01, 2007 6:58 am
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.