Skip to content

Instantly share code, notes, and snippets.

@stnc

stnc/list all categories.sql

Last active February 27, 2025 01:04
Show Gist options
  • Save stnc/fbdee4fa0b4ab91e03980b6a138d548a to your computer and use it in GitHub Desktop.
Save stnc/fbdee4fa0b4ab91e03980b6a138d548a to your computer and use it in GitHub Desktop.
Download ZIP
Mysql query to list all WordPress posts with categories and tags
Raw
list all categories.sql
#How to list all categories and their IDs using SQL query?
SELECT t.name, t.slug, t.term_id, x.count, tt.name AS parent_name FROM wp_term_taxonomy AS x
INNER JOIN wp_terms AS t ON t.term_id = x.term_id
LEFT JOIN wp_terms AS tt ON tt.term_id = x.parent
WHERE taxonomy = 'category'
ORDER BY t.name
Raw
post-listV1.sql
#SET sql_mode = 'ONLY_FULL_GROUP_BY';
SET sql_mode = '' ;
SELECT
cat_posts.ID as ID,
cat_posts.post_title as Title,
cat_posts.post_date as Published,
CASE
WHEN cat_term_taxonomy.taxonomy = 'category' THEN GROUP_CONCAT(DISTINCT cat_terms.name SEPARATOR ', ')
ELSE ""
END
as Categories,
CASE
WHEN cat_term_taxonomy.taxonomy = 'post_tag' THEN GROUP_CONCAT(DISTINCT cat_terms.name SEPARATOR ', ')
ELSE ""
END
as Tags
FROM wp_posts AS cat_posts
INNER JOIN wp_term_relationships AS cat_term_relationships ON cat_posts.ID = cat_term_relationships.object_id
INNER JOIN wp_term_taxonomy AS cat_term_taxonomy ON cat_term_relationships.term_taxonomy_id = cat_term_taxonomy.term_taxonomy_id
INNER JOIN wp_terms AS cat_terms ON cat_term_taxonomy.term_id = cat_terms.term_id
INNER JOIN wp_postmeta AS meta ON cat_posts.ID = meta.post_id
WHERE cat_posts.post_status = 'publish'
AND cat_posts.post_type = 'post'
group by cat_posts.ID
Raw
post-listV2-with-term_taxonomy_id.sql
SET sql_mode = '' ;
SELECT
cat_posts.ID as ID,
cat_posts.post_title as Title,
cat_posts.post_date as Published,
CASE
WHEN cat_term_taxonomy.taxonomy = 'category' THEN GROUP_CONCAT(DISTINCT cat_terms.name SEPARATOR ', ')
ELSE ""
END
as Categories
FROM wp_posts AS cat_posts
INNER JOIN wp_term_relationships AS cat_term_relationships ON cat_posts.ID = cat_term_relationships.object_id
INNER JOIN wp_term_taxonomy AS cat_term_taxonomy ON cat_term_relationships.term_taxonomy_id = 222
INNER JOIN wp_terms AS cat_terms ON cat_term_taxonomy.term_id = cat_terms.term_id
INNER JOIN wp_postmeta AS meta ON cat_posts.ID = meta.post_id
WHERE cat_posts.post_status = 'publish'
AND cat_posts.post_type = 'post'
group by cat_posts.ID
Raw
post-listV2.sql
SELECT DISTINCT
post_title
, post_content
,(SELECT meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = 'Asking Price (US\$)' AND wp_postmeta.post_id = wp_posts.ID) AS "Asking Price (US\$)"
,(SELECT group_concat(wp_terms.name separator ', ')
FROM wp_terms
INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE taxonomy= 'category' and wp_posts.ID = wpr.object_id
) AS "Categories"
,(SELECT group_concat(wp_terms.name separator ', ')
FROM wp_terms
INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE taxonomy= 'post_tag' and wp_posts.ID = wpr.object_id
) AS "Tags"
FROM wp_posts
WHERE post_type = 'post' AND post_status = 'publish'
ORDER BY
post_title
, post_content
Raw
script1.php
<?php
/*
SET sql_mode = '' ;
SELECT
cat_posts.ID as ID
FROM wp_posts AS cat_posts
INNER JOIN wp_term_relationships AS cat_term_relationships ON cat_posts.ID = cat_term_relationships.object_id
INNER JOIN wp_term_taxonomy AS cat_term_taxonomy ON cat_term_relationships.term_taxonomy_id = 222
INNER JOIN wp_terms AS cat_terms ON cat_term_taxonomy.term_id = cat_terms.term_id
INNER JOIN wp_postmeta AS meta ON cat_posts.ID = meta.post_id
WHERE cat_posts.post_status = 'publish'
AND cat_posts.post_type = 'post'
group by cat_posts.ID
*/
// UPDATE `education`.`wp_posts` SET `post_type`='lp_lesson' WHERE `ID`=2780; //post type degistirir
// INSERT INTO `education`.`wp_learnpress_section_items` (`section_id`, `item_id`, `item_order`, `item_type`) VALUES (11, 3288, 1, 'lp_lesson');
// echo "<pre>";
// print_r($wp_posts );
// echo "<pre>";
// print_r($wp_posts[0][0] );
// echo "<br>";
$wp_posts = [
[
2817,
],
[
2816,
],
[
2815,
],
[
2803,
],
];
$sectionID=22;
$gun=18;
$filename="gun".$gun."-sec".$sectionID.".txt";
$text ="";
foreach ($wp_posts as $x => $y) {
// echo "$y[0] <br>";
$item_id= $y[0];
$item_order= $x;
$text .= "UPDATE `education`.`wp_posts` SET `post_type`='lp_lesson' WHERE `ID`=$y[0]; \n ";
echo "<br>";
$text .= "INSERT INTO `education`.`wp_learnpress_section_items` (`section_id`, `item_id`, `item_order`, `item_type`) VALUES ($sectionID, $item_id, 1, 'lp_lesson'); \n" ;
echo "<br>";
}
$fh = fopen($filename, "a");
fwrite($fh, $text );
fclose($fh);
Raw
script2.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
//
// Create connection
$dbname = "education";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$catID=279;
$sectionID=69;
$gun=78;
/*
["64.GÜN-70.GÜN", "64-gun-70-gun", 277, 1, null]
["71.GÜN-77.GÜN", "71-gun-77-gun", 278, 6, null]
["78.GÜN-90.GÜN", "78-gun-90-gun", 279, 2, null]
*/
$sql = "
SELECT
cat_posts.ID as ID
FROM wp_posts AS cat_posts
INNER JOIN wp_term_relationships AS cat_term_relationships ON cat_posts.ID = cat_term_relationships.object_id
INNER JOIN wp_term_taxonomy AS cat_term_taxonomy ON cat_term_relationships.term_taxonomy_id = ".$catID."
INNER JOIN wp_terms AS cat_terms ON cat_term_taxonomy.term_id = cat_terms.term_id
INNER JOIN wp_postmeta AS meta ON cat_posts.ID = meta.post_id
WHERE cat_posts.post_status = 'publish'
AND cat_posts.post_type = 'post'
group by cat_posts.ID";
$result = $conn->query($sql);
$wp_posts = array();
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<br>";
echo "id: " . $row["ID"];
array_push($wp_posts, $row["ID"]);
}
} else {
echo "0 results";
}
$conn->close();
echo "<pre>";
print_r($wp_posts );
$filename="gun".$gun."-sec".$sectionID.".txt";
// $filename="datasec".$sectionID.".txt";
$text ="";
foreach ($wp_posts as $x => $y) {
echo "$y <br>";
$item_id= $y;
$item_order= $x;
$text .= "UPDATE `education`.`wp_posts` SET `post_type`='lp_lesson' WHERE `ID`=$y; \n ";
echo "<br>";
$text .= "INSERT INTO `education`.`wp_learnpress_section_items` (`section_id`, `item_id`, `item_order`, `item_type`) VALUES ($sectionID, $item_id, 1, 'lp_lesson'); \n" ;
echo "<br>";
}
$fh = fopen($filename, "a");
fwrite($fh, $text );
fclose($fh);
Raw
script3.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
//
// Create connection
$dbname = "education";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$wp_term_taxonomy = [
[
'19. GUN',
'19',
233,
5,
null,
],
[
'20. GUN',
'20',
234,
5,
null,
],
[
'21. GUN',
'21',
235,
5,
null,
],
[
'22. GUN',
'22',
236,
6,
null,
],
[
'23. GUN',
'23',
237,
5,
null,
],
[
'24. GUN',
'24',
238,
6,
null,
],
[
'25. GUN',
'25',
239,
5,
null,
],
[
'26. GUN',
'26',
240,
5,
null,
],
[
'27. GUN',
'27',
241,
5,
null,
],
[
'28.gun',
'28',
242,
5,
null,
],
[
'29 gun',
'29',
243,
8,
null,
],
[
'30.gun',
'30',
244,
5,
null,
],
[
'31. gun',
'31',
245,
6,
null,
],
[
'33 gun',
'33',
247,
5,
null,
],
[
'34 gun',
'34',
248,
5,
null,
],
[
'35 gun',
'35',
249,
5,
null,
],
[
'36 gun',
'36',
250,
6,
null,
],
[
'37 gun',
'37',
251,
5,
null,
],
[
'38 gun',
'38',
252,
5,
null,
],
[
'39 gun',
'39',
253,
5,
null,
],
[
'40 gun',
'40',
254,
5,
null,
],
[
'41 gun',
'41',
255,
5,
null,
],
[
'42 gun',
'42',
256,
5,
null,
],
[
'43 GU N',
'43',
258,
6,
null,
],
[
'44 GUN',
'44',
257,
5,
null,
],
[
'45 gun',
'45',
259,
6,
null,
],
[
'46 gun',
'46',
260,
5,
null,
],
[
'47 gu',
'47',
261,
5,
null,
],
[
'48 gun',
'48',
262,
5,
null,
],
[
'49 gun',
'49',
263,
5,
null,
],
[
'50 gun',
'50',
264,
9,
null,
],
[
'51 gun',
'51',
265,
6,
null,
],
[
'52 gun',
'52',
228,
6,
null,
],
[
'53 gun',
'53',
266,
5,
null,
],
[
'54 gun',
'54',
267,
5,
null,
],
[
'55 gun',
'55',
268,
5,
null,
],
[
'56 gun',
'56',
269,
4,
null,
],
[
'57 gun',
'57',
270,
4,
null,
],
[
'58 gun',
'58',
271,
3,
null,
],
[
'59 gun',
'59',
272,
3,
null,
],
[
'60 gun',
'60',
273,
3,
null,
],
[
'61 gun',
'61',
274,
3,
null,
],
[
'62 gun',
'62',
275,
3,
null,
],
[
'63 gun',
'63',
276,
2,
null,
],
];
$section=23;
foreach ($wp_term_taxonomy as $x => $y) {
$catID=$y[2];
$sectionID=$section++;
echo "<br>";
$gun=$y[1];
$sql = "
SELECT
cat_posts.ID as ID
FROM wp_posts AS cat_posts
INNER JOIN wp_term_relationships AS cat_term_relationships ON cat_posts.ID = cat_term_relationships.object_id
INNER JOIN wp_term_taxonomy AS cat_term_taxonomy ON cat_term_relationships.term_taxonomy_id = ".$catID."
INNER JOIN wp_terms AS cat_terms ON cat_term_taxonomy.term_id = cat_terms.term_id
INNER JOIN wp_postmeta AS meta ON cat_posts.ID = meta.post_id
WHERE cat_posts.post_status = 'publish'
AND cat_posts.post_type = 'post'
group by cat_posts.ID";
$result = $conn->query($sql);
$wp_posts = array();
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<br>";
echo "id: " . $row["ID"];
array_push($wp_posts, $row["ID"]);
}
} else {
echo "0 results";
}
echo "<pre>";
print_r($wp_posts );
// $filename="gun".$gun."-sec".$sectionID.".txt";
$filename="onfile.txt";
$text ="";
foreach ($wp_posts as $x => $y) {
echo "$y <br>";
$item_id= $y;
$item_order= $x;
$text .= "UPDATE `education`.`wp_posts` SET `post_type`='lp_lesson' WHERE `ID`=$y; \n ";
echo "<br>";
$text .= "INSERT INTO `education`.`wp_learnpress_section_items` (`section_id`, `item_id`, `item_order`, `item_type`) VALUES ($sectionID, $item_id, 1, 'lp_lesson'); \n" ;
echo "<br>";
}
$fh = fopen($filename, "a");
fwrite($fh, $text );
fclose($fh);
}
$conn->close();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment