Help with WHILE

[Presto-X]

Hello guys, I'm working on a links page, the page is going to have a table with 3 cells each cell will hold a series of categories with the links sorted by what category they are under.

 

For example:

 

Left Cell                  Center Cell                  Right Cell

-------------------------------------------------------------

Category 1              Category 3                  Category 4

Link 1                    Link 1                        Link 1

Link 2                    Link 2                       

Link 3                   

Category 2

Link 1

Link 2

 

I can get the categories to show up just fine and work in each cell and I can move them around, but when I try and add in the WHILE inside the WHILE for the categories thats when I start to get probelms. the frist category shows up and the links show up under it but any other category for example Category 2 would not show up after the links for Category 1.

 

Here is the code that I'm trying to use:

$cat_left_query="SELECT title, cell, level FROM `categories` WHERE `cell` = 'Left' ORDER BY `level` ASC";
$cat_left_result=mysql_query($cat_left_query);
$cat_left_num=mysql_numrows($cat_left_result);
$i=0;
while ($i < $cat_left_num) {
$cat_left_title=mysql_result($cat_left_result,$i,"title");
$cat_left_cell=mysql_result($cat_left_result,$i,"cell");
$cat_left_level=mysql_result($cat_left_result,$i,"level");
echo "$cat_left_title<br />";
$i++;
}

 

That code works to load the categories, the following is what I'm tying to use now:

$cat_left_query="SELECT title, cell, level FROM `categories` WHERE `cell` = 'Left' ORDER BY `level` ASC";
$cat_left_result=mysql_query($cat_left_query);
$cat_left_num=mysql_numrows($cat_left_result);
$i=0;
while ($i < $cat_left_num) {
$cat_left_title=mysql_result($cat_left_result,$i,"title");
$cat_left_cell=mysql_result($cat_left_result,$i,"cell");
$cat_left_level=mysql_result($cat_left_result,$i,"level");
echo "$cat_left_title<br />";
$linksquery="SELECT title, url, logo, logoalt, description FROM links WHERE category = '$cat_left_title'";
$linksresult=mysql_query($linksquery);
$linksnum=mysql_numrows($linksresult);
$i=0;
while ($i < $linksnum) {
$link_title=mysql_result($linksresult,$i,"title");
$link_url=mysql_result($linksresult,$i,"url");
$link_logo=mysql_result($linksresult,$i,"logo");
$link_logoalt=mysql_result($linksresult,$i,"logoalt");
$link_description=mysql_result($linksresult,$i,"description");
echo "<a href=\"$link_url\" target=\"_blank\">$link_title</a><br />";
$i++;
}
$i++;
}

 

Any help would be great thanks guys.

[redarrow]

Tell me if it works intresting?

 

<?php

$cat_left_query="SELECT title, cell, level FROM `categories` WHERE `cell` = 'Left' ORDER BY `level` ASC";

$cat_left_result=mysql_query($cat_left_query)or die (mysql_error());

while($x=mysql_fetch_assoc($cat_result)) {

echo "".$x['title']." <br />";

$linksquery="SELECT title, url, logo, logoalt, description FROM links WHERE category = '".$x['title']." ";

$linksresult=mysql_query($linksquery) or die(mysql_error());

while ($xx=mysq_fetch_assoc($linkresult)) {

echo "<a href=\" ".$xx['link_url']."\" target=\"_blank\">".$xx['category']."</a><br />";

}
}
?>

[Presto-X]

Thanks for the reply Redarrow, I get the following message:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\links\index.php on line 51

[papaface]

change it to:

<?php

$cat_left_query= mysql_query("SELECT title, cell, level FROM `categories` WHERE `cell` = 'Left' ORDER BY `level` ASC");

while($x=mysql_fetch_assoc($cat_left_query)) {

echo "".$x['title']." <br />";

$linksquery= mysql_query("SELECT title, url, logo, logoalt, description FROM links WHERE category = '".$x['title']." ");

while ($xx=mysq_fetch_assoc($linksquery)) {

echo "<a href=\" ".$xx['link_url']."\" target=\"_blank\">".$xx['category']."</a><br />";

}
}
?>

[Presto-X]

Hey Papaface, now I get this message:

Fatal error: Call to undefined function mysq_fetch_assoc() in C:\wamp\www\links\index.php on line 52

[Presto-X]

ok I got to looking at the code and found a miss spelling, mysq_fetch_assoc it was missing the L it should of been mysql_fetch_assoc any ways now it is giving me the following message:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\links\index.php on line 48

 

Does this look right?

<?php
$cat_left_query=mysql_query("SELECT title, cell, level FROM `categories` WHERE `cell` = 'Left' ORDER BY `level` ASC");
while($x=mysql_fetch_assoc($cat_left_query)) {
  echo "".$x['title']." <br />";
  $links_query=mysql_query("SELECT title, url, logo, logoalt, description FROM links WHERE category = '".$x['title']." ");
  while ($xx=mysql_fetch_assoc($links_query)) {	
echo "<a href=\" ".$xx['link_url']."\" target=\"_blank\">".$xx['category']."</a><br />";
  }
}
?>

[artacus]

When doing queries, its always good to do this:

$result = mysql_query($query) or die(mysql_error());

 

So you can get a clue where it is going wrong. And it looks to me your problem is going to be the unclosed quote around $title at the end.

[Presto-X]

Thanks for the advice Artacus, I added in the die tag I think I found the unclosed quote ' after $title.

 

This is the message that I'm getting now

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #7' at line 1

Is this a problem in my php code or in my data in mysql?

[artacus]

Yeah, see if you can do everything inside of a single query and while loop. It will make it easier to write and will perform better because you are not doing all the db calls.

Another good trouble way to troubleshoot php / mysql is to echo out the query and try running it in phpMyAdmin or similar.

[Presto-X]

Ya I'm not sure if there was a better way to do this I wanted the user to be able to make there own categories and links so I make two tables one to hold the categories and one to hold the links. If there is a better way to do this please I’m open to any and all ideas I’m new to php and still learning so I’m open to any good ideas.

 

And thanks again guys for all of your help thus far

[Presto-X]

ok so I did some more playing around with the code and got it to work I think with this code:

<?php
$cat_left_query=mysql_query("SELECT cat_id, cat_title, cat_cell, cat_level FROM `categories` WHERE `cat_cell` = 'Left' ORDER BY `cat_level` ASC");
while ($x=mysql_fetch_assoc($cat_left_query)) {
  echo "".$x['cat_title']." <br />";
  $links_query=mysql_query("SELECT links_id, links_title, links_url, links_category, links_logo, links_logoalt, links_description FROM `links` WHERE `links_category` = '".$x['cat_title']."'");
  while ($xx=mysql_fetch_assoc($links_query)) {
echo "<a href=\"".$xx['links_url']."\" target=\"_blank\">".$xx['links_title']."</a><br />";
  }
}
?>

[Presto-X]

Ok now this is starting to drive me nuts, if I have lets say category 1 set to the left cell and category 2 set to the center cell it works and looks good but if I set category 2 to be in the left cell it does not show up only the first category shows up... what up with that... grrr... I'm so close to getting this to work but now I feels like I'm back at the start lol.

 

<?php
$query=mysql_query("SELECT cat_title, cat_cell, cat_level FROM `categories` WHERE `cat_cell` = 'Left' ORDER BY `cat_level` ASC");
while ($x=mysql_fetch_assoc($query)) {
  echo "<div class=\"heading\">".$x['cat_title']."</div>";
  $query=mysql_query("SELECT links_id, links_title, links_url, links_category, links_logo, links_logoalt, links_description FROM `links` WHERE `links_category` = '".$x['cat_title']."'");
  while ($xx=mysql_fetch_assoc($query)) {
echo "<a href=\"".$xx['links_url']."\" class=\"linktitle\" target=\"_blank\"><img src=\"admin/images/link.gif\" border=\"0\" align=\"left\" />".$xx['links_title']."</a>
<div class=\"description\">";
if ("".$xx['links_logo']."") {
echo "<img src=\"".$xx['links_logo']."\" alt=\"".$xx['links_logoalt']."\" /><br />";}
echo "".$xx['links_description']."
</div>";
  }
}
?>