db insert failure in new mod

[annatar]

Greetings all.

I'm trying my hand at writing a vBulletin mod for the first time. So far things are going fairly well. I've been able to construct a sample product xml which properly creates and removes a new table to the forum database on install/uninstall.

I've created a plugin which uses the attachment_complete hook. I can see that the plugin is being called after an attachment is viewed for but some reason a database error is occurring. When I look at the query as listed in the error report that I am receiving via email it looks proper and I can in fact execute the query in phpmyadmin successfully.

The plugin I am trying to write simply records which user id downloaded which attachment id and from what host.

The plugin code is:

global $vbulletin, $db;

$userId = $vbulletin->userinfo['userid'];
$attachId = $_REQUEST['attachmentid'];
$sessionId = $vbulletin->session->vars['host'];

$db->query_write("
    insert into " . TABLE_PREFIX ."attachmentDownloadHistory 
        (userid, host, attachmentid)
    values
        ($userId, '$sessionId', $attachId)
");
$db->show_errors();

In the email report the error is

Invalid SQL:

    insert into testforum_attachmentDownloadHistory 
        (userid, host, attachmentid)
    values
        (1, '127.0.0.1', 2);

The error report lists the MySQL Error and Error Number field as both blank.

Any thoughts, suggestions or steps I might have overlooked?

[sheppardzwc]

All MySQL statements that involve INSERT have to have all the variables/strings encased with an apostrophe. In basic terms, the correct statement would be:

PHP Code:

$db->query_write("INSERT INTO " . TABLE_PREFIX . "attachmentDownloadHistory (userid, host, attachmentid) VALUES ('$userId', '$sessionId', '$attachId')"); 


So basically, each variable after VALUES needs to have an apostrophe around it.

As well, you can also just use this for the queries instead of $db->, to cut down on processing time for PHP with "global:"

PHP Code:

global $vbulletin;
$vbulletin->db->query_write(whatever); 


[kh99]

I don't know very much about it, but (if the above suggestions don't work) did you try moving the "insert" up to the previous line? Maybe it doesn't like having a newline before any other input. But that's just a guess.

[annatar]

Thanks for the responses but quoting the variables with the parameters did not resolve the behavior I'm seeing.

It's a bother that the database error report doesn't include a MySQL Error or Error Number.

In the error report I did notice this:

Warning: mysql_errno(): 7 is not a valid MySQL-Link resource in /includes/class_core.php on line 408

Any ideas on that? It's only a warning but it doesn't sound good.

[kh99]

I think I might know - looking at attachment.php, there are conditions where exec_shut_down (in includes/functions.php) or $db->close() is called before the attachment_complete hook is executed. Maybe you can use the attachment_display hook instead? It's called earlier in attachment.php, but it looks like it's called under the same conditions.

[annatar]

Ah, that was a very handy suggestion. :up:

When I changed the hook to attachment_display I started to see records inserted into my database table. However it was a small surprise to me to see that when thumbnails are display that hook is triggered. I had to make a small change to my code to skip over thumbnails since I'm not interested in tracking that data at the moment. The revised code below seems to now work.

As an aside I was able to remove the extra quoting of all the query parameters that I'd added as suggested above. Since my table schema defines userid and attachmentid as int(10) the quoting doesn't appear to be required. It did work perfectly fine with them in place but it seems extraneous.

global $vbulletin;

$isThumb = $vbulletin->GPC['thumb'];
if (!$isThumb)
{
   $userId = $vbulletin->userinfo['userid'];
   $attachId = $vbulletin->GPC['attachmentid'];

   $sessionId = $vbulletin->session->vars['host'];

   $vbulletin->db->query_write("
       insert into " . TABLE_PREFIX ."attachmentDownloadHistory 
           (userid, host, attachmentid)
       values
           ($userId, '$sessionId', $attachId)
   ");
   $vbulletin->db->show_errors();
}

[Marco van Herwaarden]

Originally Posted by sheppardzwc

All MySQL statements that involve INSERT have to have all the variables/strings encased with an apostrophe. In basic terms, the correct statement would be:

PHP Code:

$db->query_write("INSERT INTO " . TABLE_PREFIX . "attachmentDownloadHistory (userid, host, attachmentid) VALUES ('$userId', '$sessionId', '$attachId')"); 


So basically, each variable after VALUES needs to have an apostrophe around it.

As well, you can also just use this for the queries instead of $db->, to cut down on processing time for PHP with "global:"

PHP Code:

global $vbulletin;
$vbulletin->db->query_write(whatever); 


This is not correct, numeric columns do not need to be quoted, it is even against the coding standards to do so.