in reply to Double Database Search and Sort

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]

Replies are listed 'Best First'.
Re^2: Double Database Search and Sort
by awohld (Hermit) on Sep 25, 2004 at 04:22 UTC
    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]

In Section Seekers of Perl Wisdom