I have been trying to implement a search filter query for my table of vfis using React and Laravel with an API. The below code is where the search form resides.
Navbar.js
import React, {useState} from 'react';
import {Link, useHistory} from 'react-router-dom';
import swal from 'sweetalert';
import axios from 'axios';
// import Vfi from '../../components/admin/vfi/Vfi';
function Navbar() {
const history = useHistory();
const logoutSubmit = (e) => {
e.preventDefault();
axios.post(`/api/logout`).then(res => {
if(res.data.status === 200)
{
localStorage.removeItem('auth_token');
localStorage.removeItem('auth_name');
swal("Success",res.data.message,"success");
history.push('/');
}
});
}
var AuthButtons = '';
if(!localStorage.getItem('auth_token'))
{
AuthButtons = (
<ul className="navbar-nav">
<li className="nav-item">
<Link className="nav-link" to="/login">Login</Link>
</li>
<li className="nav-item">
<Link className="nav-link" to="/register">Register</Link>
</li>
<li className="nav-item">
<Link className="nav-link" to="/contact">Contact</Link>
</li>
</ul>
);
}
else
{
AuthButtons = (
<li className="nav-item">
<li><Link className="dropdown-item" onClick={logoutSubmit}>Logout</Link></li>
</li>
);
}
return (
<nav className="sb-topnav navbar navbar-expand navbar-dark bg-mycolor">
<Link className="navbar-brand ps-3" to="/admin">VFI Kenya</Link>
<button className="btn btn-link btn-sm order-1 order-lg-0 me-4 me-lg-0" id="sidebarToggle" href="#!"><i className="fas fa-bars"></i></button>
<form className="d-none d-md-inline-block form-inline ms-auto me-0 me-md-3 my-2 my-md-0">
<div className="input-group">
<input className="form-control" type="text" placeholder="Search for..." aria-label="Search for..." aria-describedby="btnNavbarSearch" />
<button className="btn btn-primary" id="btnNavbarSearch" type="button"><i className="fas fa-search"></i></button>
</div>
</form>
<ul className="navbar-nav ms-auto ms-md-0 me-3 me-lg-4">
<li className="nav-item dropdown">
<Link to="#" className="nav-link dropdown-toggle" id="navbarDropdown" role="button" data-bs-toggle="dropdown" aria-expanded="false">
<i className="fas fa-user fa-fw"></i>
</Link>
<ul className="dropdown-menu dropdown-menu-end" aria-labelledby="navbarDropdown">
<li><Link className="dropdown-item" to="#!">Settings</Link></li>
<li><Link className="dropdown-item" to="#!">Activity Log</Link></li>
<li><hr className="dropdown-divider" /></li>
{AuthButtons}
</ul>
</li>
</ul>
</nav>
);
}
export default Navbar;
This is my laravel code for the controller am using...
VFiController.php
<?php
namespace App\Http\Controllers\API;
use App\Models\Vfi;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Validator;
class VFiController extends Controller
{
public function index()
{
$vfis = Vfi::all();
return response()->json([
'status'=> 200,
'vfis'=>$vfis,
]);
}
public function store(Request $request)
{
$validator = Validator::make($request->all(),[
// 'TelNo' => 'required|regex:/(0)[0-9]{9}/',
'LengthofMembershipinVFi'=> 'required|integer',
'firstName'=> 'required|unique:vfis| |min:2',
'secondName'=> 'required|unique:vfis| |min:2',
'Email'=> 'required|unique:vfis| |email',
]);
if($validator->fails())
{
return response()->json([
'status'=> 422,
'validate_err'=> $validator->messages(),
]);
}
else
{
$vfi = new Vfi() ;
$vfi->Gender = $request->input('Gender') ;
$vfi->firstName = $request->input('firstName') ;
$vfi->secondName = $request->input('secondName') ;
$vfi->MaritalStatus = $request->input('MaritalStatus') ;
$vfi->TelNo= $request->input('TelNo') ;
$vfi->TownofResidence = $request->input('TownofResidence') ;
$vfi->Fellowshipifattendingany = $request->input('Fellowshipifattendingany') ;
$vfi->MinistryInvolvedin= $request->input('MinistryInvolvedin') ;
$vfi->ChurchYouattend = $request->input('ChurchYouattend') ;
$vfi->Profession = $request->input('Profession') ;
$vfi->LengthofMembershipinVFi = $request->input('LengthofMembershipinVFi') ;
$vfi->Email = $request->input('Email') ;
$vfi->save();
return response()->json([
'status'=> 200,
'message'=>'Thank you for your response!',
]);
}
}
public function edit($id)
{
$vfi = Vfi::find($id);
if($vfi)
{
return response()->json([
'status'=> 200,
'vfi' => $vfi,
]);
}
else
{
return response()->json([
'status'=> 404,
'message' => 'No vfi ID Found',
]);
}
}
public function update(Request $request, $id)
{
$validator = Validator::make($request->all(),[
// 'TelNo' => 'required|regex:/(0)[0-9]{9}/',
//'LengthofMembershipinVFi'=> 'required|integer',
//'firstName'=> 'required|unique:vfis| |min:2',
//'secondName'=> 'required|unique:vfis| |min:2',
//'Email'=> 'required|unique:vfis| |email',
]);
if($validator->fails())
{
return response()->json([
'status'=> 422,
'validationErrors'=> $validator->messages(),
]);
}
else
{
$vfi = Vfi::find($id);
if($vfi)
{
$vfi = Vfi::find($id);
$vfi->Gender = $request->input('Gender') ;
$vfi->firstName = $request->input('firstName') ;
$vfi->secondName = $request->input('secondName') ;
$vfi->MaritalStatus = $request->input('MaritalStatus') ;
$vfi->TelNo= $request->input('TelNo') ;
$vfi->TownofResidence = $request->input('TownofResidence') ;
$vfi->Fellowshipifattendingany = $request->input('Fellowshipifattendingany') ;
$vfi->MinistryInvolvedin= $request->input('MinistryInvolvedin') ;
$vfi->ChurchYouattend = $request->input('ChurchYouattend') ;
$vfi->Profession = $request->input('Profession') ;
$vfi->LengthofMembershipinVFi = $request->input('LengthofMembershipinVFi') ;
$vfi->Email = $request->input('Email') ;
$vfi->save();
return response()->json([
'status'=> 200,
'message'=>'Updated Successfully',
]);
}
else
{
return response()->json([
'status'=> 404,
'message' => 'No Vfi ID Found',
]);
}
}
}
public function destroy($id)
{
$vfi = Vfi::find($id);
if($vfi)
{
$vfi->delete();
return response()->json([
'status'=> 200,
'message'=>'Vfi Deleted Successfully',
]);
}
else
{
return response()->json([
'status'=> 404,
'message' => 'No Vfi ID Found',
]);
}
}
}
And my api.php from laravel
Route::get('vfis', [VFiController::class, 'index']);
Route::post('/add-vfi', [VFiController::class, 'store']);
Route::get('/edit-vfi/{id}', [VFiController::class, 'edit']);
Route::put('update-vfi/{id}', [VFiController::class, 'update']);
Route::delete('delete-vfi/{id}', [VFiController::class, 'destroy']);
Could someone show me how I can make the search form query and filter data from my table vfis where the data is already presented on the React end using an API
CodePudding user response：
Based on @Haris solution, which might not work with MySql. To handle case-sensitive problem, we can use LOWER
public function search($key)
{
return Vfi::where('LOWER(name)','LIKE',"%".$key."%")
->orWhere('LOWER(Gender)','LIKE',"%".$key."%")
->orWhere('LOWER(firstName)','LIKE',"%".$key."%")
->orWhere('LOWER(secondName)','LIKE',"%"/$key."%")
->orWhere('LOWER(TownofResidence)','LIKE',"%".$key."%")
->orWhere('LOWER(ChurchYouattend)','LIKE',"%".$key."%")
->get();
}
CodePudding user response：
If you want to search in all fields, you can alter your search function like
public function search($key)
{
return Vfi::where('name','ILIKE',"%$key%")
->orWhere('Gender','ILIKE',"%$key%")
->orWhere('firstName','ILIKE',"%$key%")
->orWhere('secondName','ILIKE',"%$key%")
->orWhere('TownofResidence','ILIKE',"%$key%")
->orWhere('ChurchYouattend','ILIKE',"%$key%")
->get();
}
You can add all the fields where you want to search your $key. Remember ILIKE is for case sensitive problems in sql, But it will not work for MySQL. You can use the LOWER() function for MySQL,
->orWhere('LOWER(ChurchYouattend)','LIKE',"%$key%")
And another best approach to filter the result is through laravel pipeline. You can see how to use pipeline here.