mysql - PHP mysql order by 未按预期工作

Question

我正在尝试在 HTLM-css(使用 Bootstrap )表中显示来自 mysql 数据库的数据。但查询结果没有按“ID”的顺序按预期显示。有人可以帮我解决我的问题吗？提前致谢。

<table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="example">
    <div class="alert alert-info">
        <strong><center>Delete Multiple Data:Check the Box and click the Delete button to Delete Data </strong></center>
    </div>
    <thead>
        <tr>
            <th>#</th>
            <th>ID</th>
            <th>Value Date</th>
            <th>Type</th>
            <th>Amount</th>
            <th>Donated By</th>
            <th>Paid To</th>
        </tr>
    </thead>
    <tbody>
        <?php
            $query=mysql_query("select * from temple_txn order by id desc")or die(mysql_error());
            while($row=mysql_fetch_array($query)){
                $id=$row['id'];
                ?>
                <tr>
                    <td><input name="selector[]" type="checkbox" value="<?php echo $id; ?>"></td>
                    <td><?php echo $row['id'] ?></td>
                    <td><?php echo $row['value_date'] ?></td>
                    <td><?php echo $row['txn_type'] ?></td>
                    <td><?php echo $row['txn_amount'] ?></td>
                    <td><?php echo $row['donated_by'] ?></td>
                    <td><?php echo $row['paid_to'] ?></td>
                </tr>
        <?php } ?>
    </tbody>
</table>

(JS DT_bootstrap)

/* Set the defaults for DataTables initialisation */
$.extend( true, $.fn.dataTable.defaults, {
    "sDom": "<'row-fluid'<'span6'l><'span6'f>r>t<'row-fluid'<'span6'i><'span6'p>>",
    "sPaginationType": "bootstrap",
    "oLanguage": {
        "sLengthMenu": "_MENU_ records per page"
    }
} );


/* Default class modification */
$.extend( $.fn.dataTableExt.oStdClasses, {
    "sWrapper": "dataTables_wrapper form-inline"
} );


/* API method to get paging information */
$.fn.dataTableExt.oApi.fnPagingInfo = function ( oSettings )
{
    return {
        "iStart":         oSettings._iDisplayStart,
        "iEnd":           oSettings.fnDisplayEnd(),
        "iLength":        oSettings._iDisplayLength,
        "iTotal":         oSettings.fnRecordsTotal(),
        "iFilteredTotal": oSettings.fnRecordsDisplay(),
        "iPage":          oSettings._iDisplayLength === -1 ?
            0 : Math.ceil( oSettings._iDisplayStart / oSettings._iDisplayLength ),
        "iTotalPages":    oSettings._iDisplayLength === -1 ?
            0 : Math.ceil( oSettings.fnRecordsDisplay() / oSettings._iDisplayLength )
    };
};


/* Bootstrap style pagination control */
$.extend( $.fn.dataTableExt.oPagination, {
    "bootstrap": {
        "fnInit": function( oSettings, nPaging, fnDraw ) {
            var oLang = oSettings.oLanguage.oPaginate;
            var fnClickHandler = function ( e ) {
                e.preventDefault();
                if ( oSettings.oApi._fnPageChange(oSettings, e.data.action) ) {
                    fnDraw( oSettings );
                }
            };

            $(nPaging).addClass('pagination').append(
                '<ul>'+
                    '<li class="prev disabled"><a href="#">&larr; '+oLang.sPrevious+'</a></li>'+
                    '<li class="next disabled"><a href="#">'+oLang.sNext+' &rarr; </a></li>'+
                '</ul>'
            );
            var els = $('a', nPaging);
            $(els[0]).bind( 'click.DT', { action: "previous" }, fnClickHandler );
            $(els[1]).bind( 'click.DT', { action: "next" }, fnClickHandler );
        },

        "fnUpdate": function ( oSettings, fnDraw ) {
            var iListLength = 5;
            var oPaging = oSettings.oInstance.fnPagingInfo();
            var an = oSettings.aanFeatures.p;
            var i, ien, j, sClass, iStart, iEnd, iHalf=Math.floor(iListLength/2);

            if ( oPaging.iTotalPages < iListLength) {
                iStart = 1;
                iEnd = oPaging.iTotalPages;
            }
            else if ( oPaging.iPage <= iHalf ) {
                iStart = 1;
                iEnd = iListLength;
            } else if ( oPaging.iPage >= (oPaging.iTotalPages-iHalf) ) {
                iStart = oPaging.iTotalPages - iListLength + 1;
                iEnd = oPaging.iTotalPages;
            } else {
                iStart = oPaging.iPage - iHalf + 1;
                iEnd = iStart + iListLength - 1;
            }

            for ( i=0, ien=an.length ; i<ien ; i++ ) {
                // Remove the middle elements
                $('li:gt(0)', an[i]).filter(':not(:last)').remove();

                // Add the new list items and their event handlers
                for ( j=iStart ; j<=iEnd ; j++ ) {
                    sClass = (j==oPaging.iPage+1) ? 'class="active"' : '';
                    $('<li '+sClass+'><a href="#">'+j+'</a></li>')
                        .insertBefore( $('li:last', an[i])[0] )
                        .bind('click', function (e) {
                            e.preventDefault();
                            oSettings._iDisplayStart = (parseInt($('a', this).text(),10)-1) * oPaging.iLength;
                            fnDraw( oSettings );
                        } );
                }

                // Add / remove disabled classes from the static elements
                if ( oPaging.iPage === 0 ) {
                    $('li:first', an[i]).addClass('disabled');
                } else {
                    $('li:first', an[i]).removeClass('disabled');
                }

                if ( oPaging.iPage === oPaging.iTotalPages-1 || oPaging.iTotalPages === 0 ) {
                    $('li:last', an[i]).addClass('disabled');
                } else {
                    $('li:last', an[i]).removeClass('disabled');
                }
            }
        }
    }
} );


/*
 * TableTools Bootstrap compatibility
 * Required TableTools 2.1+
 */
if ( $.fn.DataTable.TableTools ) {
    // Set the classes that TableTools uses to something suitable for Bootstrap
    $.extend( true, $.fn.DataTable.TableTools.classes, {
        "container": "DTTT btn-group",
        "buttons": {
            "normal": "btn",
            "disabled": "disabled"
        },
        "collection": {
            "container": "DTTT_dropdown dropdown-menu",
            "buttons": {
                "normal": "",
                "disabled": "disabled"
            }
        },
        "print": {
            "info": "DTTT_print_info modal"
        },
        "select": {
            "row": "active"
        }
    } );

    // Have the collection use a bootstrap compatible dropdown
    $.extend( true, $.fn.DataTable.TableTools.DEFAULTS.oTags, {
        "collection": {
            "container": "ul",
            "button": "li",
            "liner": "a"
        }
    } );
}


/* Table initialisation */
$(document).ready(function() {
    $('#example').dataTable( {

        "sPaginationType": "bootstrap",
        "oLanguage": {

        }
    } );
} );

Answer 1

看来您以正确的顺序从数据库中获取数据。根据我们在问题评论中的对话，我认为您的问题是由 JS 库 DataTables 引起的或页面加载后的引导插件。为了确保这一点，请停用 JavaScript，并查看如果插件未触及行，它们的顺序是否正确。

如果 DataTables 尝试按第一列中的值进行排序(其中有一些 HTML)，这可以解释看似随机的排序。尝试设置隐式顺序/排序设置 ( reference )。您的 ID 位于第二列，并且列索引从零开始，因此如下:

$(document).ready(function() {
    $('#example').dataTable( {
        "sPaginationType": "bootstrap",
        "oLanguage": {},
        "aaSorting": [[1,'desc']]
    });
});