GROUP BY, NULL values and endless iterations.

[kingnutter]

I am trying to echo a list which is essentially an index of a publication. It lists page number, title and a relevant link. Some links have no title, some titles pertain to several links.

 

My existing code works to an extent. It is listing all of my records, grouping by title.name/id and ordering by location (page number). Neat.

 

I have two problems: 

 

1) Some of my links records have a title value of 0 (should it be NULL?). I don’t wish to group all of these together, but list them individually ordered by location mixed in with above.

 

2) All my attempts at working out whether a grouped title pertains to several links and then iterating through them is producing multi-nested conditions and queries which I am certain are superfluous.

 

Here is my existing code. Apologies if some of the echos are not needed here. NB: I am using codeigniter hence the odd “anchor” but I want to sort this out as straight MySQL to start with.

 

$result2 = mysql_query("

SELECT links.id, links.link, links.title, links.itemtype_id, links.notes, MIN(links.location), titles.id, titles.name 
FROM links 
JOIN titles 
ON links.title=titles.id 
WHERE heading = $id 
AND links.hidden = 0 
GROUP BY titles.id 
ORDER BY MIN(links.location);
			");

while($linkarray = mysql_fetch_array($result2)){			

echo "<div style='text-indent:" . $headings['depth'] . "em;'>" . "pg. " . $linkarray['MIN(links.location)'] . " <b>" . $linkarray['name']  . $linkarray['title'] . "</b> ";

echo "<div style='text-indent:" . $headings['depth'] . "em;'>";

	if ($linkarray['itemtype_id']==1){ 
echo "<a href='http://$linkarray[link]' target='_blank'>";
	}

echo $linkarray['link'];

	if ($linkarray['itemtype_id']==1){ 
		echo "</a>";
	}

echo " ";


// DELETE LINK

echo anchor("edit/deleteLink/" . $linkarray['id'], "X<br />", array('class'=>'deleteLink'));

echo "</div>";

echo "<div class='notes'>" . $linkarray['notes'] . " </div>";

}

 

Here are the relevant table structures:

 

MySQL client version: 5.1.30

CREATE TABLE IF NOT EXISTS `links` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `link` varchar(256) NOT NULL,
  `title` mediumint( unsigned NOT NULL DEFAULT '0',
  `heading` int(10) unsigned NOT NULL DEFAULT '0',
  `itemtype_id` tinyint(3) unsigned NOT NULL,
  `notes` varchar(256) DEFAULT NULL,
  `location` int(10) unsigned NOT NULL,
  `instance_id` smallint(5) unsigned NOT NULL,
  `hidden` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=215 ;


CREATE TABLE IF NOT EXISTS `titles` (
  `id` mediumint( unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=201 ; 

 

Any help appreciated. Please let me know if any more information is required.

 

Thanks.

[fenway]

Sorry, I don't follow.

[kingnutter]

I appreciate that.

 

I'm trying to do too much at once. I'll spilt it up into stages and come back when stuck on a certain point.

 

Many thanks.