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

A little MySql help

Discussion in 'General PHP and MySQL Discussions' started by giorgino, Feb 26, 2013.

  1. giorgino

    giorgino Well-Known Member

    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 :)
  2. tafreehm

    tafreehm Well-Known Member

    INSERT will add new row. What you need is UPDATE

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

    karll Well-Known Member

    Something like this:
    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 likes this.
  4. giorgino

    giorgino Well-Known Member

    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
  5. tafreehm

    tafreehm Well-Known Member

    you probably have a prefix. And sorry, I misunderstood, I thought you were trying to update the existence data.
    giorgino likes this.
  6. giorgino

    giorgino Well-Known Member

    Yes, I'm an idiot! thank you!
    Thank you for suggestions ;)

    Updating query with db prefix return this
    #1054 - Unknown column 'field_id' in 'field list'
    tafreehm likes this.
  7. karll

    karll Well-Known Member

    I made a couple of mistakes, sorry. I've edited the query in my previous post, feel free to try again ...
    giorgino likes this.
  8. giorgino

    giorgino Well-Known Member

    Work great! Thank you so much! :)

Share This Page