MySQL Table joining joys!
  • mcfinstermcfinster
     
    January 5
    Right, so I am trying to join the Interspire isc_orders, isc_order_status and isc_unique_visitors all together so I can display a list of order totals of various orders with X status id and then work out the conversion rate by using the unique visitors field. 

    This works

    $order_total_week_query = "SELECT *, COUNT(orderid), SUM(subtotal_ex_tax), FROM_UNIXTIME(orddate, '%v') as total_week, SUM(total_inc_tax) FROM isc_orders LEFT JOIN isc_order_status ON isc_order_status.statusid = isc_orders.ordstatus WHERE orddate BETWEEN UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 5 WEEK)) AND UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 0 WEEK)) AND statusid IN (1, 2, 3, 7, 8, 9, 10, 11) GROUP BY total_week ORDER BY orddate DESC";

    but try as I might I cannot work out how to join the third table in (joining the orddate field in isc_orders and the datestamp field in isc_unique_visitors).  The complication (apart from not knowing MySQL very well!!) seems to be as I need to group these by week so presumably I need to get the Unix time before doing the join!

    I suspect there may be an easier way to do this but if anyone has any pointers then I'd be eternally grateful!
     
  • mcfinstermcfinster
     
    January 6
    Never mind, figured it out! Will post final code here when I'm done building what I'm building!!!
     
  • Gabriel CroweGabriel Crowe
     
    January 10
    woo! fun toys. 
     
Add a Comment