Solved

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

Posted on 2011-02-26
5
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
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 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 …

738 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