Gossamer Forum
Home : General : Perl Programming :

Search two SQL tables

Quote Reply
Search two SQL tables
Hm. I've got a database with exactly 100 entries in it.

I'm trying the following query:

Code:
$numrec = $dbh->selectrow_array("SELECT COUNT(*) FROM ata_members,ata_offers WHERE ata_offers.offer LIKE '%$term%'");

$term is passed to the script from a CGI input field. The funny thing is, it's like the thing goes into a loop. I get something like 4,000+ results! And as I said, there's only 100 entries in the database.

When you're joining two tables (is that even what I'm doing here?) can't you use LIKE at all? It just seems to go into some infinite loop, or at least output all results a 1000 times.

Does anyone know what I'm doing wrong? For reference, here's my entire sub, if it helps anyone. Thank you!!

Code:
sub ata_off_sch {

$term = $query->param('term');
$type = $query->param('type');

if ($type eq "All") {
$ifquery = "ata_offers.offer LIKE '%$term%'";
}
else {
$ifquery = "ata_offers.offer LIKE '%$term%' AND ata_members.type LIKE '%$type%'";
}

$dbh = $mysql->connect_to_db;

$numrec = $dbh->selectrow_array("SELECT COUNT(*) FROM ata_members,ata_offers WHERE $ifquery");

if ($numrec eq "0") {
&error_html("Sorry, no results found..<BR><BR>
Please try again or look through the full listing.");
exit;
}

$sth = $dbh->prepare("SELECT * FROM ata_members,ata_offers WHERE $ifquery");
$sth->execute();

while (my $ref = $sth->fetchrow_hashref())
{
$offer_details .= qq|<tr bgcolor="#F4EFF7">
<td class="text-black" width="25%">$ref->{'name'}</td>
<td class="text-black" width="57%">$offer</td>
<td class="text-black" width="18%">$ref->{'offer_end'}</td>
</tr>
|; }

$sth->finish;
$dbh->disconnect;

&print_page("/offers/search.html");

}

- wil
Quote Reply
Re: [Wil] Search two SQL tables In reply to
Ewww you don't want this:

if ($numrec eq "0") {

eq = non-numeric comparison
== = numeric comparison

hence:

if ($numrec == 0) {

As for the other problem, you can't count two tables like that - for every table you add it will just multiply the value.

eg if table1 has 10 rows, if you try to count two tables you'll get 20, 3 tables 40 etc.

http://www.mysql.com/...o/Counting_rows.html

Last edited by:

RedRum: Jan 22, 2002, 2:07 PM
Quote Reply
Re: [RedRum] Search two SQL tables In reply to
Hm. Doesn't say anything about counting or selecting from two different databases in that URL you gave me?

What's this join business all about then. I think I've reached the stage where I need to do this. Or am I joining them already by just selecting from two different tables?

- wil
Quote Reply
Re: [Wil] Search two SQL tables In reply to
>>Doesn't say anything about counting or selecting from two different databases <<

Databases or tables?
Quote Reply
Re: [Wil] Search two SQL tables In reply to
In Reply To:
SELECT COUNT(*) FROM ata_members,ata_offers WHERE ata_offers.offer LIKE '%$term%'
You also need the "ata_members.column = ata_offers.column" in there to join the tables together properly (most likely the id which associates members with offers). Without that you're getting: row in ata_members with ALL rows in ata_offers. So you get number of rows in ata_members * rows in ata_offers...

Adrian
Quote Reply
Re: [RedRum] Search two SQL tables In reply to
In Reply To:
>>Doesn't say anything about counting or selecting from two different databases <<

Databases or tables?

Tables. Sorry.

- wil
Quote Reply
Re: [brewt] Search two SQL tables In reply to
Thanks for your help. That now works OK for the first part. The first part is where no search term is entered so we only search on category (category being 'type') and that works OK. The following works:

Code:
SELECT * FROM ata_members,ata_offers WHERE
(ata_members.type = '$type') AND ata_members.id=ata_offers.id

Right. Now when someone enteres a search term it's a different story. For some reason, it doesn't come up with any results. It should match column name OR column offer, but it just turns up zero when I know there are results to be had. Here's the query:

Code:
SELECT * FROM ata_members,ata_offers WHERE
(ata_members.name LIKE '%term%' OR ata_offers.offer LIKE '%$term%') AND ata_members.id=ata_offers.id

Thanks for your help.

- wil

Last edited by:

Wil: Jan 23, 2002, 2:00 AM
Quote Reply
Re: [Wil] Search two SQL tables In reply to
your first "term" is missing an "$"

--Philip
Links 2.0 moderator
Quote Reply
Re: [Wil] Search two SQL tables In reply to
JOINs are more efficient than clarifying comma delimited list of tables.
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Heckler] Search two SQL tables In reply to
Then how does one join a table?

- wil
Quote Reply
Re: [Wil] Search two SQL tables In reply to
MySQL (http://www.mysql.com/) has tons of examples...

Here is one:

Quote:

SELECT DISTINCT(UserID)
FROM tbl_Users INNER JOIN tbl_Sessions ON tbl_Users.UserID = tbl_Sessions.UserID
WHERE (tbl_Users.UserID = $userid)
ORDER BY tbl_Sessions.Created DESC

========================================
Buh Bye!

Cheers,
Me

Last edited by:

Heckler: Jan 23, 2002, 11:58 AM
Quote Reply
Re: [Heckler] Search two SQL tables In reply to
Oh yeah. Thanks. I remember something now - am I right in thinking that this relates to left, right and inner joins?

- wil
Quote Reply
Re: [Wil] Search two SQL tables In reply to
You'll find out at the link Eliot gave by entering JOIN in the search box Wink
Quote Reply
Re: [Wil] Search two SQL tables In reply to
Yes...MySQL has a few good tutorials on the differences between the different JOIN functions.

Also, I've read a few on the Web...can't recall the web address, but they were recommended during the time period that I took database design and modelling course last year. Wink

BTW: When I asked the professor (who is a guru in the field of database design/modelling) about "to join or not to join", he implied that depending on the number of records (meaning in the 100,000's), processor of database server, type of application used to query the database, and structure of the database; it wouldn't matter that much...but he definitely implied that using JOINS is more efficient, which I agree with...
========================================
Buh Bye!

Cheers,
Me

Last edited by:

Heckler: Jan 23, 2002, 12:04 PM
Quote Reply
Re: [Heckler] Search two SQL tables In reply to
It makes for cleaner code, and greater control good I guess.

- wil