JOIN NOT WORKING!!

[Mike Podlesny <m.podlesny@xxxxxxxxxx>]

Here is the code once again.  Noticed that I changed the "AND" part of the
query to be "AND SubCategory.SubCategory_Index=?"

The $fields{'subcategory'} was changed in the script from it the value comes
from to refelct the index number of the table instead of the sub category
name.  The only thing I can think of is that in mySQL querying on numbers is
different than strings.


my $subcat=$fields{'subcategory'};

$sth = $dbh->prepare("SELECT Categories.Category_Name,
SubCategory.SubCategory_Name FROM Categories,SubCategories WHERE
Categories.Category_Index=SubCategories.Category_Index AND
SubCategory.SubCategory_Index=?");

$sth->execute($subcat);
while (@data = $sth->fetchrow_array()) {
    my $Category_Name = $data[0];
    my $SubCategory_Name = $data[1];
    $Category_Name=~tr/ /+/;
    print "<tr><td><font color=blue>Category:</td><td>$data[0]</td></tr>\n";
}


----- Original Message -----
From: "Sam Masiello" <smasiello@xxxxxxxxxxx>
To: "Mike Podlesny" <m.podlesny@xxxxxxxxxx>
Cc: "mySQL Mailing List" <mysql@xxxxxxxxxxxxxxx>
Sent: Tuesday, January 02, 2001 11:15 AM
Subject: RE: JOIN NOT WORKING!!


>
> In the previous statement:
>
>  $sth = $dbh->prepare("SELECT Categories.Category_Name FROM
> Categories,SubCategories WHERE
> Categories.Category_Index=SubCategories.Category_Index AND
> SubCategory_Name=?");
> $sth->execute($subcat);
>
> when this statement is executed, the value of $subcat is being substituted
> in for the question mark.
>
> Now that you have changed the SQL statement to:
>
> $sth = $dbh->prepare("SELECT Categories.Category_Name,
> SubCategory.SubCategory_Name FROM Categories,SubCategories WHERE
> Categories.Category_Index=SubCategories.Category_Index AND
> SubCategory.SubCategory_Index=?");
> $sth->execute($subcat);
>
> $subcat is now going to be substituted in for
subcategory.subcategory_index,
> which I assume does not contain the same value as subcategory_name.  This
is
> why your query is breaking.  You need to substitute a value other than
> $subcat in for your new query.
>
> HTH
>
> Sam Masiello
> Systems Analyst
> Chek.Com
> (716) 853-1362 x289
> smasiello@xxxxxxxxxxx
>
>  -----Original Message-----
> From: Mike Podlesny [mailto:m.podlesny@xxxxxxxxxx]
> Sent: Tuesday, January 02, 2001 11:10 AM
> To: Sam Masiello
> Cc: mySQL Mailing List
> Subject: Re: JOIN NOT WORKING!!
>
> I had to change the SQL around so that it looks based on the index number
of
> the table and now it doesn't work again and all I did is change the field
it
> searches on, here is the code, maybe you might know.  It once again does
not
> return any records
>
> my $subcat=$fields{'subcategory'};
>
> $sth = $dbh->prepare("SELECT Categories.Category_Name,
> SubCategory.SubCategory_Name FROM Categories,SubCategories WHERE
> Categories.Category_Index=SubCategories.Category_Index AND
> SubCategory.SubCategory_Index=?");
>
> $sth->execute($subcat);
> while (@data = $sth->fetchrow_array()) {
>     my $Category_Name = $data[0];
>     my $SubCategory_Name = $data[1];
>     $Category_Name=~tr/ /+/;
>     print "<tr><td><font
color=blue>Category:</td><td>$data[0]</td></tr>\n";
> }
>
> ----- Original Message -----
> From: "Sam Masiello" <smasiello@xxxxxxxxxxx>
> To: "Mike Podlesny" <m.podlesny@xxxxxxxxxx>
> Cc: "mySQL Mailing List" <mysql@xxxxxxxxxxxxxxx>
> Sent: Tuesday, January 02, 2001 10:51 AM
> Subject: RE: JOIN NOT WORKING!!
>
>
> >
> > Maybe it won't matter, but this is how I usually do it:
> >
> > my $subcat=$fields{'subcategory'};
> > $sth = $dbh->prepare("SELECT Categories.Category_Name FROM
> > Categories,SubCategories WHERE
> > Categories.Category_Index=SubCategories.Category_Index AND
> > SubCategory_Name=?");
> > $sth->execute($subcat);
> > while (@data = $sth->fetchrow_array()) {
> >     my $Category_Name = $data[0];
> >     $Category_Name=~tr/ /+/;
> >     print "<tr><td><font
> color=blue>Category:</td><td>$data[0]</td></tr>\n";
> > }
> >
> > I didn't take a long look at the code, but perhaps it will help?
> >
> > HTH
> >
> > Sam Masiello
> > Systems Analyst
> > Chek.Com
> > (716) 853-1362 x289
> > smasiello@xxxxxxxxxxx
> >
> >  -----Original Message-----
> > From: Mike Podlesny [mailto:m.podlesny@xxxxxxxxxx]
> > Sent: Tuesday, January 02, 2001 10:45 AM
> > To: Gerald L. Clark
> > Cc: mySQL Mailing List
> > Subject: Re: JOIN NOT WORKING!!
> >
> > I gave your suggestion a try and it still did not produce a result.  At
> > least one record should have been returned.  I have included the new
code,
> > maybe someone out there has an idea or two.
> >
> > my $subcat=$fields{'subcategory'};
> > $sth = $dbh->prepare("SELECT Categories.Category_Name FROM
> > Categories,SubCategories WHERE
> > Categories.Category_Index=SubCategories.Category_Index AND
> > SubCategory_Name=$subcat");
> > $sth->execute();
> > while (@data = $sth->fetchrow_array()) {
> >     my $Category_Name = $data[0];
> >     $Category_Name=~tr/ /+/;
> >     print "<tr><td><font
> color=blue>Category:</td><td>$data[0]</td></tr>\n";
> > }
> >
> >
> > ----- Original Message -----
> > From: "Gerald L. Clark" <gerald_clark@xxxxxxxxxxxxxxxxxxx>
> > To: "Mike Podlesny" <m.podlesny@xxxxxxxxxx>
> > Cc: "mySQL Mailing List" <mysql@xxxxxxxxxxxxxxx>
> > Sent: Tuesday, January 02, 2001 10:03 AM
> > Subject: Re: JOIN NOT WORKING!!
> >
> >
> > > Mike Podlesny wrote:
> > > >
> > > > The following statement is not returning any results even though it
> > should
> > > > return 1 record.  I am trying to join a table called Categories with
a
> > table
> > > > called SubCategories on the appropriate indexes where a certain
> > subcategory
> > > > was selected.
> > > >
> > >
> > > my $subcat=$fields{'subcategory'};
> > >
> > > > $sth = $dbh->prepare("SELECT Categories.Category_Name FROM
Categories,
> > > > SubCategories WHERE
> > Categories.Category_Index=SubCategories.Category_Index
> > > AND SubCategory_Name=$subcat");
> > >
> > > #> AND SubCategory_Name='$fields{'subcategory'}'");
> > > >
> > >
> > > --
> > > ---------------------------------------------------------------------
> > > Please check "http://www.mysql.com/documentation/manual.php"; before
> > > posting. To request this thread, e-mail
> mysql-thread60388@xxxxxxxxxxxxxxx
> > >
> > > To unsubscribe, send a message to:
> > >     <mysql-unsubscribe-m.podlesny=labcat.com@xxxxxxxxxxxxxxx>
> > >
> > > If you have a broken mail client that cannot send a message to
> > > the above address (Microsoft Outlook), you can use:
> > >     http://lists.mysql.com/php/unsubscribe.php
> > >
> >
> >
> > --
> > ---------------------------------------------------------------------
> > Please check "http://www.mysql.com/documentation/manual.php"; before
> > posting. To request this thread, e-mail
mysql-thread60390@xxxxxxxxxxxxxxx
> >
> > To unsubscribe, send a message to:
> >     <mysql-unsubscribe-smasiello=chekinc.com@xxxxxxxxxxxxxxx>
> >
> > If you have a broken mail client that cannot send a message to
> > the above address (Microsoft Outlook), you can use:
> >     http://lists.mysql.com/php/unsubscribe.php
> >
>
>
> --
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/documentation/manual.php"; before
> posting. To request this thread, e-mail mysql-thread60395@xxxxxxxxxxxxxxx
>
> To unsubscribe, send a message to:
>     <mysql-unsubscribe-m.podlesny=labcat.com@xxxxxxxxxxxxxxx>
>
> If you have a broken mail client that cannot send a message to
> the above address (Microsoft Outlook), you can use:
>     http://lists.mysql.com/php/unsubscribe.php
>


-- 
---------------------------------------------------------------------
Please check "http://www.mysql.com/documentation/manual.php"; before
posting. To request this thread, e-mail mysql-thread60397@xxxxxxxxxxxxxxx

To unsubscribe, send a message to:
    <mysql-unsubscribe-treed=ultraviolet.org@xxxxxxxxxxxxxxx>

If you have a broken mail client that cannot send a message to
the above address (Microsoft Outlook), you can use:
    http://lists.mysql.com/php/unsubscribe.php