Solved

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

Posted on 2011-02-26
5
408 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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now