Solved

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

Posted on 2011-02-26
5
411 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Formating field in mysql Advance formatting 1 38
Session on Html 8 37
Record locking on classic ASP 3 36
How do I fix this error with quotation marks? 4 23
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

685 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