Custom SQL for Wordpress Database Help

matt6805

Hi, I’m trying to do some custom Database stuff with Wordpress. I think I’m close to getting it working, but I’ve hit a wall. Basically, here’s the scenario. I’m using Wordpress to create hidden content that updates a website outside of the wordpress installation. I’ve gotten the content hidden and the DB connection is working fine. I can pull values from the Database on the external website, but the values are not being pulled the way I want them to be. Looking at the database table will help illustrate:

http://img18.imageshack.us/img18/1387/sqlt.png

I have two kinds of posts: News and Blog. What I want is the following:

When the story is type ‘blog, pull all the values under the meta_value column that are associated with posts of type ‘blog’. So if you look at the screenshot, post_id 80 is type ‘blog’. I want to pull, the date, the title, the URL the lead-in content.

The problem seems to be that all those values are in the same column (meta_value). I tried using the WHERE IN SQL command:

SELECT meta_value FROM wp_postmeta WHERE meta_key IN ('url','pdate','ptype','_wp_attached_file','title','lead-in')

But it’s returning rows that I don’t want. How can I filter these rows down to use the two qualifiers: ‘news’ or ‘blog’?
Thanks for any help in advance.

Regards,
Matt

dagon

try:

SELECT meta_value from wp_postmeta WHERE post_id=(select post_id where meta_key ='blog')

matt6805

Thanks for your reply Dagon, running that code in PphMyAdmin gives me a syntax error. I manged to adjust the code just enough to get it running in the program, but it returns empty result sets to me:

SELECT post_id from wp_postmeta WHERE meta_value = "(SELECT post_id WHERE meta_key = 'blog')"

Regards,
Matt

dagon

should be "SELECT meta_value", not "SELECT post_id" and try the subselect query on its own and see what it returns

matt6805

dagon;10929182 wrote:
should be "SELECT meta_value", not "SELECT post_id" and try the subselect query on its own and see what it returns

Sorry, that's what I meant. I ran it just like you posted it. When i run the subquery, It works this way:

SELECT post_id FROM wp_postmeta WHERE meta_value ='blog'

Notice i changed meta_key to meta_value, because the 'blog' value is found in the meta_value column, not meta_key.

So running returns the ID of the two posts that were marked as 'blog' in the meta_value column

Regards,
Matt

dagon

are the meta_values, where post_id is 78 or 80 what you want?

matt6805

dagon;10929186 wrote:
are the meta_values, where post_id is 78 or 80 what you want?

Yes, but I need several values associated with those ID's. Notice how in the screenshot I posted, ID 78 appears in the post_id column more then once? I need the meta_values of ID 78 that are directly related to:

meta_key: lead-in
meta_key: pdate
meta_key: title
meta_key: url

meta_key: blog or news (this is the key field that will direct which of the above records should be pulled).

I'm pretty new to database structures, so I apologize if my terminology is off. I'm try to be as clear as possible. Thanks for the continued help!

Regards,
Matt

dagon

try:

SELECT meta_values from wp_postmeta WHERE meta_value IN "(SELECT post_id WHERE meta_key = 'blog')"

change any field names needed

matt6805

Running this query gives me a syntax error.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"(SELECT post_id WHERE meta_key = 'blog')" LIMIT 0, 30' at line 1

dagon

take out the extra quotes

matt6805

Works when i put in the table I'm searching in the sub-query, but it returns zero rows:

SELECT meta_value FROM wp_postmeta WHERE meta_value IN(SELECT post_id FROM wp_postmeta WHERE meta_key = 'blog')

I'm stumped.

Regards,
Matt

johanafm

It would give you results if any rows had a meta_value matching the post_id of the subquery. To see this in action, you could set the meta value of any row to either 78 or 80.

But I recommend you normalize your table.