0

I'm moving an ancient site with a too-old-to-upgrade version of Wordpress plus some custom PHP code over to a newer server. I wasn't the one who created the site, and I'm trying to make the minimum changes necessary to get it working. Right now, it's throwing a MySQL error. Here's the query in question:

SELECT `wp_posts`.`post_title` as title, `wp_posts`.`post_date` as date, `wp_posts`.`post_name` as slug
    FROM `wp_posts`, `wp_categories`
    JOIN `wp_post2cat` ON `wp_posts`.`ID` = `wp_post2cat`.`post_id`
    WHERE wp_categories.cat_name = 'Stories'
    AND `wp_post2cat`.`category_id` = `wp_categories`.`cat_ID`
    ORDER BY date DESC
    LIMIT 0,10;

Pasting this query into PHPMyAdmin throws the following error:

#1054 - Unknown column 'wp_posts.ID' in 'on clause'

This clearly isn't the correct error, as wp_posts.ID does, in fact, exist, and the query worked on the old host (which was probably running an older version of MySQL).

So, since the error message is wrong, what's the real problem?

MySQL version: 10.2.13-MariaDB-10.2.13+maria~xenial - mariadb.org

Edit

In response to a comment, here's the structure of the tables in question:

CREATE TABLE `wp_posts` (
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `post_author` bigint(20) NOT NULL DEFAULT 0,
 `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_content` longtext NOT NULL,
 `post_title` text NOT NULL,
 `post_category` int(4) NOT NULL DEFAULT 0,
 `post_excerpt` text NOT NULL,
 `post_status` enum('publish','draft','private','static','object','attachment','inherit','future') NOT NULL DEFAULT 'publish',
 `comment_status` enum('open','closed','registered_only') NOT NULL DEFAULT 'open',
 `ping_status` enum('open','closed') NOT NULL DEFAULT 'open',
 `post_password` varchar(20) NOT NULL DEFAULT '',
 `post_name` varchar(200) NOT NULL DEFAULT '',
 `to_ping` text NOT NULL,
 `pinged` text NOT NULL,
 `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `post_content_filtered` text NOT NULL,
 `post_parent` bigint(20) NOT NULL DEFAULT 0,
 `guid` varchar(255) NOT NULL DEFAULT '',
 `menu_order` int(11) NOT NULL DEFAULT 0,
 `post_type` varchar(20) NOT NULL DEFAULT 'post',
 `post_mime_type` varchar(100) NOT NULL DEFAULT '',
 `comment_count` bigint(20) NOT NULL DEFAULT 0,
 PRIMARY KEY (`ID`),
 KEY `post_name` (`post_name`),
 KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=utf8

.

CREATE TABLE `wp_categories` (
 `cat_ID` bigint(20) NOT NULL AUTO_INCREMENT,
 `cat_name` varchar(55) NOT NULL DEFAULT '',
 `category_nicename` varchar(200) NOT NULL DEFAULT '',
 `category_description` longtext NOT NULL,
 `category_parent` bigint(20) NOT NULL DEFAULT 0,
 `category_count` bigint(20) NOT NULL DEFAULT 0,
 `link_count` bigint(20) NOT NULL DEFAULT 0,
 `posts_private` tinyint(1) NOT NULL DEFAULT 0,
 `links_private` tinyint(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`cat_ID`),
 KEY `category_nicename` (`category_nicename`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

.

CREATE TABLE `wp_post2cat` (
 `rel_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `post_id` bigint(20) NOT NULL DEFAULT 0,
 `category_id` bigint(20) NOT NULL DEFAULT 0,
 PRIMARY KEY (`rel_id`),
 KEY `post_id` (`post_id`,`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=166 DEFAULT CHARSET=utf8
Scott Severance
  • 943
  • 10
  • 27
  • Please paste output of `SHOW CREATE TABLE wp_posts;` (and maybe also of `wp_categories` and `wp_post2cat` just in case). – Amadan Aug 07 '18 at 02:27
  • @Amadan: Done.. – Scott Severance Aug 07 '18 at 02:44
  • Since I can't post an answer to a closed question, I'll put it here: The solution was add parentheses to change `FROM wp_posts, wp_categories` to `FROM (wp_posts, wp_categories)`. – Scott Severance Aug 07 '18 at 18:34
  • @ScottSeverance that may have worked, but that's not the right solution. The right solution is described in the duplicate topic: change the implicit joins to explicit ones. – Shadow Aug 07 '18 at 19:14
  • The right solution would be to replace hand crafted SQL with framework-generated code. To that end, I haven't written SQL in many years. For this application, I think the answer is whatever will make 11-year outdated code work with the least amount of effort. – Scott Severance Aug 07 '18 at 19:26
  • If Scott is right, that is a change from MySQL 4.0 to 4.1 ! I was burned by it 15 years ago. The precedence of commajoin and explicit `JOIN` changed. (I agree with it being a dup.) – Rick James Aug 23 '18 at 03:58

1 Answers1

1

Try removing wp_categories in your FROM and use a JOIN for the table instead.

sykez
  • 2,015
  • 15
  • 14
  • I haven't written SQL code directly in many years, so I'm not certain whether I wrote my query correctly. I ran the following query and got 0 rows, whereas I expected to get about 5: `SELECT wp_posts.post_title as title, wp_posts.post_date as date, wp_posts.post_name as slug FROM wp_posts JOIN wp_post2cat ON wp_posts.ID = wp_post2cat.post_id JOIN wp_categories ON wp_posts.post_category = wp_categories.cat_ID WHERE wp_categories.cat_name = 'Stories' AND wp_post2cat.category_id = wp_categories.cat_ID ORDER BY date DESC LIMIT 0,10; ` – Scott Severance Aug 07 '18 at 03:13
  • 1
    @ScottSeverance It looks alright. But try without the `WHERE` to see if you're getting the result. If there are any issues with the result, you should take a look at the data in `wp_post2cat` and `wp_categories` as well. Both joins are basically `INNER JOIN`, so if there are no matching data you wouldn't get the desire result. – sykez Aug 07 '18 at 03:45
  • Since I can't post an answer to a closed question, I'll put it here: The solution was add parentheses to change `FROM wp_posts, wp_categories` to `FROM (wp_posts, wp_categories)`. – Scott Severance Aug 07 '18 at 18:34