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'
";
Thread Starter
newboi
(@newboi)
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!
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.
Thread Starter
newboi
(@newboi)
Thanks for the response vtxyzzy, unfortunately, it hasn’t worked; i.e. It doesn’t group and add the prices.
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.
