Hi folks,
How does one place an SQl query into a plugin in order to create a shortcode?
I've used PhpMyAdmin to get a working query and after looking around the documentation I thought the way to insert it might be Omeka_Db_Select but if it is I'm obviously not using it properly.
This 'hello shortcode' plugin code works fine:
<?php
class TestPluginPlugin extends Omeka_Plugin_AbstractPlugin
{
protected $_hooks = array('initialize');
public function hookInitialize()
{
add_shortcode('test_plugin', array($this, 'shortcode'));
}
return 'Hello shortcode!';
}
}
?>
After some research I thought that adding in the query might be as simple as:
<?php
class TestPluginPlugin extends Omeka_Plugin_AbstractPlugin
{
protected $_hooks = array('initialize');
public function hookInitialize()
{
add_shortcode('test_plugin', array($this, 'shortcode'));
}
public function shortcode($sql, $view)
{
$sql = Omeka_Db_Select()
->from('omeka_element_texts',array('DISTINCT(text)'))
->where('element_id =37','element_id =39');
return $sql;
return 'Hello shortcode!';
}
}
?>
but I'm getting an error message that Omeka_Db_Select is undefined.
Does anyone have an example of how to correctly insert an sql query?
Were you looking at the regular documentation or the developer docs?
Hi, I was looking at the developer docs. What works is basically taken from there.
The first parameter that gets passed along to your shortcode function is parsed out from the shortcode itself. So
[test_shortcode foo='bar']
will have the first param
array('foo' => 'bar');
The second $view param will be the view, as you have it.
To the point, though, to get at a select object you'll want to do something like this in your function
$select = get_db()->getTable('ElementText')->getSelect();
That'll let you start building up wheres, and depending on the details probably won't need a from.
It echos out pretty well, so for testing and figuring things out, this will show you what's happening:
$select = get_db()->getTable('ElementText')->getSelect();
return $select;
I dropped that shortcode into a Simple Page just to try it out, and seemed to show the query.
Also, go to the application/models/{ModelYouAreInterestedIn}/Table directory, and see if helpful function are already there.
That's great Patrick, thanks to both of you for your help!
So after working a little on this we can echo the sql statement we want. However we can't figure out how to return the sql results!
See image here: http://i.imgur.com/TZzd2vJ.png
We have dug through Zend documentation a bit and tried execute(), fetchAll() and other methods but they're not being recognised and are resulting in errors.
Do we have to somehow import Zend methods, or is that completely the wrong way of going about things?
Link to our code so far: https://github.com/padraic7a/TestPlugin
You'll want to do all the work to build the HTML output right there in shortcode(), and return the HTML.
To get the records themselves from that MySQL, that'd be using the findBySql method that comes from the parent table class (other methods in there might also be helpful). So, if you've built the select up that way, you should just be able to do
$results = get_db()->getTable('ElementText')->findBySql($select);
That'll get you an array of ElementText objects. From those, build the HTML you want, and return it.
We'll work on that, thanks again Patrick!
When you have the Select object the way you want, the usual way to execute it is to call fetchObjects on the table:
$table = get_db()->getTable('ElementText');
$select = $table->getSelect();
// Modify $select however you want...
$texts = $table->fetchObjects($select);
// $texts is an array of ElementText objects