Re^2: Double Database Search and Sort

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]

Comment on Re^2: Double Database Search and Sort Download Code

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

Replies are listed 'Best First'.

Re^3: Double Database Search and Sort
by ikegami (Patriarch) on Sep 25, 2004 at 04:58 UTC

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]