awohld has asked for the wisdom of the Perl Monks concerning the following question:

I have two tables: 
Speedtrap
----------
id (unique)
state
city
location
discription
approval

Comments
---------
id (used to reference speedtrap id)
comment_id (unique)
email
comment
approval

[download]
Right now I can search and sort on the speedtraps according the the approval status "New,Yes,No" in the Speedtrap Table 
if ($state eq 'all' && $status eq 'all') {$query = '';}
if ($state ne 'all' && $status eq 'all') {$query = "WHERE state='$stat
+e'"}
if ($state eq 'all' && $status ne 'all') {$query = "WHERE approved='$s
+tatus'"}
if ($state ne 'all' && $status ne 'all') {$query = "WHERE state='$stat
+e' AND approved='$status'";}

$statement = "SELECT id,state,city,discription,approval FROM speedtrap
+ $query ORDER BY $sort1 $sortord";

$sth = $dbh->prepare($statement) or die "Couldn't prepare the query: "
+.$DBI::errstr;

$rv = $sth->execute or die "Couldn't execute query: ".$dbh->errstr;

print <<EOF;
<table border="1" align="center" width="100%">
    <tr>
        <td width="1" height="1">
            <p align="center">ID</p>
        </td>
        <td width="10%" height="1">
            <p align="center">State</p>
        </td>
        <td width="20%" height="1">
            <p align="center">City</p>
        </td>
        <td width="20%" height="1">
            <p align="center">Discription</p>
        </td>
        <td width="1" height="1">
            <p align="center">Speed Trap Approval</p>
        </td>
        <td width="70%" height="1">
            <p align="center">Comment Approval</p>
        </td>
    </tr>
EOF

      


        while (@row = $sth->fetchrow_array) {
($id,$state,$city,$discription,$approval) = @row;


print <<EOF;
<tr>
        <td width="1" height="1>$id</td>
        <td width="10%" height="1">$city</td>
        <td width="20%" height="1">$state</td>
        <td width="20%" height="1">$discription</td>
        <td width="1" height="1">$approval</td>
        <td width="70%" height="1">$need comment approval?</tr>

EOF
}
print "</table>";
}

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

[download]
How can I modify this so I can also query by "new,yes,no" on the approval column on the comment DB.

Thanks
Adam

Replies are listed 'Best First'.
Re: Double Database Search and Sort
by JediWizard (Deacon) on Sep 24, 2004 at 21:33 UTC

    What sort of database are you querying from? This is really an SQL question, because the Perl part is easy. The following sql will work if you are using Oracle:

    select sp.id,sp.state,sp.city,sp.discription,sp.approval 
     from SpeedTrap sp,
          Comments cm
     WHERE sp.state='$state'
       AND sp.approved='$status'
       AND cm.approval = '$approval' 
       AND cm.id = sp.id

    [download]
    May the Force be with you
[reply]
[d/l]
      I'm using MYSQL, what would that SQL look like?
[reply]
Re: Double Database Search and Sort
by ikegami (Patriarch) on Sep 24, 2004 at 21:29 UTC

    You previously mentioned a Speedtrap has 1:N Comments. Given that, your question does not provide enough information about what you want.

    1. Do you want to display all the Speedtraps, and list only Comments that match the query;
    2. do you want to display only the Speedtraps with comments that match the query, but list all the Comments; or
    3. do you want to display only the Speedtraps with comments that match the query, and only the Comments that match the query?

    btw, here's a trick to simply your if:

    $query = 'WHERE 1=1';
$query .= " AND state='$state'"     if ($state ne 'all');
$query .= " AND approved='$status'" if ($status ne 'all');

    [download]

    Also, as I mentioned before, escape your arguments!! Unless you want someone to inject a query that deletes or modifies your database... Use replaceable parameters (as shown previously) or use quote(), as shown here:

    my $q_state  = $dbh->quote($state);
my $q_status = $dbh->quote($status);

$query = 'WHERE 1=1';
$query .= " AND state=$q_state"     if ($state ne 'all');
$query .= " AND approved=$q_status" if ($status ne 'all');

    [download]
[reply]
[d/l]
[select]
      I will definitely escape my arguments, I was going to take care of all that before I made it public.

      What I want to do is only match speedtraps that match the query and comments that match the query.

      The output will have 5 columns that list the speedtrap ID number, state, city, the approval status (all/new/yes/no), and the number of comments defined by the query search (all/new/yes/no). So I may want to search for speedtraps in Illinois that are NEW and that have comments that are NEW. Or I may want to search for speedtraps in Texas that are Yes (Approved) and comments that are NEW. This way when people put new speedtraps into the DB or new comments about an existing speedtrap, I'll be able to check them to make sure they look legit.

      Here you can see how I will query the database. This is for the admin script so I can search for new speedtraps and new comments added to existing speedtraps. 

      <html>
<body bgcolor=orange>
<form action=../cgi-bin/sch_rcrd.pl method="POST" target=bottom>
<input type=hidden name=action value="search_record">    

    <table bgcolor=white align="center" width="487">
        <tr>
            <td width="173">

                <table border=1>
                    <tr>
                        <th>State</th>
                    </tr>
                    <tr>
                        <td><select name=state>
<option value=all>ALL</option>
<option value=AL>Alabama</option>
<option value=AK>Alaska</option>
<option value=AZ>Arizona</option>
<option value=AR>Arkansas</option>
<option value=CA>California</option>
<option value=CO>Colorado</option>
<option value=CT>Connecticut</option>
<option value=DE>Delaware</option>
<option value=FL>Florida</option>
<option value=GA>Georgia</option>
<option value=HI>Hawaii</option>
<option value=ID>Idaho</option>
<option value=IN>Indiana</option>
<option value=IL>Illinois</option>
<option value=IA>Iowa</option>
<option value=KS>Kansas</option>
<option value=KY>Kentucky</option>
<option value=LA>Louisiana</option>
<option value=ME>Maine</option>
<option value=MD>Maryland</option>
<option value=MA>Massachusetts</option>
<option value=MI>Michigan</option>
<option value=MN>Minnesota</option>
<option value=MO>Missouri</option>
<option value=MS>Mississippi</option>
<option value=MT>Montana</option>
<option value=NE>Nebraska</option>
<option value=NV>Nevada</option>
<option value=NH>New Hampshire</option>
<option value=NJ>New Jersey</option>
<option value=NM>New Mexico</option>
<option value=NY>New York</option>
<option value=NC>North Carolina</option>
<option value=ND>North Dakota</option>
<option value=OH>Ohio</option>
<option value=OR>Oregon</option>
<option value=OK>Oklahoma</option>
<option value=PA>Pennsylvania</option>
<option value=RI>Rhode Island</option>
<option value=SC>South Carolina</option>
<option value=SD>South Dakota</option>
<option value=TN>Tennessee</option>
<option value=TX>Texas</option>
<option value=UT>Utah</option>
<option value=VT>Vermont</option>
<option value=VA>Virginia</option>
<option value=WA>Washington State</option>
<option value=DC>Washington DC</option>
<option value=WV>West Virginia</option>
<option value=WI>Wisconsin</option>
<option value=WY>Wyoming</option>

## Canada ##################################

<option value=AB>Alberta</option>
<option value=BC>British Columbia</option>
<option value=LB>Labrador</option>
<option value=MB>Manitoba</option>
<option value=NB>New Brunswick</option>
<option value=NL>Newfoundland and Labrador</option>
<option value=NS>Nova Scotia</option>
<option value=NT>Northwest Territories</option>
<option value=NU>Nunavut</option>
<option value=PE>Prince Edward Island</option>
<option value=ON>Ontario</option>
<option value=QC>Quebec</option>
<option value=SA>Saskatchewan</option>
<option value=YU>Yukon Territory</option>
</select></td>
                    </tr>
                </table>
            </td>
            <td colspan="2" width="304">

                <table border=1 align="center" width="242">
                    <tr>
                        <th width="114">
                            <p align="center">Speedtrap Status</p>
                        </th>
                        <th width="112">Comment Status</th>
                    </tr>
                    <tr>
                        <td width="114">
                            <p align="center"><select name=status>
                                <option value="new">New</option>
                                <option value="all">All</option>
                                <option value="yes">Yes</option>
                                <option value="no">No</option>
</select></p>
                        </td>
                        <td width="112">
                            <p align="center"><select name=comment_sta
+tus>
                                <option value="new">New</option>
                                <option value="all">All</option>
                                <option value="yes">Yes</option>
                                <option value="no">No</option>
</select></p>
                        </td>
                    </tr>
                </table>

            </td>
        </tr>
        <tr>
            <td colspan=1 width="173"><center>
<input type=submit name=action value='Go'></td>
                <td width="230">
                    <p align="center">Sort Key:
&nbsp;
<select name=sort1>
                        <option value="approved" selected>Speedtrap Ap
+proval</option>
                        <option value="comment_approved">Comment Appro
+val</option>
                        <option value="id">ID</option>
</select>
&nbsp;<select name=sortord>
                        <option value="DESC">Descending</option>
                        <option value="ASC">Ascending</option>
                        <input type=hidden name=action value='search_r
+ecord'>
</select>

</p>
                </td>
                <td width="70">
&nbsp;

                </td>
            </tr>
        </table>
    </form>
</body>

      [download]
[reply]
[d/l]

        I'm gonna assume you're using MySQL 3.x, which I think doesn't have subqueries.

        my $q_state          = $dbh->quote($state);
my $q_status         = $dbh->quote($status);
my $q_comment_status = $dbh->quote($comment_status);


my $stmt1_where = "";
$stmt1_where .= " AND Speedtrap.state=$q_state\n"
   if ($state && $state ne 'all');
$stmt1_where .= " AND Speedtrap.approval=$q_status\n"
   if ($status && $status ne 'all');
$stmt1_where .= " AND Comments.approval=$q_comment_status\n"
   if ($comment_status && $comment_status ne 'all');
$stmt1_where .= "\n";

my $stmt1 = <<";";
SELECT DISTINCT
       Speedtrap.id,
       Speedtrap.state,
       Speedtrap.city,
       Speedtrap.discription,
       Speedtrap.approval,
  FROM Speedtrap
  LEFT JOIN Comments
    ON Speedtrap.id = Comments.id
 WHERE 1=1
$stmt1_where
 ORDER BY $sort $sortord
;


my $stmt2_where = "";
$stmt2_where .= " AND Comments.approval=$q_comment_status\n"
   if ($comment_status && $comment_status ne 'all');
$stmt2_where .= "\n";

my $stmt2 = <<";";
SELECT COUNT(*) AS num_comments
  FROM Comments
 WHERE Comments.id = ?
$stmt2_where
 ORDER BY $sort $sortord
;


my $sth1 = $dbh->prepare($stmt1)
|| die("...: $DBI::errstr\n");

my $sth2 = $dbh->prepare($stmt2);
|| die("...: $DBI::errstr\n");

$sth1->execute()
or die("...: $DBI::errstr\n");


...[ print start of page ]...

while (@row = $sth1->fetchrow_array()) {
   my ($id, $state, $city, $desc, $approval) = @row;

   $sth2->execute($id)
   or die("...: $DBI::errstr\n");

   # Should always return exactly one record.
   my ($comment_count) = $sth2->fetchrow_array();

   ...[ print repeated part ]...
}

...[ print bottom of page ]...


$sth2->finish();
$sth1->finish();

        [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom