Jump to content

how to break up results


generic

Recommended Posts

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.