Hello there!
I'm going to be brief. I am trying to get an automated payment system on my site working in a way that it can find a user on a forum. But I am trying to use a custom field to find the user, as the forum promotions are simply a bonus to this system I am making.
I have done this in MyBB, but unfortunately I have done my best to analyse the database format for SMF, and have came up dry for the data. (I know that prefix_custom_fields contains the table of custom fields and prefix_members contains forum users... but I cant find the actual data) It would help me a lot if someone could point out to me the location of this data on the database. Thanks!
I am running SMF 2.0.8 and the payment system will be using MySQL alone to change a user's rank.
Also, as a side note to metallica48423 or whomever maintains that sticky about how to help you guys help me, may I hopefully not be the first to let you know that users of my rank do not have the permission to post external links, therefor we are unable to include 1 out of 4 requested details (namely: A link to my forum) without circumventing such restrictions (I am not about to use the old "(dot)com" work-around. If an administrator deems me or people of my rank unworthy of external links, I shall abide by that). You're welcome!
check your themes table, the input of the fields are stored there.
Thanks, but does it have to be thrown in a table with 100 or so other unrelated pieces of information?
The only reason I have an issue with this is because it makes my MySQL query all that more complex and prone to failure, having to LEFT JOIN two tables based on an assumed value from a third table that cannot easily be joined together...
cant it be joined on the users id? that table stores a lot of info yes, not much will change with that until maybe the devs start to work on 3.0 sometime in the future.
My query to join members with themes is nice and easy.
SELECT * FROM `prefix_members` LEFT JOIN `prefix_themes` ON `prefix_members`.id_member = `prefix_themes`.id_member WHERE variable = 'cust_steami'
But if I'm to release this script with SMF support, which I intend to, I need the query to not assume the custom profile field, and to figure it out itself. Unfortunately, the Structure for prefix_custom_fields indicates that it's a bit harder than I would like it to be. Mainly because prefix_custom_fields doesn't use any similarly named columns to the others, and I've already used my ON argument, and I'm just hoping I haven't delved far enough into the MySQL documentation.
EDIT: Shortly after creating this post, I discovered a neat little trick to LEFT JOIN that I can't believe I overlooked. My end query, as I prefer it will end up being this:
SELECT * FROM `prefix_members` LEFT JOIN (`prefix_themes`,`prefix_custom_fields`) ON (`prefix_members`.id_member = `prefix_themes`.id_member AND `prefix_custom_fields`.id_field = '2') WHERE variable = col_name
This of course assumes that the custom field is ID 2, but this can be learned by your everyday end-user by reading a URL. (&fid=2 when modifying the custom field.)