Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer
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 Aug 26 '12 at 12:47

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.

share|improve this answer
    
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 Aug 26 '12 at 15:55
    
Postmeta. It's awesome. codex.wordpress.org/Function_Reference/update_post_meta –  Otto Aug 26 '12 at 19:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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