CODEWITHSUNDEEP
phpsqlarrays
zzMzz
zzMzz

Reputation: 467

PHP/SQL: Faster Way to Combine Query Results

I'm joining data from two SQL queries and I'm wondering if there is a faster way to do this as a single SQL query because there is a lot of looping involved. I've got two queries that look for different string values in the "option_name" field:

 $sql01= "SELECT user_id, option_value FROM wp_wlm_user_options WHERE option_name = 'wpm_login_date' ORDER BY user_id";

 $sql02 = "SELECT user_id, option_value FROM wp_wlm_user_options WHERE option_name ='stripe_cust_id' ORDER BY user_id ";

Then I create two arrays:

//Process the 1st SQL query data into an Array

$result_array01 = array();
$j = 0;

while($r = mysql_fetch_assoc($result01)) {

    if(!empty($r['option_value'])){

            //User Id and Last Login

            $result_array01[$j]['user_id'] = $r['user_id'];
            $result_array01[$j]['last_login'] = $r['option_value'];
            $j++;
    }
}

//Process the 2nd SQL query data into an Array

$result_array02 = array();
$k = 0;

while($s = mysql_fetch_assoc($result02)) {

    if(!empty($s['option_value'])){

            //User Id and Stripe Customer Id

            $result_array02[$k]['user_id'] = $s['user_id'];
            $result_array02[$k]['cust_id'] = $s['option_value'];
            $k++;
    }   
}

And finally, I combine the arrays:

//Combine the SQL query data in single Array

$combined_array = array();
$l = 0;

foreach($result_array01 as $arr01){

    //  Check type
    if (is_array($arr01)) {

         //mgc_account_print("hello: " . $arr01['user_id'] . "\r\n");

         foreach($result_array02 as $arr02){

            //  Check type
            if (is_array($arr02)) {         


                //Check if User Id matches
                if($arr01['user_id'] == $arr02['user_id']){

                    //Create Array with User Id, Cust Id and Last Login

                    $combined_array[$l]['user_id'] =  $arr01['user_id'];
                    $combined_array[$l]['last_login'] =  $arr01['last_login'];
                    $combined_array[$l]['cust_id'] =  $arr02['cust_id'];
                    $l++;
                }

            } 

         }

    }   
}

Upvotes: 1

Views: 58

Answers (2)

melic
melic

Reputation: 266

$sql01= "SELECT tbl1.user_id, tbl1.option_value FROM wp_wlm_user_options as tbl1 WHERE tbl1.option_name = 'wpm_login_date' 
union all 
SELECT tbl2.user_id, tbl2.option_value FROM wp_wlm_user_options as tbl2. WHERE tbl2.option_name ='stripe_cust_id' ";

But using OR/AND will your help you in your case , I didnt see at first that you want combined same table. I didnt delete my answer to help you for another solution

Also you should use DISTINCT to avoid multiple records. SELECT DISTINCT USER_ID, OPTION VALUE FROM TABLE

Upvotes: 0

Mobi
Mobi

Reputation: 46

Why you doing in two different queries? Use mysql IN('val', 'val2');

Upvotes: 2

Related Questions