• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

A little MySql help

giorgino

Well-known member
#1
Hi all.

I'm migrating a vB4 board and I have to insert a default value in a custom user field that I created in the new xenforo installation.

Let me explain.

We've the user_field_value table with the three field user_id, field_id and field_value.
I need to populate this table with a default value for each user for field_id='campo' and field_value='dato'.
All the same value for all users, nothing else.

I imagine that a INSERT INTO function should do the job, but unfortunately I'm a MySql newbe.
Can someone help me or give me some directions on this?

Thank you :)
 

tafreehm

Well-known member
#2
Hi all.

I'm migrating a vB4 board and I have to insert a default value in a custom user field that I created in the new xenforo installation.

Let me explain.

We've the user_field_value table with the three field user_id, field_id and field_value.
I need to populate this table with a default value for each user for field_id='campo' and field_value='dato'.
All the same value for all users, nothing else.

I imagine that a INSERT INTO function should do the job, but unfortunately I'm a MySql newbe.
Can someone help me or give me some directions on this?

Thank you :)
INSERT will add new row. What you need is UPDATE

it might be something like this:
Code:
UPDATE user_field_value
 
SET field_id = 'campo', field_value = 'dato';
try above at your own risk..
 

karll

Well-known member
#3
Something like this:
Code:
insert into xf_user_field_value (user_id, field_id, field_value)
      select user_id, 'campo', 'dato' from xf_user;
Edit: The above assumes the rows don't already exist in user_field_value. If they do, you need to use "update" like tafreehm suggested.

Edit 2: Sorry, there were a couple of mistakes in the query. I've modified it slightly, hopefully better now.
 

giorgino

Well-known member
#4
try above at your own risk..
I've a test DB :) Thank you for advice.
INSERT will add new row. What you need is UPDATE
Edit: The above assumes the rows don't already exist in user_field_value. If they do, you need to use "update" like tafreehm suggested.
Sure, the rows doesn't exist :) I need to create one new row in user_field_value for each user in xf_user

insert into user_field_value (user_id, field_id, field_value)
select user_id, field_id='campo', field_value='dato' from user;

phpMyAdmin return:
#1146 - Table 'mydb.user_field_value' doesn't exist
but the table exist o_O
 

tafreehm

Well-known member
#5
I've a test DB :) Thank you for advice.


Sure, the rows doesn't exist :) I need to create one new row in user_field_value for each user in xf_user

insert into user_field_value (user_id, field_id, field_value)
select user_id, field_id='campo', field_value='dato' from user;
phpMyAdmin return:
#1146 - Table 'mydb.user_field_value' doesn't exist
but the table exist o_O
you probably have a prefix. And sorry, I misunderstood, I thought you were trying to update the existence data.