I get an error when calling any stored procedure. The sp's work perfectly in phpmyadmin. I get the following error even though I have not limited the result set to 10. Does wpDataTables not support sp's? Or am I missing something related to my server configuration?

Error!

wpDataTables backend error: No data fetched! Rendered query: call uspHighPointJuniorRider('2016'); LIMIT 10 MySQL said: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 1 This usually happens when the MySQL query returns an empty result or is broken. Please check the results of the query in some DB manager (e.g. PHPMyAdmin)

Cheers, David Morgan

Thanks for responding Bogdan. 

I assumed "call mystoredprocedure()" would be seen by the plugin as a valid statement since it is supported by current MySQL databases. I would love to see this implemented in a future update. When passing VAR's I have a lot more parsing and validating power within a sp than with a simple select statement.

My immediate need that led to using a sp was to create a row_number column in the returned record set. One of the filters you pointed me to is wpdatatables_before_row( $table_id, $row_index ) which I could probably use to do this. On the other hand, is there another way to inject $row_index + 1 into its own column? I need to do this for some tables, but not all of them. 

David

I need some tables to have numbered rows such as this page:

http://octra.on.ca/high-point-novice-rider/

The SQL statement for that page uses a "joined inline variable" trick (not ANSI standard but it works in MySQL):

 SELECT @curRow := @curRow + 1 AS Rank ... JOIN (SELECT @curRow := 0) r

Complete statement:

SELECT @curRow := @curRow + 1 AS Rank, a.MemberID, udf_riderurl(a.MemberID) as Rider, TotalMiles,TotalKM, TotalOpenCtr, TotalEnd, TotalNov, SeasonPoints, CONCAT('',CONCAT(r.RideName,'-',r.TYPE),'') as LastNOVRide FROM HighpointNoviceStandings a JOIN Members b on a.MemberID = b.seqNum LEFT JOIN Rides r ON a.LastNovRideID = r.RideID JOIN (SELECT @curRow := 0) r WHERE a.Year = '%VAR1%' AND SeasonPoints > 0 ORDER BY SeasonPoints desc

My problem is the inline variable trick does not work if there is a "group by" in the statement. The order then becomes unpredictable. For a "group by"  the ANSI standard solution is to use a temp table with an auto-numbered column, insert into that using the correct "order by" and then to select from that temp table. A stored procedure, which wpdatatatables does not support, is the way to go for that. So, since I can't do that...

Is there a way for wpdatatables to insert a column with the row numbers? The row numbers would have to sort with the rest of the table (think of it as the finishing order in a race) or the table would have to be displayed as non-sortable. Either would work for me.

Cheers

Not quite. I need a table that displays the finishing order of a race. So the row numbers need to sort along with other data. If John finished 5th,  his should still be 5 if the table is sorted or searched even if he is displayed at the top of the table in row #1. "Rank" actually needs to be a column. I don't think $row_number is going to work here. For me, a stored procedure and temp table would be the perfect solution. Since I can't pass through a call to an sp I am working on a workaround. My result set is relatively small so I am experimenting with user functions to aggregate data instead of using a group by. Hopefully SELECT @curRow := @curRow + 1 AS Rank will probably work correctly then. I'll let you know.

Great plugin by the way. I am using it to do a lot of other MySQL queries quite successfully. Is there a place to post tips and solutions for other users?