Need to get productdata out of mysql database

Question

I got a question. I will try to describe my situation:

I got a wordpress installation which i installed codeigniter in. This all goes good and i also got access to the database. Now i have a plugin installed for wordpress called: Woocommerce. With this plugin you can store products and productdata in your database. Now i need to access the data from my woocommerce products within the codeigniter application.

Woocommerce stores its products like this:

All products goes into a table called: wp_posts In this table there is a column defined called: post_type Woocommerce identifies the posts as products when post_type is set to product [duh!].

Now there is another table called wp_postmeta. In this table all of the productdata get stored into 4 columns: 1. meta_id [identifier for the metarow] 2. post_id [identifies itself to the wp_posts table] 3. meta_key [there will go several keys into it like: sale_price, stock, additional_price etc..] 4. meta_value [for each key there is a value.]

In the table wp_postmeta i need to sort out all meta_keys with their values if the product got a meta_key='_rentable' and the meta_value='yes'. So if this is true, i need to get all the other meta_keys and values where the post_id is the same as the rentable product. I hope i didn't confuse anyone... For now i got this query:

    $sql = "SELECT p.id, p.post_title, p.guid, p.post_type, m.meta_key, m.meta_value
            FROM wp_posts p
            INNER JOIN wp_postmeta m
            WHERE p.id=m.post_id
            AND m.meta_key='_rentable' AND m.meta_value='yes'
            ";

This only returns the meta_key: _rentable and the value: yes.. But i also need to get the price of that product.

Answer 1

It sounds like you're trying to get several pieces of data from different rows in the same table, which means you need to do more than one join. Does this get you what you're looking for?

$sql = "SELECT p.id, p.post_title, p.guid, p.post_type, m.meta_key, m.meta_value, meta_sp.meta_value as sale_price, meta_ap.meta_value as additional_price
        FROM wp_posts p
        INNER JOIN wp_postmeta m
        INNER JOIN wp_postmeta meta_sp ON p.id=meta_sp.post_id 
            AND meta_sp.meta_key='sale_price'
        INNER JOIN wp_postmeta meta_ap ON p.id=meta_ap.post_id 
            AND meta_ap.meta_key='additional_price'
        WHERE p.id=m.post_id
        AND m.meta_key='_rentable' AND m.meta_value='yes'
        ";