WordPress.org

Ready to get started?Download WordPress

Forums

Join three tables (6 posts)

  1. newboi
    Member
    Posted 2 years ago #

    Hi guys, I have been looking to join three tables without any joy. I want to join the users table, posts table and a custom table (favorites). Favorites can easily be joined to posts using post_id and users can equally be joined to posts using user_id. But I can't seem to join all three. Somebody please help. Many thanks in advance.

  2. vtxyzzy
    Member
    Posted 2 years ago #

    You did not give the full table name of the favorites table, but assuming that it uses the same prefix as the standard WP tables, I think this is what you want:

    $sql = "
    SELECT * FROM $wpdb->posts p
    JOIN $wpdb->users u ON (p.ID = u.post_author)
    JOIN {$wpdb->prefix}favorites f ON (p.ID = f.post_id)
    WHERE p.post_type = 'post'
       AND p.post_status = 'publish'
    ";
  3. newboi
    Member
    Posted 2 years ago #

    Hi vtxyzzy, many thanks for your response and apologies for the delay - I was away.

    I sorted it before I saw your response but you are very much on point. Many thanks again. I hope this will help someone else in the future.

    I have run into another problem however; the query above returns something similar to this:

    +---------------+-----------------+
    | Type          |  Price          |
    +---------------+-----------------+
    | Music         |  19.99          |
    | Music         |   3.99          |
    | Music         |  21.55          |
    | Toy           |  89.95          |
    | Toy           |   3.99          |
    +---------------+-----------------+

    My problem is how to group the products by type, so that it returns a single product type and total price for each product type e.g:

    Music | 45.53
    Toy | 93.94 and so on.

    Once again, many thanks

    Cheers!

  4. vtxyzzy
    Member
    Posted 2 years ago #

    If you are using a foreach loop to display the results, something like this should work:

    $total = 0;
    $type = '';
    $currtype = '';
    foreach ($results as $post){
       if ($type == '') $currtype = $post->type;
       if ($type != $currtype) {  // Is this a new type?
          // Print the line for the currtype
          $total = 0; // Reset for this type
          $currtype = $type;
       }
       $total += $post->price;
    }
    // Print the line for the currtype

    Of course, the variable names are probably not what you used, but I hope you can get the idea.

  5. newboi
    Member
    Posted 2 years ago #

    Thanks for the response vtxyzzy, unfortunately, it hasn't worked; i.e. It doesn't group and add the prices.

  6. vtxyzzy
    Member
    Posted 2 years ago #

    Since I can't see your code, I can only suggest a general solution. If you will put your code into a pastebin and post a link to it here, I may be able to give more specific help.

Topic Closed

This topic has been closed to new replies.

About this Topic