Help With MySQL to WPDB Query Conversion

Question

I have a problem. I've got a PHP script that tracks individual file downloads, and I'm looking for a way to display that number in individual posts. I've got a half-baked solution- a plain ol' MySQL manual connection script, but I'm trying to convert it to use as a Wordpress plugin (I want it to use WPDB). I've tried to convert it, but I don't know what the Wordpress alternative to 'mysql_fetch_array.' Anyways, I've included the original script below, as well as my progress on a Wordpress-friendly version.

Plain Version:

function mb_download_count() {
    global $post;
    if (get_post_meta($post->ID, 'zipname', TRUE)) {
        $namemeta = get_post_meta($post->ID, 'zipname', TRUE);

        require $_SERVER['DOCUMENT_ROOT'].'/downloader-connect.php';
        $result = mysql_query("SELECT * FROM mb_download_manager
                                    WHERE filename='".$namemeta.".zip'");  

            // get the first (and hopefully only) entry from the result
        $row = mysql_fetch_array( $result );
            // Print out the contents of each row into a table 
        if ($row['downloads'] == '') { echo ('0'); } else {
        echo $row['downloads']; }
        } else { }
}

Wordpress-friendly Version so far:

function mb_download_count() {
    global $wpdb;
    global $post;
    if (get_post_meta($post->ID, 'zipname', TRUE)) {
        $namemeta = get_post_meta($post->ID, 'zipname', TRUE);

        $result = $wpdb->query("SELECT * FROM mb_download_manager
                                    WHERE filename='".$namemeta.".zip'");  

        $row = mysql_fetch_array( $result );
        if ($row['downloads'] == '') { echo ('0'); } else {
        echo $row['downloads']; }
        } else { }
}

Answer 1

Instead of using $wpdb->query(), use $wpdb->get_results(). This method allows you to specify a return type so the query is returned as an associative array. For example ...

$results = $wpdb->get_results(
    "SELECT * FROM mb_download_manager
     WHERE filename='" . $namemeta . ".zip'",
    ARRAY_A
);

This will return you an indexed array of associative arrays ... so you can iterate through the results:

foreach( $results as $row ) {
    if ( $row['downloads'] == '') // ... and so on

If you get stuck, double check the Codex documentation.

Edit 1/13

If you want to add up all of the downloads, you need to do two things:

1. Keep a running total
2. Properly cast your variable as a number.

So before you do your foreach loop, declare a download counter:

$counter = 0;
foreach( $results as $row ) {
    // etc ...

This variable will hold a running total of your downloads.

Next, you need to add the number of downloads to your counter:

$counter = 0;
foreach( $results as $row ) {
    $counter += intval( $row['downloads'] );
    // etc ...

The intval() function will take in a mixed variable and return it's integer value. So the string "0" becomes the integer 0. This allows you to actually add the results rather than concatenating the strings (for example, "1" + "2" = "12" but 1 + 2 = 3).

Answer 2

Since you only want a single value from a single column, you can use the get_var method instead of the query method--that way, you don't need to loop though any results (you get the result as a string). So in your case, you'd do something like:

$downloads = $wpdb->get_var($wpdb->prepare("SELECT downloads FROM mb_download_manager WHERE filename=%s", $namemeta.".zip"));

Then you can just echo the result:

echo $downloads;

See the wpdb codex page, which also has a long list of additional wpdb methods that can be used, depending on your situation. Some of them, like the insert method, are much easier to use than writing straight SQL.

Oh, also note the use of the prepare method, which helps prevent injection and junk. It's not required, but it's good practice. Also note that this table needs to be in your WordPress database.