sql query coding question to select and/or update userfield

[wirewolf]

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.

$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

[Marco van Herwaarden]

You can replace the whole code block above with 1 line:
$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. ;)

[wirewolf]

Hi MarcoH64,
So, regardless if the value of both fields is already - '1' -, this will just overwrite?$query = "UPDATE vbulletin_prefixuserfield SET fieldx=1, fieldxx=1 WHERE userid = $upuserid";
John

[Marco van Herwaarden]

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

[wirewolf]

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:$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

[Marco van Herwaarden]

And where did you execute $query?

[wirewolf]

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

[Marco van Herwaarden]

Try addingthe following after the first line:
$resulta = ppmysql_query($query,$link);

[wirewolf]

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

[Marco van Herwaarden]

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.

[wirewolf]

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.I had planned to post at both, and certainly with a link back to here. Not much of a hack, two lines of code. But heh, it comes in very handy.
I want to put it all together, including instructions on creating the userprofilefields. What would be the appropiate forum to post this in here? It kind of falls in between. It's really a mod (or, I should say, add on) to photopost code, but does involve integration with vbulletin. I guess, Mini Mods?

[Marco van Herwaarden]

That would be a good place yes.