Hi
I am trying a global that will return a list of links thaose have detailed description only..

sub {
# Displays the list of all detailed links.
my ($output,$sth,$link);
my $search_db = $DB->table('Links');
$search_db->select_options ('ORDER BY Title);
$sth = $search_db->select ('DISTINCT(articles)');
while ($link = $sth->fetchrow_hashref) {
$output .= qq~<li><a href="$CFG->{db_cgi_url}/detail_page.cgi?ID=$link->{ID}">$link->{articles}</a></li>~; }
return $output;
}

But it is causing a 500 error..
Any ideas?
Regards
KaTaBd

Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory

$search_db->select_options ('ORDER BY Title);


missing the closing '


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

That was my bad..
What I have is

sub {
# Displays the list of all detailed links.
my ($output,$sth,$link);
my $search_db = $DB->table('Links');
$search_db->select_options ('ORDER BY Title');
$sth = $search_db->select ('DISTINCT(articles)');
while ($link = $sth->fetchrow_hashref) {
$output .= qq~<li><a href="$CFG->{db_cgi_url}/detail_page.cgi?ID=$link->{ID}">$link->{Title}</a></li>~; }
return $output;
}

BUT it returns a blank list.. the title and ID are not printing..
Regards
KaTaBd

Users plug In - Multi Search And Remote Search plug in - WebRing plug in - Muslims Directory

I think the problem is here:

$sth = $search_db->select ('DISTINCT(articles)');

I don't think it's working the way you want/expect it to.

There was a discussion about 'distinct' a short while back.

http://www.gossamer-threads.com/...orum.cgi?post=259093

I think this will answer exactly why it's not working :)


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Probably something to do with the formatting of;


... try;



Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

Andy,

Distinct does not return a row, so Title/etc is not being filled in.

You have to JOIN the results of the distinct select to another query to pull out any rows. Check the message I referenced above. It really does not work the way you want it to, but it works the way a purely logical query would.

DISTINCT is a cookoo item.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

This works ok for me in my PPC plugin.. so hopefully you can modify it to suite your need :)


Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

If this is working, maybe GT's codes handle distinct differently? It's not unlike GT to code in helper modules to get around "'problems" encountered in the default interfaces, and this is one where such a "magical" join would be a great help!

The problem is, distinct _can't_ return a row. It can only tell you how many separate records there are, that are distinct on the field you select. Otherwise, the routine would end up in a conundrum that would force Jack the Ripper out of any computer attempting it, or at least forcing the compture to explode, and set its captive population free. <G>

If you have:

item1, 21
item2, 23
item1, 22
item3, 23

And you ask for distinct(item), what is returned along with it for item 1?? 21 or 22??

In order to avoid that sort of random, illogical decisions, what distinct returns is:

item1
item2
item3

and a count distinct item would return 3.

This is why title, url, etc is blank in the original query. It's not returned.

you'd need to do the distinct select get your list of distinct items, then link them to another table.

There used to be some good examples in the mysql manual, but I can't find them.

An example of what distinct does, or is used for,

In how many cities do the customers live?
SELECT COUNT(DISTINCT city) number_cities FROM customer

The result would be number_cities=>integer_value

This page:
http://jinxidoru.com/tutorials/union.html

Sort of explains what needs to go on, in MySQL 3x.

The "union" command in MySQL 4.x can _possibly_ do some of this, by allowing a distinct select on a field in one table, and joining that to fields in the second table.

I'm not sure how to deal with that using the object interface of GT::SQL::*

Some of the "obvious" solutions might have gotchas, as temporary tables don't have associated .def files, and some of the object routines need them.

this is why using a unique field identifier (ID, Username, etc) and asociative tables like CatLinks works, albeit with a bit of an overhead.


I banged my head on distinct for weeks awhile back, and ended up giving up, and redoing my database structure, which is what I should have done in the first place. If you find you need to use distinct, in any capacity other than a counting function, something is probably wrong with your logic, or database design. This is *not* a criticism, only poiinting out that DISTINCT is a helper count function, not a "widget" to get around more complicated programming.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Try

$sth = $search_db->select (['DISTINCT(articles)','Title','ID']);
while (my ($article,$title,$id) = $sth->fetchrow_array) {
$output .= qq~<li><a href="$CFG->{db_cgi_url}/detail_page.cgi?ID=$id">$title</a></li>~; }

Note that this will select all the rows where you have distinct values for the triple (articles,Title,ID) and not just distinct values for articles.

Hi,

Yes, but technically, it's not the same select, as there can be multiple occurances of "article" now, as long as one or both "title" or "ID" are different.

Since ID will _always_ be different, there's no point of using "distinct"

I'm getting really, really confused now, as to the purpose of the original select statement.

That is, unless ID is not the primary key of the table..... In which case, it starts to become confusing to debug, and should really be called Article_ID or something else.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Agreed - this isn't the same select as the original statement - but as you've pointed out, the original select doesn't have enough information in it to pick out values for Title and ID.

I do agree that when you want to use 'distinct' in a select statement it does take a lot of thinking about exactly what it is that you're trying to do!