How to join two MySQL queries in PHP

Question

How to make a single query by joining both the below queries:

Query 1:

$sql = "SELECT total_ce FROM mfb_agent_status_summary AS agent LEFT JOIN mfb_servicelog AS service ON agent.sl_id = service.sl_id WHERE service.h_id LIKE ('".$value[$i]."')  AND service_add_date >= DATE_FORMAT('" . $from . "', '%Y-%m-%d') AND service_add_date <=  DATE_FORMAT('" . $to . "', '%Y-%m-%d')";

Query 2:

$query="SELECT DATE_FORMAT(service_add_date, '%d/%b/%y'), DATE_FORMAT(scan_date, '%d/%b/%y'), DATE_FORMAT(ftp_date, '%d/%b/%y'),no_of_cases_reported,no_of_cases_received,case_count_diff, DATEDIFF(ftp_date, service_add_date) FROM mfb_servicelog WHERE h_id LIKE ('".$value[$i]."') AND service_add_date >= DATE_FORMAT('" . $from . "', '%Y-%m-%d') AND service_add_date <=  DATE_FORMAT('" . $to . "', '%Y-%m-%d')";

Thank You!

how should we know? you ahven't provided any schema information. most likely you couldn't really, because the query results from both are so very different, even though you're using the same source tables. you've got a join in query #1, which isn't in query #2, therefore #1 is a totally different query than #2. the fields don't match up, so you can't even fake it by using a union. — Marc B
– Marc B, Commented Apr 23, 2015 at 18:29
I am in learning phase...so i have no idea if it can be done or not... I just have a problem of column names while generating this data to excel.... and I believe if the above can be done then my problem will get solved... anyways thanks. — PeeJay
– PeeJay, Commented Apr 23, 2015 at 18:33

Michael Irigoyen · Accepted Answer · 2015-04-23 18:43:02Z

1

I'm making some assumptions about your schema here, but try something like this:

SELECT agent.total_ce,
       DATE_FORMAT(service.service_add_date, '%d/%b/%y'),
       DATE_FORMAT(service.scan_date, '%d/%b/%y'),
       DATE_FORMAT(service.ftp_date, '%d/%b/%y'),
       service.no_of_cases_reported,
       service.no_of_cases_received,
       service.case_count_diff,
       DATEDIFF(service.ftp_date, service.service_add_date)

FROM mfb_agent_status_summary AS agent
     LEFT JOIN mfb_servicelog AS service ON agent.sl_id = service.sl_id

WHERE service.h_id LIKE ('".$value[$i]."') AND
      agent.service_add_date >= DATE_FORMAT('" . $from . "', '%Y-%m-%d') AND
      agent.service_add_date <=  DATE_FORMAT('" . $to . "', '%Y-%m-%d')

I'd also like to mention that you should really be looking into prepared statements instead of injecting $from and $to directly into your query. You're opening yourself up to potential SQL injection attacks.

I highly recommend working with PHP's PDO library over the mysqli_* functions.

answered Apr 23, 2015 at 18:43

Michael Irigoyen

23.1k18 gold badges92 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PeeJay Over a year ago

Awesome...it worked flawlessly....Thank You. I am just learning mysql and haven't yet learned to use PDO and other things to secure code from Injection. I will read though.

Michael Irigoyen Over a year ago

Please do. Don't put anything into public consumption until you're using prepared statements (e.g. mysqli_prepare) or PDO. You'll open yourself to bigger headaches in the long run.

Alex · Accepted Answer · 2015-04-23 18:57:57Z

0

So my guess is:

$sql = "SELECT 
      agent.total_ce,
      service.*
   FROM mfb_agent_status_summary AS agent 
   LEFT JOIN (
      SELECT 
        DATE_FORMAT(service_add_date, '%d/%b/%y'), 
        DATE_FORMAT(scan_date, '%d/%b/%y'), 
        DATE_FORMAT(ftp_date, '%d/%b/%y'),
        no_of_cases_reported,
        no_of_cases_received,
        case_count_diff, 
        DATEDIFF(ftp_date, service_add_date) 
      FROM mfb_servicelog 
      WHERE h_id LIKE ('".$value[$i]."') 
        AND service_add_date >= DATE_FORMAT('" . $from . "', '%Y-%m-%d') 
        AND service_add_date <=  DATE_FORMAT('" . $to . "', '%Y-%m-%d')
   ) AS service 
   ON agent.sl_id = service.sl_id";

And I think you may replace LEFT JOIN with 'INNER JOIN';

edited Apr 23, 2015 at 18:57

answered Apr 23, 2015 at 18:47

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Collectives™ on Stack Overflow

How to join two MySQL queries in PHP

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related