help with db query and foreach

[Anon]

Hi!

Here's my problem:

I'm trying to query MySQL to get a list of links divided into categories. Here's my query:

$query = mysql_query("SELECT links.link_name, links.url, links.category_id_r,
categories.category_id, categories.category_name, categories.portal_id_r, portals.portal_id,
portals.user_id_r FROM categories, portals, links WHERE portals.user_id_r=1 AND
portals.portal_id=categories.portal_id_r AND categories.category_id=links.category_id_r");

I want to get 1 category and then get all the links that belong to this category, then the next category etc.

Here's how I'm TRYING to do that with foreach:

while ($sql = mysql_fetch_array($query)) {
$cat_name = $sql["category_name"];
echo("$cat_name: ");
foreach ($sql as $link_name) {
$url = $sql["category_name"];
echo("<A href=$url>$link_name</A> . ");
if ($sql["category_id"] == $sql "category_id_r"]) {
}
}
echo ("<P>");
}

But...it doesn't work...I've tried other cominations with the same result...what am I doing wrong? Any help would be...nice!

Best regards,

Simon

[Anon]

This has been asked before;

Order your results by category and
remember what the category of the first link is, and print the link.
Before you print the next link, check to see if it's category has changed relative to the category of the previous link.
If it has changed, do something fancy.
If it has not changed, just print the link.

[Anon]

A couple of problems with your code.

1. foreach ($sql as $link_name) - This iterates through each field in the last record you fetched, NOT each record in the table.

2. if ($sql["category_id"] == $sql["category_id_r"]) - Your SQL statement has "categories.category_id=links.category_id_r" in the where clause, so the if statement will always be true.

Read Vincent's reply for an overview of how to do what you want.

HTH,
Beth

[Anon]

Hi Vincent!

I checked out the question you mentioned in your reply and changed the code to fit my needs....but still it isn't working. I get an error on saying that the argment in the "while-sentence" is not a valid one or something. Here's how my code looks:

function catlist2($parentid)
{
global $catlistindent;
global $portals;

$returns = 0;
$query = mysql_db_query($portals, "SELECT links.link_name, links.url, links.category_id_r, categories.category_id, categories.category_name, categories.portal_id_r, portals.portal_id, portals.user_id_r FROM categories, portals, links WHERE portals.user_id_r=1 AND portals.portal_id=categories.category_parent AND categories.category_id='$parentid'");
echo"<P>";
while ($sql = mysql_fetch_object($query)) {
$returns++;
for ($i=0; $i < $catlistindent; $i++)
echo ">";

echo "<A href=", $sql->url,">", $sql->link_name,"</A> . ";
// list all my links
$catlistindent++;
catlist2($sql->link_name);
$catlistindent--;

return $returns;
}

}

catlist2(0);

Do you know what I'm doing wrong?

Best regards

Simon