Creating XML Output for jqgrid by Modx-DB-Api

40 Posts

LeftHanded Reply #1, 18 years, 1 month ago

In the last days i solved the connect from jqgrid ( http://www.trirand.com/jqgrid/jqgrid.html ) to the modx-db and i want to share the solution with this great community.
these are the steps:

Embed jquery in your template

<script src="assets/js/jquery/jquery.js" type="text/javascript"></script>

Embed jqGrid in your template

<script src="assets/js/jquery/jquery.jqGrid.js" type="text/javascript"></script>

Create your jqGrid Datamodel and embed in your page via chunk

<script type="text/javascript">
jQuery(document).ready(function(){
jQuery("#list1").jqGrid({ 
url:'http://myhomepage.de/index.php?id=100027', // this is the page where the XML Output is delivered
datatype: "xml", 
colNames:['Field1','Field2','Field3','Field4','Field5'], 
colModel:[ 
{name:'Field1',index:'Field1', width:180}, 
{name:'Field2',index:'Field2', width:140}, 
{name:'Field3',index:'Field3', width:175}, 
{name:'Field4',index:'Field4', width:45},
{name:'Field5',index:'Field5', width:85}
], 
rowNum:10, 
rowList:[10,20,30], 
imgpath: 'assets/js/jquery/themes/sand/images', 
pager: jQuery('#pager1'), 
sortname: 'Field1', 
viewrecords: true, 
sortorder: "desc", 
width: 620, // new width 
height: 220,
rowheight: 10, // newrow height
caption: "Table Caption"
});
}); 
jQuery("#list1").navGrid('#pager1',{edit:false,add:false,del:false,refresh:true,searchtext:"Suchen"}); 
</script>

Create a snippet and embed it with assigning template = (blank) and the content type : text/xml to your XML deliviring page

<?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(!$page) $page =1;
if(!$sidx) $sidx ="field1";
if(!$limit) $limit =10;
if(!$sord) $sord = "DESC";
// connect to the database and count records
// Query synthax '*' or 'field1,field2,...' , 'table or view' , 'where clause','sort','limit'
$cntquery = $modx->db->select('*','mytable');
$count = $modx->db->getRecordCount($cntquery); 
if( $count >0 ) { 
$total_pages = ceil($count/$limit); 
} 
else 
{ $total_pages = 0; 
} 
if ($page > $total_pages) $page=$total_pages; 
$start = $limit*$page - $limit; 
// do not put $limit*($page - 1) 
if ( stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml") ) { header("Content-type: application/xhtml+xml;charset=ISO8859-1"); 
} 
else 
{ 
header("Content-type: text/xml;charset=utf-8"); 
} 
$et = ">"; 
echo "<?xml version='1.0' encoding='utf-8'?$et\n"; 
echo "<rows>"; 
echo "<page>".$page."</page>"; 
echo "<total>".$total_pages."</total>"; 
echo "<records>".$count."</records>";
// be sure to put text data in CDATA 
// Query synthax '*' or 'field1,field2,...' , 'table or view' , 'where clause','sort','limit'
$query = $modx->db->select('*','mytable','field1>0',$sidx.' '.$sord,$start.','.$limit);
while($row = $modx->db->getRow($query)) {
echo "<row id='". $row[field1]."'>"; 
echo "<cell><![CDATA[".htmlentities($row[field1])."]]></cell>"; 
echo "<cell><![CDATA[".htmlentities($row[field2])."]]></cell>"; 
echo "<cell><![CDATA[".htmlentities($row[field3])."]]></cell>"; 
echo "<cell><![CDATA[". $row[field4]."]]></cell>"; 
echo "<cell>". $row[field5]."</cell>"; 
echo "</row>";
}
echo "</rows>";
?>

and finally put html code via chunk in your template ( this where the grid will be shown )

<div style="background-color:#E5DDCA; position:absolute; top:54px; left:240px; width:350px; z-index:9;">
<table id="list1" class="scroll" cellpadding="0" cellspacing="0"></table> <div id="pager1" class="scroll" style="text-align:center;"></div></div>

thats all... hope that anyone will use this or commenting this to do things easier
greets
LeftHanded

I love ModX!

5 Posts

crayco Reply #2, 17 years, 9 months ago

Thank you soooo much!!!
Modx definitely needed a better data management tool

There seems to be a missing </div> tag at the end of :

<div style="background-color:#E5DDCA; position:absolute; top:54px; left:240px; width:350px; z-index:9;">
<table id="list1" class="scroll" cellpadding="0" cellspacing="0"></table> <div id="pager1" class="scroll" style="text-align:center;"></div>

And don’t forget to change the path for jquery.jqGrid.js to access its includes. So edit jquery.jqGrid.js line 3 to what ever is your path.

var pathtojsfiles = "js/";

Finally, working in French and using utf-8, I had issue with accentuated characters comming from the snippet generating the xml. To have my accentuated character back I removed the .htmlentities() call for each fields :

echo "<cell><![CDATA[".htmlentities($row[field1])."]]></cell>";

5 Posts

crayco Reply #3, 17 years, 8 months ago

Using in-line editing, I haven’t found the way to save the data on mysql server. I get a 404 error (inside a pop up). I guess its the editurl option that doesn’t work. But I’m not sure how to fix this.

Anyone have an idea?

40 Posts

LeftHanded Reply #4, 17 years, 8 months ago

Quote from: crayco at Jan 05, 2009, 12:00 PM

Using in-line editing, I haven’t found the way to save the data on mysql server. I get a 404 error (inside a pop up). I guess its the editurl option that doesn’t work. But I’m not sure how to fix this.

Anyone have an idea?

What did Firebug result in Console? btw. you know flexigrid? http://code.google.com/p/flexigrid/
A lot of people currently working on your wanted functionalitiy, ( but at different grid ) take a look at their code... maybe it helps

I love ModX!

19 Posts

triden Reply #5, 17 years, 8 months ago

hi there,

i try to use jqgrid but till now i failed.
here the steps i make.
i use my basic template and added
<script src="assets/js/jquery/jquery.jqGrid.js" type="text/javascript"></script>
<script src="assets/js/jquery/jquery.jqGrid.js" type="text/javascript"></script>

then i make a document uses my basic template. (doc id = 104)
here i call [!jqgrid!]

then i make a empty document blank and text/xml (doc id= 97)

then i make a chunk with name jqTelefoon and put javascript in there
line 4 i changed to url:’http://intranet2008/index.php?id=97’

then i make a snippet name jqgrid and put this in there:

<?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(!$page) $page =1;
if(!$sidx) $sidx ="tlf_naam";
if(!$limit) $limit =10;
if(!$sord) $sord = "DESC";
// connect to the database and count records
// Query synthax '*' or 'field1,field2,...' , 'table or view' , 'where clause','sort','limit'
$cntquery = $modx->db->select('*','hkp_telefoon');
$count = $modx->db->getRecordCount($cntquery); 
if( $count >0 ) { 
$total_pages = ceil($count/$limit); 
} 
else 
{ $total_pages = 0; 
} 
if ($page > $total_pages) $page=$total_pages; 
$start = $limit*$page - $limit; 
// do not put $limit*($page - 1) 
if ( stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml") ) { header("Content-type: application/xhtml+xml;charset=ISO8859-1"); 
} 
else 
{ 
header("Content-type: text/xml;charset=utf-8"); 
} 
$et = ">"; 
echo "<?xml version='1.0' encoding='utf-8'?$et\n"; 
$modx->getChunk('jqTelefoon'); 
echo($modx->putChunk('jqTelefoon')); 
echo "<rows>"; 
echo "<page>page: ".$page."</page>"; 
echo "<total>total: ".$total_pages."</total>"; 
echo "<records>records: ".$count."</records>";
// be sure to put text data in CDATA 
// Query synthax '*' or 'field1,field2,...' , 'table or view' , 'where clause','sort','limit'
$query = $modx->db->select('*','hkp_telefoon','',$sidx.' '.$sord,$start.','.$limit);
while($row = $modx->db->getRow($query)) {
//echo2($row);
echo "<row id='". $row[tlf_id]."'>"; 
echo "<cell><![CDATA[".htmlentities($row[tlf_naam])."]]></cell>"; 
echo "<cell><![CDATA[".htmlentities($row[tlf_tel1])."]]></cell>"; 
echo "<cell><![CDATA[".htmlentities($row[tlf_tel2])."]]></cell>"; 
echo "<cell><![CDATA[". $row[tlf_afdeling]."]]></cell>"; 
//echo "<cell>". $row[field5]."</cell>"; 
echo "</row>";
}
echo "</rows>";
?>

result:
page: 1total: 1records: 3
and
2 error messages:
object not supperted line 84 char 1 and line 61 char 1.
here is the generated html:

<!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>
  <title> Intranet | grid</title>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  <link rel="stylesheet" href="assets/templates/modxhost2/layout.css" type="text/css" media="screen" />
  <link rel="stylesheet" href="assets/templates/modxhost2/modxmenu.css" type="text/css" media="screen" />
  <link rel="stylesheet" href="assets/templates/modxhost2/form.css" type="text/css" media="screen" />
  <link rel="stylesheet" href="assets/templates/modxhost2/modx.css" type="text/css" media="screen" />
  <link rel="stylesheet" href="assets/templates/modxhost2/print.css" type="text/css" media="print" />
  <link rel="alternate" type="application/rss+xml" title="RSS 2.0" href="http://intranet2008/index.php?id=11" />
  <base href="http://intranet2008/">
<script src="assets/js/jquery/jquery.js" type="text/javascript"></script>
<script src="assets/js/jquery/jquery.jqGrid.js" type="text/javascript"></script>
  <script src="manager/media/script/mootools/mootools.js" type="text/javascript"></script>
  <script src="assets/templates/modxhost2/drop_down_menu.js" type="text/javascript"></script>
</head>
<body>
<div id="wrapper">
  <div id="minHeight"></div>
  <div id="outer">
    <div id="inner">
      <div id="right">
        <div id="right-inner">
          <h1 style="text-indent: -5000px;padding: 0px; margin:0px; font-size: 1px;">Intranet</h1>
          <div id="sidebar">
            <h2>News:</h2>
            <strong><a href="index.php?id=79" title="Admin">Admin</a></strong><br />
<br /><strong><a href="index.php?id=58" title="Zoeken">Zoeken</a></strong><br />
<br />
            <div id="recentdocsctnr">
              <h2>Most Recent:</h2>
              <a name="recentdocs"></a><ul class="LIn_shortMode">
<li><a href="index.php?id=104" title="grid">grid</a> <span class="LIn_date">2009.01.20</span> <span class="LIn_desc"></span></li>
<li><a href="index.php?id=103" title="Manager">Manager</a> <span class="LIn_date">2009.01.14</span> <span class="LIn_desc">Functies die voor de managers zijn gemaakt</span></li>
<li><a href="index.php?id=103" title="Manager">Manager</a> <span class="LIn_date">2009.01.14</span> <span class="LIn_desc">Functies die voor de managers zijn gemaakt</span></li>
</ul>
 </div>
            <h2>Login:</h2>
            <div id="sidebarlogin">Aangemeld als: </div>
            <h2>Meta:</h2>
            <p><a href="http://validator.w3.org/check/referer" title="This page validates as XHTML 1.0 Transitional">Valid <abbr title="eXtensible HyperText Markup Language">XHTML</abbr></a></p>
            <p><a href="http://jigsaw.w3.org/css-validator/check/referer" title="This page uses valid Cascading Stylesheets" rel="external">Valid <abbr title="W3C Cascading Stylesheets">css</abbr></a></p>
            <p><a href="http://modxcms.com" title="Ajax CMS and PHP Application Framework">MODx</a></p>
          </div>
          <!-- close #sidebar -->
        </div>
        <!-- end right inner-->
      </div>
      <!-- end right -->
      <div id="left">
        <div id="left-inner">
          <div id="content">
            <div class="post">
              <h2>grid</h2>
              <p><span style="font-family: 'Times New Roman'; font-size: 16px;">
<div style="color: #000000; font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; background-image: initial; background-repeat: initial; background-attachment: initial; -webkit-background-clip: initial; -webkit-background-origin: initial; background-color: #ffffff; background-position: initial initial; margin: 8px;">
<p><?xml version='1.0' encoding='utf-8'?>
<script type="text/javascript">
jQuery(document).ready(function(){
jQuery("#list1").jqGrid({ 
url:'http://intranet2008/index.php?id=97', // this is the page where the XML Output is delivered
datatype: "xml", 
colNames:[ 'tlf_naam', 'tlf_tel1', 'tlf_tel2', 'tlf_afdeling'], 
colModel:[ 
{name:'tlf_naam',index:'tlf_naam', width:180}, 
{name:'tlf_tel1',index:'tlf_tel1', width:140}, 
{name:'tlf_tel2',index:'tlf_tel2', width:175}, 
{name:'tlf_afdeling',index:'tlf_afdeling', width:45}
], 
rowNum:10, 
rowList:[10,20,30], 
imgpath: 'assets/js/jquery/themes/sand/images', 
pager: jQuery('#pager1'), 
sortname: 'tlf_naam', 
viewrecords: true, 
sortorder: "desc", 
width: 620, // new width 
height: 220,
rowheight: 10, // newrow height
caption: "Table Caption"
});
}); 
jQuery("#list1").navGrid('#pager1',{edit:false,add:false,del:false,refresh:true,searchtext:"Suchen"}); 
</script>
<div style="background-color:#E5DDCA; position:absolute; top:54px; left:240px; width:350px; z-index:9;">
<table id="list1" class="scroll" cellpadding="0" cellspacing="0"></table> <div id="pager1" class="scroll" style="text-align:center;"></div></div>

<rows><page>page: 1</page><total>total: 1</total><records>records: 3</records><row id='4'><cell><![CDATA[Robert Westenbroek]]></cell><cell><![CDATA[9015]]></cell><cell><![CDATA[0655154040]]></cell><cell><![CDATA[Automatisering]]></cell></row><row id='3'><cell><![CDATA[Rob Oude Engberink]]></cell><cell><![CDATA[9011]]></cell><cell><![CDATA[0655154321]]></cell><cell><![CDATA[]]></cell></row><row id='1'><cell><![CDATA[lala]]></cell><cell><![CDATA[lala1]]></cell><cell><![CDATA[lala2]]></cell><cell><![CDATA[]]></cell></row></rows></p>
</div>
</span></p> </div>
            <!-- close .post (main column content) -->
          </div>
          <!-- close #content -->
        </div>
        <!-- end left-inner -->
      </div>
      <!-- end left -->
    </div>
    <!-- end inner -->
    <div id="clearfooter"></div>
    <div id="header">
      <h1><a id="logo" href="index.php?id=1" title="Intranet">Intranet</a></h1>
<!--      <div id="search">-->
<!--search_terms -->
<!--      
    </div> -->
      <div id="ajaxmenu">   <ul id="myajaxmenu">
    <li class="category first"><a href="http://intranet2008/" title="Startpagina">Home</a></li>
<li class="category "><a href="/index.php?id=93" title="Tools">Tools</a><ul style="display:none">
  <li class="first"><a href="/index.php?id=98" title="Telefoonlijst">Telefoonlijst</a></li>
<li class="last"><a href="/index.php?id=94" title="Dagstaten tools">Dagstaten tools</a></li>

</ul></li>
<li class="category "><a href="/index.php?id=2" title="Beheer">Beheer</a><ul style="display:none">
  <li class="first"><a href="/index.php?id=52" title="Rapportage">Rapportage</a><ul style="display:none">
  <li class="first"><a href="/index.php?id=49" title="DD - Statistieken">DD - Statistieken</a></li>
<li><a href="/index.php?id=51" title="Scan - Statistieken">Scan - Statistieken</a></li>
<li class="last"><a href="/index.php?id=87" title="Bemanning">Bemanning</a></li>

</ul></li>
<li><a href="/index.php?id=47" title="Dagstaten">Dagstaten</a><ul style="display:none">
  <li class="first"><a href="/index.php?id=48" title="Dagstaten truck">Dagstaten truck</a></li>

</ul></li>
<li><a href="/index.php?id=58" title="Zoeken">Zoeken</a></li>
<li class="last"><a href="/index.php?id=79" title="Admin">Admin</a><ul style="display:none">
  <li class="first"><a href="/index.php?id=95" title="Scan Bulkwijziging">Scan Bulkwijziging</a></li>
<li class="last"><a href="/index.php?id=80" title="OM - Controle">OM - Controle</a></li>

</ul></li>

</ul></li>
<li class="category "><a href="/index.php?id=33" title="Bugs">Bugs</a></li>
<li class="category "><a href="/index.php?id=103" title="Manager">Manager</a><ul style="display:none">
  <li class="first"><a href="/index.php?id=50" title="Bakindeling">Bakindeling</a></li>
<li><a href="/index.php?id=101" title="iPos Facturen">iPos Facturen</a></li>
<li class="last"><a href="/index.php?id=102" title="Kwaliteitssysteem">Kwaliteitssysteem</a></li>

</ul></li>
<li class="category "><a href="/index.php?id=6" title="Contact Us">Contact us</a><ul style="display:none">
  <li class="first"><a href="/index.php?id=72" title="Betermelding">Betermelding</a></li>
<li><a href="/index.php?id=75" title="Nieuwe Klant">Nieuwe Klant</a></li>
<li><a href="/index.php?id=69" title="Salaris Wijziging">Salaris Wijziging</a></li>
<li class="last"><a href="/index.php?id=71" title="Ziekmelding">Ziekmelding</a></li>

</ul></li>
<li class="category "><a href="/index.php?id=15" title="MODx Features">Features</a><ul style="display:none">
  <li class="first"><a href="/index.php?id=16" title="Ajax">Ajax</a></li>
<li><a href="/index.php?id=22" title="Menus and Lists">Menus and Lists</a></li>
<li><a href="/index.php?id=14" title="Content Management">Manage Content</a></li>
<li class="last"><a href="/index.php?id=24" title="Extendable by design">Extendability</a></li>

</ul></li>
<li class="category "><a href="/index.php?id=88" title="Sitemap">Sitemap</a></li>
<li class="category last"><a href="/index.php?id=104" title="grid">grid</a></li>

  </ul> </div>
      <!-- end topmenu -->
    <!-- end header -->
    </div> 
    <br style="clear:both;height:0;font-size: 1px" />
    <div id="footer">
      <p> <a href="http://modxcms.com" title="Ajax CMS and PHP Application Framework">Powered
          by MODx</a>  <a href="http://www.modxhost.com/" title="Template Designed by modXhost.com">Template © 2006
          modXhost.com</a><br />
        MySQL: 0.0020 s, 24 request(s), PHP: 0.0977 s, total: 0.0997 s, document retrieved
        from database. </p>
    </div>
    <!-- end footer -->
  </div>
  <!-- end outer div -->
</div>
<!-- end wrapper -->
</body>
</html>

any ideas?

no mail. no icq, no aim, no msn, no yim, no sms, no phone, no fax, no telex, no satellite.... just yell

40 Posts

LeftHanded Reply #6, 17 years, 8 months ago

Quote from: triden at Jan 20, 2009, 05:01 AM

...
header("Content-type: text/xml;charset=utf-8");
}
$et = ">";
echo "<?xml version=’1.0’ encoding=’utf-8’?$et\n";
$modx->getChunk(’jqTelefoon’);
echo($modx->putChunk(’jqTelefoon’));
echo "<rows>";
echo "<page>page: ".$page."</page>";
echo "<total>total: ".$total_pages."</total>";
[/QUOTE]
Why do you want to insert your Chunk into the result file?
IMHO thats the reason why the xml output is not valid...
The Chunk must be inserted at the page where the grid will shown, not on the page that delivers the data...
HTH
Greetings LeftHanded

I love ModX!