Moved to HowTo by Moderator
Hi everyone!
For my application, I make use of Ajax. Therefore, I need to pull data from my database and send these data as JSON, so my jQuery can handle the response.
You might ask, why I’m using JSON. Well, because JSON has low redundant data and it has a very low overhead, so it is very fast 😊
Currently, I use a template to get this data. The template code looks like this:
<?php
global $DB, $IN;
$list = $IN->GBL('list', 'POST');
$start = $IN->GBL('start', 'POST');
$limit = $IN->GBL('limit', 'POST');
$sort = $IN->GBL('sort', 'POST');
$dir = $IN->GBL('dir', 'POST');
$q = $IN->GBL('q', 'POST');
// search query
if ($q && $q != '')
{
$q = $DB->escape_str($q);
$where_part = " AND first_name LIKE '{$q}%' ";
$where_part .= " OR last_name LIKE '{$q}%' ";
}
else
{
$where_part = '';
}
// all entries
if ($limit == 'all')
{
$limit_part = '';
}
elseif (is_numeric($limit))
{
$limit_part = " LIMIT {$DB->escape_str($start)}, {$DB->escape_str($limit)}";
}
else
{
$limit_part = '';
}
$sql = "SELECT *
FROM my_table
WHERE lst = {$DB->escape_str($list)} {$where_part}
ORDER BY {$DB->escape_str($sort)} {$DB->escape_str($dir)}
{$limit_part}";
$query = $DB->query($sql);
$query_count = $DB->query("SELECT COUNT(*) AS count_total
FROM _ns_hv_TS2
WHERE lst = {$DB->escape_str($list)} {$where_part}");
$count = 0;
$i = 0;
$json = array();
$json['sql'] = $sql;
$json['total_count'] = $query_count->result[0]['count_total'];
$json['list'] = $list;
$json['start'] = $start;
$json['limit'] = $limit;
$json['sort'] = $sort;
$json['dir'] = $dir;
if ($q) $json['q'] = $q;
if ($query->num_rows > 0)
{
$json['x'] = array();
foreach ($query->result as $row)
{
$json['x'][$i]['id'] = $row['id'];
$json['x'][$i]['data_1'] = $row['data_1'];
$json['x'][$i]['data_2'] = $row['data_2'];
++$i;
}
}
echo json_encode($json);
/* End of file get_geraete_ajax.php */
/* Location: ./system/templates/json/get_data_1.php */
?>Of course, this does the job, but I wonder if there is a more elegant way to get such a JSON object?!
I thought about a plugin which does the same job, the result is, that I can use this tag:
{exp:export_format:json list="vegetables" start="0" limit="25" sort="id" dir="asc"}This works as it should: First the plugin checks if there are POSTed params, after this it checks the template parameters.
The only problem is, that I still have to create a single template with just this one line of code, so I ask, how I can get rid of that?
In the end, I want to call this via the following URL:
<a href="http://mysite/export_format/json/data_to_pull/table_id/">http://mysite/export_format/json/data_to_pull/table_id/</a>I need some different JSON objects, e.g. one is just to count data. The difference should look like this:
<a href="http://mysite/export_format/json/count/vegetables/4/">http://mysite/export_format/json/count/vegetables/4/</a>
<a href="http://mysite/export_format/json/all_data/vegetables/4/">http://mysite/export_format/json/all_data/vegetables/4/</a>So with 1), I just get this: “{total_count:432}” and 2) should create a more complex JSON object; something like the template code I posted above.
In the future I also want to have these features:
<a href="http://mysite/export_format/xml/all_data_/vegetables/4/">http://mysite/export_format/xml/all_data_/vegetables/4/</a>
<a href="http://mysite/export_format/excel/all_data_/vegetables/4/">http://mysite/export_format/excel/all_data_/vegetables/4/</a>
<a href="http://mysite/export_format/pdf/all_data_/vegetables/4/">http://mysite/export_format/pdf/all_data_/vegetables/4/</a>
- Can you tell me, which is the right way to code?
- Plugin or template?
- How can I pull data whithout using a template?
- Any other ideas on this?
Thank you very much.