vb.org Archive - db insert failure in new mod

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - db insert failure in new mod (https://vborg.vbsupport.ru/showthread.php?t=234327)

db insert failure in new mod

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:

Code:

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

Code:

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?

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);

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.

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.

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.

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.

Code:

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();

}

Quote:

Originally Posted by sheppardzwc (Post 1968365)

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')");

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.

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (3)bbcode_code_printable (4)bbcode_php_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01079 seconds Memory Usage 1,744KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (3)bbcode_code_printable (4)bbcode_php_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (7)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: