Help: custom db queries in WP, the right way

[doobie]

Hey Tricksters,

I have a couple custom tables setup in a WordPress install. I’m doing a FoxyCart integration with FoxyShop and some heavy lifting via GravityForms. I’m running the self-hosted download script. None of this is very pertinent to the matter at hand…

One table holds all of my products while the other table holds all of the purchases (with transaction ID for tracking downloads).

Here’s what I need to do: Once every X hours run a script that queries my custom tables for purchases. I need to grab the Author (WP User) ID and update some custom user meta with the number of purchases that have been made per product.

Put another way: I need to report to each of my WP Users how many purchases have been made for each of their products + how many $$ they’ve earned from these purchases. All the data is in my tables, I just need to figure out the most efficient way to get it.

Here’s what I have so far:

global $wpdb;



$sales  = "SELECT download_id, product_id FROM FoxyDownload";



if ( $sales   = $wpdb->get_results($sales) ) {



$revenue = array();



foreach ( $sales as $sale ) {



$pid = $sale->product_id;



$purch = "SELECT author, price FROM FoxyProduct WHERE product_id = $pid";

$purch = $wpdb->get_results($purch);



if ( $purch ) {



$revenue[$purch[0]->author] = $revenue[$purch[0]->author] + floatval($purch[0]->price);



$user = get_users( 'include => array( $purch[0]->author )' );



update_user_meta( $purch[0]->author, 'ebooks_author_revenue', $revenue[$purch[0]->author] );



}



}



}

I *think* that I should be doing an INNER JOIN or something like that so that I don’t have to run the main query twice. Then, I’m having trouble figuring out how I should be tallying the results and when to run the update_user_meta function.

Any help you can offer is greatly appreciated!

-Jacob

[doobie]

How’s this look:

SELECT download_id, FoxyDownload.product_id, author, price, book_id

FROM `FoxyDownload`

INNER JOIN FoxyProduct

ON FoxyProduct.product_id = FoxyDownload.product_id

Any significant drawbacks to doing this? Am I on the right track?

Thanks,
Jacob

[Author]

Posts