CODEWITHSUNDEEP
phpmysql
Stokes860
Stokes860

Reputation: 23

MySQL - Query a temp table to retrieve 2 rows from table

Thank you for taking the time to read my question.

I am trying to combine data from 2 tables in such a way that it can be presented on a webpage with PHP -- I am using a WordPress site so the table structure may look familiar.

The table layout:

wp_3bpf9a_posts - This table has 1 row for each 'post', and I need this table so I can query for a specific type of post, and also obtain the date.

wp_3bpf9a_postmeta - Table with many records for each post (there is an ID that links the two together). I actually need data from 2 (of many) rows for each ID to complete the displayed dataset I am looking for.

I tried to accomplish this by creating a temp table joining the first record together between both tables, and then I was going to go back and join the temp table to the second record I needed. I actually connected to MySQL DB using Toad, and got the code below to work when I run each statement below one at a time in sequence. When I try to run the whole thing I do not get an error but also get no results, same when I try it on my PHP page. I know there is likely an easier way to do this, but this was my first step out of a simple MySQL query. Any guidance that you could provide would be appreciated, thanks!

DROP TABLE IF EXISTS bookingTempTable;

CREATE TEMPORARY TABLE bookingTempTable AS
SELECT ID, post_title, post_name, meta_value
FROM  `wp_3bpf9a_posts` 
    INNER JOIN `wp_3bpf9a_postmeta` ON `wp_3bpf9a_postmeta`.`post_id`=`wp_3bpf9a_posts`.`ID` 
    WHERE  `post_type` LIKE  'booked_appointments' AND `post_title` LIKE '%October 9%' AND `wp_3bpf9a_postmeta`.`meta_key` LIKE 'slot_members';

SELECT *
FROM `wp_3bpf9a_postmeta`
    INNER JOIN `bookingTempTable` ON `bookingTempTable`.`ID`=`wp_3bpf9a_postmeta`.`post_id` 
    WHERE `wp_3bpf9a_postmeta`.`meta_key` LIKE '_appointment_timeslot'
    ORDER by `wp_3bpf9a_postmeta`.`meta_value` ASC;

Upvotes: 2

Views: 88

Answers (1)

Barmar
Barmar

Reputation: 780724

You don't need the temporary table, just join with wp_sbpf9a_postmeta twice.

SELECT p.*, m1.meta_value AS slot_member, m2.meta_value AS appointment_timeslot
FROM wp_sbpf9a_posts AS p
JOIN wp_3bpf9a_postmeta AS m1 ON p.id = m1.post_id
JOIN wp_sbpf9a_postmeta AS m2 ON p.id = m2.post_id
WHERE p.post_type = 'booked_appointments'
    AND p.post_title LIKE '%October 9%'
    AND m1.meta_key = 'slot_members'
    AND m2.meta_key = '_appointment_timeslot'
ORDER BY appointment_timeslot

Upvotes: 1

Related Questions