Basic sql query for plugin

Technical discussion for developers and service providers

Basic sql query for plugin

Postby E15BAER » 7:14pm, Tue 09 Apr, 2013

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?
E15BAER
phpList newbie
 
Posts: 3
Joined: 9:41pm, Wed 23 Jan, 2013

Re: Basic sql query for plugin

Postby duncanc » 8:57am, Wed 10 Apr, 2013

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
}
duncanc
Moderator
 
Posts: 2440
Joined: 6:34am, Sat 08 May, 2010
Location: London


Return to Developers Space

Who is online

Users browsing this forum: No registered users and 1 guest