Right, i have a database table that has details on cases.
Now the user wants to be able to search for these cases by things like name, year , judgement data ect..
Now i have a working example, however this only works when you search by name in the name field and by year in the year field. You cant for example search for name in the year field.
So i want to have a main search field that you can input any string and it will result a row from the database where the search term relates to any column.
controller:
public function actionIndex()
{
$model = new Cases;
$searchModel = new CaseSearch();// This is the data to be displayed in gridview
$newsearchModel = new CaseSearch();
$mainCategory = Category::find()->all();// This is grabing the first category list to populate first drop down
$dataProvider = $newsearchModel->search(Yii::$app->request->queryParams);// This is the filter options for the gridview
$request = Yii::$app->request;
$post = $request->post();//$post now takes place of normal $_POST array;
/*
* This deals with the post values when the drop down lists are chosen and submitted via POST
*/
if($newsearchModel->load(Yii::$app->request->post())){
$count = Yii::$app->db->createCommand('SELECT COUNT(*) FROM cases')->queryScalar();
$dataProvider = new SqlDataProvider([
'sql' => 'SELECT * FROM cases',
'totalCount' => $count,
'sort' => [
'attributes' => [
'case_id',
'name',
/*'name' => [
'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC],
'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC],
'default' => SORT_DESC,
'label' => 'Name',
],*/
],
],
'pagination' => [
'pageSize' => 20,
],
]);
// get the user records in the current page
$models = $dataProvider->getModels();
//This now renders the index page with new set of filters corresponding to the categories picked.
// Also sends back the first category_id to auto populate first category.
return $this->render('index', [
'searchModel' => $searchModel,
'newsearchModel' => $newsearchModel,
'dataProvider' => $dataProvider,
'mainCategory' => $mainCategory,
//'subcategory' => $subcategory,
//'childcategory' => $childcategory,
'model' => $models,
//'model' => $model,
//'category_id' => $category_id,
//'subcategory_id' => $subcategory_id,//for debugging
//'childcategory_id' => $childcategory_id,//for debugging
]);
}
Cases model that relates the cases table in database
<?php
namespace app\models;
use Yii;
/**
* This is the model class for table "Cases".
*
* @property integer $case_id
* @property string $name
* @property string $judgement_date
* @property integer $year
* @property string $neutral_citation
* @property string $all_ER
* @property string $building_law_R
* @property string $const_law_R
* @property string $const_law_J
* @property string $CILL
* @property string $adj_LR
*/
class Cases extends \yii\db\ActiveRecord
{
public $citation;
public $mainSearch;
/**
* @inheritdoc
*/
public static function tableName()
{
return 'Cases';
}
/**
* Returns the first Citation which is populated
* @return String
*/
public function getCitation()
{
//Check each citation and return fist match
if (!empty($this->neutral_citation)) {
return $this->neutral_citation;
}
if (!empty($this->all_ER)) {
return $this->all_ER;
}
if (!empty($this->building_law_R)) {
return $this->building_law_R;
}
if (!empty($this->const_law_R)) {
return $this->const_law_R;
}
if (!empty($this->const_law_J)) {
return $this->const_law_J;
}
if (!empty($this->CILL)) {
return $this->CILL;
}
if (!empty($this->adj_LR)) {
return $this->adj_LR;
}
return "";
}
/**
* @inheritdoc
*/
public function rules()
{
return [
[['name', 'judgement_date', 'year', 'neutral_citation', 'all_ER', 'building_law_R', 'const_law_R', 'const_law_J', 'CILL', 'adj_LR'], 'required'],
[['judgement_date'], 'safe'],
[['year'], 'integer'],
[['name', 'neutral_citation', 'all_ER', 'building_law_R', 'const_law_R', 'const_law_J', 'CILL', 'adj_LR'], 'string', 'max' => 150]
];
}
/**
* @inheritdoc
*/
public function attributeLabels()
{
return [
'case_id' => 'Case ID',
'name' => 'Name',
'judgement_date' => 'Judgement Date',
'year' => 'Year',
'neutral_citation' => 'Neutral Citation',
'all_ER' => 'All Er',
'building_law_R' => 'Building Law R',
'const_law_R' => 'Const Law R',
'const_law_J' => 'Const Law J',
'CILL' => 'Cill',
'adj_LR' => 'Adj Lr',
];
}
}
CaseSearch model to search the cases from case Model
class CaseSearch extends Cases
{
public $category;
public $subcategory;
public $childcategory;
/**
* @inheritdoc
*/
public function rules()
{
return [
[['case_id', 'year'], 'integer'],
[['name', 'judgement_date', 'neutral_citation', 'all_ER', 'building_law_R', 'const_law_R', 'const_law_J', 'CILL', 'adj_LR'], 'safe'],
];
}
/**
* @inheritdoc
*/
public function scenarios()
{
// bypass scenarios() implementation in the parent class
return Model::scenarios();
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)//This is only used by the search box's NOT THE DROP DOWN CATEGORIES
{
$query = Cases::find();//find all cases
$dataProvider = new ActiveDataProvider([
'query' => $query,
]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
$query->andFilterWhere([
'case_id' => $this->case_id,
'judgement_date' => $this->judgement_date,
'year' => $this->year,
]);
// This is different to the above, as in it filters where a string may be part of the result.
//Example:: SHOW * FROM table WHERE `name` LIKE $this->name;
$query->andFilterWhere(['like', 'name', $this->name])
->andFilterWhere(['like', 'neutral_citation', $this->neutral_citation])
->andFilterWhere(['like', 'all_ER', $this->all_ER])
->andFilterWhere(['like', 'building_law_R', $this->building_law_R])
->andFilterWhere(['like', 'const_law_R', $this->const_law_R])
->andFilterWhere(['like', 'const_law_J', $this->const_law_J])
->andFilterWhere(['like', 'CILL', $this->CILL])
->andFilterWhere(['like', 'adj_LR', $this->adj_LR]);
return $dataProvider;//return the filters
}
index.php
table style="border:1px solid black;">
<tr style="border:1px solid black;">
<th>Name</th>
<th>Citation</th>
<th>Chapter No(s)</th>
</tr>
<?= ListView::widget([
'dataProvider' => $dataProvider,
'itemOptions' => ['class' => 'col-xs-6 col-sm-3'],
'itemView' => 'list',
]);?>
</table>
the rest of the table is rendered here
<tr>
<td><?= $model->name ?></td>
<td><?= $model->getCitation() ?></td>
<td>@chapterno</td>
</tr>
now the search form is rendered here
<div class="cases-search">
<?php $form = ActiveForm::begin([
'action' => ['index'],
'method' => 'get',
]); ?>
<?= $form->field($newsearchModel, 'mainSearch') ?>
<?= $form->field($newsearchModel, 'name') ?>
<?= $form->field($newsearchModel, 'judgement_date') ?>
<?= $form->field($newsearchModel, 'year') ?>
<div class="form-group">
<?= Html::submitButton('Search', ['class' => 'btn btn-primary']) ?>
<?= Html::resetButton('Reset', ['class' => 'btn btn-default']) ?>
</div>
<?php ActiveForm::end(); ?>
so when you search the mainSearch i want it to populate the data in the list view with any record that has any thing related to it
