"Sql iterator error" (#2714575) / TMS-Plugins

Roman started the conversation

March 9, 2021 at 11:03am

hello, i use wpdatatables plugin in my site and in some of tables i need to get row_number in sql code.

as mysql doesn't have possiblity to use row_number https://stackoverflow.com/questions/1895110/row-number-in-mysql then i use iterator sql code for getting it.

in phpmyadmin i have code and it works in phpmyadmin https://prnt.sc/10gzq6j but the same code doesn't work in wpdatatables editor https://prnt.sc/10gzm2x

pls tell me how can i solve this issue?

sql code:

SELECT
@i:=@i+1 AS 'iterator',
t1.*
FROM (
SELECT
1 AS '',
t.spacname,
t.industry,
t.common,
t.ipodate,
t.ipoproceeds,
t.overallotmentproceeds,
t.privateplacementsproceeds,
( t.ipoproceeds+t.overallotmentproceeds ) as 'total',
( ( ( t.ipoproceeds+t.overallotmentproceeds ) * t.heldintrust ) / 100 ) as 'total_gross',
t.heldintrust,
t.warrantsinclinunit,
COALESCE ( t.rights, '' ) as 'rights',
t.monthstocomplete as 'monthstocomplete',

( ( ( DATEDIFF( t.completiondeadlinedate, CURRENT_DATE() )) /30.42 ) ) as 'monthstoleft',
t.completiondeadlinedate as 'completiondeadlinedate',
SUBSTRING_INDEX( t.underwriter, ",", 1) as 'Left Lead / UW',

t.spactype,
t.heldintrustperunit,

t.warrant,
t.unit,

t.right,
t.underwriter,
t.additionalterms,
t.lawyerissuerscounsel,
t.underwritercounsel,
t.comanagers,
t.cashorcashlessredemption,
t.feesupfrontdeferred,
t.businesscombinationrecorddate,
t.businesscombinationshareholdervotedate,
COALESCE(t.impliedenterprisevalue, 0) as 'impliedenterprisevalue',

t.currentendingtrustbal as 'ending_trust_ball',
t.currentimpliedcashpershare as 'implied_cash_per_share',
t.singlespacsharetext,
t.singlespacexerciseprice,
t.singlespacredemptionprice,
t.completedsymbolwarrant,
t.completedsymbolcommon

FROM `ecwnyr4L_wpdatatable_1` t
WHERE t.spactype LIKE '%SEARCHING%'
ORDER BY t.ipodate DESC
) t1,
(SELECT @i:=0) AS foo

2,498

Aleksandar replied

March 10, 2021 at 10:54am

Hello Roman

As indicated in our documentation, using variables ( @ ), stored procedures and nested queries is not supported, but you can prepare a MySQL view (which will return the data you need); call it “view1” for example, and then build a wpDataTable based on a simple query like “SELECT * FROM view1”.

You can use a custom JS to add row numbers to the table:

[wpdatatable id=1]
<script type="text/javascript">
jQuery(window).on('load',function(){
   wpDataTables.table_1.api().on( 'order.dt search.dt', function ( e, dt, type, indexes ) {
        wpDataTables.table_1.api().column(0, {search:'applied', order:'applied'}).nodes().each( function (cell, i) {
            cell.innerHTML = i+1;
        }
 );
    }
 ).draw();
 });

Of course, you will use a table shortcode with an id that you need (in the example we use 1 )

[wpdatatable id=1]

Another way would be, as indicated above - to create a view in the database, and then create a table from that view.

Kind Regards,

Aleksandar Vuković
[email protected]

Rate my support

You can try wpDataTables add-ons before purchasing on these sandbox sites:

Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables

Roman replied

March 10, 2021 at 11:11am

it's okay but i need right numeration for downloading tables in excel files.
how can i save this numeration for downloading table?
after downloading in downloaded excel file i should have this right numeration

2,498

Aleksandar replied

March 11, 2021 at 9:05am

Hi again Roman

The column would be added to the table, so when you export the table in Excel format, that column will also be included. Does this not happen?

Kind Regards,

Aleksandar Vuković
[email protected]

Rate my support