Confused in how to perform this Query-ing ..

PHP Code:

if ($action=="main") {

$result = $DB_site->query("SELECT * FROM links_cat ORDER by catorder");
if ($row = $DB_site->num_rows($result)) {
while ($row=$DB_site->fetch_array($result)) {
    $catid=$row["linkcatid"];
    $catname=$row["catname"];
    $catbyline=$row["catbyline"];    

$result1= $DB_site->query("SELECT * FROM links_content WHERE categoryid='$catid'");
if ($row = $DB_site->num_rows($result1)) {
while ($row=$DB_site->fetch_array($result1)) {
            $name=$row["name"];
            $byline=$row["byline"];
            $content=$row["content"];    
eval("\$catlistbit .= \"".gettemplate('links_catbit')."\";");    
}}
}}
eval("dooutput(\"".gettemplate('links_main')."\");");
} 

[cinq]

This is the DB structure :

PHP Code:

#
# Table structure for table 'links_cat'
#

CREATE TABLE links_cat (
  linkcatid int(11) NOT NULL auto_increment,
  catorder int(50) NOT NULL default '0',
  catname varchar(255) NOT NULL default '',
  catbyline text NOT NULL,
  PRIMARY KEY  (linkcatid)
) TYPE=MyISAM;

#
# Table structure for table 'links_content'
#

CREATE TABLE links_content (
  linkid int(11) NOT NULL auto_increment,
  categoryid varchar(255) NOT NULL default '',
  name varchar(255) NOT NULL default '',
  byline text NOT NULL,
  content text NOT NULL,
  PRIMARY KEY  (linkid)
) TYPE=MyISAM; 

[cinq]

i want to display the results as follows :

Cat 1 name
- link1 belonging to cat 1
- link2 belonging to cat 1

Cat 2 name
- link3 belonging to cat 2
- link4 belonging to cat 2

Currently, the 'links_catbit' template displays info in the following format :

Cat name
- link belonging to cat
- link belonging to cat
- ....


But with the above code , I only manage to display the results for the 1st cat in my tables.

How can i display all of them ?

Any help is greatly appreciated

[cinq]

anyone ?

[Dean C]

Moved upon request

[cinq]

No help at all ?

[Lesane]

Try this:

PHP Code:

if ($action=="main") { 

$result = $DB_site->query("SELECT * FROM links_cat ORDER by catorder"); 
if (mysql_num_rows($result)) { 
while ($row=$DB_site->fetch_array($result)) { 
    $catid=$row["linkcatid"]; 
    $catname=$row["catname"]; 
    $catbyline=$row["catbyline"];     

$result1= $DB_site->query("SELECT * FROM links_content WHERE categoryid='$catid'"); 
if (mysql_num_rows($result1)) { 
while ($row2=$DB_site->fetch_array($result1)) { // changed $row into $row2 because $row is used in the other while
            $name=$row2["name"]; 
            $byline=$row2["byline"]; 
            $content=$row2["content"];     
eval("\$catlistbit .= \"".gettemplate('links_catbit')."\";"); // edit the template, change $row into $row2
} // end while $row2
} // end if rows $result1
 
} // end while $row
} // end if rows $result

eval("dooutput(\"".gettemplate('links_main')."\");"); 
} 

[cinq]

Thanks for the reply Lesane,
however in my template i do not have a $row variable.

This is the code i am now using ( still DOESNT work )

PHP Code:

$result = $DB_site->query("SELECT * FROM links_cat ORDER by catorder");
if ($row = $DB_site->num_rows($result)) {
while ($row=$DB_site->fetch_array($result)) {
            $catid=$row["linkcatid"];
            $catname=$row["catname"];
            $catbyline=$row["catbyline"];    

$result1 = $DB_site->query("SELECT * FROM links_content WHERE categoryid='$catid' ORDER BY name ASC ");
if ($row2 = $DB_site->num_rows($result1)) {
while ($row2=$DB_site->fetch_array($result1)) {
            $name=$row2["name"];
            $byline=$row2["byline"];
            $content=$row2["content"];    
eval("\$linkslistbit .= \"".gettemplate('links_linkbit')."\";");    
}}
eval("\$catlistbit .= \"".gettemplate('links_catbit')."\";");    
}} 

Template :
links_catbit

PHP Code:

<table cellpadding="{tableouterborderwidth}" cellspacing="0" border="0" bgcolor="{tablebordercolor}" {tableouterextra} width="{contenttablewidth}" align="center">
<tr>
<td>
<table cellpadding="4" cellspacing="1" border="0" {tableinnerextra} width="100%">
<tr id="cat">
<td bgcolor="{firstaltcolor}">
<normalfont><b><A NAME="$catid">$catname</b></normalfont><br><smallfont>$catbyline</smallfont><br>
</td>
</tr>
<tr><td width=500 bgcolor="{tableheadbgcolor}">
<ul>
$linkslistbit
</ul>
</td></tr>
<tr><td bgcolor="{tableheadbgcolor}">
<div align="right"><smallfont><a href="#top"><b>Back to Top</b></a></smallfont></div>
</td></tr>
</table>

</td>
</tr>
</table> 

Template :
links_linkbit

PHP Code:

<li><smallfont><a href="$content" target="_blank"><b><font color="{categoryfontcolor}">$name</font></b></a></smallfont><br><smallfont>[ $byline ]</smallfont><br> 

[cinq]

Its some error in the looping i think , because each category is showing more links than there is supposed to be in that respective category.

[assassingod]

Not a good idea to loop a query