Converting MYSQL to WordPress $WPDB

Question

I’m upgrading an archaic Wordpress site from 3.7. It has some SQL query requests that don’t function after 3.9+ where you now have to connect to the DB via $WPDB (https://codex.wordpress.org/Class_Reference/wpdb). I've taken an initial stab at it but am hung up on the mysql_fetch_array part and also unclear if what I've already done is even correct. Seems like the mysql_fetch_array needs to use get_results (https://developer.wordpress.org/reference/classes/wpdb/get_results/).

I'm fully aware that this is a less than ideal way to approach this and fully intend to modernize everything but in keeping a long story short it would be very helpful to be able to sort this out within this old jumbled paradigm if at all possible.

Attached is the original code and then my attempt (also added a v2 attempt). Thanks for taking a look.

SQL:

$sql = "SELECT wp_acf_values.value FROM wp_acf_values, wp_acf_fields WHERE wp_acf_fields.post_id = '620' AND wp_acf_fields.id = wp_acf_values.field_id ORDER BY wp_acf_fields.order_no"; 
$result = mysql_query($sql) or die('Content was not loaded. Please refresh your page.'); 
$counter = 0; 
while($post = mysql_fetch_array($result)) { 

    $sqlSecond = "SELECT meta_value FROM wp_postmeta WHERE meta_id = '".$post['value']."'"; 
    $result1 = mysql_query($sqlSecond) or die('Content was not loaded.'); 
    while($post1 = mysql_fetch_array($result1)) { 
        $data[$counter] = $post1['meta_value']; 
        $counter++; 
    } 

} 

// get image 
$sql = "SELECT meta_value FROM wp_postmeta WHERE post_id = '".$data['1']."' AND meta_key = '_wp_attached_file'"; 
$result = mysql_query($sql) or die('Content was not loaded.'); 
$counter = 0; 
while($post = mysql_fetch_array($result)) { 
    $imgURL = $post['meta_value']; 
} 

---

WPDB v1:

global $wpdb;

$sql = $wpdb->prepare( "SELECT wp_acf_values.value FROM wp_acf_values, wp_acf_fields WHERE wp_acf_fields.post_id = '620' AND wp_acf_fields.id = wp_acf_values.field_id ORDER BY wp_acf_fields.order_no" );
$result = $wpdb->query ( $sql ) or die('Content was not loaded.');
$counter = 0;
while($post = mysql_fetch_array($result)) {

    $sqlSecond = $wpdb->prepare( "SELECT meta_value FROM wp_postmeta WHERE meta_id = %s", $post['value'] );
    $result1 = $wpdb->query ( $sqlSecond ) or die('Content was not loaded.');
    while($post1 = mysql_fetch_array($result1)) {
          $data[$counter] = $post1['meta_value'];
          $counter++;
    }

}

// get image
$sql = $wpdb->prepare( "SELECT meta_value FROM wp_postmeta WHERE post_id = %s AND meta_key = '_wp_attached_file'", $data['1'] );
$result = $wpdb->query ( $sql ) or die('Content was not loaded.');
$counter = 0;
while($post = mysql_fetch_array($result)) {
    $imgURL = $post['meta_value'];
}

---

WPDB v2:

global $wpdb;

$sql = $wpdb->prepare( "SELECT wp_acf_values.value FROM wp_acf_values, wp_acf_fields WHERE wp_acf_fields.post_id = '620' AND wp_acf_fields.id = wp_acf_values.field_id ORDER BY wp_acf_fields.order_no" );
$result = $wpdb->get_results ( $sql ) or die('Content was not loaded.');
$counter = 0;
foreach ($result as $post ) {
  $sqlSecond = $wpdb->prepare( "SELECT meta_value FROM wp_postmeta WHERE meta_id = %s", $post->value );
  $result1 = $wpdb->get_results ( $sqlTitle ) or die('Content was not loaded.');
  foreach ( $result1 as $post1 ) {
     $data[$counter] = $post1->meta_value;
     $counter++;
  }
}

// get image
$sql = $wpdb->prepare( "SELECT meta_value FROM wp_postmeta WHERE post_id = %s AND meta_key = '_wp_attached_file'", $data['1'] );
$result = $wpdb->get_results ( $sql ) or die('Content was not loaded.');
$counter = 0;
foreach ( $result as $post ) {
  $imgURL = $post->meta_value;
  $counter++;
}

Answer 1

The root problem here is the assumption that WordPress deals in mysql functions and mysql objects, and that knowledge of these functions is helpful.

The truth is, this is a complete API in of itself, and so the assumptions that knowledge of mysql_ functions provides are not useful or relevant beyond the syntax of SQL.

For example:

$result = $wpdb->query ( $sql ) or die('Content was not loaded.');
$counter = 0;
while($post = mysql_fetch_array($result)) {

Here the assumption is that $wpdb->query returns a results object, but that is not true.

If we look at what query actually returns by looking at the examples in the documentation we can see:

This function returns an integer value indicating the number of rows affected/selected

And that:

If a MySQL error is encountered, the function will return FALSE. Note that since both 0 and FALSE may be returned for row queries, you should be careful when checking the return value.

So How Do I Fetch Rows?

Using get_results:

$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );

Don't forget to run your SQL statement through $wpdb->prepare first to prevent injection attacks!

There are other methods that allow you to fetch a singular value, row, or column, but here $myrows is literally an array with the rows selected ( or an error response ). There is a second parameter to get_results that determines the return type, e.g. an associative array, an object with keys, etc

https://codex.wordpress.org/Class_Reference/wpdb#SELECT_Generic_Results

The Problem of Your Second and Third Queries

Even if you fix these queries, they are fundamentally bad practice, because they have a faster, trivial replacement:

$value = get_post_meta( $post_id, 'key' );

$value now contains all the meta values for that post with the meta key key.

You can also use WP_Query to fetch posts via their meta keys and values ( but be warned, this is a cardinal sin for performance wether you use the API, or a direct SQL )

There's probably an ACF function or API that can replace the direct SQL query in the beginning. It's very rare that you should need to do a direct SQL query. In my day job I review code for major websites handling billions of page views, and millions of lines of code, of which I've encountered direct SQL queries only a handful of times