CODEWITHSUNDEEP
phpmysql
mk_89
mk_89

Reputation: 2742

How to combine two similiar MySQL statements into one

Im having trouble combining two MySQL statements into one, at the moment im having to run them each and then placing them into an array and then merging those arrays.

here are the the functions

1st function

/* returns all books from table book */
function getProfitableBooksNew(){
    $q = "SELECT *, 

    ROUND((amazon_new_price/100*80) - lowest_new_price,2) AS margin 

    FROM ".TBL_BOOKS." 

    WHERE rank < 200000 AND rank IS NOT NULL 
    AND lowest_new_price < amazon_new_price/100*80 
    AND amazon_new_price < 9999 
    AND ROUND((amazon_new_price/100*80) - lowest_new_price) > (lowest_new_price/100*".MARGIN.") 

    ORDER BY rank ASC";

    $result = mysql_query($q, $this->connection);

    if(!$result || (mysql_numrows($result) < 1))
        return null;

    $arr = array();
    $numRows = mysql_num_rows($result);//count
    if($numRows>0){
        for($i=0; $i<$numRows; $i++){
            $arr[] = array(
                "isbn"                  => mysql_result($result, $i, "isbn"),
                "title"                 => mysql_result($result, $i, "title"),
                "rank"                  => mysql_result($result, $i, "rank"),                   
                "amazon_price"          => mysql_result($result, $i, "amazon_new_price"),
                "amazon_condition"      => "New",

                "lowest_price"          => mysql_result($result, $i, "lowest_new_price"),
                "lowest_condition"      => "New",

                "margin"                => mysql_result($result, $i, "margin"),

                "last_price_updated"    => mysql_result($result, $i, "last_price_updated"),
                "last_rank_updated"     => mysql_result($result, $i, "last_rank_updated")
            );
        }
    }       

    return $arr;
}

2nd function

/* returns all books from table book */
function getProfitableBooksUsed(){
    $q = "SELECT *, 

    ROUND((amazon_used_price/100*80) - lowest_used_price, 2) AS margin 

    FROM ".TBL_BOOKS." 

    WHERE rank < 200000 AND rank IS NOT NULL 
    AND lowest_used_price < amazon_used_price/100*80 
    AND amazon_used_price < 9999 
    AND amazon_used_price < amazon_new_price 
    AND ROUND((amazon_used_price/100*80) - lowest_used_price) > (lowest_used_price/100*".MARGIN.") 

    ORDER BY rank ASC";

    $result = mysql_query($q, $this->connection);

    if(!$result || (mysql_numrows($result) < 1))
        return null;

    $arr = array();
    $numRows = mysql_num_rows($result);//count
    if($numRows>0){
        for($i=0; $i<$numRows; $i++){
            $arr[] = array(
                "isbn"                  => mysql_result($result, $i, "isbn"),
                "title"                 => mysql_result($result, $i, "title"),
                "rank"                  => mysql_result($result, $i, "rank"),                   
                "amazon_price"          => mysql_result($result, $i, "amazon_used_price"),
                "amazon_condition"      => mysql_result($result, $i, "amazon_used_condition"),

                "lowest_price"          => mysql_result($result, $i, "lowest_used_price"),
                "lowest_condition"      => "Used",

                "margin"                => mysql_result($result, $i, "margin"),

                "last_price_updated"    => mysql_result($result, $i, "last_price_updated"),
                "last_rank_updated"     => mysql_result($result, $i, "last_rank_updated")
            );
        }
    }       

    return $arr;
}

join function

/* returns profitable books */
function getProfitableBooks(){
    $new = $this->getProfitableBooksNew();
    $used = $this->getProfitableBooksUsed();

    if($new && $used){
        return array_merge($new, $used);

    }else if($new){
        return $new;

    }else if($used){
        return $used;

    }else{
        return null;
    }
}

Can anyone give me atleast an idea of how to approach this problem? Thanks

Upvotes: 1

Views: 120

Answers (2)

Ghassan Elias
Ghassan Elias

Reputation: 2233

You can use UNION syntax

$sql = "(SELECT a FROM t1 WHERE a=10 AND B=1) 
UNION 
(SELECT a FROM t2 WHERE a=11 AND B=2) ORDER BY a LIMIT 10;

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71422

You could just UNION those two queries into a single query like this

SELECT *, 

    ROUND((amazon_new_price/100*80) - lowest_new_price,2) AS margin,
    'new' AS type 

    FROM ".TBL_BOOKS." 

    WHERE rank < 200000 AND rank IS NOT NULL 
    AND lowest_new_price < amazon_new_price/100*80 
    AND amazon_new_price < 9999 
    AND ROUND((amazon_new_price/100*80) - lowest_new_price) > (lowest_new_price/100*".MARGIN.") 

UNION

SELECT *, 

    ROUND((amazon_used_price/100*80) - lowest_used_price, 2) AS margin,
    'used' as type 

    FROM ".TBL_BOOKS." 

    WHERE rank < 200000 AND rank IS NOT NULL 
    AND lowest_used_price < amazon_used_price/100*80 
    AND amazon_used_price < 9999 
    AND amazon_used_price < amazon_new_price 
    AND ROUND((amazon_used_price/100*80) - lowest_used_price) > (lowest_used_price/100*".MARGIN.") 

ORDER BY type ASC, rank ASC

Note I have added a field 'type' to give you a way to differentiate between the new and used results.

Upvotes: 3

Related Questions