Gossamer Forum
Quote Reply
Search
Is it possible to search two tables (main table plus it's user table) at the same time?

I want to be able to search the two tables using search_form.html, modify_search_form.html and delete_search_form.html (e.g be able to search using fields such as Username, Name, Email, etc that are stored in the user table and/or other fields from the main table.) I have added a lot of additional fields to the user table that I need to search on from within the main tables html forms.

As the tables are related, is it possible to do a join or somthing and search them together?

What code would I need to change?

Thanks.

Simon.
Quote Reply
Re: [jai] Search In reply to
Hi Simon,

You might create some new plugins which hooks into search_results, modify_search_results, delete_search_results function and use
(Assuming you defined a relation between two tables with a foreign key)

$tables = db-object->table('your_table' => 'User_table') ;

After that you can make a query_sth with $tables (left_join tables) and return the results.

Cheers,

Jean(at)Gossamer Threads Inc.

Notes: You can change the code directly in Dbsql/Home.pm instead of using the plugin but the deal is you have to update them whenever you upgrade the product.
Quote Reply
Re: [jean] Search In reply to
Hi Jean,

Thanks for your help.

I have never created a plugin before but have spent the last few hours reading over the Links SQL Developers Guide (which I believe applies to DBMan SQL 2.0.3 as well). I have created a plugin and it hooks into the search_result function but I have no idea what to put in the sub to make it return the results from my two tables. My sub is as follows -

sub search2 {
# -------------------------------------------------------------------
# This subroutine will get called whenever the hook 'search_results'
# is run. You should call GT::Plugins->action ( STOP ) if you don't
# want the regular code to run, otherwise the code will continue as
# normal.
#
my (@args) = @_;

# Do something useful here
GT::Plugins->action ( STOP );
my $tables = db-object->table('contact' => 'users') ;

return @args;
}


I would appreciate it if you could help me some more.

Thank you.

Simon.



Quote Reply
Re: [jai] Search In reply to
You may use the code below:
Code:


sub search2 {
# -------------------------------------------------------------------
# This subroutine will get called whenever the hook 'search_results'
# is run. You should call GT::Plugins->action ( STOP ) if you don't
# want the regular code to run, otherwise the code will continue as
# normal.
#
my ($home) = @_;
# Do something useful here
# Ignores the plugin if this is not contact table

($home->{cgi}->{db} ne 'contact') and return @_;
GT::Plugins->action ( STOP );

return $home->home($home->_language('PER_VIEW')) unless ($home->{user}->{view_p});

# Make sure the user passed in some values to search on
$home->_check_opts or return $home->search_form ($home->_language('SRC_FAILURE'));


# Format the cgi for searching
$home->format_search_cgi;


# Check if users can view only their own record
if ($home->{cfg}->{'auth_view_own'} and $home->{cfg}->{'auth_user_field'} and !$home->{user}->{admin_p}) {
$home->{cgi}->{$home->{cfg}->{'auth_user_field'}}= $home->{user}->{'Username'};
}


# Do the search and count the results.
my $sth = $home->{sql}->table('contact'=>'users')->query_sth ($home->{cgi}) or return $home->search_form ($GT::SQL::error);
my $hits = $home->{db}->hits();


# Return if we haven't found anything.
if ($hits == 0) {
return $home->search_form ($home->_language('SRC_NOTFOUND'));
}


# Build speed bar
my $speedbar = '';
if ($hits > ($home->{cgi}->{mh} || 25)) {
my $name = GT::CGI->url;
$speedbar .= $home->{disp}->toolbar( $home->{cgi}->{nh} || 1, $home->{cgi}->{mh} || 25, $hits, $name);
}

# Return results
my (@output);
while (my $row = $sth->fetchrow_hashref) {
push @output, $row;
}
return ('search_results.html',
{
header => $home->_language('HEA_SRC_RESULT'),
results => \@output,
speedbar=> $speedbar,
msg => $home->_language('SRC_RESULT',$hits),
hits => $hits});


}
You must have a foreign key defined for this to work properly.
Cheers,

Jean(at)Gossamer Threads
Quote Reply
Re: [jean] Search In reply to
Hi Jean,

Thanks again.

I have intalled the plugin with your sub included but when I search using any of the fields from the user table I get the message "You must specify at least one search term". If I search on any of the fields from the contact table I get double results (two of each matching record).

I am using one html form with 3 fields from the user table and 4 fields from the contact table.

Why doesn't it recognize the user table fields?

How do I get it to only return one of each match?

Thanks.

Simon.
Quote Reply
Re: [jai] Search In reply to
Hi Simon,

I guess you didn't define a correct foreign key between contact and users table . You have to run an SQL command for updating your table relation by using Admin >> SQL Query (or mySQL command line):

i.e. :

ALTER TABLE contacts ADD FOREIGN KEY (userid) REFERENCES users(Username);

Cheers,

Jean(at)Gossamer-Threads.com

Last edited by:

jean: Mar 20, 2002, 11:13 PM
Quote Reply
Re: [jean] Search In reply to
Hi Jean,

I'm not sure if the problem is with the Foreign Key.

I did a mySQL command line query using MySQLMan
ALTER TABLE contact ADD FOREIGN KEY (Username) REFERENCES users(Username);
but it didn't seem to change anything. It just said MySQL message - 0 Row(s) affected.

I also tried adding a unique key to the Username field and my contact table properties (in MySQLMan) shows Key Name - Username, Unique - Yes, Field - Username. (Username is the Primary key in the users table). Does this mean the foreign key is set?

I would appreciate a bit more help with setting the foreign key and also what foreign key info should appear in the contact.def file.



If my foreign key has been set, it would appear that the following line of code doesn't recognize the users table fields -

# Make sure the user passed in some values to search on
$home->_check_opts or return $home->search_form ($home->_language('SRC_FAILURE'));


Do the field names in my html form have to be changed ? I tried adding the table names (e.g users.field_name) but it didn't make any difference.

Thank you.

Simon.
Quote Reply
Re: [jean] Search In reply to
Please Help.

I've tried everything and searched hundreds of posts but I can't find out the detailed steps for setting up a foreign key(s). How do I set them up and what should my def file(s) look like.

Thank you.

Simon.
Quote Reply
Re: [jai] Search In reply to
Hi Simon,

Your def file(s) should have this:

'fk' => {
'remote_table' => {
'this_column' => 'remote_column'
}
},

i.e contact.def:

'fk' => {
'users' => {
'Username' => 'Username'
}
},

Cheers,
Jean(at)Gossamer-Threads.com
Quote Reply
Re: [jean] Search In reply to
Hi Jean,

Thanks for the def file details but what about the actual steps for setting up the foreign key in the MySQL tables. As I said before, using ALTER TABLE contact ADD FOREIGN KEY (Username) REFERENCES users(Username); doesn't appear to do anything. Is definining a Unique key the same thing? Also, do we always have to modify the foreign key info in the def file manually????

Thanks for your help.

Simon.



Quote Reply
Re: [jai] Search In reply to
Hi Jean,

I'm sorry but I still can't get this to work.

Could you please answer the questions in my last post so I can determine if the foreign key is the problem with my search plugin.

Thank you.

Simon.
Quote Reply
Re: [jai] Search In reply to
Hi Simon,

Sorry for missing the post, but you need to run the SQL command as well as modifying your def file(s) manually for the relation search to work. We are looking forward to adding this feature into the GT::SQL admin.

Cheers,

Jean(at) Gossamer Threads Inc.

Last edited by:

jean: Apr 11, 2002, 5:20 PM
Quote Reply
Re: [jean] Search In reply to
Jean,

After running the SQL query (using MySQLMan) ALTER TABLE contact ADD FOREIGN KEY (Username) REFERENCES users(Username); should I get a message to say it has been set or should the field appear as unique?? How can we tell if the Foreign Key has been set??

Thank you.

Simon.