插件的MySQL数据库帮助

时间：2010-12-28 作者：Niraj Chauhan

我正在使用一个名为MyReview 但我想对它做一些修改。

该插件具有评论帖子的功能，用户将根据帖子的类别对帖子进行星级评分，但在该插件中，用户可以根据用户的需要对帖子进行任意次数的星级评分，这是我不想要的，我想要的是用户只需一次就可以对帖子进行星级评分，为此我与该插件的作者进行了交流，他建议我这样做，

通过相同的电子邮件ID查询评论表中的任何评论，检查是否有任何附加的评分，如果有，则不接受评分。所有这些变化都将纳入多年电价节约计划。php。如果您了解PHP，并且熟悉使用phpMyAdmin来理解数据库表，那么这并不困难。

SELECT id FROM myrp_ratings as rat, wp_comments as com WHERE com.comment_ID=rat.comment_id AND com.comment_author_email=\'[email protected]\' AND rat.value!=\'0\' AND rat.value!=\'1\' LIMIT 1
我如何将这一行放在插件文件中myrp-save.php? 我认为应该在这一行之后或这一行之前插入代码：

$wpdb->query("INSERT INTO `" . $wpdb->myrp_ratings . "`(`ID`, `category_id`, `post_id`, `comment_id`, `value`, `outof`, `weighted`) VALUES(\'\', \'{$name}\', \'{$postID}\', \'-1\', \'{$value}\', \'{$outof}\', \'" . ($value/$outof) . "\')");
如何做到这一点？

<?php

    function myrp_save_comment_fields($commentID)
    {
        global $wpdb;

        $fields = $wpdb->get_results("SELECT * FROM " .$wpdb->myrp_comment_fields." ORDER BY `order`");

        $rq = get_option("myrp_comment_fields_required");
        foreach($fields as $f)
        {
            $puniqid = str_replace(".", "_", $f->uniqid);
            if(!isset($_POST[$puniqid])) continue;
            $postdata = $_POST[$puniqid];

            switch($f->type)
            {
                case "date":
                    if(strlen($postdata) > 2)
                    {
                        $date = strtotime($postdata);
                        if($date > strtotime($f->minimum))
                        {
                            add_comment_meta($commentID, $f->uniqid, $date, true)
                                or
                            update_comment_meta($commentID, $f->uniqid, $date);
                        } else {
                            if($rq)
                            {
                                wp_die(__("Field {$f->name} is required and must be after {$f->minimum}!", "myrp") . " <a href=\'javascript:history.back(1);\'>Click here to continue...</a>");
                            } else {
                                continue;
                            }
                        }
                    }
                break;


                case "short": case "long":
                    if(strlen($postdata) > 0)
                    {
                        $text = ($postdata);
                        if(strlen($text) > intval($f->minimum) && strlen($text) < $f->maximum)
                        {
                            add_comment_meta($commentID, $f->uniqid, $text, true)
                                or
                            update_comment_meta($commentID, $f->uniqid, $text);
                        } else {
                            if($rq)
                            {
                                wp_die(__("Field {$f->name} is required and must be between {$f->minimum} and {$f->maximum} characters!", "myrp") . " <a href=\'javascript:history.back(1);\'>Click here to continue...</a>");
                            } else {
                                continue;
                            }
                        }
                    }
                break;

                case "number":
                    if(strlen($postdata) > 0)
                    {
                        $text = intval($postdata);
                        if(($text) > intval($f->minimum) && strlen($text) < $f->maximum)
                        {
                            add_comment_meta($commentID, $f->uniqid, $text, true)
                                or
                            update_comment_meta($commentID, $f->uniqid, $text);
                        } else {
                            if($rq)
                            {
                                wp_die(__("Field {$f->name} is required and must be between {$f->minimum} and {$f->maximum}!", "myrp") . " <a href=\'javascript:history.back(1);\'>Click here to continue...</a>");
                            } else {
                                continue;
                            }
                        }
                    }
                break;

                case "select": case "radio": case "check":
                    if(strlen($postdata) > 0)
                    {
                            add_comment_meta($commentID, $f->uniqid, $postdata, true)
                                or
                            update_comment_meta($commentID, $f->uniqid, $postdata);
                    }
                break;
            }
        }
    }

// myrp_require_rating_comment, myrp_require_all_rating_comment
    function myrp_save_ratings($commentID) 
    {
        global $wpdb; global $myrp_type_outof;
        $post_id = $_POST[\'comment_post_ID\'];
        $rating_categories = get_post_meta($post_id, "_myrp_rating_categories", true);

        if(is_array($rating_categories) && count($rating_categories) > 0)
        {
            $submit_count = 0;
            foreach($rating_categories as $rc)
            {
                $type = $wpdb->get_var("SELECT `type` FROM `" . $wpdb->myrp_categories . "` WHERE `ID`=\'{$rc}\' LIMIT 1");
                $outof = $myrp_type_outof[$type];

                if(isset($_POST[$rc . "_input"]) && $_POST[$rc."_input"] != "")
                {
                    $submit_count++;
                    $value = $_POST[$rc . "_input"];


                        if(!empty($value))
                        {
                            if($value < 1) $value = 1;
                            if($value > $outof) $value = $outof;
                            $value = round($value*2)/2;
                        } else {
                            $value = 0;
                        }

                    $sql =<<< SQL
SELECT
  id
FROM
  myrp_ratings AS rat,
  wp_comments AS com
WHERE 1=1
  AND com.comment_ID = rat.comment_id
  AND com.comment_author_email = \'%s\'
  AND rat.value != \'0\'
  AND rat.value != \'1\'
LIMIT
 1
SQL;
$sql = $wpdb->prepare($sql,\'[email protected]\');
$id = $wpdb->get_var($sql);
                    if (is_null($id)) {
  $wpdb->query("INSERT INTO `" . $wpdb->myrp_ratings . "`(`ID`, `category_id`, `post_id`, `comment_id`, `value`, `outof`, `weighted`) VALUES(\'\', \'{$name}\', \'{$postID}\', \'-1\', \'{$value}\', \'{$outof}\', \'" . ($value/$outof) . "\')");    
}
                }
            }


            if(
                (
                    get_post_meta($post_id, "_myrp_no_allow_comments_without_ratings", true) == 1 && $submit_count == 0
                )
                    ||
                (
                    (
                        (get_option("myrp_require_rating_comment") == 1 && $submit_count == 0)
                            ||
                        (get_option("myrp_require_all_rating_comment") == 1 && $submit_count != count($rating_categories))
                    )
                        &&
                    (
                        get_post_meta($post_id, "_myrp_allow_comments_without_ratings", true) != 1
                    )
                )
            ) {
                //$wpdb->query("DELETE FROM `".$wpdb->myrp_ratings."` WHERE `comment_id`=\'{$commentID}\' LIMIT {$submit_count}");
                wp_delete_comment($commentID);  // should clean up ratings on its own.
                wp_die(__("Ratings are required! Please select star ratings for this review.", "myrp") . " <a href=\'javascript:history.back(1);\'>Click here to continue...</a>");
            }

        }
        if(get_post_meta($post_id, "_myrp_automatically_approve_comments", true) == 1) $wpdb->query("UPDATE `" . $wpdb->comments . "` SET `comment_approved`=1 WHERE comment_ID=\'{$commentID}\' AND `comment_approved`=0");
        wp_update_comment_count($post_id);
        do_action("myrp-savingratings");
        update_option("myrp_notChanged",0);

        return true;
    }

    function myrp_save_post($postID) 
    {
        global $wpdb;
        global $myrp_type_outof;

        myrp_save_hides($postID);
        if(isset($_POST[\'myrp_affiliate_link\'])) {
            $affiliate_link = trim($_POST[\'myrp_affiliate_link\']);
            if(!empty($affiliate_link))
            {
                $link = parse_url($affiliate_link);
                $scheme = $link[\'scheme\'];
                if($scheme == "")  $affiliate_link = "http://" . $affiliate_link;
            }

            add_post_meta($postID, "_myrp_affiliate_link", $affiliate_link, true)
                or 
            update_post_meta($postID, "_myrp_affiliate_link",$affiliate_link);
        }

        if(isset($_POST[\'myrp_icon_image\'])) {
            $icon_image = $_POST[\'myrp_icon_image\'];
            if(!empty($icon_image))
            {
                $link = parse_url($icon_image);
                $scheme = $link[\'scheme\'];
                if($scheme == "")  $icon_image = "http://" . $icon_image;
            }

            add_post_meta($postID, "_myrp_icon_image",$icon_image, true)
                or 
            update_post_meta($postID, "_myrp_icon_image", $icon_image);
        }

        $checked = array();
        $rating_categories = array();
        if(isset($_POST[\'myrp_save_categories_please\']))
        {
            if(count($_POST[\'myrp_checked_categories\']) > 0)
            {
                foreach($_POST[\'myrp_checked_categories\'] as $cid)
                {
                    if(@$_POST["myrp_value_" . $cid] != "")
                    {
                        $rating = (@$_POST["myrp_value_" . $cid]);
                    }
                    else
                    {
                        $rating = "";
                    }


                    $checked[] = array($cid, $rating);
                    $rating_categories[] = $cid;
                }
            }
            add_post_meta($postID, "_myrp_rating_categories", $rating_categories, true)
                or
            update_post_meta($postID, "_myrp_rating_categories", $rating_categories);


            if(count($rating_categories) > 0)
            {
                update_option("myrp_previously_checked", $rating_categories);
            } else {
                update_option("myrp_previously_checked", array());
            }

            $wpdb->query("DELETE FROM `" . $wpdb->myrp_ratings . "` WHERE `post_id`=\'{$postID}\' AND `comment_id`=\'-1\'");

            foreach($checked as $rating) 
            {
                $name = $rating[0];
                $value = $rating[1];

                $type = $wpdb->get_var("SELECT `type` FROM `" . $wpdb->myrp_categories . "` WHERE `ID`=\'{$name}\' LIMIT 1");
                $outof = $myrp_type_outof[$type];

                if($outof == 1)
                {
                    if($value == "Yes") $value = 1;
                    if($value == "No") $value = 0;
                }

                if($outof == 40)
                {

                    $value = myrp_translate_letter_to_number($value);

                }
                $value = floatval($value);

                $wpdb->query("INSERT INTO `" . $wpdb->myrp_ratings . "`(`ID`, `category_id`, `post_id`, `comment_id`, `value`, `outof`, `weighted`) VALUES(\'\', \'{$name}\', \'{$postID}\', \'-1\', \'{$value}\', \'{$outof}\', \'" . ($value/$outof) . "\')");
            }
        }


        update_option("myrp_notChanged",0);

    }

    function myrp_save_hides($postID)
    {

        if(isset($_POST[\'_myrp_hide_set\']))
        {
            add_post_meta($postID, "_myrp_hide_float_left", $_POST[\'_myrp_hide_float_left\'], true)
                or
            update_post_meta($postID, "_myrp_hide_float_left",$_POST[\'_myrp_hide_float_left\']);

            add_post_meta($postID, "_myrp_hide_float_right", $_POST[\'_myrp_hide_float_right\'], true)
                or
            update_post_meta($postID, "_myrp_hide_float_right",$_POST[\'_myrp_hide_float_right\']);

            add_post_meta($postID, "_myrp_disable_traffic_tracking", $_POST[\'_myrp_disable_traffic_tracking\'], true)
                or
            update_post_meta($postID, "_myrp_disable_traffic_tracking",$_POST[\'_myrp_disable_traffic_tracking\']);

            add_post_meta($postID, "_myrp_disable_advanced_excerpts", $_POST[\'_myrp_disable_advanced_excerpts\'], true)
                or
            update_post_meta($postID, "_myrp_disable_advanced_excerpts",$_POST[\'_myrp_disable_advanced_excerpts\']);

            add_post_meta($postID, "_myrp_hide_microformats", $_POST[\'_myrp_hide_microformats\'], true)
                or
            update_post_meta($postID, "_myrp_hide_microformats",$_POST[\'_myrp_hide_microformats\']);

            add_post_meta($postID, "_myrp_allow_comments_without_ratings", $_POST[\'_myrp_allow_comments_without_ratings\'], true)
                or
            update_post_meta($postID, "_myrp_allow_comments_without_ratings",$_POST[\'_myrp_allow_comments_without_ratings\']);

            add_post_meta($postID, "_myrp_automatically_approve_comments", $_POST[\'_myrp_automatically_approve_comments\'], true)
                or
            update_post_meta($postID, "_myrp_automatically_approve_comments",$_POST[\'_myrp_automatically_approve_comments\']);
        }
    }
?>

1 个回复
最合适的回答,由SO网友:MikeSchinkel 整理而成

你好@ntechi:

我想这就是你想要的：

$sql =<<< SQL
SELECT
  id
FROM
  myrp_ratings AS rat,
  INNER JOIN wp_comments AS com ON com.comment_ID = rat.comment_id
WHERE 1=1
  AND com.comment_author_email = \'%s\'
  AND rat.value != \'0\'
  AND rat.value != \'1\'
LIMIT
 1
SQL;
$sql = $wpdb->prepare($sql,\'[email protected]\');
$id = $wpdb->get_var($sql);
if (is_null($id)) {
  $wpdb->query("INSERT INTO `" . $wpdb->myrp_ratings . "`(`ID`, `category_id`, `post_id`, `comment_id`, `value`, `outof`, `weighted`) VALUES(\'\', \'{$name}\', \'{$postID}\', \'-1\', \'{$value}\', \'{$outof}\', \'" . ($value/$outof) . "\')");    
}
请注意我是如何格式化SQL以便您可以实际读取它的（虽然我承认我没有INSERT.)$wpdb->prepare() 确保没有从无效的电子邮件地址使用SQL注入攻击（很遗憾，插件开发人员没有这样做。）

还要小心修改插件；如果需要，您需要将版本号更改为999之类的大版本，以确保您或其他用户在供应商更新插件时不会意外覆盖您的更改。如果可能，最好让他们将您的更改合并到下一版本的插件中。

结束
标签： plugins   mysql   小码农  

相关推荐

How do you debug plugins?

我对插件创作还很陌生，调试也很困难。我用了很多echo，它又脏又丑。我确信有更好的方法可以做到这一点，也许是一个带有调试器的IDE，我可以在其中运行整个站点，包括插件？