database/table snippet

I thought i’d share a couple snippets i’ve been working on, and use quite a bit.

The first one takes a sql statement and returns an array as well as an option total for one of the fields. It’s very basic and has no real error checking. I eventually want to add a little more intelligence to it so that it would recognize datatypes and possibly total up multiple fields.

$table = isset($table) ? $modx->getFullTableName($table) : ’empty’;
$fields = isset($fields) ? $fields : ’*’;
$where = isset($where) ? $where : ’’;
$totalfield = isset($totalfield) ? $totalfield: "";
$total=0;
$sql= isset($sql) ? $sql : ’SELECT ’.$fields.’ FROM ’.$table.’ ’.$where.’;’;
if($result=$modx->dbQuery($sql)) {
$resourceArray2 = array();
$fieldname = array();
$n=$a=0;
while ($property = mysql_fetch_field($result)) {
$fieldname[$n]=$property->name;
$n++;
}
for($i=0;$i < $modx->recordCount($result); $i++) {
$row=$modx->fetchRow($result);
$record2[$i]=array();
for($a=0; $a < $n; $a++) {
$field=$fieldname[$a];
$value=$row[$field];
$total = ($field==$totalfield)? $total+$value: $total ;
$temparray[$a]=array($field=>$value);
$record2[$i]=array_merge($record2[$i],$temparray[$a]);
}
array_push($resourceArray2,$record2[$i]);
}
$modx->setPlaceholder(’sql2arrayResult’,$resourceArray2);
$modx->setPlaceholder(’total’,$total);

} else {
$modx->setPlaceholder(’debug’,’no result returned from sql - $sql’);
}
return ;

It can be called with either a table name or an sql statement
eg:

$modx->runSnippet(’sql2array’, array(’table’=>’expenses’, ’totalfield’=>’amount’));
$result=$modx->getPlaceholder(’sql2arrayResult’);
$grandtotal=$modx->getPlaceholder(’totalhours’);
$table=$modx->runSnippet(’ShowData’,array(’array’=>$result,’display’=>’table’))

The last line in the above example refers to the second snippet i use. It outputs an array in a few different formats. As a series of placeholders for uses in forms or templates, or as an unordered list, or as a table, or as a form control such as a dropdown, checkbox, or a textbox.

if(isset($display) && isset($array)) {
if($array) {
if($display=="form") {
for($i=0; $i < count($array); $i++) {
$keys=array_keys($array[$i]);
$keycount=count($keys);
for($n=0; $n < $keycount; $n++) {
$key=$keys[$n];
$modx->setPlaceholder($key, $array[$i][$key]);
}
}
}
if($display=="list") {
$output = "<ul>";
for($i=0; $i < count($array); $i++) {
$output.= "<li>";
$keys=array_keys($array[$i]);
$keycount=count($keys);
for($n=0; $n < $keycount; $n++) {
$key=$keys[$n];
$output .= $array[$i][$key]." ";
}
$output .="</li>";
}
$output.= "</ul>
";
}

if($display=="table") {
$tablewidth = isset($tablewidth) ? $tablewidth : "";
$totalfield= isset($totalfield) ? $totalfield: "";
$modx->setPlaceholder(’debug3’,$totalfield);
$output = "
<table".$tablewidth.">";
$output.= "<tr bgcolor=#FCF6CF>";
$keys=array_keys($array[0]);
$keycount=count($keys);
for($n=0; $n < $keycount; $n++) {
$key=$keys[$n];
$output.="<td><strong>".$key."</strong></td>";
}
$output.="</tr>";
for($i=0; $i < count($array); $i++) {
if($i%2==0) $bg="bgcolor=#FEFEF2"; else $bg="bgcolor=#FCF6CF";
$output.= "<tr ".$bg.">";
$keys=array_keys($array[$i]);
$keycount=count($keys);
for($n=0; $n < $keycount; $n++) {
$key=$keys[$n];
if($key==$totalfield) {
$runningtotal=$runningtotal+$array[$i][$key];

}
$output .= "<td>".$array[$i][$key]."</td> ";
}
$output .="</tr>";
}
$output.= "</table>
";
$modx->setPlaceholder(’total’,$runningtotal);
}

if($display=="dropdown") {
$ddName = isset($ddName) ? $ddName : "dropdown";
$size = isset($size) ? ’[] size=’.$size : ’’;
$multiple = isset($multiple) ? ’ multiple=’.$multiple : ’’;
$output ="<select name=".$ddName.$size.$multiple.">";
for($i=0; $i < count($array); $i++) {
$output.= "<option value=";
$keys=array_keys($array[0]);
$keycount=count($keys);
$key=$keys[0];
$item=$keys[1];
$output .= $array[$i][$key].">".$array[$i][$item];

}
$output.="</select>";
}

if($display=="checkbox") {
$cbName=isset($cbName) ? $cbName : "checkbox";
if($array[’value’]==1) {
$checkvalue=’checked’;
} else {
$checkvalue=’’;
}
if($array[’readonly’]==’true’) {
$readwrite=’ disabled’;
} else {
$readwrite=’’;
}
$output="<input type=’checkbox’ name=$cbName ".$checkvalue.$readwrite.">";
}

if($display=="textbox") {
$tbName=isset($tbName) ? " name=’".$tbName."’" : " name=’".$array[0]."’" ;
$size=isset($size) ? " size=".$size : "";
$rw=isset($rw) ? $rw : "";
$value=" value=’".$array[1]."’ ";
$output="<input type=’text’".$tbName.$value.$size.$rw.">";
}

} else {
$output="No data available.";
}
} else {
return ;
}

return $output;

Again, its all very rudimentary, but i hope it helps someone, or maybe inspires someone to help expand on it all. Or maybe some can point out how i can do all the same and more in much less code/effort

Or maybe some can point out how i can do all the same and more in much less code/effort

Well, you could consider looking at my xPDO project which does much of the same stuff for you, except in an object-oriented approach, plus a whole lot more, including full table metadata available without requiring a db connection, automatic table creation, automatic class generation, reverse-engineering existing db tables into xPDO schemas, and even db result-set caching to PHP and/or JSON. It also includes tools for generating tables and forms, as well as processing forms by simply passing around an object representing a table row. Future features include automatic schema change handling (i.e. change db field names/types or add new ones and provide automatic data migration routines), database-agnostic import/export capabilities, and hopefully much more.

Now, it would not automatically handle the totaling part of your code, but that’s all you’d really need left in your snippet if you chose to use xPDO as the O/R map between the db tables and the PHP code.

This is likely going to power future versions of MODx itself as well, so your investment into learning and using xPDO would not be wasted. Of course, I’m biased, since I’m authoring xPDO, but I just want to make people aware of what it is, what it does, and what it means to the future of MODx.

Well, you could consider looking at my xPDO project which does much of the same stuff for you, except in an object-oriented approach, plus a whole lot more, including full table metadata available without requiring a db connection, automatic table creation, automatic class generation, reverse-engineering existing db tables into xPDO schemas, and even db object caching to PHP and/or JSON. It also includes tools for generating tables and forms, as well as processing forms by simply passing around an object representing a table row. Future features include automatic schema change handling (i.e. change db field names/types or add new ones and provide automatic data migration routines), database-agnostic import/export capabilities, and hopefully much more.

lol now thats what i’m saying ... or at least would be saying if i knew what i was talking about. i’ll definitely take a look at the project. my work is pretty database intensive so it sounds like it would be extremely useful.

_Oooooh... Aaaaahhh..._ Are we there yet? Are we there yet?

Hi Machiavelli,
looks interesting for me, but can you explain a little more detailed what i must do to get the db-content?
Quote from: Machiavelli at Oct 20, 2006, 12:29 PM

eg:
1. $modx->runSnippet(’sql2array’, array(’table’=>’expenses’, ’totalfield’=>’amount’));
2. $result=$modx->getPlaceholder(’sql2arrayResult’);
3. $grandtotal=$modx->getPlaceholder(’totalhours’);
4. $table=$modx->runSnippet(’ShowData’,array(’array’=>$result,’display’=>’table’))

Question to Point 1: Looks like i get the whole rows of a table called : modx_expenses and a total of the amounts ( if i named your first snippet ’sql2array’ ) and create another snippet with the content of
’$modx->runSnippet(’sql2array’, array(’table’=>’expenses’, ’totalfield’=>’amount’));’
Is this right?

the other 3 examples i don’t understand at all. Can you help me?
Thank you

Greetings LeftHanded

1. $modx->runSnippet(’sql2array’, array(’table’=>’expenses’, ’totalfield’=>’amount’));
2. $result=$modx->getPlaceholder(’sql2arrayResult’);
3. $grandtotal=$modx->getPlaceholder(’total’);
4. $table=$modx->runSnippet(’ShowData’,array(’array’=>$result,’display’=>’table’));

This is just the most basic example usage of the two snippets. In step 1, the entire table of ’[prefix_]expenses’ is read and stored in a placeholder called ’sql2arrayResult’, in the form of an array. the field ’amount’ is totalled up and stored in a placeholder called ’total.’ Steps 2 and 3 are just how you get at those values. Step 4 takes the result of step one and runs it through the snippet ’ShowData,’ which formats it into a table for output.

here’s another example: (when using full sql statements, you have to include the table prefix.)

// get data from table
$sql="SELECT pool_web_users.username AS User, pool_scores.OilScore AS Oilers, pool_scores.OtherScore AS Opponent
FROM pool_scores
INNER JOIN pool_web_users ON pool_scores.user_id = pool_web_users.id
WHERE pool_scores.game_id =".$gameid;
$modx->runSnippet(’sql2array’, array(’sql’=>$sql));
$result=$modx->getPlaceholder(’sql2arrayResult’);
$scores=$result;
// count number of records in array
$count=count($scores);
//modify table data before outputting
for($i=0;$i<count($result);$i++) {
if($scores[$i][’Oilers’]==$goodguys_score && $scores[$i][’Opponent’]]==$opponent_score) {
$scores[$i][’result’]="Win ";
$sql="SELECT amount FROM pool_winnings WHERE game_id=".$gameid;
$modx->runSnippet(’sql2array’, array(’sql’=>$sql3));
$result=$modx->getPlaceholder(’sql2arrayResult’);
$winnings = $result;
$scores[$i][’result’].="$".$winnings[0][’amount’].".00";
} else {
$scores[$i][’result’]=" - ";
}
}
// send array for formatting
$body=$modx->runSnippet(’ShowData’,array(’array’=>$scores,’display’=>’table’));
return $body;

Here, I’ve used a JOIN sql statement to get the data i needed. I then went through each record to check who was/were the winner(s). When i found a winner, i used the two snippets again to find out how much they won. i then added a field to the original array with the winnings. that array is then sent on for formatting and then finally returned for output.

I hope that helps Lefthanded.

Thank you,
now i understand one more way to get the database-data to the frontend in an easy way...

Greetings LeftHanded

this is a bit more of the code i’m working on. It automatically creates a sql join statement. unfortunately there are some severe limitations (ie. must use mysql, innodb tables and have the pmadb configuration properly set) But, with those limitations met, it discovers which field in the table references another table, and then creates the sql statement.

so, if you have one table such as:

linked to another table such as:

and then set the variables in the following code to:
$mainTable=’articles’;
$replacingFieldName=’name’;
you would get the joined table on articles.authorid=authors.id with the authors.name showing up in your final table.

here’s the code:

i will be fixing this up shortly to work for tables with multiple referenceing indexes, and hope to add the option for nesting tables. It is in raw php form, and i’ll convert it to a snippet as soon as i get a few issues sorted out. hope its helpful!