This topic is locked

Calling a MySQL Stored Procedure in a list page

4/11/2023 2:59:49 PM
PHPRunner General questions
A
asawyer13 authorDevClub member

I have a complex MySQL Stored Procedure that I want to use for a List Page
WHen the user clicks on the menu item, I open the Search Page. It opens the Advanced Search and that appears to work.
I want the user to fill in the criteria, then I want to construct my call to my stored procedure and call it. I assume I can do this in the Custom Query of the List Page?? To test this I am just calling the stored procedure and hardcoding the value.
So this goes in teh Custom Query section
return DB::Query("call sp_SearchBA2(null,null,'AZ',null,null,null,null,null,null,10)");This should query for rows with state of AZ and return 10 rows max.
In the fetch records event I have this:
return $rs->fetchAssoc();and I don't care about counting the number of records that I get back but I hardcode this in the Get Row Count
return 10;My problem is that the results come back ignoring my call to the stored procedure, so in other words if in my search is say state = NM, I get just New Mexico records in my list.
Can someone tell me what I'm doing wrong?
Thanks in advance
Alan

Admin 4/11/2023

Alan,
it is hard to tell what exactly is wrong, your code and approach seem to be correct.
I can suggest using a SQL View in PHPRunner based on this stored procedure call. The method with three events is sort of an old way of doing things and SQL View shoul be easier to configure in general.
https://xlinesoft.com/phprunner/docs/sql-views.htm

A
asawyer13 authorDevClub member 4/11/2023

Thanks, I will take a look at it.
Alan