combining multiple mysql queries into one

Question

hey guys

im learning Mysql commands and methods of combine multiple queries , so in order to gain that goal , now i need to combine these queries :

    list($TotalVisitsToday) = $db->sql_fetchrow($db->sql_query("SELECT  COUNT(DISTINCT ip_address) FROM table_iptracking WHERE `date_time` between '$yesterday' and '$today' "));
    list($TotalVisitsYesterday) = $db->sql_fetchrow($db->sql_query("SELECT  COUNT(DISTINCT ip_address) FROM table_iptracking WHERE `date_time` between '$yesterday_1' and '$yesterday'  "));

    list($TotalPVisitsToday) = $db->sql_fetchrow($db->sql_query("SELECT count(ipid) FROM table_iptracking WHERE `date_time` between '$yesterday' and '$today'"));
    list($TotalPVisitsYesterday) = $db->sql_fetchrow($db->sql_query("SELECT count(ipid) FROM table_iptracking WHERE `date_time` between '$yesterday_1' and '$yesterday'"));

    list($TotalCrawlers) = $db->sql_fetchrow($db->sql_query("SELECT count(ipid) FROM table_iptracking WHERE  hostname LIKE '%crawl%' "));

i have no clue on how to mix the above lines !

Answer 1

You could form the SQL statement into a UNION, something like...

SELECT COUNT(DISTINCT ip_address)
    FROM table_iptracking
    WHERE `date_time` between '$yesterday' and '$today'
UNION
SELECT COUNT(DISTINCT ip_address)
    FROM table_iptracking
    WHERE `date_time` between '$yesterday_1' and '$yesterday'
UNION
SELECT count(ipid)
    FROM table_iptracking
    WHERE `date_time` between '$yesterday' and '$today'
UNION
SELECT count(ipid)
    FROM table_iptracking
    WHERE `date_time` between '$yesterday_1' and '$yesterday'
UNION
SELECT count(ipid)
    FROM table_iptracking
    WHERE hostname LIKE '%crawl%'

I can't say that I would recommend this, though. A result set where different rows mean different things is likely to prove troublesome in the long run.

Answer 2

I'm not sure about your final goal, but if you just want it in one query, you can do it like this:

SELECT
(SELECT COUNT(DISTINCT ip_address) FROM table_iptracking WHERE 'date_time' between '$yesterday' and '$today') COUNT1,
(SELECT COUNT(DISTINCT ip_address) FROM table_iptracking WHERE 'date_time' between '$yesterday_1' and     '$yesterday') COUNT2,
(SELECT COUNT(ipid) FROM table_iptracking WHERE 'date_time' between '$yesterday' and '$today') COUNT3,
(SELECT COUNT(ipid) FROM table_iptracking WHERE 'date_time' between '$yesterday_1' and '$yesterday') COUNT4,
(SELECT COUNT(ipid) FROM table_iptracking WHERE  hostname LIKE '%crawl%') COUNT5

(you can omit the FROM caluse in MySQL)