Trying to make my search box work with my database. I have my first set of checkboxes working (type_code), but they pull from a separate joined table and are various int. values. I can't get the second set of checkboxes to pull (ws_review). Ws_review is a field in my main table and is a tinyin 1/0
Searchbar.php
<?php if (isset($error)) echo "<p class=\"bg-warning\">", $error, "</p>" ?>
<form method="POST" action="/publications/searchresults.php">
<div class="form-group">
<label for="type_code[]">TYPE</label>
<br />
<label>
<input type="checkbox" name="type_code[]"value="10" <?php if (isset($type_code_ar) && in_array($row->code, $type_code_ar)) echo "checked" ?>>
Reports/Publications</label>
<br />
<label>
<input type="checkbox" name="type_code[]"value="11" <?php if (isset($type_code_ar) && in_array($row->code, $type_code_ar)) echo "checked" ?>>
Op-eds/Statements</label>
<br />
<label>
<input type="checkbox" name="type_code[]"value="12" <?php if (isset($type_code_ar) && in_array($row->code, $type_code_ar)) echo "checked" ?>>
Congressional Testimonies</label>
<br />
<label>
<input type="checkbox" name="type_code[]"value="14" <?php if (isset($type_code_ar) && in_array($row->code, $type_code_ar)) echo "checked" ?>>
News Releases</label>
<br />
<label>
<input type="checkbox" name="type_code[]"value="21" <?php if (isset($type_code_ar) && in_array($row->code, $type_code_ar)) echo "checked" ?>>
Tool/Templates</label>
<br />
<label>
<input type="checkbox" name="type_code[]"value="22" <?php if (isset($type_code_ar) && in_array($row->code, $type_code_ar)) echo "checked" ?>>
External Resources</label>
<br />
<label>
<input type="checkbox" name="type_code[]"value="13" <?php if (isset($type_code_ar) && in_array($row->code, $type_code_ar)) echo "checked" ?>>
In the News</label>
<br />
</div>
<div class="form-group">
<label for="workstreams[]">TYPE</label>
<br />
<label>
<input type="checkbox" name="workstreams[]"value="1" <?php if(isset($ws_review_ar) && in_array($ws_review == 1)) echo "checked" ?>>
Agency Review teams</label>
<br />
<label>
Searchresults.php:
// declare criteria variables
$sort = null; // default to title sort
$recordstart = 0;
$pagesize = 100;
$type_code_ar = NULL;
$ws_review = NULL;
$date_fr = NULL;
$date_to = NULL;
$keywords_ar = NULL;
$_COVERS_URL = "/publications/covers/";
// validate sort
if (isset($_REQUEST['sort']) && is_numeric($_REQUEST['sort']))
{
$sort = $_REQUEST['sort'];
if ($sort < 0 || $sort > 3)
$sort = null;
}
// validate recordstart
if (isset($_REQUEST['recordstart']) && is_numeric($_REQUEST['recordstart']))
$recordstart = intval($_REQUEST['recordstart']);
// validate pagesize
if (isset($_REQUEST['pagesize']) && is_numeric($_REQUEST['pagesize']))
$pagesize = $_REQUEST['pagesize'];
// validate type codes
if (isset($_REQUEST['type_code']))
{
foreach ($_REQUEST['type_code'] as $type_code)
{
if (is_numeric($type_code))
$type_code_ar[] = $type_code;
}
}
// validate date from
$mm_fr = "";
$dd_fr = "";
$yyyy_fr = "";
if (isset($_REQUEST['mm_fr']))
$mm_fr = trim($_REQUEST['mm_fr']);
if (isset($_REQUEST['dd_fr']))
$dd_fr = trim($_REQUEST['dd_fr']);
if (isset($_REQUEST['yyyy_fr']))
$yyyy_fr = trim($_REQUEST['yyyy_fr']);
if (!empty($mm_fr) || !empty($dd_fr) || !empty($yyyy_fr))
{
if (is_numeric($mm_fr) && is_numeric($dd_fr) && is_numeric($yyyy_fr) && checkdate($mm_fr, $dd_fr, $yyyy_fr))
{
$date_fr = mktime(0, 0, 0, $mm_fr, $dd_fr, $yyyy_fr);
}
else
{
$error = "Starting date in publication date range is invalid.";
require 'index.php';
return;
}
}
// validate date to
$mm_to = "";
$dd_to = "";
$yyyy_to = "";
if (isset($_REQUEST['mm_to']))
$mm_to = trim($_REQUEST['mm_to']);
if (isset($_REQUEST['dd_to']))
$dd_to = trim($_REQUEST['dd_to']);
if (isset($_REQUEST['yyyy_to']))
$yyyy_to = trim($_REQUEST['yyyy_to']);
if (!empty($mm_to) || !empty($dd_to) || !empty($yyyy_to))
{
if (is_numeric($mm_to) && is_numeric($dd_to) && is_numeric($yyyy_to) && checkdate($mm_to, $dd_to, $yyyy_to))
{
$date_to = mktime(0, 0, 0, $mm_to, $dd_to, $yyyy_to);
}
else
{
$error = "Ending date in publication date range is invalid.";
require 'index.php';
return;
}
}
// validate range combination
if ($date_fr != NULL && $date_to == NULL)
$date_to = $date_fr;
else if ($date_fr == NULL && $date_to != NULL)
$date_fr = $date_to;
// validate keywords
if (array_key_exists('keywords', $_REQUEST))
{
$keywords = mysql_escape_string(trim($_REQUEST['keywords']));
if (strlen($keywords) > 0)
$keywords_ar = preg_split("/[\s,]+/", $keywords);
}
// start query
require '../_db.php';
$query_search_count = "SELECT count(*) cnt
FROM publications P
LEFT JOIN lookup LT ON P.type_code = LT.code
LEFT JOIN lookup LFT ON P.file_type_code = LFT.code
INNER JOIN publication_topics PT ON PT.publication_id = P.id
WHERE P.publish_flag = 1 AND PT.topic_code = 20";
$query_search = "SELECT P.id,P.pub_type,P.title,P.summary,P.filename,P.thumbnail_filename,
P.file_size,P.publication_url,P.publication_name,
DATE_FORMAT(P.publication_date, '%m/%d/%Y') AS publication_date_text,
LT.short_desc AS type_text,
LFT.short_desc AS file_type_text,
LFT.long_desc AS file_type_logo
FROM publications P
LEFT JOIN lookup LT ON P.type_code = LT.code
LEFT JOIN lookup LFT ON P.file_type_code = LFT.code
INNER JOIN publication_topics PT ON PT.publication_id = P.id
WHERE P.publish_flag = 1 AND PT.topic_code = 20";
if ($type_code_ar != NULL && count($type_code_ar) > 0)
{
$query_search_count .= " AND P.type_code IN (" . implode(",", $type_code_ar) . ")";
$query_search .= " AND P.type_code IN (" . implode(",", $type_code_ar) . ")";
}
if ($ws_review != NULL && count($ws_review_ar) > 0)
{
$query_search_count .= " AND P.ws_review == 1";
$query_search .= " AND P.ws_review == 1";
}
if ($date_fr != NULL && $date_to != NULL)
{
$query_search_count .= " AND P.publication_date BETWEEN '" . date(DATE_ATOM, $date_fr - 1) . "' AND '" . date(DATE_ATOM, $date_to + 1) . "'";
$query_search .= " AND P.publication_date BETWEEN '" . date(DATE_ATOM, $date_fr - 1) . "' AND '" . date(DATE_ATOM, $date_to + 1) . "'";
}
// Keyword Filter:
if ($keywords_ar != NULL && count($keywords_ar) > 0)
{
foreach ($keywords_ar as $value) {
if($value != Null && $value !='')
{
$query_search_count .= " AND (P.title LIKE '%" . $value . "%'" ;
$query_search_count .= " OR P.summary LIKE '%" . $value . "%'";
$query_search_count .= " OR P.overview LIKE '%" . $value . "%')";
$query_search .= " AND (P.title LIKE '%" . $value . "%'" ;
$query_search .= " OR P.summary LIKE '%" . $value . "%'";
$query_search .= " OR P.overview LIKE '%" . $value . "%')";
}
}
}
Aucun commentaire:
Enregistrer un commentaire