Miami-Dade Clerk of Courts: Cases

ID	State Case No.
ID	Case ID	Last name	First name	Middle name	Race	Sex
$field
$field
$field	$value	$value	Charges	Dockets	Charges	Dockets
ID	+ Court Case No. + A + D +	State Case No.	Name	Date of Birth	Date Filed	Date Closed	+ Date Filed + A + D +	+ Date Closed + A + D +	Warrant Type	Hearing Date	Hearing Time	Hearing Type	Probation Start	Probation End	+ Probation Start + A + D +	+ Probation End + A + D +	Probation Length	In Jail?	+ In Jail? + A + D +	Released To	Bond Amount	Bond Status	Bond Type	Bond Date	Court Case No.	Defense Attorney	Charges	Dockets
$value	{$row[$field]}	$value	{$row[$field]}	Charges

- Year - '', + 'case_years' => '', 'case_num' => '', 'defendant' => '', 'name' => '', @@ -61,7 +61,9 @@ $request_params = array( 'prob_end_year' => '', ); foreach ( $request_params as $param => $value ) { - if ( isset($_REQUEST[$param]) ) { + if ( isset($_REQUEST[$param]) && is_array($_REQUEST[$param]) ) { + $$param = $_REQUEST[$param]; + } elseif ( isset($_REQUEST[$param]) ) { $$param = trim($_REQUEST[$param]); } } @@ -76,117 +78,149 @@ if ( $docket ) { $select .= "LEFT JOIN dockets ON cases.id = dockets.case_id "; } -$where_parts = array(); +// Two arrays for storing where clauses, one for clauses that need to be joined +// by AND and one for those needing to be joined by OR +$where_parts_and = array(); +$where_parts_or = array(); -if ( $case_year && $case_num && $defendant ) { - $case_year = substr($case_year, -2); - $case_num = str_pad($case_num, 6, '0', STR_PAD_LEFT); - $where_parts[] = "cases.court_case_no = 'F-{$case_year}-{$case_num}-{$defendant}'"; -} elseif ( $case_year && $case_num ) { - $case_year = substr($case_year, -2); - $case_num = str_pad($case_num, 6, '0', STR_PAD_LEFT); - $where_parts[] = "cases.court_case_no LIKE 'F-{$case_year}-{$case_num}%'"; -} elseif ( $case_year && $defendant ) { - $case_year = substr($case_year, -2); - $where_parts[] = "cases.court_case_no LIKE 'F-{$case_year}-%-{$defendant}'"; +if ( $case_years && $case_num && $defendant ) { + foreach ( $case_years as $case_year ) { + $case_year = substr($case_year, -2); + $case_num = str_pad($case_num, 6, '0', STR_PAD_LEFT); + $where_parts_or[] = "cases.court_case_no = 'F-{$case_year}-{$case_num}-{$defendant}'"; + } +} elseif ( $case_years && $case_num ) { + foreach ( $case_years as $case_year ) { + $case_year = substr($case_year, -2); + $case_num = str_pad($case_num, 6, '0', STR_PAD_LEFT); + $where_parts_or[] = "cases.court_case_no LIKE 'F-{$case_year}-{$case_num}%'"; + } +} elseif ( $case_years && $defendant ) { + foreach ( $case_years as $case_year ) { + $case_year = substr($case_year, -2); + $where_parts_or[] = "cases.court_case_no LIKE 'F-{$case_year}-%-{$defendant}'"; + } } elseif ( $case_num && $defendant ) { $case_num = str_pad($case_num, 6, '0', STR_PAD_LEFT); - $where_parts[] = "cases.court_case_no LIKE 'F-%-{$case_num}-{$defendant}'"; -} elseif ( $case_year ) { - $case_year = substr($case_year, -2); - $where_parts[] = "cases.court_case_no LIKE 'F-{$case_year}-%'"; + $where_parts_and[] = "cases.court_case_no LIKE 'F-%-{$case_num}-{$defendant}'"; +} elseif ( $case_years ) { + foreach ( $case_years as $case_year ) { + $case_year = substr($case_year, -2); + $where_parts_or[] = "cases.court_case_no LIKE 'F-{$case_year}-%'"; + } } elseif ( $case_num ) { $case_num = str_pad($case_num, 6, '0', STR_PAD_LEFT); - $where_parts[] = "cases.court_case_no LIKE 'F-%-{$case_num}%'"; + $where_parts_and[] = "cases.court_case_no LIKE 'F-%-{$case_num}%'"; } elseif ( $defendant ) { - $where_parts[] = "cases.court_case_no LIKE 'F-%-%-{$defendant}'"; + $where_parts_and[] = "cases.court_case_no LIKE 'F-%-%-{$defendant}'"; } if ( $name ) { $select .= " LEFT JOIN akas on cases.id = akas.case_id "; - $where_parts[] = "( cases.name LIKE '%{$name}%' OR akas.last_name LIKE '{$name}%' + $where_parts_and[] = "( cases.name LIKE '%{$name}%' OR akas.last_name LIKE '{$name}%' OR akas.first_name LIKE '{$name}%' )"; } if ( $charge ) { - $where_parts[] = "charges.charge LIKE '%${charge}%'"; + $where_parts_and[] = "charges.charge LIKE '%${charge}%'"; } if ( $disposition ) { - $where_parts[] = "charges.disposition LIKE '%{$disposition}%'"; + $where_parts_and[] = "charges.disposition LIKE '%{$disposition}%'"; } if ( $docket ) { - $where_parts[] = "dockets.docket LIKE '%{$docket}%'"; + $where_parts_and[] = "dockets.docket LIKE '%{$docket}%'"; } if ( $prob_given ) { - $where_parts[] = "cases.prob_start_date IS NOT NULL"; + $where_parts_and[] = "cases.prob_start_date IS NOT NULL"; } if ( $prob_still ) { $today = date('Y-m-d'); - $where_parts[] = "'$today' BETWEEN cases.prob_start_date AND cases.prob_end_date"; + $where_parts_and[] = "'$today' BETWEEN cases.prob_start_date AND cases.prob_end_date"; } if ( $prob_per_complete ) { - $where_parts[] = "( DATEDIFF(prob_end_date, prob_start_date) * .{$prob_per_complete} < + $where_parts_and[] = "( DATEDIFF(prob_end_date, prob_start_date) * .{$prob_per_complete} < DATEDIFF(CURDATE(), prob_start_date) AND CURDATE() < prob_end_date )"; } if ( $prob_start_year && $prob_start_month && $prob_start_day ) { - $where_parts[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}' + $where_parts_and[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}' AND MONTH(cases.prob_start_date) = '{$prob_start_month}' AND DAY(cases.prob_start_date) = '{$prob_start_day}'"; } elseif ( $prob_start_year && $prob_start_month ) { - $where_parts[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}' + $where_parts_and[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}' AND MONTH(cases.prob_start_date) = '{$prob_start_month}'"; } elseif ( $prob_start_year && $prob_start_day ) { - $where_parts[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}' + $where_parts_and[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}' AND DAY(cases.prob_start_date) = '{$prob_start_day}'"; } elseif ( $prob_start_month && $prob_start_day ) { - $where_parts[] = "MONTH(cases.prob_start_date) = '{$prob_start_month}' + $where_parts_and[] = "MONTH(cases.prob_start_date) = '{$prob_start_month}' AND DAY(cases.prob_start_date) = '{$prob_start_day}'"; } elseif ( $prob_start_year ) { - $where_parts[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}'"; + $where_parts_and[] = "YEAR(cases.prob_start_date) = '{$prob_start_year}'"; } elseif ( $prob_start_month ) { - $where_parts[] = "MONTH(cases.prob_start_date) = '{$prob_start_month}'"; + $where_parts_and[] = "MONTH(cases.prob_start_date) = '{$prob_start_month}'"; } elseif ( $prob_start_day ) { - $where_parts[] = "DAY(cases.prob_start_date) = '{$prob_start_day}'"; + $where_parts_and[] = "DAY(cases.prob_start_date) = '{$prob_start_day}'"; } if ( $prob_end_year && $prob_end_month && $prob_end_day ) { - $where_parts[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}' + $where_parts_and[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}' AND MONTH(cases.prob_end_date) = '{$prob_end_month}' AND DAY(cases.prob_end_date) = '{$prob_end_day}'"; } elseif ( $prob_end_year && $prob_end_month ) { - $where_parts[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}' + $where_parts_and[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}' AND MONTH(cases.prob_end_date) = '{$prob_end_month}'"; } elseif ( $prob_end_year && $prob_end_day ) { - $where_parts[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}' + $where_parts_and[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}' AND DAY(cases.prob_end_date) = '{$prob_end_day}'"; } elseif ( $prob_end_month && $prob_end_day ) { - $where_parts[] = "MONTH(cases.prob_end_date) = '{$prob_end_month}' + $where_parts_and[] = "MONTH(cases.prob_end_date) = '{$prob_end_month}' AND DAY(cases.prob_end_date) = '{$prob_end_day}'"; } elseif ( $prob_end_year ) { - $where_parts[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}'"; + $where_parts_and[] = "YEAR(cases.prob_end_date) = '{$prob_end_year}'"; } elseif ( $prob_end_month ) { - $where_parts[] = "MONTH(cases.prob_end_date) = '{$prob_end_month}'"; + $where_parts_and[] = "MONTH(cases.prob_end_date) = '{$prob_end_month}'"; } elseif ( $prob_end_day ) { - $where_parts[] = "DAY(cases.prob_end_date) = '{$prob_end_day}'"; + $where_parts_and[] = "DAY(cases.prob_end_date) = '{$prob_end_day}'"; } $where = ''; -if ( $where_parts ) { - foreach ( $where_parts as $part ) { + +if ( $where_parts_and ) { + foreach ( $where_parts_and as $and_part ) { if ( $where ) { - $where .= " AND $part"; + $where .= " AND $and_part"; } else { - $where = "WHERE $part"; + $where = "WHERE $and_part"; } } -} else { +} + +if ( $where_parts_or ) { + // The first element of the OR list should be appended to the existing + // where clause with an AND + if ( $where ) { + $where .= " AND " . array_shift($where_parts_or); + } + // If there are still any where_parts_or left, then join them with OR + if ( count($where_parts_or) ) { + foreach ( $where_parts_or as $or_part ) { + if ( $where ) { + $where .= " OR $or_part"; + } else { + $where = "WHERE $or_part"; + } + } + } +} + +if ( empty($where) ) { echo "You must enter at least one search criteria."; exit; } diff --git a/style.css b/style.css index e06ff81..c762e50 100644 --- a/style.css +++ b/style.css @@ -72,3 +72,5 @@ label { #search_cases #name, #charge, #disposition, #docket { width: 40ex; } + +label { vertical-align: top; } -- cgit v1.2.3 From cfda813afc403615e39cffde902b5f8ad15d9e0a Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Mon, 25 Nov 2013 19:08:14 +0000 Subject: Added ability for user to enter comma separated lists into text boxs, and even negate an element with a dash. --- search.php | 46 +++++++++++++++++++++++++++++++++++++++++----- 1 file changed, 41 insertions(+), 5 deletions(-) (limited to 'search.php') diff --git a/search.php b/search.php index f53e219..fde2fed 100644 --- a/search.php +++ b/search.php @@ -117,20 +117,56 @@ if ( $case_years && $case_num && $defendant ) { if ( $name ) { $select .= " LEFT JOIN akas on cases.id = akas.case_id "; - $where_parts_and[] = "( cases.name LIKE '%{$name}%' OR akas.last_name LIKE '{$name}%' - OR akas.first_name LIKE '{$name}%' )"; + + $names = explode(',', $name); + foreach ( $names as $name ) { + $name = trim($name); + if ( preg_match('/^-(.*)$/', $name, $matches) ) { + $where_parts_and[] = "( cases.name NOT LIKE '%{$matches[1]}%' + OR akas.last_name NOT LIKE '{$matches[1]}%' + OR akas.first_name NOT LIKE '{$matches[1]}%' )"; + } else { + $where_parts_and[] = "( cases.name LIKE '%{$name}%' + OR akas.last_name LIKE '{$name}%' + OR akas.first_name LIKE '{$name}%' )"; + } + } } if ( $charge ) { - $where_parts_and[] = "charges.charge LIKE '%${charge}%'"; + $charges = explode(',', $charge); + foreach ( $charges as $charge ) { + $charge = trim($charge); + if ( preg_match('/^-(.*)$/', $charge, $matches) ) { + $where_parts_and[] = "charges.charge NOT LIKE '%{$matches[1]}%'"; + } else { + $where_parts_and[] = "charges.charge LIKE '%${charge}%'"; + } + } } if ( $disposition ) { - $where_parts_and[] = "charges.disposition LIKE '%{$disposition}%'"; + $dispositions = explode(',', $disposition); + foreach ( $dispositions as $disposition ) { + $disposition = trim($disposition); + if ( preg_match('/^-(.*)$/', $disposition, $matches) ) { + $where_parts_and[] = "charges.disposition NOT LIKE '%{$matches[1]}%'"; + } else { + $where_parts_and[] = "charges.disposition LIKE '%{$disposition}%'"; + } + } } if ( $docket ) { - $where_parts_and[] = "dockets.docket LIKE '%{$docket}%'"; + $dockets = explode(',', $docket); + foreach ( $dockets as $docket ) { + $docket = trim($docket); + if ( preg_match('/^-(.*)$/', $docket, $matches) ) { + $where_parts_and[] = "dockets.docket NOT LIKE '%{$matches[1]}%'"; + } else { + $where_parts_and[] = "dockets.docket LIKE '%{$docket}%'"; + } + } } if ( $prob_given ) { -- cgit v1.2.3 From 2bfa063c9a7444c701d98af50839974b0634ffdb Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Tue, 26 Nov 2013 22:11:30 +0000 Subject: Fix issue with negative search assertions. --- search.php | 15 ++++++++++----- 1 file changed, 10 insertions(+), 5 deletions(-) (limited to 'search.php') diff --git a/search.php b/search.php index fde2fed..e8b5b31 100644 --- a/search.php +++ b/search.php @@ -123,8 +123,11 @@ if ( $name ) { $name = trim($name); if ( preg_match('/^-(.*)$/', $name, $matches) ) { $where_parts_and[] = "( cases.name NOT LIKE '%{$matches[1]}%' - OR akas.last_name NOT LIKE '{$matches[1]}%' - OR akas.first_name NOT LIKE '{$matches[1]}%' )"; + OR cases.id NOT IN ( + SELECT case_id FROM akas + WHERE last_name LIKE '{$matches[1]}%' + OR first_name LIKE '{$matches[1]}%' ) + )"; } else { $where_parts_and[] = "( cases.name LIKE '%{$name}%' OR akas.last_name LIKE '{$name}%' @@ -138,7 +141,7 @@ if ( $charge ) { foreach ( $charges as $charge ) { $charge = trim($charge); if ( preg_match('/^-(.*)$/', $charge, $matches) ) { - $where_parts_and[] = "charges.charge NOT LIKE '%{$matches[1]}%'"; + $where_parts_and[] = "cases.id NOT IN ( SELECT case_id FROM charges WHERE charge LIKE '%{$matches[1]}%' )"; } else { $where_parts_and[] = "charges.charge LIKE '%${charge}%'"; } @@ -150,7 +153,7 @@ if ( $disposition ) { foreach ( $dispositions as $disposition ) { $disposition = trim($disposition); if ( preg_match('/^-(.*)$/', $disposition, $matches) ) { - $where_parts_and[] = "charges.disposition NOT LIKE '%{$matches[1]}%'"; + $where_parts_and[] = "cases.id NOT IN ( SELECT case_id FROM charges WHERE disposition LIKE '%{$matches[1]}%' )"; } else { $where_parts_and[] = "charges.disposition LIKE '%{$disposition}%'"; } @@ -162,7 +165,7 @@ if ( $docket ) { foreach ( $dockets as $docket ) { $docket = trim($docket); if ( preg_match('/^-(.*)$/', $docket, $matches) ) { - $where_parts_and[] = "dockets.docket NOT LIKE '%{$matches[1]}%'"; + $where_parts_and[] = "cases.id NOT IN ( SELECT case_id FROM dockets WHERE docket LIKE '%{$matches[1]}%' )"; } else { $where_parts_and[] = "dockets.docket LIKE '%{$docket}%'"; } @@ -382,6 +385,8 @@ if ( $_GET['page'] && $row_count > $results_per_page ) { echo "
Next Page >>"; } +echo "

$sql

"; + echo << -- cgit v1.2.3 From 917f9f6602659fb5d0a859cb8035f808f7a0ecf5 Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Tue, 26 Nov 2013 23:16:04 +0000 Subject: Added the ability for a user to export search results to a CSV file. --- export.php | 24 ++++++++++++++++++++++++ search.php | 7 +++++++ style.css | 8 ++++++++ 3 files changed, 39 insertions(+) create mode 100644 export.php (limited to 'search.php') diff --git a/export.php b/export.php new file mode 100644 index 0000000..2ceca89 --- /dev/null +++ b/export.php @@ -0,0 +1,24 @@ +prepare($sql); +if ( $st->execute() ) { + readfile($filename); +} else { + $errors = $st->errorInfo(); + print_r($errors); +} + +unlink($filename); + +?> diff --git a/search.php b/search.php index e8b5b31..c2409c5 100644 --- a/search.php +++ b/search.php @@ -279,6 +279,10 @@ echo "New Search"; $st = $dbh->prepare($sql); if ( $st->execute() ) { $row_count = $st->rowCount(); + if ( $row_count == '0' ) { + echo "

No records found.

"; + exit; + } if ( $row_count <= $results_per_page ) { echo "

{$row_count} records found.

"; } elseif ( $_GET['page'] ) { @@ -288,6 +292,9 @@ if ( $st->execute() ) { } else { echo "

Viewing 1 to {$results_per_page} of {$row_count} records found.

"; } + $url_sql = urlencode($sql); + // Give the user an export button + echo "

Export

"; } # Pagination diff --git a/style.css b/style.css index c762e50..5f2c712 100644 --- a/style.css +++ b/style.css @@ -74,3 +74,11 @@ label { } label { vertical-align: top; } + +#export { + margin-bottom: 1ex; +} + +#export a { + color: blue; +} -- cgit v1.2.3 From 544fd291b0f19012bee24b57cd3ccc1924deab8e Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Tue, 26 Nov 2013 23:30:29 +0000 Subject: Dont' show the Next Page>> link if we are already viewing the last of the records. --- search.php | 15 +++++++++------ 1 file changed, 9 insertions(+), 6 deletions(-) (limited to 'search.php') diff --git a/search.php b/search.php index c2409c5..d2fdb71 100644 --- a/search.php +++ b/search.php @@ -275,7 +275,8 @@ if ( $_REQUEST['orderby'] && $_REQUEST['orderdir'] ) { echo "New Search"; -# Get the total row count so we can display it to the user +# Get the total row count so we can display it to the user and deal with +# pagination $st = $dbh->prepare($sql); if ( $st->execute() ) { $row_count = $st->rowCount(); @@ -288,7 +289,11 @@ if ( $st->execute() ) { } elseif ( $_GET['page'] ) { $viewing = ($_GET['page'] - 1) * $results_per_page + 1; $upto = ($_GET['page'] - 1) * $results_per_page + $results_per_page; - echo "

Viewing {$viewing} to {$upto} of {$row_count} records found.

"; + if ( $upto >= $row_count ) { + echo "

Viewing {$viewing} to {$row_count} of {$row_count} records found.

"; + } else { + echo "

Viewing {$viewing} to {$upto} of {$row_count} records found.

"; + } } else { echo "

Viewing 1 to {$results_per_page} of {$row_count} records found.

"; } @@ -385,15 +390,13 @@ HTML; echo ""; -if ( $_GET['page'] && $row_count > $results_per_page ) { +if ( $_GET['page'] && $row_count > $results_per_page && $upto < $row_count ) { $next_page = $_GET['page'] + 1; echo "
Next Page>>"; -} elseif ( $row_count > $results_per_page ) { +} elseif ( $row_count > $results_per_page && $upto < $row_count ) { echo "
Next Page >>"; } -echo "

$sql

"; - echo << -- cgit v1.2.3 From 8502be42068dcb6f89cd4321e6841dbf2056aefa Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Fri, 10 Jan 2014 19:54:00 +0000 Subject: Optimize searched for dockets a little bit. Still not perfect, but vastly better than before. --- search.php | 25 +++++++++++++------------ 1 file changed, 13 insertions(+), 12 deletions(-) (limited to 'search.php') diff --git a/search.php b/search.php index d2fdb71..f15fc4a 100644 --- a/search.php +++ b/search.php @@ -160,18 +160,6 @@ if ( $disposition ) { } } -if ( $docket ) { - $dockets = explode(',', $docket); - foreach ( $dockets as $docket ) { - $docket = trim($docket); - if ( preg_match('/^-(.*)$/', $docket, $matches) ) { - $where_parts_and[] = "cases.id NOT IN ( SELECT case_id FROM dockets WHERE docket LIKE '%{$matches[1]}%' )"; - } else { - $where_parts_and[] = "dockets.docket LIKE '%{$docket}%'"; - } - } -} - if ( $prob_given ) { $where_parts_and[] = "cases.prob_start_date IS NOT NULL"; } @@ -229,6 +217,19 @@ if ( $prob_end_year && $prob_end_month && $prob_end_day ) { $where_parts_and[] = "DAY(cases.prob_end_date) = '{$prob_end_day}'"; } +if ( $docket ) { + $dockets = explode(',', $docket); + foreach ( $dockets as $docket ) { + $docket = trim($docket); + if ( preg_match('/^-(.*)$/', $docket, $matches) ) { + $where_parts_and[] = "NOT EXISTS ( SELECT case_id FROM dockets WHERE docket LIKE '%{$matches[1]}%' AND dockets.case_id = cases.id )"; + } else { + $where_parts_and[] = "dockets.docket LIKE '%{$docket}%'"; + } + } +} + + $where = ''; if ( $where_parts_and ) { -- cgit v1.2.3 From fc605fa4dbe8f5a9af8cd7a20bc45a11a52a0f0e Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Fri, 10 Jan 2014 20:39:33 +0000 Subject: Try to slightly speed up fetching total row count for pagination by using count(*) instead of the normal select. --- search.php | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) (limited to 'search.php') diff --git a/search.php b/search.php index f15fc4a..19bb361 100644 --- a/search.php +++ b/search.php @@ -69,6 +69,7 @@ foreach ( $request_params as $param => $value ) { } $select = "SELECT DISTINCT cases.* FROM cases "; +$select_num_rows = "SELECT DISTINCT count(cases.id) FROM cases "; if ( $charge || $disposition ) { $select .= "LEFT JOIN charges ON cases.id = charges.case_id "; @@ -229,7 +230,6 @@ if ( $docket ) { } } - $where = ''; if ( $where_parts_and ) { @@ -266,6 +266,7 @@ if ( empty($where) ) { } $sql = $select . $where; +$sql_num_rows = $select_num_rows . $where; # If no ordering info was passed then order by court_case_no by default if ( $_REQUEST['orderby'] && $_REQUEST['orderdir'] ) { @@ -278,9 +279,9 @@ echo "New Search"; # Get the total row count so we can display it to the user and deal with # pagination -$st = $dbh->prepare($sql); +$st = $dbh->prepare($sql_num_rows); if ( $st->execute() ) { - $row_count = $st->rowCount(); + $row_count = $st->fetchColumn(); if ( $row_count == '0' ) { echo "

No records found.

"; exit; -- cgit v1.2.3 From ec5e9062a92fec78ee7f01e6623230658b5db595 Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Fri, 10 Jan 2014 20:51:45 +0000 Subject: Forgot to modify with docket and charge joins. --- search.php | 9 ++++++--- 1 file changed, 6 insertions(+), 3 deletions(-) (limited to 'search.php') diff --git a/search.php b/search.php index 19bb361..de29810 100644 --- a/search.php +++ b/search.php @@ -73,10 +73,12 @@ $select_num_rows = "SELECT DISTINCT count(cases.id) FROM cases "; if ( $charge || $disposition ) { $select .= "LEFT JOIN charges ON cases.id = charges.case_id "; + $select_num_rows .= "LEFT JOIN charges ON cases.id = charges.case_id "; } if ( $docket ) { $select .= "LEFT JOIN dockets ON cases.id = dockets.case_id "; + $select_num_rows .= "LEFT JOIN dockets ON cases.id = dockets.case_id "; } // Two arrays for storing where clauses, one for clauses that need to be joined @@ -299,11 +301,12 @@ if ( $st->execute() ) { } else { echo "

Viewing 1 to {$results_per_page} of {$row_count} records found.

"; } - $url_sql = urlencode($sql); - // Give the user an export button - echo "

Export

"; } +// Give the user an export button +$url_sql = urlencode($sql); +echo "

Export

"; + # Pagination if ( $_GET['page'] ) { $start_num = ($_GET['page'] - 1) * $results_per_page; -- cgit v1.2.3 From 5e731b85742e01abafce2bdf4482d288d9d1299b Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Fri, 10 Jan 2014 21:48:45 +0000 Subject: It would appear that where clause order does matter, and significantly, at least in terms of these queries on the dockets table. --- search.php | 28 ++++++++++++++-------------- 1 file changed, 14 insertions(+), 14 deletions(-) (limited to 'search.php') diff --git a/search.php b/search.php index de29810..42b9218 100644 --- a/search.php +++ b/search.php @@ -69,7 +69,7 @@ foreach ( $request_params as $param => $value ) { } $select = "SELECT DISTINCT cases.* FROM cases "; -$select_num_rows = "SELECT DISTINCT count(cases.id) FROM cases "; +$select_num_rows = "SELECT count(DISTINCT cases.id) FROM cases "; if ( $charge || $disposition ) { $select .= "LEFT JOIN charges ON cases.id = charges.case_id "; @@ -234,34 +234,35 @@ if ( $docket ) { $where = ''; -if ( $where_parts_and ) { - foreach ( $where_parts_and as $and_part ) { +if ( $where_parts_or ) { + foreach ( $where_parts_or as $or_part ) { if ( $where ) { - $where .= " AND $and_part"; + $where .= " OR $or_part"; } else { - $where = "WHERE $and_part"; + $where = "WHERE $or_part"; } } } -if ( $where_parts_or ) { - // The first element of the OR list should be appended to the existing +if ( $where_parts_and ) { + // The first element of the AND list should be appended to the existing // where clause with an AND if ( $where ) { - $where .= " AND " . array_shift($where_parts_or); + $where .= " AND " . array_shift($where_parts_and); } - // If there are still any where_parts_or left, then join them with OR - if ( count($where_parts_or) ) { - foreach ( $where_parts_or as $or_part ) { + // If there are still any where_parts_or left, then join them with AND + if ( count($where_parts_and) ) { + foreach ( $where_parts_and as $and_part ) { if ( $where ) { - $where .= " OR $or_part"; + $where .= " AND $and_part"; } else { - $where = "WHERE $or_part"; + $where = "WHERE $and_part"; } } } } + if ( empty($where) ) { echo "You must enter at least one search criteria."; exit; @@ -315,7 +316,6 @@ if ( $_GET['page'] ) { $sql .= " LIMIT 0, $results_per_page"; } - $st = $dbh->prepare($sql); if ( $st->execute() ) { echo << Date: Fri, 7 Feb 2014 16:25:42 +0000 Subject: Allow someone to search by case ID. --- search.php | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'search.php') diff --git a/search.php b/search.php index 42b9218..7dd118b 100644 --- a/search.php +++ b/search.php @@ -43,6 +43,7 @@ $database_fields = array( // Get all the request parameters into local variables for readability $request_params = array( + 'case_id' => '', 'case_years' => '', 'case_num' => '', 'defendant' => '', @@ -86,6 +87,10 @@ if ( $docket ) { $where_parts_and = array(); $where_parts_or = array(); +if ( $case_id ) { + $where_parts_and[] = "id = '$case_id'"; +} + if ( $case_years && $case_num && $defendant ) { foreach ( $case_years as $case_year ) { $case_year = substr($case_year, -2); -- cgit v1.2.3 From 25abc7ff120c4dea3c35203eefcf75c5a319f98c Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Tue, 18 Feb 2014 19:28:48 +0000 Subject: Added ability to search on whether a case is open or closed. --- search.php | 10 +++++++++- 1 file changed, 9 insertions(+), 1 deletion(-) (limited to 'search.php') diff --git a/search.php b/search.php index 7dd118b..d6ab5ed 100644 --- a/search.php +++ b/search.php @@ -50,6 +50,7 @@ $request_params = array( 'name' => '', 'charge' => '', 'disposition' => '', + 'case_closed' => '', 'docket' => '', 'prob_given' => '', 'prob_still' => '', @@ -68,7 +69,6 @@ foreach ( $request_params as $param => $value ) { $$param = trim($_REQUEST[$param]); } } - $select = "SELECT DISTINCT cases.* FROM cases "; $select_num_rows = "SELECT count(DISTINCT cases.id) FROM cases "; @@ -168,6 +168,14 @@ if ( $disposition ) { } } +if ( $case_closed ) { + if ( $case_closed == 'yes' ) { + $where_parts_and[] = " cases.date_closed IS NOT NULL OR cases.date_closed != '' "; + } elseif ( $case_closed == 'no' ) { + $where_parts_and[] = " cases.date_closed IS NULL OR cases.date_closed = '' "; + } +} + if ( $prob_given ) { $where_parts_and[] = "cases.prob_start_date IS NOT NULL"; } -- cgit v1.2.3 From 551c7579b5d818abd5c7452bae1a5077e5fae3d6 Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Tue, 18 Feb 2014 21:55:22 +0000 Subject: Added a special 'between' search that I'll use internally. And also fixed a problem where a MySQL date field can't be compared to an empty string. --- search.php | 10 ++++++++-- 1 file changed, 8 insertions(+), 2 deletions(-) (limited to 'search.php') diff --git a/search.php b/search.php index d6ab5ed..cbbd290 100644 --- a/search.php +++ b/search.php @@ -44,6 +44,7 @@ $database_fields = array( // Get all the request parameters into local variables for readability $request_params = array( 'case_id' => '', + 'between' => '', 'case_years' => '', 'case_num' => '', 'defendant' => '', @@ -91,6 +92,11 @@ if ( $case_id ) { $where_parts_and[] = "id = '$case_id'"; } +if ( $between ) { + list($btwn_start, $btwn_end) = explode(':', $between); + $where_parts_and[] = "court_case_no BETWEEN '$btwn_start' AND '$btwn_end'"; +} + if ( $case_years && $case_num && $defendant ) { foreach ( $case_years as $case_year ) { $case_year = substr($case_year, -2); @@ -170,9 +176,9 @@ if ( $disposition ) { if ( $case_closed ) { if ( $case_closed == 'yes' ) { - $where_parts_and[] = " cases.date_closed IS NOT NULL OR cases.date_closed != '' "; + $where_parts_and[] = "cases.date_closed IS NOT NULL"; } elseif ( $case_closed == 'no' ) { - $where_parts_and[] = " cases.date_closed IS NULL OR cases.date_closed = '' "; + $where_parts_and[] = "cases.date_closed IS NULL"; } } -- cgit v1.2.3 From 8cd53e0e90df2768b930ea8880fd752ef8bd8815 Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Sat, 22 Feb 2014 03:29:53 +0000 Subject: Modify the existing page= query param rather than continually adding new ones. --- search.php | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'search.php') diff --git a/search.php b/search.php index cbbd290..bd60540 100644 --- a/search.php +++ b/search.php @@ -416,7 +416,8 @@ echo ""; if ( $_GET['page'] && $row_count > $results_per_page && $upto < $row_count ) { $next_page = $_GET['page'] + 1; - echo "
Next Page>>"; + $pagination_query_string = preg_replace('/page=\d+/', "page=$next_page", $_SERVER['QUERY_STRING']); + echo "
Next Page>>"; } elseif ( $row_count > $results_per_page && $upto < $row_count ) { echo "
Next Page >>"; } -- cgit v1.2.3 From ffc6a6ce863e04ed8f3f9664f085ed280cddaf67 Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Tue, 11 Mar 2014 23:51:21 +0000 Subject: Fix path to new search now that we are running on dighelaw.com. --- search.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'search.php') diff --git a/search.php b/search.php index bd60540..85a8c31 100644 --- a/search.php +++ b/search.php @@ -297,7 +297,7 @@ if ( $_REQUEST['orderby'] && $_REQUEST['orderdir'] ) { $sql .= " ORDER BY court_case_no DESC"; } -echo "New Search"; +echo "New Search"; # Get the total row count so we can display it to the user and deal with # pagination -- cgit v1.2.3 From b5f551fc9f2e791255d40724962fd035504d39a9 Mon Sep 17 00:00:00 2001 From: Nathan Kinkade Date: Wed, 12 Mar 2014 00:11:47 +0000 Subject: Added an 'In jail?' field. --- index.php | 9 +++++++++ search.php | 9 +++++++++ 2 files changed, 18 insertions(+) (limited to 'search.php') diff --git a/index.php b/index.php index 15531d5..16cc010 100644 --- a/index.php +++ b/index.php @@ -69,6 +69,15 @@ foreach ( range('A', 'Z') as $letter ) {

$row_count records found.

$row_count records found.

AKAs for case {$_GET['case_no']}

Charges for case {$row['court_case_no']}

Charges for case {$_GET['case_no']}

Dockets for case {$row['court_case_no']}

Dockets for case {$_GET['case_no']}

$row_count records found.

$row_count records found.

{$row_count} records found.

Viewing {$viewing} to {$upto} of {$row_count} records found.

Viewing 1 to {$results_per_page} of {$row_count} records found.

No records found.

{$row_count} records found.

Viewing 1 to {$results_per_page} of {$row_count} records found.

Viewing {$viewing} to {$upto} of {$row_count} records found.

Viewing {$viewing} to {$row_count} of {$row_count} records found.

Viewing {$viewing} to {$upto} of {$row_count} records found.

Viewing 1 to {$results_per_page} of {$row_count} records found.

No records found.

Viewing 1 to {$results_per_page} of {$row_count} records found.