1

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++;
}
  • 2
    Note that some of those DB queries can actually be replaced with direct calls to get_post_meta. Also wpdb does not returns values that fit into mysql functions, it returns arrays or objects containing data, WP_Error objects, or error values. There's also no guarantee that ACF still uses a wp_acf_values table. The or die() style syntax is super unusual for WP too, I would strongly recommend removing it in favor of conditional checks – Tom J Nowell Aug 14 '18 at 16:38
  • Yea, I could add the conditionals once I get something working again. This site is nearing EOL. Was trying to get it out of the WP dark ages one final time to a more "modern" build starting with 3.9. Might be more trouble than it's worth though to sort all this out. Seems like 3.8 still allowed these types of archaic calls maybe I just clean a few things up and leave things relatively as-is rather than rebuild everything for a site nearing EOL. Was just hopeful someone out there might have a simple idea for how to update this. I'll have to look into get_post_meta and see. – chef_3072485 Aug 14 '18 at 17:36
  • If this is a dirty fix just replace mysql_query( with mysqli_query( $wpdb->dbh – TheDeadMedic Aug 14 '18 at 18:42
  • I tried adding mysqli to all mysql in the SQL version of the code above and got the following error. Warning: mysqli_query() expects at least 2 parameters, 1 given in ... Am I not using it correctly? – chef_3072485 Aug 15 '18 at 3:28
  • @chef_3072485 you might want to update to 4.9 instead of 3.9, a lot of references and information will assume the newer versions. At the moment though you have multiple queries and it isn't clear which ones fail and where things stop. So break things down further, and explain explicitly what is going wrong and where things do not match your expectations. Check return values and make sure they are what you expect them to be rather than assuming ( e.g. the first example where you assume a mysql results object is returned but never check the docs/variable to see if that's the case ) – Tom J Nowell Aug 15 '18 at 13:09
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.