Errors on server side script

Errors on server side script

craigwardcraigward Posts: 17Questions: 0Answers: 0
edited May 2010 in General
Hi, Love this script but having problems getting it to work.

I am using the server side part of the script as the database holds 1000s of records.

I have two files, lets call them pull.php (made from the script at the server side example, but changed all the various things needed) and display.php just shows the table.

This seems to be the problem as i get the following errors when I run pull.php in the browser:
Notice: Undefined index: sSearch in .....etc
Notice: Undefined variable: sOrder in .....etc
Notice: Undefined index: sEcho in .....etc

Now, the table actually displays the data pulled from the database but pagination and search doesn't seem to work. It also says "Showing 1 to 10 of 10 entries (filtered from 1030 total entries)" but I haven't filtered anything and if I sort the table it sorts through the whole 1030 records.

I really am stuck with this so any help would be really great.

Keep up the good work.

Regards

Craig

Replies

  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    Sorry to bump this but I could really do with some help
  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    Just been having more of a play with it.

    I have enabled "bLengthChange": true, and I can now show upto 100 rows, this works but the pagination doesn't show anymore pages, not sure why. That and the search are the only things I need to fix. When I enter something in the search it just sits there saying "processing" but nothing happens
  • codingavenuecodingavenue Posts: 22Questions: 0Answers: 0
    Hi,

    Could you post your code?

    codingavenue
  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    Sure can :)

    [code]
    <?php
    $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
    die( 'Could not open connection to server' );

    mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
    die( 'Could not select database '. $gaSql['db'] );

    /* Paging */
    $sLimit = "";
    if ( isset( $_GET['iDisplayStart'] ) )
    {
    $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
    mysql_real_escape_string( $_GET['iDisplayLength'] );
    }

    /* Ordering */
    if ( isset( $_GET['iSortCol_0'] ) )
    {
    $sOrder = "ORDER BY ";
    for ( $i=0 ; $i
    [/code]
  • codingavenuecodingavenue Posts: 22Questions: 0Answers: 0
    Hi,

    Could you post the server response of this two variables 'iTotalRecords' and 'iTotalDisplayRecords'

    codingavenue
  • codingavenuecodingavenue Posts: 22Questions: 0Answers: 0
    Hi (again) :)

    Could you also include your init code for DataTables.

    codingavenue
  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    Sorry I ran out of space :)

    [code]
    script type="text/javascript">
    $(document).ready(function() {
    $('#properties_table').dataTable( {
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "pull_propertylist.php",
    "bJQueryUI": true,
    "bPaginate": true,
    "bFilter": true,
    "bSort": true,
    "bInfo": true,
    "sPaginationType": "full_numbers",
    "bStateSave": true,
    "bLengthChange": true,
    "bAutoWidth": true } );
    } );

    [/code]
  • codingavenuecodingavenue Posts: 22Questions: 0Answers: 0
    Hi,

    I can't see any problem in your js code. So the problem might be on your server code. I suggest you look on firebug to see what data the server is giving back to datatables. Look on the value of 'iTotalDisplayRecords' if its correct. This is used by datatable for pagination.

    codingavenue
  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    As you can see from my json the records are correct, snippet below:

    [code]
    {"sEcho": 0, "iTotalRecords": 1029, "iTotalDisplayRecords": 1029, "aaData": [ ["142500","295 Middlewich Road, Rudheath","CW9 6EB","Northwich","Not Specified","3","0","12131","12131_MBN395"," edit "," delete "," info "]
    [/code]

    All the records are there but the search and pagination just do not work. Completely out of ideas
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    Notice: Undefined index: sSearch

    That looks very odd indeed - you've got server-side processing enabled, so it sure should be there :-) If you look at the parameters sent to the server in Firebug, is sSearch and friends there? Any chance you can link to an example page?

    Allan
  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    Everything is just being run from my Mac at the moment, I will look in firebug and see what I can find
  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    Just managed to find this error in firebug

    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 ''%testsearch%'
    ORDER BY PRICE
    asc
    LIMIT 0, 10' at line 2

    Do you know what could be causing this?
  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    That error is what comes back when you start typing a search
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    AGENT_REF is missing a 'LIKE' after it. Possibly that?

    If not that,t hen it would be worth printing out the created SQL statement so we can see what might not be valid about it.

    Allan
  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    Hi Allan,

    Found the missing LIKE and that has got the search working but not the pagination. Any ideas?
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    Can you print out the generated SQL statement and post that here please.

    Allan
  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    Hi Allan,

    All the pagination controls are greyed out. If I click on the greyed out "1" I get the following

    [code]
    {"sEcho": 3, "iTotalRecords": 1029, "iTotalDisplayRecords": 10, "aaData": [ ["Lots of data in here in json format, all looks fine. ]] }
    [/code]
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    iTotalDisplayRecords is 10 - which suggests that there are only 10 records to show, and this pagination will be disabled.

    Should there be more than 10 records? If so, then iTotalDisplayRecords is wrong... I think you are missing "SQL_CALC_FOUND_ROWS" from the first query. Have a look at this example for it's use: http://datatables.net/development/server-side/php_mysql

    Allan
  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    Your a star, I will give this a try as it seems perfect.

    Thanks for all the help and such a fantastic script.

    Regards

    Craig
  • craigwardcraigward Posts: 17Questions: 0Answers: 0
    Just got it all working, a few tweaks needed like merging cells and adding in extra but at least the big stuff is done.

    Thanks again
This discussion has been closed.