vb.org Archive - sql query coding question to select and/or update userfield

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

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

- - sql query coding question to select and/or update userfield (https://vborg.vbsupport.ru/showthread.php?t=81227)

sql query coding question to select and/or update userfield

Need some help with writing a query to check the condition of two userfields in vbulletin after a user has uploaded photos in photopost. This query will be in a photopost php file (the file used just after a user has finished uploading). The photopost tables and the vbulletin tables are in the same database (makes it a little easier).

The scenario:
I have two userfields in vbulletin that if true (value = 1). field#x will display a link to that users photo gallery in the postbit and the member profile. field#xx will dispay the last photo uploaded as a thumbnail in the users' postbit with a link to that users' photo. Both of these are now user sectable in the user options (UserCP), and in the Admin user menu, but I would like this to be set automatically when the user uploads.

If this is the first time a user has uploaded photos, then the two values of these fields will be ' ' (null). But if the user has previously uploaded then most likely these values will be '1' (true). Most likely already set by me or the user.

So the first step is to check for the values of both fields. Then, based on their conditions, set or don't set the new value

The varible for the uploading user in the photopost script is $upuserid.
$userid is the variable (PRIMARY KEY) used by vbulletin in the userfield table.

This is what I have so far, but I'm stuck on the the two 'if' conditionals. And should I use UPDATE or INSERT INTO
Also, in the pohotopost scripting, they use - ppmysql_query - instead of mysql_query. Should I use the same syntax to query the vbulletin table.

PHP Code:


		
			
$query = "SELECT fieldx, fieldxx FROM vbulletin_prefix_userfield WHERE userid = $upuserid";

$get_fields = mysql_query($query);

list( $fieldx, $fieldxx ) = mysql_fetch_row($get_fields);

mysql_free_result($get_fields);

if ($fieldx == 1) {



} else {



$query = "UPDATE vbulletin_prefix_userfield SET fieldx=1  WHERE userid = $upuserid";



}



if ($fieldxx == 1) {



} else {



$query = "UPDATE vbulletin_prefixuserfield SET fieldxx=1  WHERE userid = $upuserid";



}

This is the my first time writing this type of code from scratch, so any assistance would be greatly appreciated.
John

You can replace the whole code block above with 1 line:

PHP Code:


		
			
$query = "UPDATE vbulletin_prefixuserfield SET fieldx=1, fieldxx=1  WHERE userid = $upuserid";

When you go through this code, you always want to set fieldx and fieldxx to the value of 1, regardless of the previous value. So there is no need to retrieve old values first. Ifi i understand you correct that is. ;)

Hi MarcoH64,
So, regardless if the value of both fields is already - '1' -, this will just overwrite?

PHP Code:


		
			
$query = "UPDATE vbulletin_prefixuserfield SET fieldx=1, fieldxx=1  WHERE userid = $upuserid";

John

Yes, this just tells MySQL to change these fields to the value you supply, regardless of their previous value.

Odd, didn't work. I used my id as a test. I first went to my UserCp and unchecked (set to null) those two field options. Went into the database to view the change in the userfield table. My two fields, fieldx and fieldxx where indeed null. I then went and uploaded a test image in photopost. Everything went fine. No sql or php errors generated, so I assume that the syntax of the UPDATE query is correct. However, I went back to the database, but the two field values were still null, no value (1) was set. So, it seems that query was not executed.

Here's the block of code at the end of the photopost upload script, with my UPDATE query included:

PHP Code:


		
			
$query = "UPDATE vbulletin_prefix_userfield SET field9=1, field18=1  WHERE userid = $upuserid";



    if ($Globals['ppostcount'] == "yes")

        inc_user_posts();



if ($Globals['usenotify'] == "yes" && $User['adminedit'] = 1 ) {

       $User['userid'] = $upuserid;

        }



    if ($Globals['usenotify'] == "yes" && $User['userid'] > 0 ) {

        if ($notify == "yes") {

            $query = "SELECT id FROM {$Globals['pp_db_prefix']}photos WHERE userid={$User['userid']} AND bigimage='$realname'";

            $resulta = ppmysql_query($query,$link);

            list( $photoid ) = mysql_fetch_row($resulta);

            ppmysql_free_result($resulta);



            $query = "INSERT INTO {$Globals['pp_db_prefix']}notify (id,userid,photo) values(NULL,$upuserid,$photoid)";

            $resulta = ppmysql_query($query,$link);



        }

    }

    

    return( $lastphotoid );

}



?>

If the value is already 'null', does the query need some kind of other statement prior to setting the value of '1' ?

BTW, Thank you for the time you are taking to assist me, John

And where did you execute $query?

Quote:

Originally Posted by MarcoH64

And where did you execute $query?

Not quite sure what you mean. The $query is near the end of the photopost upload script as shown above. Both photopost and vbulletin use the same database, but with different prefixes.

The user has just uploaded photo(s). The next steps:
If this is the first time he has uploaded photo(s), the existing values are null, then set the two userfields (x and xx) for this user to a value of 1 (a vbulletin table)

If he has uploaded photo(s) before then do not set the two userfields, as they are already set to the value of 1, or maybe just overwrite.

The remaining steps in this script are (these lines of code were already in the original photopost script):
increase user post count in the vbulletin user postcount? -yes, no (a vbulletin table)

notify section of code (subscription). If the user wants an email if someone comments on one of their photos (photopost tables)

End of script

Try addingthe following after the first line:

PHP Code:


		
			
$resulta = ppmysql_query($query,$link);

BINGO!!! :up: :cool: :classic:
Did the trick. Did a bunch of test uploads, using different conditions (upload as me, upload for another user, with the two values set as null, both set as 1, etc) Works like a charm!
Thank you very much for your help MarcoH64. I'm going to post this over at Photopost (with kudos to you). There are other vbulletin owners with photopost integration (or photopost owners with vbulletin integration depending on your point of view), that have similar conditions and would certainly be interested in having this block of code.
Again, Much thanks
John

We would prefer if you (also) posted your hack here on vbulletin.org and put a link to here on photopost. But htat is your own choice.

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 (5)bbcode_php_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete 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.01238 seconds Memory Usage 1,763KB 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 (5)bbcode_php_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: