jQuery Grid Plugin - jqGridForum

05/10/2012
02:02

sebastiancb

COlombia

Member

Members

Forum Posts: 23

Member Since:
05/09/2011

Offline

1

Hello , what I need is to load some cities depending of the country, until now when I want to add registers the second combobox does this, but the problem happens when I need to edit the registers then when the form to edit popups the city asociated to the country is not loading in the second combobox, please help to solve this problem and finish my project.

here is the source code

index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Ejemplo</title>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="jquery-ui.js"></script>
<script type="text/javascript" src="grid.locale-es.js"></script>
<script type="text/javascript" src="jquery.jqGrid.min.js"></script>
<link href="jquery-ui.css" rel="stylesheet" type="text/css" />
<link href="ui.jqgrid.css" rel="stylesheet" type="text/css" />

//--------------------------------------
$("#list3").jqGrid('navGrid','#pager3',{edit:true,add:true,del:true,search:true});
//**********************************************************************************
});
</script>
</head>
<body>
<table id="list3"></table>
<div id="pager3"></div>
</body>
</html>

list.php

<?php
$page = $_GET['page']; // get the requested page
$limit = $_GET['rows']; // get how many rows we want to have into the grid
$sidx = $_GET['sidx']; // get index row - i.e. user click to sort
$sord = $_GET['sord']; // get the direction
if(!$sidx) $sidx =1; // connect to the database $cmd->Connect($servidor,$usuario,$clave,$db);
$bd = mysql_connect("localhost","root","") or die("Connection Error: " . mysql_error());
mysql_select_db("depselect") or die("Error conecting to db.");
   $result = mysql_query("SELECT COUNT(*) AS count FROM people");
   $row = mysql_fetch_array($result);
   $count = $row['count'];
   if( $count >0 ) { $total_pages = ceil($count/$limit); }
   else { $total_pages = 0; }
   if ($page > $total_pages)
   $page=$total_pages;
   $start = $limit*$page - $limit;
   $sql = "SELECT idp,name,namect,namecy from people,countries,cities where people.country=countries.id and people.city=cities.id order by $sidx $sord LIMIT $start , $limit";
   $result = mysql_query($sql) or die("Couldn t execute query.".mysql_error());

   if ( stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml") )
   { header("Content-type: application/xhtml+xml;charset=utf-8"); }
   else { header("Content-type: text/xml;charset=utf-8"); }
   echo "<?xml version='1.0' encoding='utf-8'?>";
   echo "<rows>"; echo "<page>".$page."</page>";
   echo "<total>".$total_pages."</total>";
   echo "<records>".$count."</records>"; // be sure to put text data in CDATA
   while($row = mysql_fetch_array($result) )
   { echo "<row id='". $row['idp']."'>";
     echo "<cell>". $row['idp']."</cell>";
     echo "<cell>". $row['name']."</cell>";
     echo "<cell>". $row['namect']."</cell>";
     echo "<cell>". $row['namecy']."</cell>";
   echo "</row>";
   }
   echo "</rows>";
   mysql_close($bd);
?>

load_countries.php

<?php
$bd = mysql_connect("localhost","root","") or die("Connection Error: " . mysql_error());
mysql_select_db("depselect") or die("Error conecting to db.");
$sql="select id,namect from countries";
$rs = mysql_query($sql);
$countries="";
while ($reg=mysql_fetch_array($rs)) {
$countries=$countries.$reg['id'].":".$reg['namect'].";";
}

$countries=substr($countries,0,strlen($countries)-1);
echo utf8_encode($countries);
?>

load_cities.php

<?php
$bd = mysql_connect("localhost","root","") or die("Connection Error: " . mysql_error());
mysql_select_db("depselect") or die("Error conecting to db.");
$id_country=$_GET['id_country'];
$sql="select id,namecy from cities where idc=$id_country";
$rs = mysql_query($sql);
while ($reg=mysql_fetch_array($rs)) {
echo "<option value=".$reg['id'].">".$reg['namecy']."</option>";
}
mysql_close($bd);
?>

database

CREATE TABLE `cities` (
`id` int(11) NOT NULL,
`idc` int(11) NOT NULL,
`namecy` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `idc` (`idc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcar la base de datos para la tabla `cities`
--

INSERT INTO `cities` (`id`, `idc`, `namecy`) VALUES
(0, 0, '----'),
(1, 1, 'Washington'),
(2, 1, 'New York'),
(3, 2, 'Berlin'),
(4, 2, 'Hannover');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `countries`
--

CREATE TABLE `countries` (
`id` int(11) NOT NULL,
`namect` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcar la base de datos para la tabla `countries`
--

INSERT INTO `countries` (`id`, `namect`) VALUES
(0, '-----'),
(1, 'USA'),
(2, 'Germany');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `people`
--

CREATE TABLE `people` (
`idp` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`country` int(11) NOT NULL,
`city` int(11) NOT NULL,
PRIMARY KEY (`idp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcar la base de datos para la tabla `people`
--

INSERT INTO `people` (`idp`, `name`, `country`, `city`) VALUES
(1, 'Johann', 2, 3),
(2, 'John', 1, 1);

--
-- Filtros para las tablas descargadas (dump)
--

--
-- Filtros para la tabla `cities`
--
ALTER TABLE `cities`
ADD CONSTRAINT `cities_ibfk_1` FOREIGN KEY (`idc`) REFERENCES `countries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

16/10/2012
18:00

ppr

France

Member

Members

Forum Posts: 13

Member Since:
17/02/2009

Offline

2

Hi,

In your index.php change this :

function(data)
{ $("select#namecy").html(data);
}); // end get

by this

function(data)
{ $("#namecy").html(data);
}); // end get

and it will be Ok

Best regards

Phil

19/10/2012
18:16

sebastiancb

COlombia

Member

Members

Forum Posts: 23

Member Since:
05/09/2011

Offline

3

Hello, thank you for your help, but when I add a register this example works fine, the problem is when I need edit such register because the city associated with the register is not the right.

The problem is this line:

{name:'namecy',index:'namecy',editable: true,edittype:"select",editoptions:{dataUrl:'load_cities.php?id_country=1'}}

20/10/2012
15:27

ppr

France

Member

Members

Forum Posts: 13

Member Since:
17/02/2009

Offline

4

Hi sebastian,

This line :

{name:'namecy',index:'namecy',editable: true,edittype:"select",editoptions:{dataUrl:'load_cities.php?id_country=1'}}

doesn't work because it needs to be changed dynamically on each record.

To make your project running, here is a quick solution:

In index.php, replace this code

editurl:"edit.php"

by

editurl:"edit.php",
onSelectRow: function(rowid) {

                    if (rowid != null) {
                        var record = $("#list3").getRowData(rowid);
                        var pgm="load_cities.php?country="+record.namect;
                        $("#list3").setColProp('namecy',{editoptions:{dataUrl:pgm}});
                        }
                    }

On each record, it will change dynamically the call of "load_cities.php" with the value of "namect". Be careful, it'not the id_country value because it's not existing in the jqgrid...it's a name country value. We change the load_cities.php to manage this case below.

next: change this code in index.php

$("#list3?).jqGrid('navGrid','#pager3',{edit:true,add:true,del:true,search:true});

by this:

$("#list3").jqGrid('navGrid','#pager3',{edit:true,add:true,del:true,search:true},

   { viewPagerButtons:false,
    recreateForm:true
    });

RecreateForm=true : it's necessary to refresh dropdown cities when the form edit appears

viewPagerButtons=false : disable the next and prev buttons because the Form Edit requires a special management for next/prev buttons to initialize dynamic dropdown cities and the source code would become a little more complicated. (we can see this management in a next step if wished).

Then replace your load_cities.php by this new one (to manage the name country parameter case):

<?php

$bd = mysql_connect("localhost","root","") or die("Connection Error: " . mysql_error());
mysql_select_db("columbia") or die("Error conecting to db.");
$id_country=$_GET['id_country'];

if(isset($id_country))
    $sql="select id,namecy from cities where idc=$id_country";
else {
    $country= $_GET['country'];
    $sql="select cities.id,cities.namecy from countries inner join cities on countries.id=cities.idc where countries.namect='$country'";
}

$rs = mysql_query($sql);
echo "<select>";
while ($reg=mysql_fetch_array($rs)) {
echo "<option value='".$reg['id']."'>".$reg['namecy']."</option>";

}
echo"</select>";
mysql_close($bd);

?>

Regards,

Phil

03/11/2012
22:24

sebastiancb

COlombia

Member

Members

Forum Posts: 23

Member Since:
05/09/2011

Offline

5

Hello, excuse me for bothering you, I replaced the code where you indicated me, but I don't know what to do with this line

{name:'namecy',index:'namecy',editable: true,edittype:"select",editoptions:{dataUrl:'load_cities.php?id_country=1'}}

if I deleted it when I want to add a register the dropdown doesn't work.

here is the complete source with the modifications:

$("#list3").jqGrid({ url:'list.php',
datatype: "xml",
colNames:['Id','name','country','city'],
colModel:[{name:'idp',index:'idp', width:100,sortable:true,editable:true},
          {name:'name',index:'name', width:100,sortable:false,editable:true,sortable:true},
          {name:'namect',index:'namect', width:150,sortable:false,editable:true,sortable:true,
               edittype:'select',editoptions: {value:load_countries(),
                                                   dataEvents:[{type: 'change', fn: function(e) {
                                                                     var thisval = $(e.target).val();
                                                                      $.get('load_cities.php?id_country='+thisval,
                                                                      function(data)
                                                                      { $("select#namecy").html(data);
                                                                      }); // end get
                                                               }//end func
                                                             } // end type
                                                            ] // dataevents
                                              } // edit option
                                             },
          {name:'namecy',index:'namecy',editable: true,edittype:"select",editoptions:{dataUrl:'load_cities.php?id_country=1'}}],
        recreateForm:true,
        rowNum:10,
        rowList:[10,20,30],
        pager: '#pager3',
        sortname: 'idp',
        viewrecords: true,
        sortorder: "asc",
        xmlReader: {root: "rows",
                            repeatitems : true,
                           id: "[id]"
                          },
        caption: "Students",
        height:'100%',
        editurl:"edit.php",
        onSelectRow: function(rowid) {

                    if (rowid != null) {
                        var record = $("#list3").getRowData(rowid);
                        var pgm="load_cities.php?country="+record.namect;
                        $("#list3").setColProp('namecy',{editoptions:{dataUrl:pgm}});
                        }
                    }
});

//————————————–
$("#list3").jqGrid('navGrid','#pager3',{edit:true,add:true,del:true,search:true},
{ viewPagerButtons:false,
recreateForm:true
});

13/11/2012
16:38

ppr

France

Member

Members

Forum Posts: 13

Member Since:
17/02/2009

Offline

6

Hi,

you must leave this line in the source code. Otherwise it will not work.

Your complete source with modifications in your message is correct.

Note, as this version is quick and dirty to show one of the ways of managing a depend combobox mechanism. It does not handle record saving.

If you want to use "save record" button, your edit.php has to manage the conversion from "country name" string and "city name" string into "country" id and "city" id.

Regards

17/11/2012
23:44

sebastiancb

COlombia

Member

Members

Forum Posts: 23

Member Since:
05/09/2011

Offline

7

Hello, it worked, but I had to modify the load_cities.php->

<?php
$bd = mysql_connect("localhost","root","") or die("Connection Error: " . mysql_error());
mysql_select_db("depselect") or die("Error conecting to db.");

if(isset($_GET['id_country'])){
   $id_country=$_GET['id_country'];
    $sql="select id,namecy from cities where idc=$id_country";
}
else {
    $country= $_GET['country'];
    $sql="select cities.id,cities.namecy from countries inner join cities on countries.id=cities.idc where countries.namect='$country'";
}

$rs = mysql_query($sql);
echo "<select>";
while ($reg=mysql_fetch_array($rs)) {
echo "<option value='".$reg['id']."'>".$reg['namecy']."</option>";

}
echo"</select>";
mysql_close($bd);
?>

Now, you had told me that there is a way to enable the buttons "next" and "prev", I would like that you helped me with that

20/11/2012
00:16

ppr

France

Member

Members

Forum Posts: 13

Member Since:
17/02/2009

Offline

8

Hi,

Ok, to handle the "next" et "previous" button in edit form:

Replace in the index.php

onSelectRow: function(rowid) {

                    if (rowid != null) {
                        var record = $("#list3").getRowData(rowid);
                        var pgm="load_cities.php?country="+record.namect;
                        $("#list3").setColProp('namecy',{editoptions:{dataUrl:pgm}});
                        }
                    }

by this:

onSelectRow: function(rowid) {

                    if (rowid != null) {
                        var record = $("#list3").getRowData(rowid);
                        var pgm="load_cities.php?country="+record.namect;
                        $("#list3").setColProp('namecy',{editoptions:{dataUrl:pgm}});
                        if (gbFormEditOpen) {
                            // management of Form Edit particularly to manage dropdown
                            // get the dynamically selection dropdown
                            $.get(pgm, function(data) {
                                    // remove the <select></selected> tag from received data
                                    // to keep only the options
                                data=data.replace("<select>","");
                                data=data.replace("</select>","");
                                    // clear the dropdown of FormEdit and fill it with new data
                                $("#namecy").empty().append(data);
                                    // and put the selected item choose in jqGrid
                                $("#namecy").find("option:contains('"+record.namecy+"')").attr("selected","selected");

                            });


                        }
                    }
        }

and replace this code

$("#list3″).jqGrid('navGrid','#pager3',{edit:true,add:true,del:true,search:true},
{ viewPagerButtons:false,
recreateForm:true
});

by this:

$("#list3").jqGrid('navGrid','#pager3',{edit:true,add:true,del:true,search:true},
    {
    recreateForm:true,
    afterShowForm : function (formid) {gbFormEditOpen=true;},
    onClose: function(s) {gbFormEditOpen=false;}
    });

and replace this:

$(document).ready(function(){

by this

var gbFormEditOpen=false; // global variable to know the state of FormEdit: true=open, false=close

$(document).ready(function(){

That's all !

Regards

Philippe