jQWidgets Forums

This topic contains 4 replies, has 2 voices, and was last updated by smithgr@jhmi.edu 10 years, 7 months ago.

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

Author

PHP, MySQL Grid Problem Posts

July 28, 2014 at 5:42 pm PHP, MySQL Grid Problem #57764

Participant

I can't seem to get my add to work.  Please take a look at the code.  It's not opening a blank row and I've hardcoded inserting a record which doesn't work either.  Thank you.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en">
<head>
    <title>Getting Started</title>    <!-- add one of the jQWidgets styles -->
    <link rel="stylesheet"     href="jqwidgets/styles/jqx.base.css" type="text/css" />
    <link rel="stylesheet"     href="jqwidgets/styles/jqx.energyblue.css" type="text/css" />
    <!-- add the jQuery script -->
    <script type="text/javascript" src="scripts/jquery-1.10.2.min.js"></script>
    <!-- add the jQWidgets framework -->
    <script type="text/javascript" src="jqwidgets/jqxcore.js"></script>
    <!-- add one or more widgets -->
    <script type="text/javascript" src="jqwidgets/jqxmenu.js"></script>

    <script type="text/javascript" src="jqwidgets/jqxdata.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/jqxgrid.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.selection.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxdatatable.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.columnsresize.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.pager.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxdropdownlist.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxlistbox.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.sort.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.filter.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.edit.js"></script>

    <title id='Description'>In order to enter in edit mode, select a grid cell and start typing, "Click" or press the "F2" key. You
        can also navigate through the cells using the keyboard arrows or with the "Tab" and "Shift + Tab" key combinations. To cancel the cell editing, press the "Esc" key. To save
        the changes press the "Enter" key or select another Grid cell. Pressing the 'Space' key when a checkbox cell is selected will toggle the check state.</title>

</head>
<body>
<script type="text/javascript">
    $(document).ready(function () {

           $("#main_menu").jqxMenu({width:850, height: 30, theme: 'energyblue'});

            var source2 = {
                datatype: "json",
                datafields: [{ name: 'lastName'}, {name: 'firstName'}, {name: 'phone'}, {name: 'email'}],
                url: 'getPIs.php',

                addrow: function (rowid, rowdata, position, commit) {
                    alert("in add");
                    // synchronize with the server - send insert command
                    var data = "insert=true&" + $.param(rowdata);
                    $.ajax({
                        dataType: 'json',
                        url: 'getPIs.php',
                        data: data,
                        cache: false,
                        success: function (data, status, xhr) {
                            // insert command is executed.
                            alert("true");
                            commit(true);
                        },
                        error: function(jqXHR, textStatus, errorThrown)
                        {
                            alert("false");
                            commit(false);
                        }
                    });
                }

            };

            $("#piGrid").jqxGrid({
                width: 850,
                editable: true,
                selectionmode: 'singlerow',
                editmode: 'selectedrow',
                columnsresize: true,
                source: source2,
                pageable: true,
                autoheight: true,
                filterable: true,
                sortable: true,
                enabletooltips: true,
                showtoolbar: true,
                showfilterrow: true,
                rendertoolbar: function (toolbar) {
                    var me = this;
                    var container = $("<div style='margin: 5px;'></div>");
                    toolbar.append(container);
                    container.append('<input id="addrowbutton" type="button" value="Add New Row" />');
                    container.append('<input style="margin-left: 5px;" id="deleterowbutton" type="button" value="Delete Selected Row" />');
                    $("#addrowbutton").jqxButton();

                    $("#addrowbutton").unbind('click').bind('click', function () {
                        var rowscount = $("#piGrid").jqxGrid('getdatainformation').rowscount;
                        var datarow = {};

                        $("#piGrid").jqxGrid('addrow', null, []);
                    });

                },
                theme: 'energyblue',
                columns: [{text: 'Last Name', datafield: 'lastName', width: 200},{text: 'First Name', datafield: 'firstName', width: 200},
                    {text: 'Phone', datafield: 'phone', width: 200},{text: 'email', datafield: 'email', width: 250}]

                // create new row.

            });

    });
</script>
<div id="main_menu" style="width: 50%; margin:0 auto">
    <ul >
        <li><a href="#">PI</a></li>
        <li><a href="#">Study</a>
        </li><li><a href="#">Inventory</a>
            <ul>
                <li><a href="#">Edit Clinical</a></li>
                <li><a href="#">Bulk Insert Clinical</a></li>
                <li><a href="#">Add-Edit PreClinical Samples</a>
                </li><li><a href="#">Bulk Move Samples</a>
            </li></ul>
        </li>
        <li><a href="#">Reports</a>
            <ul>
                <li>Assay</li>
                <li>Assay with Sum</li>
                <li>Need Assay</li>
                <li>Export To Excel</li>
            </ul>
        </li>
        <li><a href="#">Admin</a>
            <ul>
                <li>Edit Lookup Tables
                    <ul>
                        <li>Affiliation</li>
                        <li>Buidling</li>
                    </ul>
                </li>
            </ul>
        </li>
    
</div>

<br>
<div style="width: 850px; margin:0 auto" >
<p>In order to enter in edit mode, select a grid cell and start typing, "Click" or press the "F2" key.  To cancel the cell editing, press the "Esc" key. To save
    the changes press the "Enter" key or select another Grid cell. Pressing the 'Space' key when a checkbox cell is selected will toggle the check state.</p>
</div>

<div id='jqxWidget' style="font-size: 13px; font-family: Verdana; float: left;"></div>
    <div style="width: 50%; margin:0 auto" id="piGrid"></div>

</body>
</html>

// php file getPIs.php
//mysql connection lines deleted for posting

if(isset($_GET['insert']))
{
    $lastName=$_GET['lastName'];
    $firstName=$_GET['firstName'];
    $phone=$_GET['phone'];
    $email=$_GET['email'];

    $stmt = $dbh->prepare("insert into pi (lastName, firstName, phone, email) VALUES (:lastName, :firstName, :phone, :email)");
//    $stmt->bindParam(':lastName', $lastName);
//    $stmt->bindParam(':fastName', $firstName);
//    $stmt->bindParam(':phone', $phone);
//    $stmt->bindParam(':email', $email);

    $stmt->bindParam(':lastName', "ss");
    $stmt->bindParam(':fastName', "ss");
    $stmt->bindParam(':phone', "ss");
    $stmt->bindParam(':email', "ss");

    $rs=$stmt->execute();

    echo $rs;
}
else if(isset($_GET['update']))
{
    $lastName=$_GET['lastName'];
}
else
{
    try {
        $rs = $db->query("select lastName, firstName, phone, email from pi order by lastName");
    }
    catch (PDOException $ex) {
        echo "Error:";
    }
       $arr = array();
       while($obj = $rs->fetch(PDO::FETCH_ASSOC)) {
              $arr[] = $obj;
       }
       //header("Content-type: application/json");
       echo json_encode($arr);
}
?>

July 28, 2014 at 6:50 pm PHP, MySQL Grid Problem #57768

Peter Stoev

Keymaster

Hi smithgr,

The main problem here is that you did not bind jqxGrid to jqxDataAdapter. This is required. Please, look at the http://www.jqwidgets.com/jquery-widgets-documentation/documentation/jqxgrid/jquery-grid-datasources.htm to learn how to data bind jqxGrid.

Best Regards,
Peter Stoev

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

July 29, 2014 at 6:14 pm PHP, MySQL Grid Problem #57793

smithgr@jhmi.edu

Participant

The update works fine, but the add is driving me nuts.  I want to open a blank row and use the update to post the change back to mysql.  It looks like I need to get the db index created and add it to the grids new row, but how do I get the new id from the db?  Thanks!!

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html lang="en">
<head>
    <title>Getting Started</title>    <!-- add one of the jQWidgets styles -->
    <link rel="stylesheet"     href="jqwidgets/styles/jqx.base.css" type="text/css" />
    <link rel="stylesheet"     href="jqwidgets/styles/jqx.energyblue.css" type="text/css" />
    <!-- add the jQuery script -->
    <script type="text/javascript" src="scripts/jquery-1.10.2.min.js"></script>
    <!-- add the jQWidgets framework -->
    <script type="text/javascript" src="jqwidgets/jqxcore.js"></script>
    <!-- add one or more widgets -->
    <script type="text/javascript" src="jqwidgets/jqxmenu.js"></script>

    <script type="text/javascript" src="jqwidgets/jqxdata.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/jqxgrid.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.selection.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxdatatable.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.columnsresize.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.pager.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxdropdownlist.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxlistbox.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.sort.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.filter.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.edit.js"></script>

    <title id='Description'>In order to enter in edit mode, select a grid cell and start typing, "Click" or press the "F2" key. You
        can also navigate through the cells using the keyboard arrows or with the "Tab" and "Shift + Tab" key combinations. To cancel the cell editing, press the "Esc" key. To save
        the changes press the "Enter" key or select another Grid cell. Pressing the 'Space' key when a checkbox cell is selected will toggle the check state.</title>

</head>
<body>
<script type="text/javascript">
    $(document).ready(function () {

           $("#main_menu").jqxMenu({width:850, height: 30, theme: 'energyblue'});

            var source2 =
            {
                datatype: "json",
                datafields: [{name: 'piIndex'},{ name: 'lastName', type: 'string'}, {name: 'firstName', type: 'string'}, {name: 'phone', type: 'string'}, {name: 'email', type: 'string'}],
                url: 'getPIs.php',
                id: 'piIndex',
                updaterow: function (rowid, rowdata, commit)
                {
                    var data = "update=true&firstName="+rowdata.firstName + "&piIndex="+rowdata.piIndex;
                    data = data + "&lastName="+rowdata.lastName + "&phone="+rowdata.phone+"&email="+rowdata.email;

                    $.ajax({
                                datatype: 'json',
                                url: 'getPIs.php',
                                data: data,
                                success: function (data, status, xhr)
                                {
                                    commit(true);
                                },
                                error: function(jqXHR, textStatus, errorThrown)
                                {
                                    commit(false);
                                }
                            });

                },
                addrow: function (rowid, rowdata, position, commit)
                {
                    var data = {};
                    $.ajax({
                        datatype: 'json',
                        url: 'getPIs.php',
                        data: data,
                        type: 'POST',
                        async: false,
                        cache: false,
                        success: function (data, status, xhr)
                        {
                            commit(true);
                        },
                        error: function(jqXHR, textStatus, errorThrown)
                        {
                            alert("add false");
                            commit(false);
                        }
                    });

                }

            };

            var dataAdapter = new $.jqx.dataAdapter(source2);

            $("#piGrid").jqxGrid({
                width: 850,
                editable: true,
                selectionmode: 'singlecell',
                editmode: 'click',
                columnsresize: true,
                source: dataAdapter,
                pageable: true,
                autoheight: true,
                filterable: true,
                sortable: true,
                enabletooltips: true,
                showtoolbar: true,
                showfilterrow: true,
                rendertoolbar: function (toolbar) {
                    var me = this;
                    var container = $("<div style='margin: 5px;'></div>");
                    toolbar.append(container);
                    container.append('<input id="addrowbutton" type="button" value="Add New Row" />');
                    container.append('<input style="margin-left: 5px;" id="deleterowbutton" type="button" value="Delete Selected Row" />');
                    $("#addrowbutton").jqxButton();

                    $("#addrowbutton").bind('click', function () {
                       var commit = $("#piGrid").jqxGrid('addrow', null, {}, 'first');
                    });

                },
                theme: 'energyblue',
                columns: [{text: 'Last Name', datafield: 'lastName', width: 200},{text: 'First Name', datafield: 'firstName', width: 200},
                    {text: 'Phone', datafield: 'phone', width: 200},{text: 'email', datafield: 'email', width: 250}]

            });

    });
</script>
<div id="main_menu" style="width: 50%; margin:0 auto">
    <ul >
        <li><a href="#">PI</a></li>
        <li><a href="#">Study</a>
        </li><li><a href="#">Inventory</a>
            <ul>
                <li><a href="#">Edit Clinical</a></li>
                <li><a href="#">Bulk Insert Clinical</a></li>
                <li><a href="#">Add-Edit PreClinical Samples</a>
                </li><li><a href="#">Bulk Move Samples</a>
            </li></ul>
        </li>
        <li><a href="#">Reports</a>
            <ul>
                <li>Assay</li>
                <li>Assay with Sum</li>
                <li>Need Assay</li>
                <li>Export To Excel</li>
            </ul>
        </li>
        <li><a href="#">Admin</a>
            <ul>
                <li>Edit Lookup Tables
                    <ul>
                        <li>Affiliation</li>
                        <li>Buidling</li>
                    </ul>
                </li>
            </ul>
        </li>
    
</div>

<br>
<div style="width: 850px; margin:0 auto" >
<p>In order to enter in edit mode, select a grid cell and start typing, "Click" or press the "F2" key.  To cancel the cell editing, press the "Esc" key. To save
    the changes press the "Enter" key or select another Grid cell. Pressing the 'Space' key when a checkbox cell is selected will toggle the check state.</p>
</div>

<div id='jqxWidget' style="font-size: 13px; font-family: Verdana; float: left;"></div>
    <div style="width: 50%; margin:0 auto" id="piGrid"></div>

</body>
</html>

// PHP CODE

if(isset($_GET[‘update’]))
{

$lastName=$_GET[‘lastName’];
$firstName=$_GET[‘firstName’];
$phone=$_GET[‘phone’];
$email=$_GET[’email’];
$piIndex=$_GET[‘piIndex’];

$stmt = $db->prepare(“update pi set lastName=:lastName, firstName=:firstName, phone=:phone, email=:email where piIndex=:piIndex”);
$stmt->bindParam(‘:lastName’, $lastName);
$stmt->bindParam(‘:firstName’, $firstName);
$stmt->bindParam(‘:phone’, $phone);
$stmt->bindParam(‘:email’, $email);
$stmt->bindParam(‘:piIndex’, $piIndex);

$rs=$stmt->execute();

echo $rs;

}
else if(isset($_GET[‘insert’]))
{

$lastName=”aa”;
$firstName=”aa”;
$phone=”aa”;
$email=”aa”;

$stmt = $db->prepare(“insert into pi (lastName, firstName, phone, email) VALUES (:lastName, :firstName, :phone, :email)”);
$stmt->bindParam(‘:lastName’, $lastName);
$stmt->bindParam(‘:firstName’, $firstName);
$stmt->bindParam(‘:phone’, $phone);
$stmt->bindParam(‘:email’, $email);

$rs=$stmt->execute();

echo $rs;
}
else
{
try {
$rs = $db->query(“select piIndex, lastName, firstName, phone, email from pi order by lastName”);
}
catch (PDOException $ex) {
echo “Error:”;
}
$arr = array();
while($obj = $rs->fetch(PDO::FETCH_ASSOC)) {
$arr[] = $obj;
}
//header(“Content-type: application/json”);
echo json_encode($arr);
}

July 29, 2014 at 6:50 pm PHP, MySQL Grid Problem #57799

Peter Stoev

Keymaster

Hi smithgr,

I think that when you add a new row to your DB, you should return to the client the auto-generated ID by your DB. Then in the Ajax call’s success, call commit(true, theNewRowID) where theNewRowIID is passed from the server to the client.

Best Regards,
Peter Stoev

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

July 30, 2014 at 1:50 pm PHP, MySQL Grid Problem #57828

smithgr@jhmi.edu

Participant

That worked! I very impressed with the support you provide. Thanks!

Author

Posts

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

You must be logged in to reply to this topic.