my first attempt at Perl mysql JOIN query 1

[iluvperl]

I need help from someone who's familiar with Perl AND MySQL, more specifically JOIN statements where I can SELECT from two separate tables completely inside one statement.

I have 2 tables (engines, special_fields) and the fields that are identical between them are engines.name and special_fields.engine).

When I was doing ONE select from just the engines table, it worked perfectly but since my attempt at JOINING the two, my rows <= 0 always fails.

Can someone help me fix this problem?

# I looked in the database and I DO have
#
#  engines.name WITH value of "test" WHERE engines.id = $choice
#  special_fields.engine WITH value of "test"

      my $data = qq(SELECT engines.id, engines.name, engines.engineurl, engines.submiturl, engines.title_field, 
                  engines.name_field, engines.keywords_field, engines.description_field, engines.email_field, 
                  engines.catname_field, engines.catoptions_field, engines.successtext, engines.callby, 
                  engines.formname, engines.status, engines.url_field, special_fields.name1,
                  special_fields.name2, special_fields.name3, special_fields.name4, 
                  special_fields.lable1, special_fields.lable2, special_fields.lable3, 
                  special_fields.lable4, special_fields.value1, special_fields.value2, 
                  special_fields.value3, special_fields.value4, special_fields.settings1, 
                  special_fields.settings2, special_fields.settings3, special_fields.settings4 
                  FROM engines JOIN special_fields ON engines.name = special_fields.engine WHERE engines.id="$choice");
      my $sth = $dbh->prepare($data);
      $sth->execute() or die $dbh->errstr;

      if ($sth->rows <= 0)
      {
         print "Error: No search engine found with that ID. Please try again. You chose $choice.";
         exit;
      }

 

[stevexff]

The SQL looks OK, I suspect it's your quoting. To avoid the issue (and protect yourself from SQL injection attacks), try changing

WHERE engines.id="$choice");

to

WHERE engines.id=[red]?[/red]);

and pass the parameter on the execute

$sth->execute([red]$choice[/red]);

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]