Grow your CSS skills. Land your dream job.

Help: custom db queries in WP, the right way

  • # September 26, 2011 at 11:55 pm

    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

    # September 27, 2011 at 10:57 am

    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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".