SQL query in wp_postmeta table

Asked

Viewed 278 times

0

How to make an SQL query in a table that has the following fields:

SELECT meta_key,meta_value from wp_postmeta where meta_key = 'cidade' and meta_key = 'bairro'

the fields of the table wp_postmeta field meta_key = 'City' and meta_value = 'Curitiba' and meta_key = 'neighborhood' and meta_value = 'Center' is the following I want to make a select when I select a city in the other select it shows only the neighborhoods related to the city that was selected then I want to know if you can do this, if there is how I do this relationship in sql?

  • 2

    Posts more information about the problem, it is not clear what you want and what you do not know.

3 answers

1


Your query seems to be correct, but stay tuned to the base name. In Wordpress (which I assume you are using), the table name is wp_postmeta and not wp_postpostmeta.

In a more comprehensive case, a simple select meta_key, meta_value from wp_postmeta returns all occurrences of the two fields

EDIT

Your appointment isn’t quite right, at least for me. Are you searching all cities and all neighborhoods, or all neighborhoods of a city? In Wordpress logic, meta_key determines the field name, while meta_value, your value. Therefore, you would have meta_key = 'Cidade' and meta_value = 'Curitiba', for example. IN THIS CASE (i.e., if this is your logic), to select all the cities in your relationship, your query would be:

SELECT meta_key, meta_value FROM wp_postmeta WHERE meta_key='Cidade'

So you’d get returns like

meta_key | meta_value 
Cidade   | Curitiba
Cidade   | São Paulo

If, on the other hand, your meta_key be the NAME of the city, and the meta_value the NAME of the neighborhood, the consultation

SELECT meta_key, meta_value FROM wp_postmeta WHERE meta_key='São Paulo'

Could return:

meta_key  | meta_value 
São Paulo | Morumbi
São Paulo | Jabaquara

If this last logic which I have represented is the one which you are following (i.e., meta_key = 'nome_da_cidade'), and you want to list all the neighborhoods of all cities (again, I’m assuming things here), do:

SELECT meta_key, meta_value FROM wp_postmeta ORDER BY meta_key

I drew my conclusions based on your question. See if I am correct and let me know

  • You are right I am performing the query with correct table name wp_postmeta, field meta_key = 'City' and meta_value = 'Curitiba' and meta_key = 'neighborhood' and meta_value = 'Center' is the following I want to make a select when I select a city in the other select it shows only the neighborhoods related to the city that was selected so I want to know if you have how to do that, if there is how I do this relationship in sql?

  • Saul, if you have the pairs meta_key = 'Cidade' , meta_value = 'Curitiba', and meta_key = 'bairro' , meta_value = 'centro', they become independent, unrelated to each other. In my opinion, you would have to have the meta_key of the second pair as the name of the city, and not as the neighborhood (i.e., meta_key = 'São Paulo'), there the meta_value will be the name of the neighborhood and then you can relate the neighborhoods to the city

  • 1

    Antedi thanks for the tip I’ll test, thank you very much!

  • Look at the example I gave in my reply. Imagine that meta_key is another name for the city, and meta_value is another name for neighborhood. So your relationship becomes simpler.

  • Thanks for the tips, I solved the problem by creating a custom taxonomy to add cities. Thanks to all

  • Don’t forget to validate the answers you thought helped you

  • I have to have 15 reputation points, I don’t have one yet!

Show 2 more comments

0

SELECT meta_key,meta_value FROM wp_postpostmeta WHERE meta_key = 'cidade' OR meta_key = 'bairro'
  • 1

    I only considered the query, in case the WP tables name do what Caio Felipe said.

0

The problem with your query is that you are using the and between the two conditions. This way, the bank will understand that it should return the fields meta_key and meta_value of all lines containing the meta_key value of 'city' and 'neighborhood', but there is no meta_key contain two different values on the same line.

To fix, you must use the operator or, whereas in your logic you want the bank to return the columns of the records that have the meta_key value of 'city' or 'neighborhood'.

SELECT meta_key,meta_value from wp_postpostmeta where (meta_key = 'cidade' OR meta_key = 'bairro')
  • Is the following I want to make a select when I select a city in the other select it shows only the neighborhoods related to the city that was selected so I want to know how to do this relationship in sql?

  • Simple, you need to identify in the records referring to the neighborhood, which column is the column that presents the city. And then filter the select there. Soon I change my answer and show you how to do ok?

  • @For me to be able to edit the answer, I need you to put in the answer all the columns and their values of a row referring to the neighborhood, because surely there is some column that makes the relationship between the neighborhood and the city, and that column is none of the ones you specified.

  • Only that I have already checked the tables to know which field I will use to do this filtering but so far I did not find, if in my post = Casa01 in the category = city sale = São Paulo neighborhood = Morumbi, and in another post = casa02 category = city location = São Paulo, neighborhood = center, which field will I use to make the relationship?

Browser other questions tagged

You are not signed in. Login or sign up in order to post.