Solved

how to run a php / sql function to add data to WordPress page

Posted on 2011-02-26
5
410 Views
Last Modified: 2012-05-11
I am using WordPress with a theme and plugin named ReviewsPress.
On this web page:
http://www.tcmassagereviews.com/ratings.asp
you can see that I am pulling some data from the wordpress database but the "Overall Rating" numbers are not correct.
I am pulling the data from the WordPress database and ~trying~ to create an html page.  On the web page below:
http://tcmassagereviews.com/wordpress/
you can see the first post has an overall score of 2.67       however, the number 2.67 does not exist in the database, I believe the number is generated dynamically from a php script.  I would like to put the correct numbers into my ratings.asp page.  I think I found the function in the php code that generates the overall ratiing score but I don't know how to run the function and get the data from the function into my ratings.asp page.
I think this part of the php code (below) is the function.  I have also attached the entire .php file and the ratings.asp code.  Let me know if you need more information, etc.
/**
 * Method used for your wordpress templates files where you want to show
 * total score of a post.
 *

Open in new window

sb-review.php
ratings-asp.txt
0
Comment
Question by:STP8377
  • 3
  • 2
5 Comments
 
LVL 1

Accepted Solution

by:
axbernardi earned 500 total points
ID: 34995728
You need a bit of work there... and probably you can't do it in only one query.
As you might see from the php you have posted there are 2 functions responsible for the rating scorig:

1) sb_get_post_ratings

that's the query you need to run first:
            $sql = "SELECT `" . $sb_ratings_cats_table . "`.`cat`, "
                 . "AVG(`" . $sb_ratings_table . "`.`rating_value`) AS `avg` ,"
                 . "{$sb_ratings_cats_table}.`id`"
                 . "FROM `" . $sb_ratings_table . "` "
                 . "INNER JOIN `" . $comment_table . "` "
                 . "ON (`" . $sb_ratings_table . "`.`comment_id` = `" . $comment_table . "`.`comment_ID`) "
                 . "INNER JOIN `" . $sb_ratings_cats_table . "` "
                 . "ON (`" . $sb_ratings_table . "`.`rating_cat_id` = `" . $sb_ratings_cats_table . "`.`id`) "
                 . "WHERE `" . $comment_table . "`.`comment_post_ID` = " . $post_id . " and `" . $comment_table . "`.`comment_approved` = 1 "
                 . "GROUP BY `" . $sb_ratings_cats_table . "`.`id` "
                 . "ORDER BY `" . $sb_ratings_cats_table . "`.`display_order`";

Open in new window


this gives you a recordset with the average rating per each category of rating

with this recordset you need to run a do loop on this second function

2) sb_get_rating_weight_by_post_id
 
   $sql = "SELECT `weight`
                    FROM `{$sb_cat_global_table}`
                    WHERE `id` = {$rating_cat_id}
                    AND `wp_cat_id` = $categoryID";

Open in new window

where the rating_cat_id is the category you have found in the first function and categoryID is the category of the Post (php finds it with sb_getCategoryByPostId(PostId)  )


Try to set a function that does it, using the real name of the tables, and post the script in case you have problems



0
 

Author Comment

by:STP8377
ID: 34998565
Since the Overall Rating scores exist on the home page of the website, would it be easier to "scrape" the scores from that page somehow?
Or,  since the function is already running on the home page and adding the number to the Posts, is it possible to run that same code on a new page and once I have the numbers on that page, I can display them how I want, in a table.
Those are just some ideas, I don't know if they will work or not, meanwhile I will try to do the queries as you suggested.  
I was not thinking of generating the numbers and manually plugging the numbers into the html because I already have the numbers on the main page.  I was hoping that I could get the existing code to run automatically and fill in the numbers on a different page just the same way that the code automatically runs on the main page and fills in those numbers.
0
 
LVL 1

Expert Comment

by:axbernardi
ID: 34998695
I definitively think that the way is to create the function that builds the rating and use them as you want.
It should be a piece of cake using the 2 queries above
0
 

Author Comment

by:STP8377
ID: 35023658
I'm still waiting for my web host to get .asp working in the WordPress sub-folder.  Should I run these queries with .asp or .php?  Is one better for running these queries than the other?
0
 

Author Comment

by:STP8377
ID: 35024076
Ok, I realized that I can run this from .asp from the main job level.  I'm still trying to figure out the correct names for the tables, etc.
0

Featured Post

How our DevOps Team Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question