I hope some of you find this useful. What I needed to do was to query a table, and have the
results formatted in an html table without having to hard code the html in the snippet code.
I wanted the snippet code to be generic and reusable as well as the chunk.
Here is what I came up with.
1) Create a Chunk to display the results:
<table id="tb1" border=0 cellspacing=3 cellpadding=1 width=50% BGCOLOR="#FF9933" CELLSPACING=3 CELLPADDING=1>
<tr>
<td width=50%>
<b>[+name+]</b>
[+description+] </br>
<p></p>
</td>
<td width=10% align=right>
<b>[+price+]</b>
</td>
</table>
2) Declare 3 tv’s: tvDBName, tvUname, tvPwd. For each tv, use the Default Value field to seed the tv.
(Make sure to assign the tv’s to your template(s))
3) Create a template to call the snippet:
<p>[[snipDoQuery? &uname=`[*tvUname*]` &pwd=`[*tvPwd*]` &dbname=`[*tvIndMenus*]` &qry=`SELECT * FROM vegetarian` &chnkName=`chkMnu`]]</p>
4) Create a new snippet and paste the following code:
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// snipDoQuery - A fairly simple generic mechanism for performing mySQL db queries using tv’s to
// provide basic table retrieval.
//
// Arguments:
// $uname - mySQL username - (TV)
// $pwd - mySQL password - (TV)
// $dbname - mySQL database name - (TV)
// $qry - SQL query string - (CHUNK)
// $chnkName - Name of chunk to receive query results - (CHUNK)
//
// Revision history: (Make entries in revserse chron. order please)
//==============
// o Revised: 25-mar-2010 Robert Fonda
// Improved error reporting. Tidy up code comments
//
// o Revised: 2-mar-2010 Robert Fonda/Steve Klein
// Removed all the row references since they are resolved by the single call to
// $modx->parseChunk($chnkName, $row, ’[+’, ’+]’);
//
// Also removed the $tbl argument since this can be passed by the
// $qry argument from the calling chunk.
//
// Moved the mysql_close() below the test for success
// on the query since we don’t need it after that.
// Don’t need it, get rid of it.
//
// o Revised: 2-mar-2010 Robert Fonda
// Removed most of the hard coded references and moved them to tv’s or chunks.
//
// o Initial version: 1-mar-2010 Robert Fonda
//------------------------------------------------------------------------------------------------------------------------
// Initialize
$output = ’’;
$link = ’’;
$result = ’’;
$row = ’’;
$message = ’’;
// Connect to the database server
$link = mysql_connect(’localhost’,$uname,$pwd);
if (!$link)
{
print ’snipDoQuery - Err - mysql_connect():
’;
die(mysql_error());
}
// Select the database to perform queries on
if (!mysql_select_db($dbname))
{
print ’snipDoQuery - Err-mysql_select_db():
’;
die(mysql_error());
}
// Execute the query passed by the calling chunk. The entire query string
// is created in the calling chunk
$result=mysql_query(mysql_real_escape_string($qry));
if (!$result)
{
$message = ’snipDoQuery - Err-Invalid query: ’ . mysql_error() . ’\n’;
$message .= ’Whole query: ’ . $qry;
mysql_close($link);
die($message);
}
// Since we’re done with the database, might as well close the connection now
mysql_close($link);
// set the placeholders
while ($row = mysql_fetch_assoc($result))
$output .= $modx->parseChunk($chnkName, $row, ’[+’, ’+]’);
// now return the output of the processed chunk
return $output;
If anyone has any a better way or comments on my approach I would be pleased to hear them.
Please feel free to use this and modify to suit your needs.
Robert
