1

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?

2 Answers 2

2

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".

1
  • 1
    yeah! Of course I would take care of the security part thereafter. And thank you for the prefix part, it helps me a lot!
    – atmon3r
    Commented Aug 26, 2012 at 12:47
1

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.

2
  • I understand, I create a new section on my blog with register_post_type I create this separate table to add new information for each new post... I will also encode a voting system for each post but how can I do otherwise? add new fields in the table wp_post?
    – atmon3r
    Commented Aug 26, 2012 at 15:55
  • Postmeta. It's awesome. codex.wordpress.org/Function_Reference/update_post_meta
    – Otto
    Commented Aug 26, 2012 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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