SELECT table1.id,state,city,discription,comment
    FROM table1 NATURAL LEFT JOIN table2
   WHERE state=$state

Or else specify the id with a USING, ON, or WHERE clause.

Update: Was missing a FROM clause

my $dbh = DBI->connect(
   "DBI:mysql:$database:$db_server",
   $user, $password
);

my $spdtrap_stmt = <<';';
SELECT id,
       state,
       city,
       discription
  FROM speedtrap
 WHERE state = ?
;

my $comment_stmt = <<';';
SELECT comment_id,
       comments,
       name
  FROM comments
 WHERE id = ?
;


# You need to make two dbh with many databases.
# MySQL allows two queries to share the same dbh.
# Create two dbh if need be.

# Since we're using replaceable parameters,
# We can prepare the comment sth once, and
# executed multiple times.

my $spdtrap_sth = $dbh->prepare($spdtrap_stmt)
or die "Couldn't prepare the query: ".$DBI::errstr;

my $comment_sth = $dbh->prepare($comment_stmt)
or die "Couldn't prepare the comment query: ".$DBI::errstr;


$spdtrap_sth->execute($state)
or die "Couldn't execute query: ".$DBI::errstr;

print <<EOF;
<table border="1" align="center" width="100%">
    <tr>
<!-- why bother? -- 
        <td width="1">
            <p align="center">ID</p>
        </td>
-->
        <td width="10%">
            <p align="center">State</p>
        </td>
        <td width="15%">
            <p align="center">City</p>
        </td>
        <td width="15%">
            <p align="center">Location</p>
        </td>
        <td width="30%">
            <p align="center">Description</p>
        </td>
        <td width="30%">
            <p align="center">Comments</p>
        </td>

    </tr>
EOF


my @spdtrap_row;
while (@spdtrap_row = $spdtrap_sth->fetchrow_array) {
   my ($id, $state, $city, $description) = @spdtrap_row;

   print <<EOF;
    <tr>
        <!-- why bother? -- <td>$id</td> -->
        <td>$state</td>
        <td>$city</td>
        <td>$location</td>
        <td>$description</td>
        <td><table border="0" width="100%">
            <tr>
                <td width="70%">Comment</td>
                <td width="30%">By</td>
            </tr>
EOF


   $comment_sth->execute($id)
   or die "Couldn't execute comment query: ".$DBI::errstr;

   my @comment_row;
   while (@comment_row = $comment_sth->fetchrow_array) {
      my ($comment_id, $comment_text, $commenter_name) = @comment_row;

      print <<EOF;
            <tr>
                <td>$comment_text</td>
                <td>$commenter_name</td>
            </tr>
EOF
   }


   print <<EOF;
        </table></td>
    </tr>
EOF
}

print "</table>";

$comment_sth->finish;
$spdtrap_sth->finish;
$dbh->disconnect;
[download]

Fixes:

• btw, the word is spelled "description". I didn't correct it in the database field name.
• The height of every row was 1 pixel.
• Your widths added up to more than 100%.
• Your header widdths didn't match your field widths.
• An error handler was using a handle that was undef.
• You weren't escaping $state or even putting it in quotes. I don't see how that statement could ever execute. I used replaceable parameters, so no more need to quote or escape.

output:

Awesome, looks good. I get it. You guys always get it right when I'm in a crunch.

How does this statement work:


my $spdtrap_stmt = <<';';
SELECT id,
       state,
       city,
       discription
  FROM speedtrap
 WHERE state = ?
;
[download]

Does it mean everything is really on the same line?

You're welcome

<<';' is just like the <<EOF you've used elsewhere in your program, except the single quotes mean $ and @ won't be treated as variables, and it's ends with a line containing only ';' (and no spaces). In other words, the statement in question compiles to exactly the same as:

my $spdtrap_stmt = 
'SELECT id,'         . "\n" .
'       state,'      . "\n" .
'       city,'       . "\n" .
'       discription' . "\n" .
'  FROM speedtrap'   . "\n" .
' WHERE state = ?'   . "\n";
[download]

SQL doesn't care about tabs (not used) and newlines outside of quote strings; it just treats them as another space. So the statement in question is functionally equivalent to what's below, but easier way to read:

my $spdtrap_stmt = 'SELECT id, state, city, discription FROM speedtrap
+ WHERE state = ?';
[download]

I'm running the code you suggested and got it to work. Now if there is a comment I want to print the comment, if there isn't I want to say that there aren't any comments:

$comment_sth->execute($id) or die "Couldn't execute comment query: ".$
+DBI::errstr;

my @comment_row;
while (@comment_row = $comment_sth->fetchrow_array) {
my ($speedtrap_id,$comment_id,$comment_text,$comment_date,$commenter) 
+= @comment_row;        

if(@comment_row){
print <<EOF;
<tr> 
<td colspan="2" bgcolor="#E6ECF0">$comment_text</td>
</tr>
EOF
} else {
print <<EOF;
<tr>                            
<td colspan="2" bgcolor="#E6ECF0">There are no comments from others fo
+r this speedtrap.</td>
</tr>
EOF
}
[download]

But it always executes the the first condition. I've tried all kinds of ways to get the condition to work but I can't figure it out. Your help is greatly appreciated.

Thanks
Adam

The problem is you'll only get into the while if @comment_row is true, so testing if (@comment_row) inside the while is useless.

$comment_sth->execute($id)
or die "Couldn't execute comment query: ".$DBI::errstr;

my @comment_row;
my $comment_count;
while (@comment_row = $comment_sth->fetchrow_array) {
   my ($speedtrap_id,$comment_id,$comment_text,$comment_date,$commente
+r) = @comment_row;        

   $comment_count++;

   print <<EOF;
<tr> 
<td colspan="2" bgcolor="#E6ECF0">$comment_text</td>
</tr>
EOF
}

print <<EOF unless ($comment_count);
<tr>                            
<td colspan="2" bgcolor="#E6ECF0">There are no comments from others fo
+r this speedtrap.</td>
</tr>
EOF
[download]