MySQL queries on meta_key in wp_usermeta table

  • Hi folks,

    This may be more of an MySQL question than a WordPress question per se but I thought I’d throw it out there.

    Is there a way to concatenate the meta_key fields and alias them as one?
    For example, if I wanted to run
    SELECT * FROMwp_aufu_usermeta` WHERE
    meta_key = ‘first_name’+”+’last_name’ AS NAME`

    could this be done at all? The SQL syntax editor freaks out when I try to run this whereas most times this would be valid in MySQL or any other SQL DB.

    It would be handy as I’m trying to fish out various values. Any feedback is welcomed here, foilks.

    Thanks!

Viewing 6 replies - 1 through 6 (of 6 total)

  • It’s bc your sql syntax is wrong – you’re trying to set a condition (WHERE) on something that does not exist.

    In English, what are you trying to accommplish?
    Not sure what sort of data you’re trying to extract, but it seems like you’re looking for this instead:
    select CONCAT(fName , ‘ ‘ , lName) AS Name FROM wp_aufu_usermeta
    WHERE ………….. where what? I dunno.

    Definitely not a WordPress question.

    Thread Starter redraider90

    (@redraider90)

    Ok, sorry. Thank you for the acknowledgement of the post here.

    I was referencing this link on the concatenation part. I know different DB platforms run concat functions slightly different sometimes.

    https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_alias_column2&ss=-1

    But, while I thought I had conveyed this the first time, as in above, I am indeed trying to concatenate a “first_name” field and a “last_name” field as a faux column, “NAME.”

    For example I can choose
    `SELECT * FROM wp_usermeta
    WHERE meta_key= ‘street_no’ `

    OR
    `SELECT * FROM wp_usermeta
    WHERE meta_key= ‘street_name’ `

    But I cannot seem to concatenate the two and get results. Either of those meta_keys in the WordPress usermata table (one of the reasons I asked if this was maybe a WordPress issue as I was running this in PHPMyAdmin) will render a lengthy result set.

    So I hope this clarifies it some and if I should go to a separate forum please let me know. If there’s a similar forum within the WP support, please inform me of that as well.
    Thank you!

    Now I understand better. So to dumb down the overall question, we could say:
    How can we concat two aliases?

    Yes?

    Thread Starter redraider90

    (@redraider90)

    That may very well be. It’s interesting to me. A glance at the “table” (which may be some view or other) of the wp_usermata table shows what appear to be 4 columns: umeta_id, user_id, meta_key, and meta_value.
    So this meta_key can take on different identifiers or field names of sorts: i.e. the meta_key can be named ‘street_no’ and its value, 433. Or the meta_key could be ‘home_phone_no’ and the value 313-326-0924 for example, like embedded value pairs within the meta_key.
    So maybe tnat is correct, concatenating two aliases.

    Can this be done or not really? And I’m talking about at the MySQL level, not within a WOrdPress PHP file or other type of file.

    Thanks,
    redraider90

    It can be done.
    This meta table is just a key value pair like any array you would build in PHP or C or whatever.
    fruitarray[apple=>red,grapes=>green], etc

    I can try some things, my first guess would be it will require an inner join, with a second select statement.
    Like,
    https://stackoverflow.com/questions/38042927/mysql-query-inner-join-with-aliases

    Thread Starter redraider90

    (@redraider90)

    Let me look at that and see if I can recreate it on my end.
    Thank you for the time and suggestions!

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘MySQL queries on meta_key in wp_usermeta table’ is closed to new replies.