[SOLVED] Error with while function.

[Anzeo]

hi everyone, decided to start a new topic concerning this matter (however it's related to my previous post).

 

Anyway, I've just started working on my own forum. I already have the table structure and in the first steps I want only a couple of forums working (like the news section and such). But next to that I've already started on my forum index to show all categories and their respective forums.

 

Now the basic code I've written for this is:

 

<table width="700" border="0" cellspacing="0" cellpadding="0">
<?php
/* these two lines are my connection set up, so I left them out,
*/
while($catqry = mysql_fetch_array(mysql_query("SELECT * FROM CATEGORIE ORDER BY VolgNr")))
{
	$CatNaam = $catqry["Naam"];
	$CatId = $catqry["ID"];
?>
<tr><td><a href="view.php?cat=<?php echo "$CatId";?>"><?php echo "$CatNaam"; ?></a></td></tr>
<?php
	$frmrowsqry = mysql_fetch_array(mysql_query("SELECT COUNT(*) AS Limit FROM FORUM WHERE Categorie='$CatId'"));
	$Limit = $frmrowsqry["Limit"];
	while($frmqry = mysql_fetch_array(mysql_query("SELECT * FROM FORUM WHERE Categorie = '$CatId' ORDER BY VolgNr LIMIT='$Limit'")))
	{
		$FrmNaam = $frmqry["Naam"];
		$FrmId = $frmqry["ID"];
		$FrmBeschrijving = $frmqry["Beschrijving"];
?>
<tr><td><a href="vieuw.php?frm=<?php echo "$FrmId";?>"><?php echo "$FrmNaam";?></a></td></tr>
<?php
	}
}
?>
</table>

 

and when I run this on my site I get this output

 

NEWS

General

General

General

General

...

..

.

 

So instead of fetching the forum only once it loops indefinatly. Anyone knows what in my code is causing it to loop like this?

 

Any help greatly appreciated,

Thanks In Advance

[rpadilla]

while($catqry = mysql_fetch_array(mysql_query("SELECT * FROM CATEGORIE ORDER BY VolgNr")))

 

you get a new resource everytime... thats why it loops forever.

you should do like this

 

$sql   = " SELECT * from ...";
$res   = mysql_query($sql) or die(mysql_error());
while($rows = mysql_fetch_array($res))
{
  ....
...

[Anzeo]

$rows is the count(*) result right?

 

Thanks for your reply

[rpadilla]

nope, $rows just contain one instance,

 

the count is

 

$count = mysql_num_rows($res);

[wildteen88]

Its would be better if you used joins for your query.

 

That way you will have 1 query and 1 while loop.

 

Overtime if you continue with that code your will cause stress on on the MySQL server. Look in to SQL Optimization.

[Anzeo]

Something like

 

SELECT * FROM CATEGORIE INNER JOIN FORUM ON CATEGORIE.ID = FORUM.Categorie ?

 

Why didn't I thought of that lol I hope this'll work. Thanks for your replies and time so far.

[Anzeo]

Can't seem to get this working, this is the code I'm using to test wether it fetches the forums and categories, but I get no output

 

<table width="700" border="0" cellspacing="0" cellpadding="0">
<?php
include("connect.php");
while($catqry = mysql_fetch_array(mysql_query("SELECT * FROM FORUM INNER JOIN CATEGORIE ON FORUM.Categorie = CATEGORIE.ID ORDER BY CATEGORIE.VolgNr")))
{
	$CatNaam = $catqry["CATEGORIE.Naam"];
	$CatId = $catqry["CATEGORIE.ID"];
	$FrmNaam = $frmqry["FORUM.Naam"];
	$FrmId = $frmqry["FORUM.ID"];
	$FrmBeschrijving = $frmqry["FORUM.Beschrijving"];
?>
<tr><td><a href="vieuw.php?mode=frm&fid=<?php echo "$FrmId";?>"><?php echo "$FrmNaam";?></a></td></tr>
<?php
}
?>
</table>

 

Can someone point me out where I'm wrong?

Thanks in advance

[Anzeo]

<table width="700" border="0" cellspacing="0" cellpadding="0">
<?php
include("connect.php");
$qry = "SELECT FORUM.Naam FROM FORUM INNER JOIN CATEGORIE ON FORUM.Categorie = CATEGORIE.ID ORDER BY CATEGORIE.VolgNr";
if($result = mysql_query($qry))
{
	if(mysql_num_rows($result))
	{
		while($catqry = mysql_fetch_array($result))
		{
			$CatNaam = $catqry["CATEGORIE.Naam"];
			$CatId = $catqry["CATEGORIE.ID"];
			$FrmNaam = $catqry["FORUM.Naam"];
			$FrmId = $catqry["FORUM.ID"];
			$FrmBeschrijving = $catqry["FORUM.Beschrijving"];
?>
<tr><td><a href="vieuw.php?mode=frm&fid=<?php echo "$FrmId";?>"><?php echo "$FrmNaam";?></a></td></tr>
<?php
		} 
	}
	else
	{
		echo "No results found";
	}
}
else 
{
	echo "Query failed <br />$sql<br />" . mysql_error();
}
?>
</table>

 

I realised the code in my previous post was wrong so I altered it to this code, which I believe is right, but I still get no output.. Anyone who knows what I'm doing wrong here?

 

Thanks in advance

[per1os]

else 
{
	echo "<tr><td>Query failed <br />$sql<br />" . mysql_error() . "</td></tr>";
}
?>

 

Try that.

[Anzeo]

Still no output, I did the same with echo "no results found" but all I get is a blank page.

My head's starting to hurt , why won't this code work dammit.

 

Any ideas left?

TIA

[per1os]

Testing Output
<table width="700" border="0" cellspacing="0" cellpadding="0">
<?php
include("connect.php");
$qry = "SELECT FORUM.Naam FROM FORUM INNER JOIN CATEGORIE ON FORUM.Categorie = CATEGORIE.ID ORDER BY CATEGORIE.VolgNr";
        print "<tr><td>" . $qry . "</td></tr>";
if($result = mysql_query($qry))
{
	if(mysql_num_rows($result))
	{
		while($catqry = mysql_fetch_array($result))
		{
			$CatNaam = $catqry["CATEGORIE.Naam"];
			$CatId = $catqry["CATEGORIE.ID"];
			$FrmNaam = $catqry["FORUM.Naam"];
			$FrmId = $catqry["FORUM.ID"];
			$FrmBeschrijving = $catqry["FORUM.Beschrijving"];
?>
<tr><td><a href="vieuw.php?mode=frm&fid=<?php echo "$FrmId";?>"><?php echo "$FrmNaam";?></a></td></tr>
<?php
		} 
	}
	else
	{
		echo "No results found";
	}
}
else 
{
	echo "Query failed <br />$sql<br />" . mysql_error();
}
?>
</table>

 

Try running that see if you still get a blank page.

[Anzeo]

This is the output I get: SELECT FORUM.Naam FROM FORUM INNER JOIN CATEGORIE ON FORUM.Categorie = CATEGORIE.ID ORDER BY CATEGORIE.VolgNr

[per1os]

Testing Output
<table width="700" border="0" cellspacing="0" cellpadding="0">
<?php
include("connect.php");
$qry = "SELECT FORUM.Naam FROM FORUM INNER JOIN CATEGORIE ON FORUM.Categorie = CATEGORIE.ID ORDER BY CATEGORIE.VolgNr";
        print "<tr><td>";
if($result = mysql_query($qry))
{
	if(mysql_num_rows($result) > 0)
	{
		while($catqry = mysql_fetch_array($result))
		{
			$CatNaam = $catqry["CATEGORIE.Naam"];
			$CatId = $catqry["CATEGORIE.ID"];
			$FrmNaam = $catqry["FORUM.Naam"];
			$FrmId = $catqry["FORUM.ID"];
			$FrmBeschrijving = $catqry["FORUM.Beschrijving"];
?>
<tr><td><a href="vieuw.php?mode=frm&fid=<?php echo "$FrmId";?>"><?php echo "$FrmNaam";?></a></td></tr>
<?php
		} 
	}
	else
	{
		echo "No results found";
	}
}
else 
{
	echo "Query failed <br />$sql<br />" . mysql_error();
}
       print "</td></tr>";
?>
</table>

 

See what happens there.

[Anzeo]

Still no output :/, I'm going to check all table names but they should be right.

 

Thanks for your help and time so far, I appreciate I'm not alone on this skull crushing problem.

 

EDIT: Nope all names are correct as I assumed...

[Anzeo]

Any ideas left ? :/

[Glyde]

Run a print_r($catqry) in the while loop.  I'm willing to bet your column names are not "CATEGORIE.Naam" and "CATEGORIE.ID", but rather "Naam" and "ID"

[Anzeo]

This my output

 

But ain't it logical that I use that column names as it is a join query?

[Glyde]

It is a join query, but those are not the column names of the tables.  The column name will not get modified no matter how many tables you join together.  That's the purpose of the "AS" syntax.

 

SELECT columnname AS someothername FROM table

[Anzeo]

but I have a column named 'Name' in both tables??

[Glyde]

You might want to rename that column.  It's smarter to use an identifier if you know two columns will be joined together.  I usually use the first letter of the table name.  For example, if I have a table named categories, I'd prefix all of the column names with c_, such as:

 

c_id, c_name

 

That way, when you join two tables together, you never have two of the same column names.

[Anzeo]

Hmm true, but I learned that bu putting TABLENAME.COLUMNNAME, you could identify them also. Atleast if sql works that way (I know it works with MS Acces)

[Glyde]

It is referenced that way within the MySQL query, however the resultset returned does not work that way.  You can, within the query, use something such as: TABLENAME.COLUMNNAME='test', but when PHP receives the results, you'll have to use $row['COLUMNNAME'].

[Anzeo]

Ah I see, so I could do CATEGORIE.Name AS CName for example?

[Glyde]

Exactly, modify your table so the column name is CName instead of just Name.  Then, in PHP, when the resultset is returned, you'll have CName as the column with the category name, and Name as the column with the forum name.  This way you can distinguish them.

[Anzeo]

Right .

 

Thanks alot for clearing this out and for your time and patience. I'll go test it right away^^