如何从所有wp用户中选择第二个附件

r7s23pms  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(288)

到目前为止,我得到的是以下查询,它为我提供了每个用户的第一个附件的post id。

$sql = "
        SELECT ID, post_author, post_title, post_type 
        FROM wp_posts 
        WHERE post_author > '1' AND post_type = 'attachment' 
        GROUP BY post_author 
        ORDER BY ID;";

    $result = mysqli_query($link, $sql) OR die(mysqli_error($link));

    while($row = mysqli_fetch_assoc($result)) {

    $USERID = $row['post_author'];
    $ID = $row['ID'];
    $TITLE = $row['post_title'];

    $sql2 = "UPDATE alle 
    SET `image0` = '$ID'
    WHERE `Userid` = '$USERID';";

    if(mysqli_query($link, $sql2)){
    echo "Image0 inserted successfully.";
    } else {
    echo "ERROR: Could not able to execute $sql2. " . mysqli_error($link);
    }

    }
    echo "finished";
    mysqli_close($link);

我现在需要检查的是,如果用户有第二个附件,如果有,请列出第二个附件的post id,将它们放在非wordpress表中。我试了好几个小时,但是我卡住了。

amrnrhlw

amrnrhlw1#

这可能会解决您的问题:
只需在查询中包含count(*),即可获得给定作者的附件数

SELECT ID, post_author, post_title, post_type, count(*) AS number_of_attachments 
FROM wp_posts WHERE post_author > '1' AND post_type = 'attachment' 
GROUP BY post_author 
ORDER BY ID

如果您还需要所有附件的post id,可以使用group\u concat

SELECT ID, post_author, post_title, post_type, count(*) AS number_of_attachments, GROUP_CONCAT(ID) AS all_post_ids 
FROM wp_posts WHERE post_author > '1' AND post_type = 'attachment' 
GROUP BY post_author 
ORDER BY ID

这里的列all post\u id将包含给定post\u作者的附件类型的每个post的逗号分隔的id

相关问题