Textpattern CMS

#1 2005-12-20 13:56:10

smaida

New Member

Registered: 2005-08-05

Posts: 3

Create Plugin which returns SQL Query results

Ok…. So I’m sure this is documented somewhere or that it’s really simple but I need help.

Basically I have a website for my soccer club which I’ve been porting over to text pattern. The website had a few pages which displayed information from a database such as Schedules / Scores / Standings / List of teams / etc….

I want to create text pattern plugins that accomplish each of these tasks….

One for listing teams
One that takes a team from the list and displays the schedule
One that takes a team from the list and displays the league standings
etc….

My basic question is….

Can some one give me a basic plugin example that displays rows of data from a table?
I’m wondering what textpattern functions exist that I should use when querying a database from a plugin….

I appreciate the help.

-Shawn

#2 2005-12-20 19:18:29

wet

Developer Emeritus

From: Schoerfling, Austria

Registered: 2005-06-06

Posts: 3,330

Website Mastodon

Re: Create Plugin which returns SQL Query results

The database interface is neatly packaged in textpattern/lib/txplib_db.php. safe_row() would be a nice starting point. There is no limit on functions which can be used in plugins, as plugins act as part of the core code, basically. There is no list of core functions which are considered 100 percent futureproof of which I know.

To make use of these functions, your tables have to be present in the same database as Textpattern’s and must have an identical table prefix. If one of my plugins may serve as a tiny sample, I’d suggest wet_thumbfilter which is not much more than a query based on some passed attributes plus a small html wrapper. Or visit section_list(), a function to be found in textpattern/publish/taghandlers.php.

Last edited by wet (2005-12-20 19:22:04)

#3 2005-12-20 21:41:49

Mary

Sock Enthusiast

Registered: 2004-06-27

Posts: 6,236

Re: Create Plugin which returns SQL Query results

Use safe_rows_start(), nextRow() and while().

Edit:

I should mention: in order to use Txp’s functions, your table(s) will need to be within the same database, and have the same table prefix.

Last edited by Mary (2005-12-20 21:43:51)

#4 2005-12-21 18:01:50

smaida

New Member

Registered: 2005-08-05

Posts: 3

Re: Create Plugin which returns SQL Query results

Thanks for your help….

Is there a way I can do joins using safe_rows_start() or am I stuck to one table?

Thanks again.

#5 2005-12-22 06:06:13

Mary

Sock Enthusiast

Registered: 2004-06-27

Posts: 6,236

Re: Create Plugin which returns SQL Query results

Nope, you are indeed stuck to one table. But it’s okay, you can use the other functions to do joins. Here’s an example taken from one of my own plugins:

$rs = safe_query("select $post_table.*, unix_timestamp($post_table.Posted) as uPosted,
	$file_table.downloads as num
	$match
	from $post_table $join_type outer join $file_table on($post_table.$upm_file_packets_field = $file_table.id)
	where $where
	order by num desc, $sortby $sortdir
	limit $pgoffset $limit");

if ($rs)
{
	while ($a = nextRow($rs))
	{

As you can see, I was working with Textpattern tables, so I made a couple variables to use, earlier in the script:

$post_table = PFX.'textpattern';
$file_table = PFX.'txp_file';

But since you’re not working with Textpattern tables, then you don’t have to worry about that step.

#6 2005-12-22 19:25:11

smaida

New Member

Registered: 2005-08-05

Posts: 3

Re: Create Plugin which returns SQL Query results

Thanks Mary,

I’ve got everything working now….

I appreciate your help.
-Shawn

Last edited by smaida (2005-12-23 18:14:26)