query not working

[varunkrish]

SELECT ID, post_title, post_date, post_excerpt, post_name
FROM wp_posts
WHERE ID
IN (
SELECT post_id
FROM wp_post2cat
WHERE category_id =7
)
ORDER BY post_date DESC
LIMIT 15

i used this query on my old server.it was woking fine.

now i am on a ev1 server and its not working even in phpmyadmin.

what could be wrong.

is there any way to make the query work?

thanks

[jatar_k]

what does it do? any error messages?

[varunkrish]

just could not execute query..

[grandpa]

Could it be this?

SELECT post_id AS ID, post_title, post_date, post_excerpt, post_name
FROM wp_posts
WHERE ID
IN (
SELECT post_id
FROM wp_post2cat
WHERE category_id =7
)
ORDER BY post_date DESC
LIMIT 15

[varunkrish]

not working still...

thanks for ur help so far..

could it be a bug in mysql itself?

my new server where i tried running this script runs on
4.0.24-standard

the old host was running 4.1.11-standard

could i ask the host to upgrade the mysql installation

[grandpa]

You should get an error message from MySQL. Below is a modified version of your original query, and any errors will be displayed. This assumes you are running the query in a script.

$sql = " SELECT ID, post_title, post_date, post_excerpt, post_name
FROM wp_posts
WHERE ID
IN (
SELECT post_id
FROM wp_post2cat
WHERE category_id =7
)
ORDER BY post_date DESC
LIMIT 15";
$result = mysql_query($sql) or die("Select Failed: " . mysql_error());

could i ask the host to upgrade the mysql installation

Sure, but I wouldn't expect my host to upgrade anything upon request, no matter how often I made a request. Your host may react differently.

Maybe one of the experts here will know about the version changes, or you might have a look at the MySQL Development Roadmap [dev.mysql.com]

[mcibor]

I found in mysql manual [dev.mysql.com] that this query is not very well optimized. (And is not working on my mysql either)

And they reccommended using this instead:

SELECT wp_posts.ID, wp_posts.post_title, wp_posts.post_date, wp_posts.post_excerpt, wp_posts.post_name
FROM wp_posts, wp_post2cat
WHERE wp_posts.ID=wp_post2cat.post_id AND wp_post2cat.category_id=7
ORDER BY post_date DESC
LIMIT 15

Best regards!
Michal Cibor

[varunkrish]

great work guys....
i love webmasterworld

[varunkrish]

SELECT ID, post_title, post_excerpt, post_date, post_author, post_name
FROM wp_posts
WHERE ID
IN (
SELECT post_id
FROM wp_post2cat
WHERE category_id
IN (
SELECT cat_ID
FROM wp_categories
WHERE category_nicename = '$_GET[cat]'
)
)
ORDER BY ID DESC

how do i remove the ins and replace by joins...

[Mr_Fern]

my new server where i tried running this script runs on
4.0.24-standard

the old host was running 4.1.11-standard

A note from the MySQL reference manual. The query does not work on the new server because subqueries did not become a part of MySQL until 4.1 and beyond.

So if you can get the upgrade, that would be best.

[mcibor]

SELECT wp_posts.ID, wp_posts.post_title, wp_posts.post_excerpt, wp_posts.post_date, wp_posts.post_author, wp_posts.post_name
FROM wp_posts, wp_post2cat, wp_categories
WHERE wp_posts.ID = wp_post2cat.post_id
AND category_id=wp_categories.cat_ID
AND wp_categories.category_nicename = '$_GET[cat]'
ORDER BY ID DESC

In this case I am not so sure. However I think it should work.

Best regards
Michal Cibor