Join new table with SQL query

Question

I created a plugin with a new table (wp_soundcloud) and I need to retrieve the data from that new table along with the post data.

Here is my query:

 $querystr = "
    SELECT $wpdb->posts.*, 
    FROM $wpdb->posts 
    LEFT JOIN $wpdb->soundcloud ON($wpdb->posts.ID = $wpdb->soundcloud.idpost)
    WHERE $wpdb->posts.post_type = 'soundcloud'
    AND $wpdb->posts.post_name = '$name'
    ";
 $pageposts = $wpdb->get_results($querystr, OBJECT);

…and here is debug:

  ["last_query"]=>
  string(176) "
    SELECT wp_posts.*, .*
    FROM wp_posts 
    LEFT JOIN  ON(wp_posts.ID = .idpost)
    WHERE wp_posts.post_type = 'soundcloud'
    AND wp_posts.post_name = 'test-demo'
    "

…and the error:

  ["last_error"]=>
  string(226) "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM wp_posts 
    LEFT JOIN  ON(wp_posts.ID = .idpost)
    WHERE wp_posts.post_' at line 2"

The problem is the LEFT JOIN ON(wp_posts.ID = .idpost). But why did the query not JOIN the table?

Answer 1

Always escape/prepare your data

Don't leave it open to injections:

global $wpdb;
$all_posts = $wpdb->get_results( $wpdb->prepare(
    "
        SELECT %s.*
            FROM %s 
            LEFT JOIN %s ON(%s = %s)
        WHERE %s = 'soundcloud'
        AND %s = '%s'
    "
    ,$wpdb->posts
    ,$wpdb->posts
    ,"{$wpdb->prefix}soundcloud"
    ,$wpdb->posts.ID
    ,"{$wpdb->prefix}soundcloud.idpost"
    ,$wpdb->posts.post_type
    ,$wpdb->posts.post_name
    ,$name
), OBJECT );

echo '<pre>'.var_export( $all_posts, true ).'</pre>';

Use the prefix

$wpdb; offers the $wpdb->prefix, which would be whatever you set in your wp-config.php as table prefix. Then append your table name: {$wpdb->prefix}{$your_table_name} or "{$wpdb->prefix}soundcloud".

Answer 2

Your original problem is that you didn't have the $wpdb->soundcloud variable defined.

Your longer term problem is that you have created a separate table in the first place. There is almost never a good reason to create your own table, and given your particular JOIN, you probably should have used the postmeta functions instead.