Confused in how to perform this Query-ing ..

[cinq]

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 :


#
# 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 :D

[cinq]

anyone ? :(

[Dean C]

Moved upon request :)

[cinq]

No help at all ? :(

[Lesane]

Try this:


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 )

$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

<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

<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

[cinq]

Any other code suggestions then ?
I just need to display the details properly ( as per what i want stated above )

Pretty new at this :(

[cinq]

help ? :(
this close to finishing up my hack :(

[cinq]

:(

[cinq]

hate to do this, but help anyone ? :(

[Lesane]

This line:
eval("\$catlistbit .= \"".gettemplate('links_catbit')."\";");
is not on the right place. Try to do it first without templates and then implement it in the templates:


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

$result1 = $DB_site->query("SELECT * FROM links_content WHERE categoryid='$catid' ORDER BY name ASC ");
if ($row4 = $DB_site->num_rows($result1)) {
while ($row2=$DB_site->fetch_array($result1)) {
$name=$row2["name"];
$byline=$row2["byline"];
$content=$row2["content"];
echo "<br>$name $byline";

} // end if row3
} // end while row
} // end if row4
} // end while row2

[cinq]

Thanks Lesane.
The query works....but I am now not sure how to implement it in templates form. :(