How do you write to (or import) a database's custom profile field if empty?

[rcane]

How do you write to a custom profile field in the themes table if it doesn't even show in myPhpAdmin until that field (for any user) is populated?

I created a custom profile field and exported the db to look at it, and the entry isn't there for each user until that text field has something in it in their user profile.

[Arantor]

Well, that's normal... SMF isn't going to make a row in a table until it has a reason to.

Imagine if this site added a new profile field - that would instantly add best part of 400,000 rows to a table that will all be empty. It doesn't until the user populates it.

Not sure I really understand your question but I think the answer you're ultimately trying to get to is what the rest of us understand as an UPSERT; attempt to update it and if that doesn't modify anything, do an insert. The exact method of implementing this depends a lot on how you're trying to bulk import, whether you're trying to do this solely in SQL and what version of MySQL this is and whether you could get by with a INSERT INTO ... ON DUPLICATE KEY UPDATE query.

[Diego Andrés]

Funny thing, it will actually add two (once populated/set by the user) if you are using a custom theme, it adds a fallback for the default theme too which has less priority than the theme in use but higher when can't find it for the current theme

[rcane]

Well, I'm working on adding users' congressional districts.

- State
- district number's
- rep name

I have a coding thread going on so I don't want to mix thread and double dip.  This was more about, if I make just ONE of the above--take state--it won't show in phpadmin until populated. 

If I just type a "." on everyone I guess it will show something like "cust_state",and then I can run a query.  I suppose I could even do it in excel so long as I know what rows it falls between for each person.

But, if I make the custom field, and leave the text field blank, how would I know what the field is called in themes table (where the custom profile fields are)?

It's the same way that a custom field doesn't show in a member's profile until they enter something in it.

[Arantor]

Funny thing, it will actually add two (once populated/set by the user) if you are using a custom theme, it adds a fallback for the default theme too which has less priority than the theme in use but higher when can't find it for the current theme

The makeCustomFieldChanges function explicitly reads and writes to theme id 1 (and only theme id 1) for custom profile fields' values. Any other behaviour is a bug and a rogue mod.

But, if I make the custom field, and leave the text field blank, how would I know what the field is called in themes table (where the custom profile fields are)?

This should be predictable; the col_name field in the custom_fields table is used to base the entry in the themes table off, just with cust_ in front of it.

[Diego Andrés]

Oh yea I know what you mean, but I was referring to the other function in Themes.php which op confused me saying themes table instead of custom fields table.

[Arantor]

which op confused me saying themes table instead of custom fields table

Well that's where the custom field *values* are stored in SMF so... it's not confusing in that respect

[rcane]

Funny thing, it will actually add two (once populated/set by the user) if you are using a custom theme, it adds a fallback for the default theme too which has less priority than the theme in use but higher when can't find it for the current theme

The makeCustomFieldChanges function explicitly reads and writes to theme id 1 (and only theme id 1) for custom profile fields' values. Any other behaviour is a bug and a rogue mod.

But, if I make the custom field, and leave the text field blank, how would I know what the field is called in themes table (where the custom profile fields are)?

This should be predictable; the col_name field in the custom_fields table is used to base the entry in the themes table off, just with cust_ in front of it.

Ah, partly there (mentally) now.

I'd always just gone straight to the themes table since all the profile fields had a value of some sort.  I figured they'd have to store the name 'somewhere' first.

That being said though, let's say "cust_state" is made but is empty for all 4 users (SMF just installed).

SQL will find it fine, if I write to the themes table using "cust_state"?

[Arantor]

If that's what the column is called in the custom fields table, yes.

After all, SMF is just using SQL to do the leg work under the hood.

[rcane]

If that's what the column is called in the custom fields table, yes.

After all, SMF is just using SQL to do the leg work under the hood.

ok. it's smart enough to know.     I'm glad one of us is.  haha

[Steve]

So, problem resolved then?


* still wonders if rcane works for the NSA   

[rcane]

So, problem resolved then?


* still wonders if rcane works for the NSA   

Almost,

If I'm writing a php to pull some stuff from the member list, AND a few custom profiles, will it know to grab the data from themes or would i have to direct SQL specifically?  Kind of a nebulous area.

I know themes and members both share the user_id, so my first thought is it's done using both tables' names by user_id.

[Arantor]

Well, if you're writing the code... it'll do what you tell it? It's not a mind reader.

[rcane]

Well, if you're writing the code... it'll do what you tell it? It's not a mind reader.

well yeah

  I meant, because of the discussion above, that since the members table doesn't show the fields' data until entered (but the custom_field table has it)--- along those lines would calling for "custom field blah" from the members table work, or would i have to call it from themes?

[Arantor]

The custom fields table says "hey I know about a custom field" but if there's no value in the themes table, there's nothing to show, so it... shows nothing. I really think you're overthinking this a bit.

[rcane]

The custom fields table says "hey I know about a custom field" but if there's no value in the themes table, there's nothing to show, so it... shows nothing. I really think you're overthinking this a bit.

I am, but since i don't have a lot of practical sql work I'm trying to think ahead before i start getting errors.

[Arantor]

That's really not how SQL works though. It's not a programming language, it's a query language - you ask the database questions, it gives you answers. The only errors are 1) the query isn't valid (you'll know that very soon) or 2) you asked the wrong question and got answers that didn't make as much sense as they should.

Thing is, you're doing about the simplest possible setup - joining (A and B) to C, in the form of (users and custom fields) to themes. There's really not a lot you can do wrong there.

[rcane]

That's really not how SQL works though. It's not a programming language, it's a query language - you ask the database questions, it gives you answers. The only errors are 1) the query isn't valid (you'll know that very soon) or 2) you asked the wrong question and got answers that didn't make as much sense as they should.

Thing is, you're doing about the simplest possible setup - joining (A and B) to C, in the form of (users and custom fields) to themes. There's really not a lot you can do wrong there.

well that's encouraging.  like i said, new territories.

[Doug Heffernan]

well that's encouraging.  like i said, new territories.

It may feel a bit overwhelming when you're new to it, but it's easy once you get the hang of it.