Check only specific information from the orders post meta data in Woocommerce

Question

I am currently working on a WordPress project that has the WooCommerce plugin on it for the store. However all the billing info from orders go to the wp_postmeta table, where the all of the info is saved in the same column, "meta_value".

I have a form, that a customer can fill out using their order number, first name and post number, and after filing this field their order would show on the site.

Hence in my query I would only like to check if the user inputted order number, first name and post number are true, and I don't need anything else from the column.

I have made a test order with my credentials and at the moment my query looks like this, but it finds two results because there are two fields in the wp_postmeta table that have the same order number and the same first name in them as the order:

$ordernumber = $_POST['ordernmbr'];
$orderfirstname = $_POST['firstname'];
$orderpostnumber = $_POST['postnmbr'];

$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}postmeta
WHERE post_id = %d AND meta_value = %s",
$ordernumber, $orderfirstname);
$res = $wpdb->get_results($sql, ARRAY_A);

Ask me if anything was unclear, I'm pretty bad at explaining things!

EDIT: Updated query after getting help from Vel, still not working as intended as the query doesn't want to find any results.

$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}postmeta
WHERE post_id = %d AND (meta_value = %s AND meta_key  ='_billing_first_name' AND  meta_value = %s AND meta_key ='_billing_postcode') ",
$ordernumber, $orderfirstname, $orderpostnumber );
$res = $wpdb->get_results($sql, ARRAY_A);

Answer 1

The following SQL query will check if an entry exist for the given order ID, first name and post code:

$ordernumber = $_POST['ordernmbr'];
$orderfirstname = $_POST['firstname'];
$orderpostnumber = $_POST['postnmbr'];

$result = $wpdb->get_col( $wpdb->prepare( "
    SELECT pm.post_id
    FROM $wpdb->postmeta as pm
    JOIN $wpdb->postmeta as pm1 ON pm.post_id = pm1.post_id
    WHERE pm.post_id = %d
    AND pm.meta_key  ='_billing_first_name'
    AND pm.meta_value = %s
    AND pm1.meta_key ='_billing_postcode'
    AND pm1.meta_value = %s
", $ordernumber, $orderfirstname, $orderpostnumber ) );

if( sizeof($result) ) 
    $result = true;

Tested and works.

---

It can be also done with get_post_meta() Wordpress function this way:

$firstname = get_post_meta( $_POST['ordernmbr'], '_billing_first_name', true );
$postnmbr  = get_post_meta( $_POST['ordernmbr'], '_billing_postcode', true );

if( $firstname == $_POST['firstname'] && $postnmbr == $_POST['postnmbr'] ) {
    // The data match
} elseif( $firstname == $_POST['firstname'] || $postnmbr == $_POST['postnmbr'] ) {
    // The data match partially
else {
    // The data don't match
}

Answer 2

You need to use meta_key.

$ordernumber = $_POST['ordernmbr'];
$orderfirstname = $_POST['firstname'];
$orderpostnumber = $_POST['postnmbr'];

$sql = $wpdb->prepare( "select * from (SELECT * FROM wp_postmeta WHERE ( meta_key = '_billing_first_name' AND meta_value =%s ) 
OR ( meta_key = '_shipping_postcode' AND meta_value =%s ) ) as p 
where post_id=%s", $orderfirstname, $orderpostnumber, $ordernumber );
$res = $wpdb->get_results($sql, ARRAY_A);

updated query

select * from (SELECT * FROM wp_postmeta WHERE ( meta_key = '_billing_first_name' AND meta_value ='Gnanavel' ) OR ( meta_key = '_shipping_postcode' AND meta_value ='622222' ) ) as p where post_id=2899

Answer 3

Thank you guys for all the help, but I actually managed to solve it by myself:

$sql = $wpdb->prepare("SELECT post_id FROM wp_postmeta
WHERE post_id = %d AND meta_key in ('_billing_first_name', '_billing_postcode')
and meta_value in ('%s', '%d')

group by post_id", $ordernumber, $orderfirstname, $orderpostnumber);