SQL Statement for MarketPress

We are using marketpress and want to run SQL statement in DB to pull information on order (product and who ordered it). How do I get the SQL statement?

  • Panos

    Hi robert_velarde ,

    If you need to run this from WordPress then you don't need to use any sql.

    You can use WordPress post and meta functions.

    Given you have the order id you can get the order object with:
    $order = new MP_Order( $order_id );
    and the WP_Post object with:
    $order_post = get_post( $order_id );

    You can check if the user_kind is "registered" user or "guest" user (in case you have guest chckout enabled), eg:

    $mp_user_kind = get_post_meta( $order_id, 'mp_user_kind', true );
    	if( 'guest' != $mp_user_kind ){
    		$buyer_id = $order_post->post_author;
    }

    For products list:
    $mp_cart_items = get_post_meta( $order_id, 'mp_cart_items', true );
    Shipping list (including first and last names):
    $mp_shipping_info = get_post_meta( $order_id, 'mp_shipping_info', true );
    Billing info:
    $mp_billing_info = get_post_meta( $order_id, 'mp_billing_info', true );
    and order total:
    $mp_order_total = get_post_meta( $order_id, 'mp_order_total', true );

    If you need to translate all the above to sql, you can do something like:

    global $wpdb;
    	$q = "SELECT meta_value FROM {$wpdb->postmeta} WHERE post_id={$order_id} AND meta_key='mp_shipping_info'";
    	$results = $wpdb->get_var( $q );

    for "mp_shipping_info". The results would be serialized with sql queries, though so you will have to unserialize results.

    Hope this helps! However if you need something more specific please feel free to post!

    Thanks!

  • Panos

    Hi Robert!

    I'm not sure how you are sending this info nor how your LMS integrates with WP. This is a custom requirement actually that exceeds the purpose of this support. I can only provide some guidance about where the information is stored and how to do a SELECT query to fetch it.

    As I mentioned in previous message customer info ( name, email ) are stored in a serialized array in the order meta "mp_shipping_info".

    The products list is stored in order meta field 'mp_cart_items' again serialized.

    A query to fetch these :
    SELECT meta_value FROM PREFIX_postmeta WHERE post_id=ORDERID AND meta_key IN ( 'mp_shipping_info', 'mp_cart_items' ) ORDER BY meta_key DESC

    You need to replace the PREFIX with your prefix and ORDERID with the order id.

    Once you fetch this information is serialized so you will need some php. Lets say you have your query results stored in a variable called $results.

    To get the shipping info:
    $shipping_info = unserialize( $results[0]->meta_value );
    so now you can get the name and email :

    $name = $shipping_info[ 'last_name' ] . ' ' . $shipping_info[ 'first_name' ];
    $email = $shipping_info[ 'email' ];

    then get the cart info which contains the products:
    $cart_info = unserialize( $results[1]->meta_value );
    and to add products in an array:

    $products = array();
    
    	foreach( $cart_info as $order_id => $items ){
    		foreach( $items as $key => $item ){
    			$products[] = $item[ 'name' ];
    		}
    	}

    The $products variable is an array and contains a list with the products.

    If you think this is above you comfort zone of coding I would suggest to hire a developer to do this work for you. You can post this requirement as a job at our Jobs board: https://premium.wpmudev.org/wordpress-development/ where you can find and hire a developer.

    Hope you find this information helpful for your project :slight_smile:

    Kind regards,
    Panos

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.