Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Search custom tables

Quote Reply
Search custom tables
Hi,

I've been stuck on this for a few days now - I can't see what could be wrong so I'm hoping some fresh eyes might spot a stupid mistake.

I've added some custom tables to my setup and I'm trying to search them. One of them works and the other doesn't. I've set them both to internal indexing, I've rewritten the nph-index.cgi file so that it includes these two tables. I've checked in mysqlman that the Score_List and Word_List tables have both been added for both tables and have entries in them.

If I try searching using query_sth I get the correct number of results from hits but no results are returned for one of the tables. I know the code is ok because it works if I search for a particular LinkID just not for a query. I get the same result in the admin database editor using the indexed search - it says 'Your search returned 2 results' but doesn't print them.

Does anyone have any ideas what the problem might be?

Thanks, Laura.
The UK High Street
Quote Reply
Re: [afinlr] Search custom tables In reply to
Hi,

Hmm, very strange. Nothing obvious jumps out at me.

I'd recommend turning on debug and testing from shell:

./admin.cgi 'query=args;here'

and see the output and make sure it's running the proper queries.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Search custom tables In reply to
Thanks Alex,

I tried that and found that it is doing something strange. I've decided that it might be a problem with the primary keys I'm using.

I'm filling the tables using xml feeds so I need to use whatever unique field I'm given as the ID so that I can use get(ID) to compare the new data and update or insert as necessary. I've set these as VARCHAR fields.

For internal indexing do the primary keys need to be integers?

Alternatively, I could just use keyword searches which do work but I'd prefer to use internal indexing if possible.

Thanks, Laura.
The UK High Street
Quote Reply
Re: [afinlr] Search custom tables In reply to
I changed the Item_ID to VARCHAR in the Score_List table so that it would accept my ID values - I had been wondering why these didn't match Blush

Now the problem it is giving is:

Failed to execute query: '
SELECT *
FROM
Table
WHERE
TableID IN(1ACBE0022,4FCTR0002,1FCTR0033)
' Reason: Unknown column '1ACBE0022' in 'where clause' at ..../MYSQL.pm line 117

So, it seems that the key does need to be an integer for the IN() to work.
Quote Reply
Re: [afinlr] Search custom tables In reply to
Right, I think I've decided to add a new ID field as the primary key which is an auto incrementing integer. This means I can't use get() to check whether the items already exist in the database but I can just use a select statement instead I think. Before I make big changes to the structure of my tables I just wanted to make sure that this is a sensible thing to do. Any comments?

I know that if I add the field to the existing database I wont be able to fill the new ID field automatically - can I dump the table and then import it to fill the values?

Thanks, Laura.
Quote Reply
Re: [afinlr] Search custom tables In reply to
I would ditch using get(), select() is almost as easy:

my $result = $table->select({ ID => $val })->fetchrow_hashref;

a little more typing, but still not too bad. =)

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] Search custom tables In reply to
Thanks for replying Alex. That's what I thought - just needed a bit of reassurance before I spend a few hours sorting it all out. I'll try to stop putting it off any longer - its amazing the diversions I can find!