jQuery UI Widgets Forums Grid jqxGrid with MySQLi

Tagged: , ,

This topic contains 9 replies, has 4 voices, and was last updated by  Peter Stoev 7 years, 2 months ago.

Viewing 10 posts - 1 through 10 (of 10 total)
  • Author
  • jqxGrid with MySQLi #31729

    webwired
    Member

    I was wondering if anyone has successfully gotten the Grid to work with MySQLi? I have spent about 6 hours attempting to convert the MySQL to MySQLi in the data.php page,… and if I look at just the data.php page in the browser, It shows the Totalcount and result Rows in Json format, but when I go to the Grid and have it pull up the newly coded page, it just says, No data to display

    Here’s my new page code, with the MySQLi

    <?php
    session_start();
    $ExaminerID = $_SESSION['ExaminerID'];
    include('connect.php');
    $pagenum = $_GET['pagenum'];
    $pagesize = $_GET['pagesize'];
    $start = $pagenum * $pagesize;
    if (!isset($_GET['sortdatafield']))
    {
    $query = "SELECT SQL_CALC_FOUND_ROWS OrderTickets.OrderTicketID, OrderTickets.OrderTicketFirstName, OrderTickets.OrderTicketLastName, OrderTickets.OrderTicketDateReceived, OrderTickets.OrderTicketDateRejectedCanceled, Agencies.AgencyName, Agents.AgentFirstName, Agents.AgentLastName, InsuranceCarriers.InsuranceCarrier
    FROM OrderTickets
    LEFT JOIN Agencies ON OrderTickets.AgencyID = Agencies.AgencyID
    LEFT JOIN Agents ON OrderTickets.AgentID = Agents.AgentID
    LEFT JOIN InsuranceCarriers ON OrderTickets.InsuranceCarrierID = InsuranceCarriers.InsuranceCarrierID
    WHERE ((ExaminerID = 0 OR ExaminerID = ?) AND OrderTicketCurrentStatus = 'Rejected') LIMIT ?, ?";
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param("iii", $ExaminerID, $start, $pagesize);
    $stmt->execute();
    $stmt->bind_result($OrderTicketID, $OrderTicketFirstName, $OrderTicketLastName, $OrderTicketDateReceived, $OrderTicketDateRejectedCanceled, $AgencyName, $AgentFirstName, $AgentLastName, $InsuranceCarrier);
    while ($stmt -> fetch()) {
    $customers[] = array(
    'OrderTicketID' => $OrderTicketID,
    'ClientName' => $OrderTicketLastName .', '. $OrderTicketFirstName,
    'AgencyName' => $AgencyName,
    'AgentName' => $AgentLastName .', '. $AgentFirstName,
    'InsuranceCarrier' => $InsuranceCarrier,
    'OrderTicketDateReceived' => $OrderTicketDateReceived,
    'OrderTicketDateRejectedCanceled' => $OrderTicketDateRejectedCanceled
    );
    }
    $sql = "SELECT FOUND_ROWS()";
    $stmt = $mysqli->prepare($sql);
    $stmt->execute();
    $stmt->bind_result($total_rows);
    $stmt->fetch();
    }
    if (isset($_GET['sortdatafield']))
    {
    $sortfield = $_GET['sortdatafield'];
    if ($sortfield == "ClientName") {
    $sortfield = "OrderTicketLastName";
    }
    if ($sortfield == "AgentName") {
    $sortfield = "AgentLastName";
    }
    $sortorder = $_GET['sortorder'];
    if ($sortfield != NULL)
    {
    if ($sortorder == "desc")
    {
    $query = "SELECT OrderTickets.*, Agencies.AgencyName, Agents.AgentFirstName, Agents.AgentLastName, InsuranceCarriers.InsuranceCarrier
    FROM OrderTickets
    LEFT JOIN Agencies ON OrderTickets.AgencyID = Agencies.AgencyID
    LEFT JOIN Agents ON OrderTickets.AgentID = Agents.AgentID
    LEFT JOIN InsuranceCarriers ON OrderTickets.InsuranceCarrierID = InsuranceCarriers.InsuranceCarrierID
    WHERE ((ExaminerID = 0 OR ExaminerID = ?) AND OrderTicketCurrentStatus = 'Rejected') ORDER BY ? DESC LIMIT ?, ?";
    }
    else if ($sortorder == "asc")
    {
    $query = "SELECT OrderTickets.*, Agencies.AgencyName, Agents.AgentFirstName, Agents.AgentLastName, InsuranceCarriers.InsuranceCarrier
    FROM OrderTickets
    LEFT JOIN Agencies ON OrderTickets.AgencyID = Agencies.AgencyID
    LEFT JOIN Agents ON OrderTickets.AgentID = Agents.AgentID
    LEFT JOIN InsuranceCarriers ON OrderTickets.InsuranceCarrierID = InsuranceCarriers.InsuranceCarrierID
    WHERE ((ExaminerID = 0 OR ExaminerID = ?) AND OrderTicketCurrentStatus = 'Rejected') ORDER BY ? ASC LIMIT ?, ?";
    }
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param("isii", $ExaminerID, $sortfield, $start, $pagesize);
    $stmt->execute();
    $stmt->bind_result($OrderTicketID, $OrderTicketFirstName, $OrderTicketLastName, $OrderTicketDateReceived, $OrderTicketDateRejectedCanceled, $AgencyName, $AgentFirstName, $AgentLastName, $InsuranceCarrier);
    while ($stmt -> fetch()) {
    $customers[] = array(
    'OrderTicketID' => $OrderTicketID,
    'ClientName' => $OrderTicketLastName .', '. $OrderTicketFirstName,
    'AgencyName' => $AgencyName,
    'AgentName' => $AgentLastName .', '. $AgentFirstName,
    'InsuranceCarrier' => $InsuranceCarrier,
    'OrderTicketDateReceived' => $OrderTicketDateReceived,
    'OrderTicketDateRejectedCanceled' => $OrderTicketDateRejectedCanceled
    );
    }
    $sql = "SELECT FOUND_ROWS()";
    $stmt = $mysqli->prepare($sql);
    $stmt->execute();
    $stmt->bind_result($total_rows);
    $stmt->fetch();
    }
    }
    $data[] = array(
    'TotalRows' => $total_rows,
    'Rows' => $customers
    );
    echo json_encode($data);
    ?>
    jqxGrid with MySQLi #31732

    Peter Stoev
    Keymaster

    Hi webwired,

    I suggest you to debug your PHP page and check the result that json_encode returns to the client. In addition, you can use the jqxDataAdapter’s helpful callbacks for debugging – loadError and loadComplete. For more information about them, look at: http://www.jqwidgets.com/jquery-widgets-documentation/documentation/jqxdataadapter/jquery-data-adapter.htm

    Best Regards,
    Peter Stoev

    jQWidgets Team
    http://www.jqwidgets.com/

    jqxGrid with MySQLi #31737

    webwired
    Member

    I did debug on the data.php page, that’s what I said up top, that the data comes out perfectly and exactly the same, in the browser, as when I pull it up with the original mysql syntax, here’s an example of what it puts out …

    [{“TotalRows”:2,”Rows”:[{“OrderTicketID”:52,”ClientName”:”*****, *****”,”AgencyName”:”John Nowak Agency”,”AgentName”:”Nowak, John”,”InsuranceCarrier”:”Nationwide Life Insurance”,”OrderTicketDateReceived”:”2013-07-31″,”OrderTicketDateRejectedCanceled”:”2013-10-10″},{“OrderTicketID”:68,”ClientName”:”*****, *****”,”AgencyName”:”John Nowak Agency”,”AgentName”:”Nowak, John”,”InsuranceCarrier”:”Nationwide Life Insurance”,”OrderTicketDateReceived”:”2013-08-12″,”OrderTicketDateRejectedCanceled”:”2013-10-10″}]}]

    Isn’t that perfectly formatted json for the Grid page to consume?

    I just took a look at the jqxDataAdapter documenation, I’ll try to implement that tomorrow, I’ll post back.

    jqxGrid with MySQLi #31805

    webwired
    Member

    Well, I had a look at the jqxDataAdapter documentation, and from what I can tell from the examples, it only works by showing HTML Tables…

    I’m sorry to say that after careful consideration, I will not be continuing to use jqxwidgets products, for the lack of current standards integration and documentation, and the lack of support (as in unanswered forum posts).

    jqxGrid with MySQLi #31813

    Peter Stoev
    Keymaster

    Hi webwired,

    jqxDataAdapter works with JSON, JSONP, XML, TSV, CSV and Array. And its output is always Array. It is not the jqxDataAdapter’s job to render a Table. The documentation page points how you can Read data from the jqxDataAdapter and Load data into it.

    I am sorry to learn that you will not use jQwidgets and thank you about your opinion about the Support Team. Unfortunately, it seems that we still do not have the specific sample for that you are looking for. However, I prepared a sample with MySQLI as a proof that our widget works with it:

    Index.php

    <!DOCTYPE html>
    <html lang="en">
    <head>
    <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
    <link rel="stylesheet" href="../../jqwidgets/styles/jqx.classic.css" type="text/css" />
    <script type="text/javascript" src="../../scripts/jquery-1.10.2.min.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcheckbox.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxlistbox.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdropdownlist.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.pager.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.sort.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
    <script type="text/javascript">
    $(document).ready(function () {
    // prepare the data
    var theme = 'classic';
    var source =
    {
    datatype: "json",
    datafields: [
    { name: 'CompanyName', type: 'string'},
    { name: 'ContactName', type: 'string'},
    { name: 'ContactTitle', type: 'string'},
    { name: 'Address', type: 'string'},
    { name: 'City', type: 'string'},
    { name: 'Country', type: 'string'}
    ],
    cache: false,
    url: 'data.php',
    type: "POST",
    root: 'Rows',
    beforeprocessing: function(data)
    {
    source.totalrecords = data[0].TotalRows;
    },
    sort: function()
    {
    // update the grid and send a request to the server.
    $("#jqxgrid").jqxGrid('updatebounddata', 'sort');
    }
    };
    var dataadapter = new $.jqx.dataAdapter(source);
    // initialize jqxGrid
    $("#jqxgrid").jqxGrid(
    {
    width: 600,
    source: dataadapter,
    theme: theme,
    autoheight: true,
    pageable: true,
    virtualmode: true,
    sortable: true,
    rendergridrows: function()
    {
    return dataadapter.records;
    },
    columns: [
    { text: 'Company Name', datafield: 'CompanyName', width: 250 },
    { text: 'Contact Name', datafield: 'ContactName', width: 200 },
    { text: 'Contact Title', datafield: 'ContactTitle', width: 200 },
    { text: 'Address', datafield: 'Address', width: 180 },
    { text: 'City', datafield: 'City', width: 100 },
    { text: 'Country', datafield: 'Country', width: 140 }
    ]
    });
    });
    </script>
    </head>
    <body class='default'>
    <div id='jqxWidget'">
    <div id="jqxgrid"></div>
    </div>
    </body>
    </html>

    connect.php

    <?php
    # FileName="connect.php"
    $hostname = "localhost";
    $database = "northwind";
    $username = "root";
    $password = "root";
    ?>

    data.php

    <?php
    #Include the connect.php file
    include('connect.php');
    #Connect to the database
    $mysqli = new mysqli("$hostname", "$username", "$password", $database);
    /* check connection */
    if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
    }
    // Initialize pagenum and pagesize
    $pagenum = $_POST['pagenum'];
    $pagesize = $_POST['pagesize'];
    $start = $pagenum * $pagesize;
    if (isset($_POST['sortdatafield']))
    {
    $sortfield = $_POST['sortdatafield'];
    $sortorder = $_POST['sortorder'];
    if ($sortorder != '')
    {
    if ($sortorder == "desc")
    {
    $query = "SELECT CompanyName, ContactName, ContactTitle, Address, City, Country FROM Customers ORDER BY" . " " . $sortfield . " DESC LIMIT ?, ?";
    }
    else if ($sortorder == "asc")
    {
    $query = "SELECT CompanyName, ContactName, ContactTitle, Address, City, Country FROM Customers ORDER BY" . " " . $sortfield . " ASC LIMIT ?, ?";
    }
    $result = $mysqli->prepare($query);
    $result->bind_param('ii', $start, $pagesize);
    }
    else
    {
    $result = $mysqli->prepare("SELECT SQL_CALC_FOUND_ROWS CompanyName, ContactName, ContactTitle, Address, City, Country FROM Customers LIMIT ?, ?");
    $result->bind_param('ii', $start, $pagesize);
    }
    }
    else
    {
    $result = $mysqli->prepare("SELECT SQL_CALC_FOUND_ROWS CompanyName, ContactName, ContactTitle, Address, City, Country FROM Customers LIMIT ?, ?");
    $result->bind_param('ii', $start, $pagesize);
    }
    /* execute query */
    $result->execute();
    /* bind result variables */
    $result->bind_result($CompanyName, $ContactName, $ContactTitle, $Address, $City, $Country);
    /* fetch values */
    while ($result -> fetch()) {
    $customers[] = array(
    'CompanyName' => $CompanyName,
    'ContactName' => $ContactName,
    'ContactTitle' => $ContactTitle,
    'Address' => $Address,
    'City' => $City,
    'Country' => $Country
    );
    }
    // get the total rows.
    $result = $mysqli->prepare("SELECT FOUND_ROWS()");
    $result->execute();
    $result->bind_result($total_rows);
    $result->fetch();
    $data[] = array(
    'TotalRows' => $total_rows,
    'Rows' => $customers
    );
    echo json_encode($data);
    /* close statement */
    $result->close();
    /* close connection */
    $mysqli->close();
    ?>

    Best Wishes,
    Peter Stoev

    jQWidgets Team
    http://www.jqwidgets.com/

    jqxGrid with MySQLi #80723

    Ben1
    Participant

    Hello Guys,

    I am using a fresh download of the jqwidgets zip files with demos, version 3.9.1.

    I have been trying to do these PHP MySQL data binding examples but I keep getting the same result… no data is displayed. I do see it working on your demos, but when I try to replicate this on my local server, it just does not work. I tried the previous example that Peter describes and it gives the same results. Ultimately, In the debug process I think I should be able to browse to the URL for the “data.php” file and which should “echo” the data in JSON format.

    Instead the browser display a portion of the “data.php” file. as follows:

    prepare($query); $result->bind_param('ii', $start, $pagesize); } else { $result = $mysqli->prepare("SELECT SQL_CALC_FOUND_ROWS CompanyName, ContactName, ContactTitle, Address, City, Country FROM Customers LIMIT ?, ?"); $result->bind_param('ii', $start, $pagesize); } } else { $result = $mysqli->prepare("SELECT SQL_CALC_FOUND_ROWS CompanyName, ContactName, ContactTitle, Address, City, Country FROM Customers LIMIT ?, ?"); $result->bind_param('ii', $start, $pagesize); } /* execute query */ $result->execute(); /* bind result variables */ $result->bind_result($CompanyName, $ContactName, $ContactTitle, $Address, $City, $Country); /* fetch values */ while ($result -> fetch()) { $customers[] = array( 'CompanyName' => $CompanyName, 'ContactName' => $ContactName, 'ContactTitle' => $ContactTitle, 'Address' => $Address, 'City' => $City, 'Country' => $Country ); } // get the total rows. $result = $mysqli->prepare("SELECT FOUND_ROWS()"); $result->execute(); $result->bind_result($total_rows); $result->fetch(); $data[] = array( 'TotalRows' => $total_rows, 'Rows' => $customers ); echo json_encode($data); /* close statement */ $result->close(); /* close connection */ $mysqli->close(); ?>

    Your help would be appreciated.
    Thanks,
    Ben

    jqxGrid with MySQLi #80741

    Peter Stoev
    Keymaster

    Hi Ben,

    First test whether your server returns any data and then try to build a page with a Grid which binds to that data. That is the advice I can give you.

    Best Wishes,
    Peter Stoev
    jQWidgets Team
    http://www.jqwidgets.com/

    jqxGrid with MySQLi #80765

    Ben1
    Participant

    Hi Peter and thanks for the response. I should have mentioned that I had confirmed that the MySQL server was returning data to other applications, just not within the browser using the example files. The PHP Mysql binding examples are fairly straight forward so that is why I started there. I have never used the data.php method and it was a bit hard to diagnose that at first, but that was obstacle. It had nothing to do with the grid.

    I made progress and was able to get the examples I was testing to work…

    To run these tests I have been using XAMPP v3.2.2. I installed the same day I started my testing. In short the PHP/MSQLi functionality was not performing as expected. It had nothing to do with the JQwidgets technology.

    I am correct about the the expected output of the data.php file for the simpler JQwidget examples. See my post above. You should be able to browse to the data.php file and the browser should display JSON encoded text contents (of something like)…

    [{"CompanyName":"Alfreds Futterkiste","ContactName":"Maria Anders","ContactTitle":"Sales Representative","Address":"Obere Str. 57","City":"Berlin"},{"CompanyName":"Ana Trujillo Emparedados y helados","ContactName":"Ana Trujillo","ContactTitle":"Owner","Address":"Avda. de la Constitucin 2222","City":"Mxico D.F."},{"CompanyName":"Antonio Moreno Taquera","ContactName":"Antonio Moreno","ContactTitle":"Owner","Address":"Mataderos 2312","City":"Mxico D.F."},{"CompanyName":"Around the Horn","ContactName":"Thomas Hardy","ContactTitle":"Sales Representative","Address":"120 Hanover Sq.","City":"London"},{"CompanyName":"Berglunds snabbkp","ContactName":"Christina Berglund","ContactTitle":"Order Administrator","Address":"Berguvsvgen 8","City":"Lule"},{"CompanyName":"Blauer See Delikatessen","ContactName":"Hanna Moos","ContactTitle":"Sales Representative","Address":"Forsterstr. 57","City":"Mannheim"},{"CompanyName":"Blondesddsl pre et fils","ContactName":"Frdrique Citeaux","ContactTitle":"Marketing Manager","Address":"24, place Klber","City":"Strasbourg"},{"CompanyName":"Blido Comidas preparadas","ContactName":"Martn Sommer","ContactTitle":"Owner","Address":"C\/ Araquil, 67","City":"Madrid"},{"CompanyName":"Bon app'","ContactName":"Laurence Lebihan","ContactTitle":"Owner","Address":"12, rue des Bouchers","City":"Marseille"},{"CompanyName":"Bottom-Dollar Markets","ContactName":"Elizabeth Lincoln","ContactTitle":"Accounting Manager","Address":"23 Tsawassen Blvd.","City":"Tsawassen"},{"CompanyName":"B's Beverages","ContactName":"Victoria Ashworth","ContactTitle":"Sales Representative","Address":"Fauntleroy Circus","City":"London"},{"CompanyName":"Cactus Comidas para llevar","ContactName":"Patricio Simpson","ContactTitle":"Sales Agent","Address":"Cerrito 333","City":"Buenos Aires"},{"CompanyName":"Centro comercial Moctezuma","ContactName":"Francisco Chang","ContactTitle":"Marketing Manager","Address":"Sierras de Granada 9993","City":"Mxico D.F."},{"CompanyName":"Chop-suey Chinese","ContactName":"Yang Wang","ContactTitle":"Owner","Address":"Hauptstr. 29","City":"Bern"},{"CompanyName":"Comrcio Mineiro","ContactName":"Pedro Afonso","ContactTitle":"Sales Associate","Address":"Av. dos Lusadas, 23","City":"Sao Paulo"},{"CompanyName":"Consolidated Holdings","ContactName":"Elizabeth Brown","ContactTitle":"Sales Representative","Address":"Berkeley Gardens 12 Brewery","City":"London"},{"CompanyName":"Drachenblut Delikatessen","ContactName":"Sven Ottlieb","ContactTitle":"Order Administrator","Address":"Walserweg 21","City":"Aachen"},{"CompanyName":"Du monde entier","ContactName":"Janine Labrune","ContactTitle":"Owner","Address":"67, rue des Cinquante Otages","City":"Nantes"},{"CompanyName":"Eastern Connection","ContactName":"Ann Devon","ContactTitle":"Sales Agent","Address":"35 King George","City":"London"},{"CompanyName":"Ernst Handel","ContactName":"Roland Mendel","ContactTitle":"Sales Manager","Address":"Kirchgasse 6","City":"Graz"},{"CompanyName":"Familia Arquibaldo","ContactName":"Aria Cruz","ContactTitle":"Marketing Assistant","Address":"Rua Ors, 92","City":"Sao Paulo"},{"CompanyName":"FISSA Fabrica Inter. Salchichas S.A.","ContactName":"Diego Roel","ContactTitle":"Accounting Manager","Address":"C\/ Moralzarzal, 86","City":"Madrid"},{"CompanyName":"Folies gourmandes","ContactName":"Martine Ranc","ContactTitle":"Assistant Sales Agent","Address":"184, chausse de Tournai","City":"Lille"},{"CompanyName":"Folk och f HB","ContactName":"Maria Larsson","ContactTitle":"Owner","Address":"kergatan 24","City":"Brcke"},{"CompanyName":"Frankenversand","ContactName":"Peter Franken","ContactTitle":"Marketing Manager","Address":"Berliner Platz 43","City":"Mnchen"},{"CompanyName":"France restauration","ContactName":"Carine Schmitt","ContactTitle":"Marketing Manager","Address":"54, rue Royale","City":"Nantes"},{"CompanyName":"Franchi S.p.A.","ContactName":"Paolo Accorti","ContactTitle":"Sales Representative","Address":"Via Monte Bianco 34","City":"Torino"},{"CompanyName":"Furia Bacalhau e Frutos do Mar","ContactName":"Lino Rodriguez","ContactTitle":"Sales Manager","Address":"Jardim das rosas n. 32","City":"Lisboa"},{"CompanyName":"Galera del gastrnomo","ContactName":"Eduardo Saavedra","ContactTitle":"Marketing Manager","Address":"Rambla de Catalua, 23","City":"Barcelona"},{"CompanyName":"Godos Cocina Tpica","ContactName":"Jos Pedro Freyre","ContactTitle":"Sales Manager","Address":"C\/ Romero, 33","City":"Sevilla"}]

    Peter’s example files also work (in his explanation above). But, Peter’s example data.php file is a bit more complex and does not return the JSON encoded data, if browsed directly. But, it works fine in conjunction with the rest of its example files.

    Cause of the issue…The data.php file is using MSQLi methodology to connect to the database but was having issues on my system yesterday. I suspect the XAMPP PHP functionality was not working right and was not displaying any useful information. I found a suggestion on the Internet from someone else that the XAMPP PHP ext folder may need to be given read permissions to the /User. Mine did already have the READ permissions, so I gave my /User all permissions to this folder (test environment only) and the data.php file is working now.

    I know this is a long winded explanation, but I am hoping some of the OTHER PHP testers having a similar issue can benefit from this info.
    Regards,
    Ben

    jqxGrid with MySQLi #91076

    jcansell
    Participant

    Hi Peter,
    Can you explain what the ‘ii’ does in to following statement in your example:

    $result->bind_param(‘ii’, $start, $pagesize);

    I can see that $start, $pagesize replace the ?, ? in the $mysqli->prepare( statement, but google is not helping me understand the ‘ii’ parameter.

    many thanks,
    jonathan

    jqxGrid with MySQLi #91079

    Peter Stoev
    Keymaster

    Hi Jonathan,

    I would suggest you to take a look at this manual http://php.net/manual/en/mysqli-stmt.bind-param.php

    Best Wishes,
    Peter Stoev

    jQWidgets Team
    http://www.jqwidgets.com/

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.