generic Posted November 30, 2006 Share Posted November 30, 2006 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 Quote Link to comment Share on other sites More sharing options...
generic Posted December 1, 2006 Author Share Posted December 1, 2006 hmm, guess not. Alrighty, multiple if's it is.thanks anyway folks. Quote Link to comment Share on other sites More sharing options...
obsidian Posted December 1, 2006 Share Posted December 1, 2006 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. Quote Link to comment Share on other sites More sharing options...
generic Posted December 1, 2006 Author Share Posted December 1, 2006 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>-DOJpreferred output:<h1>Search Engines</h1>-Google-Yahoo<h1>Government</h1>-DOJThanks again for helping out, I really do appreciate it. Quote Link to comment Share on other sites More sharing options...
HuggieBear Posted December 1, 2006 Share Posted December 1, 2006 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]RegardsHuggie Quote Link to comment Share on other sites More sharing options...
obsidian Posted December 1, 2006 Share Posted December 1, 2006 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] Quote Link to comment Share on other sites More sharing options...
generic Posted December 1, 2006 Author Share Posted December 1, 2006 Ahhhh ok, that's awesome. Works like a charm now, thanks to both of you! :)rk Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.