Using PHPMaker2021 I’ve been trying to:
A) Make Ajax call with multiple values and data types from client event to get data;
B) Call a Stored Procedure/Function on server side to get the data needed to return in the Ajax call.
I’ve succeeded in Ajax calls and calling Stored Procedures, but not in calling Stored Functions.
Below is a summary of my code and experiences, which I hope will help others who are new to this.
On Client Table Specific Startup script add:
$(“input[data-field=‘x_timeslot_name’]”).change(function() { //This format is critical: input[data-field=‘x_timeslot_name’]“)
$.get(ew.getApiUrl([“getWeeklyDatesCount”, $(this).fields(“slot_date_start”).toDate().format(‘YYYY-MM-DD’), $(this).fields(“slot_date_end”).toDate().format(‘YYYY-MM-DD’), $(this).fields(“day_of_week”).value(), $(this).fields(“skip_holidays”).value() ? 1 : 0]), function(res) {
if (res) { // ^^^^ date fields must be converted to toDate().format(‘YYYY-MM-DD’) to make MYSQL compatible and locale independent. Uses Moment object!
console.log(res); //<== use this for debugging res values and messages!
$(”#x_repeat_count").val(res); //Only this format works
//$(this).fields(“repeat_count”).value(res); // This format does not work!
}
});
});
I hope the variable names are self-evident. Comments are critical. Date field must be converted as above to string. If they contain “/” as in “2020/12/05” then the “/” will mess up the Ajax call. This method is independent of locale and timezone. Do not use toISOString() as it changes dates in correcting for time-zone!
The field skip_holidays is boolean. JS treats boolean False as Null. So you have to convert it to number using " ? 1 : 0" as above, otherwise Ajax call will fail. All fields must convert to string forms which are URL safe.
Use console.log(res); to get details of error messages on res. Press F12 to open console in Chrome. This can save you lots of trouble in debugging.
On the Server side in Global API Action insert the following:
$app->get(‘/getWeeklyDatesCount/{start_date}/{end_date}/{weekday_num}/{filter_holiday}’, function ($request, $response, $args) {
$start_date = $args[“start_date”] ?? null; // Get input values
$end_date = $args[“end_date”] ?? null;
$weekday_num = $args[“weekday_num”] ?? 0;
$filter_holiday = $args[“filter_holiday”] ?? 1;
//echo “Create slots from $start_date to $end_date, day_of_week: $weekday_num, filter_holiday $filter_holiday.
”; // Use this kind of code for debugging
//DO NOT USE console.log(“something”) anywhere on server-side code. While the message does get logged in text file, the $response code gets corrupted and always returns Server Error!
if ($start_date !== null) {
//must use ExecuteUpdate for all PROCEDURE calls. It is also critical that date fields must have single quotes around, though not needed for number/string fields
$response = $response->write( ExecuteUpdate(“CALL get_weekly_dates_count_proc(‘$start_date’,‘$end_date’,$weekday_num,$filter_holiday);”) );
//$response = $response->write( ExecuteScalar(“SELECT COUNT(*) FROM calendar WHERE (calendar_date BETWEEN ‘$start_date’ AND ‘$end_date’) AND (day_of_week=$weekday_num) AND ((NOT $filter_holiday) or (NOT is_holiday));”) ); // <== use this as alternative to calling Stored Function to get data from server
}
return $response; // Return Psr\Http\Message\ResponseInterface object
});
Format for multi-variable calls in Ajax is as above. Did not find any such example in Help pages or on the BB, until Arbei give me this code.
All data coming in from Ajax is string. Make sure to clean it using “?? default;” as above.
If your SELECT string is in double quotes, then PHP will automatically replace $variables by their actual string values. But for dates you HAVE to use single quotes around as they contain ‘-’ which can confuse SQL.
You must use ExecuteUpdate() for any calls to Stores PROCEDURES.
Stored FUNCTION calls require two calls as indicated by Sangnandar in previous reply. But I have not solved this yet.
So if you desperately need a function value returning, you can use a workaround by calling a PROCEDURE using ExecuteScalar or ExecuteRow or ExecuteRows. Sample code for ExecuteScalar is given above. In practice you can manage to do workarounds to avoid need for Functions in most cases.
ExecuteScalar returns a single value which you can use as is, both on server side and on the Ajax return on client side (see Client code as above).
ExecuteRows is more complicated. Sample below is from Server side (PHP) Row_Inserting event code. You can adapt for the Ajax event and make equivalent on Client side in JS. I cannot give sample here as I did not tryr it on Client side.
$rows = ExecuteRows(“SELECT calendar_date FROM calendar WHERE (DATE(calendar_date) BETWEEN DATE('”.date_format($slot_date_start,“Y-m-d”).“‘) AND DATE(’”.date_format($slot_date_end,“Y-m-d”).“')) AND (day_of_week=$day_of_week) AND ((NOT $skip_holidays) or (NOT is_holiday));”);
The date_format() was needed in this query because the format needed to be converted to match MySQL. This is not needed if you use the dates in the Ajax event as we already converted before sending in Ajax. But I left this code here as the format will be useful to anyone who has to make similar calls using dates on Server side query.
Then process the received rows like this:
foreach( $rows as $row ):
$class_date = date_create($row[“calendar_date”]);
// … etc
endforeach;
The above represents painful lessons learnt from many many hours of experimentation over several days and wide reading and search on the web. I hope it will save you the time and effort!
Special thanks to Mobhar and Arbei who gave critical guidance. Apologies that all the neat indentations I made are lost in this BulletinBoard settings, making the code difficult to read.
That’s all for now!