Page 1 of 1

Basic sql query for plugin

PostPosted: 7:14pm, Tue 09 Apr, 2013
by E15BAER
I am building a very basic plugin which simply adds a menu item and link to execute a sql query which moves messages from Drafts to Sent. We often wind up viewing sent messages in edit mode which moves them to Drafts.

The direct query is:
'UPDATE phplist_message SET status = "sent" WHERE processed > 0'

I have the plugin, page, and link ready to execute whatever function I add to refreshsent.php when clicked.

What is the best way to approach this function so it properly utilizes phplist's sql abstraction and sanitation?

Re: Basic sql query for plugin

PostPosted: 8:57am, Wed 10 Apr, 2013
by duncanc
You should use the sql_xxx functions that are defined in admin/mysql.inc. They are a wrapper around the php mysql_xxx functions with similar names.

Specifically use sql_query() to execute your sql, and then sql_affected_rows() to get the number of rows that were updated.

Also, you can use the global $tables array to get the actual table name including prefixes for the user table.

From your sample query there doesn't appear to be a need for sanitisation as it is not using any user input.

The query should test the status of the rows that it is updating, otherwise it may update rows with other status values such as a message that is currently being sent.

Code: Select all
global $tables;
$sql = "UPDATE {$tables['user']}
SET status = 'sent'
WHERE status = 'draft' AND processed > 0";
$result = sql_query($sql);

if (result) {
// it worked
    $count = sql_affected_rows();
} else {
// error handling
}