Need to get productdata out of mysql database

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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'
        ";

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0