Level 29
Which output do you get in your console from this log console.log(sut); ?
Mar 29, 2024
13
Level 1
Passing DB Query via Ajax to Populate Options Based on Previous Selection
I am new at this and I could be missing something very basic. I have two select boxed in my view. The first 'select' I populate via a query in my controller. I have another query in that same function that gets data for my second 'select' to be populated based on whatever is selected in the first box via Ajax.
Route:
Route::get( 'qryStructures_start', [\App\Http\Controllers\FieldStructures::class, 'structuresStart'] )
Controller, at the end of my SQL statement that I need to populate the second 'select' box:
$ajaxDataSql = DB::getPdo()->prepare($ajaxDataSql);
$ajaxDataSql->setFetchMode(\PDO::FETCH_OBJ);
$ajaxDataSql->execute([]);
$ajaxData = collect($ajaxDataSql->fetchAll());
$ajaxData = response()->json($ajaxData);
If I dump $ajaxData I get data like this:
+headers: Symfony\Component\HttpFoundation\ResponseHeaderBag {#66144 ▶}
#content: "[{"Site":"5MT1825","ESR_SUcat":"Kivas","ESR_SUcatdesc":"kivas.","SUDescCode":10},{"Site":"5MT1825","ESR_SUcat":"Rooms","ESR_SUcatdesc":"rooms.","SUDescCode":20} ▶"
#version: "1.0"
#statusCode: 200
#statusText: "OK"
#charset: null
#data: "[{"Site":"5MT1825","ESR_SUcat":"Kivas","ESR_SUcatdesc":"kivas.","SUDescCode":10},{"Site":"5MT1825","ESR_SUcat":"Rooms","ESR_SUcatdesc":"rooms.","SUDescCode":20} ▶"
#callback: null
#encodingOptions: 0
+original: Illuminate\Support\Collection {#66153 ▶}
+exception: null
or if I use return response()->json($ajaxData); it just shows regular json on the view when I load it.
In my view I have:
<select id="site" name="site[]" size="5" multiple>
@foreach( $selectSite as $site)
<option value="{{ $site->Site }}">{{ $site->SiteName }} ({{ $site->Site }})
@endforeach()
</select>
ajax:
$('#site').change(function(){
var siteID = $(this).val();
if(siteID){
$.ajax({
type:"get",
data:"{{url('ResearchReports/ResearchDatabase/field/qryStructures_start)}}"+ siteID,
success:function(sut)
{
console.log(sut);
if(sut)
{
$("#suTyp").empty();
$("#suTyp").append('<option>Select Study Unit Type</option>');
$.each(sut,function(key,value){
$("#suTyp").append('<option value="'+key+'">'+value+'</option>');
});
}
}
});
}
});
I am new so not too sure what I am missing but the next select box doesn't populate with anything other than "Select Study Unit Type" and I just get the view code in my console. Can someone point me in the right direction?
Thanks!
Level 1
@gych I just get the view code im my console.
blah
jquery.min.js:2 Uncaught TypeError: Cannot use 'in' operator to search for 'length' in <!DOCtype html>
<!--[if IEMobile 7 ]> <html class="no-js iem7"> <![endif]-->
<!--[if (gt IEMobile 7)|!(IEMobile)]><!--> <html class="no-js"> <!--<![endif]-->
<head>
<meta charset="utf-8" />
<meta content="IE=edge,chrome=1" http-equiv="X-UA-Compatible" />
<meta name="description" content="">
blah blah
Level 29
@stanhook Ok in the controller you're propably returning the view instead of json data.
Can you share the controller method with the part where you return the data after fetching the $ajaxData ?
And what is the SQL query you are using?
Level 1
@gych My Controller:
public function structuresStart(Request $request)
{
$ajaxDataSql = " SELECT
tblSU.Site,
tlkpSUDesc.ESR_SUcat,
tlkpSUDesc.ESR_SUcatdesc,
tlkpSUDesc.SUDescCode
FROM
tlkpSUDesc
GROUP BY
tblSU.Site,
tlkpSUDesc.ESR_SUcat,
tlkpSUDesc.ESR_SUcatdesc,
tlkpSUDesc.ESR_SUcatdisp
HAVING
$site tlkpSUDesc.ESR_SUcat IS NOT NULL
ORDER BY
tblSU.Site + 0,
tlkpSUDesc.ESR_SUcatdisp,
tlkpSUDesc.ESR_SUcat
DESC";
$ajaxDataSql = DB::getPdo()->prepare($ajaxDataSql);
$ajaxDataSql->setFetchMode(\PDO::FETCH_OBJ);
$ajaxDataSql->execute([]);
$ajaxData = collect($ajaxDataSql->fetchAll());
$ajaxData = response()->json($ajaxData);
return view( 'ResearchReports.ResearchDatabase.field.qryStructures_start' )
->with( 'selectSite', $selectSite )
->with( 'studyUnit', $studyUnit );
}
There are a bunch of other functions in this controller, not just this one.
Thanks for the help!
Level 29
@stanhook You are returning a view but you should return your data as json instead.
Also add dataType: 'json', to your Ajax get request
Level 1
@gych Okay, I see that but I guess I am not sure what to do. I am thinking I need to take this out of the function that is displaying data from the DB and create a new one with the code for Ajax. Then create a route for that function. Any code help you can provide that might help me get each piece started would be appreciated.
Level 1
I probably should have mentioned, this is an existing site and it is currently getting data from the database via a form in this view and displays the results on a page. I am now trying to make it so the options boxes are populated based on the previous selection.
Level 47
You can create another end point. If you can not change the url, you can redirect if the request excepts json (api) via middleware and redirect internally (serverside) the to correct endpoint you just created.
Quick search in the docs: https://laravel.com/docs/11.x/errors#rendering-exceptions-as-json
Even though it is for error handling, use it to return json end point or view
Level 1
@jekinney I am new but I will take a look and see what i can figure out from the docs. But I have already gotten in there and went down a rabbit trail or two. I cannot change the URL and I am not sure that I need something outside of Laravel. In the high overview in my head, I just need to make a DB call and populate the box based on the previous choice. Any help pointing to maybe some other docs or tutorial that would help me make sense of it is greatly appreciated.
Level 1
I am still stuck. I have a single controller for this page and I need to populate the options boxes with information from the DB based on the previous selection using Ajax on the same page. The queries work. I am just having trouble with the Ajax part as mentioned above. Thanks for the help!
Level 29
@stanhook Is the ajax request using the same route and method as the initial web request that loads the page?
Level 1
@gych Yeah. I understand a little now about what you mean. But since it is staying on the same lage what would my Route be? I listed my code in the OP (Controller, View and Route) but here is the Route here:
Route::get( 'qryStructures_start', [\App\Http\Controllers\FieldStructures::class, 'structuresStart'] )
I did have my Ajax code in the structuresStart function but I can place it in its own.
Thanks for the help. I only seem to be going around in circles.
Level 6
I think you could create a new route just for the ajax request to populate the second select.
Then, on the change event of the first select, call a function which makes that request to populate the second select. The new route should only return the json data.
Please or to participate in this conversation.