how to break up results

[generic]

Warning,

I am a full-on power n00b. Apologies in advance. Having said that...

I know I've come across this solution before - I need to take the results of the following query and break them up so that links come up under an associated category heading.

mySQL:
[code]
-- Table structure for table `linkcats`
--

CREATE TABLE `linkcats` (
`linkcat` int(11) NOT NULL auto_increment, //category id #
`linkcattext` varchar(255) default NULL, //title of category
PRIMARY KEY (`linkcat`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `links`
--

CREATE TABLE `links` (
`linkid` int(11) NOT NULL auto_increment, //link id #
`linkcat` int(11) NOT NULL, //associated category id #
`linktext` varchar(255) default NULL, //title of link
`linkurl` varchar(255) default NULL, //url of link
PRIMARY KEY (`linkid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
[/code]


PHP:
[code]
<?php
$result = mysql_query("SELECT links.*, linkcats.linkcattext FROM links INNER JOIN linkcats ON  (links.linkcat = linkcats.linkcat) ORDER BY linkcats.linkcat",$connect);

while($row = mysql_fetch_array($result)) {//begin loop
if($bgcolor == "#efefef") {
  $bgcolor = "#fff";
} else {
$bgcolor = "#efefef";
}

echo "<li style=\"background-color:".$bgcolor.";\"><a href=\"".$row[linkurl]."\" target=\"_blank\" onclick='return confirm(\"Click OK to leave our site and visit ".$row[linktext]."\");'>".$row[linktext]."</a></li>\n";

  }//end of loop
?>
[/code]

Hopefully that makes sense. I just don't want to have extraneous amounts of code - a hundred if statements - when I'm fairly certain it can be sorted much simpler from within the sql query itself... somehow. :)

Cheers,

rk

[generic]

hmm, guess not. Alrighty, multiple if's it is.

thanks anyway folks.

[obsidian]

Well, there are definitely ways to do this, but I'm not familiar with [b]what[/b] the distinguishing feature between them is. Is it your [i]linkcattext[/i] column that you're wanting to split them up on? If so, just try something like this with your loop:
[code]
<?php
$result = mysql_query("SELECT links.*, linkcats.linkcattext FROM links INNER JOIN linkcats ON  (links.linkcat = linkcats.linkcat) ORDER BY linkcats.linkcat",$connect);

$row = mysql_fetch_array($result); // Prime your layout
$cat = $row['linkcattext'];
echo "<h1>$cat</h1>\n";
echo "<a href=\"$row[linkurl]\">$row[linktext]</a><br />\n";

while($row = mysql_fetch_array($result)) {//begin loop
  if ($cat != $row['linkcattext']) {
    // New category, break it up!
    echo "<h1>$row[linkcattext]</h1>\n";
  }

  echo "<a href=\"$row[linkurl]\">$row[linktext]</a><br />\n";
}//end of loop
?>
[/code]

Do you see what I'm doing? I think this may be the gist of what you're after.

Actually, another (and possibly much better solution) would be to assign all your records to an associative array. Then, you could display them however you like:
[code]
<?php
// Assume $result holds your query results:
$records = array();
while ($row = mysql_fetch_array($result)) {
  $records[$row['linkcattext']][] = $row;
}
?>
[/code]

That way, you have them all split up into groups, and you can list them however you like.

[generic]

Thanks a million Obisidian! That seems pretty close to what I need, the first example anyway. I see what it is that you're doing there, but it's outputting in an odd way. It's giving each link entry it's own heading rather than putting groups of links under associated headings:

current output, something like:
<h1>Search Engines</h1>
-Google
<h1>Search Engines</h1>
-Yahoo
<h1>Government</h1>
-DOJ

preferred output:
<h1>Search Engines</h1>
-Google
-Yahoo
<h1>Government</h1>
-DOJ


Thanks again for helping out, I really do appreciate it.

[HuggieBear]

Try this:

[code]<?php
$result = mysql_query("SELECT links.*, linkcats.linkcattext FROM links INNER JOIN linkcats ON  (links.linkcat = linkcats.linkcat) ORDER BY linkcats.linkcat",$connect);

$category = null;
while($row = mysql_fetch_array($result)) {//begin loop
  if (is_null($category || strcmp($category, $row['linkcattext']) != 0)
    $category = $row['linkcattext'];
    // New category, break it up!
    echo "<h1>$row[linkcattext]</h1>\n";
  }

  echo "<a href=\"$row[linkurl]\">$row[linktext]</a><br />\n";
}//end of loop
?>[/code]

Regards
Huggie

[obsidian]

Thanks, Huggie... indeed, I did forget one key line of code:
[code]
<?php
// this:
if ($cat != $row['linkcattext']) {
  // New category, break it up!
  echo "<h1>$row[linkcattext]</h1>\n";
}

// should be this:
if ($cat != $row['linkcattext']) {
  // New category, break it up!
  echo "<h1>$row[linkcattext]</h1>\n";

  // Reset category to new one
  $cat = $row['linkcattext'];
}
?>
[/code]

[generic]

Ahhhh ok, that's awesome. Works like a charm now, thanks to both of you! :)

rk