Use the mysql command-line interface. Type the query in and use
literal values. See if that works. If so, you know the query is okay.
Next, modify your code to use the same exact query. Use literal
values, not variables. See if that works. If so, you know that
it's an issue with building the query.
If you use this type of methodology, you'll usually be able to
isolate your problems much more quickly.
Maurice Aubrey
On Tue, Jan 02, 2001 at 11:28:14AM -0500, Mike Podlesny wrote:
> 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-maurice=hevanet.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
>
>
Attachment:
pgppH5kAsD4b7.pgp
Description: PGP signature