Here's a SQL query to find XenForo resources with duplicate titles:
This query will return a list of all resource titles that appear more than once in the
Code:
SELECT title, COUNT(*) as count
FROM xf_resource
GROUP BY title
HAVING count > 1;This query will return a list of all resource titles that appear more than once in the
xf_resource table, along with the number of times each title appears.-
Kevin
- OP
What version of XenForo is that query for? The current version of XenForo Resources table is
xf_rm_resource.
I apologize for the confusion. The query I provided is for XenForo 1.x versions where the resource table is named
xf_resource. For XenForo 2.x versions, the resource table is named
xf_rm_resource. Here's the updated query for XenForo 2.x:
Code:
SELECT title, COUNT(*) as count
FROM xf_rm_resource
GROUP BY title
HAVING count > 1;This query will return a list of all resource titles that appear more than once in the
xf_rm_resource table, along with the number of times each title appears.-
Kevin
- OP
Can you write a SQL query to update the XenForo table xf_rm_resource to update the resource_date to be the same as the associated discussion thread?
Sure, here's a SQL query to update the
xf_rm_resource table in XenForo 2.x to set the resource_date column to be the same as the associated discussion thread:
Code:
UPDATE xf_rm_resource AS r
INNER JOIN xf_thread AS t ON r.discussion_thread_id = t.thread_id
SET r.resource_date = t.post_date;This query uses an
INNER JOIN to match the discussion_thread_id column in the xf_rm_resource table with the thread_id column in the xf_thread table. It then sets the resource_date column in the xf_rm_resource table to be the same as the post_date column in the xf_thread table.Please note that running direct SQL queries on your database can be risky and may cause unintended consequences. It's always recommended to take a backup of your database before running any SQL queries.
Sure, here's the revised SQL query to update the
xf_rm_resource table in XenForo 2.x to set the resource_date column to be the same as the associated discussion thread, but only for the resource with resource_id equal to 1:
Code:
UPDATE xf_rm_resource AS r
INNER JOIN xf_thread AS t ON r.discussion_thread_id = t.thread_id
SET r.resource_date = t.post_date
WHERE r.resource_id = 1;This query uses the
WHERE clause to filter the update to only the resource with resource_id equal to 1. Again, please note that running direct SQL queries on your database can be risky and may cause unintended consequences. It's always recommended to take a backup of your database before running any SQL queries.
Sure, here's the revised SQL query to update both the
resource_date and last_update columns in the xf_rm_resource table in XenForo 2.x to be the same as the associated discussion thread, but only for the resource with resource_id equal to 1:
Code:
UPDATE xf_rm_resource AS r
INNER JOIN xf_thread AS t ON r.discussion_thread_id = t.thread_id
SET r.resource_date = t.post_date, r.last_update = t.last_post_date
WHERE r.resource_id = 1;This query uses the
WHERE clause to filter the update to only the resource with resource_id equal to 1. Again, please note that running direct SQL queries on your database can be risky and may cause unintended consequences. It's always recommended to take a backup of your database before running any SQL queries.
