php - Left join return duplicate data although I used GROUP BY -
sql:
$sql = "select orders.*,order_products.*,products.*,psettings.*,cities.*,locations.* orders left join order_products on orders.id=order_products.order_id left join products on products.id=order_products.product_id left join psettings on psettings.product_id=order_products.product_id left join cities on cities.id=orders.city_id left join locations on locations.id=orders.location_id orders.status = '$status' order orders.id asc "; it returns unique data. here returned data:
array ( [orders] => array ( [id] => 12 [name] => abdus sattar bhuiyan [email] => sattar.kuet@gmail.com [mobile] => 01673050495 [alt_mobile] => 01818953250 [city_id] => 2 [location_id] => 5 [status] => confirmed [cashed] => 1115 [created] => 2015-07-02 01:07:18 [modified] => 2015-07-02 01:01:57 [comment] => 07/02/2015 06:00 ) [city] => array ( [id] => 2 [name] => comilla ) [location] => array ( [id] => 5 [city_id] => 2 [name] => homna ) [order_products] => array ( [0] => array ( [id] => 10 [order_id] => 12 [product_id] => 1 [pieces] => 1 ) [1] => array ( [id] => 11 [order_id] => 12 [product_id] => 2 [pieces] => 1 ) [2] => array ( [id] => 12 [order_id] => 12 [product_id] => 3 [pieces] => 3 ) ) [products] => array ( [0] => array ( [id] => 1 [category_id] => 1 [name] => নভোযানের নাম সি প্রোগ্রামিং [writer] => engr. abdus sattar bhuiyan [created] => 2015-06-24 16:17:45 ) [1] => array ( [id] => 2 [category_id] => 1 [name] => resonance of creativity c++ [writer] => engr. abdus sattar bhuiyan [created] => 2015-06-26 07:32:52 ) [2] => array ( [id] => 3 [category_id] => 1 [name] => programming story c [writer] => hasibul hasan shanto [created] => 2015-06-26 07:35:57 ) ) [psettings] => array ( [0] => array ( [id] => 1 [category_id] => 1 [product_id] => 1 [img] => 1.jpg [desc] => description [created] => 2015-06-29 15:15:58 [bppp] => 165 [sppp] => 300 [discount] => 20 [service_charge] => 30 ) [1] => array ( [id] => 2 [category_id] => 1 [product_id] => 2 [img] => 2.jpg [desc] => [created] => 2015-06-26 07:33:41 [bppp] => 150 [sppp] => 250 [discount] => 20 [service_charge] => 30 ) [2] => array ( [id] => 3 [category_id] => 1 [product_id] => 3 [img] => 3.jpg [desc] => [created] => 2015-06-26 07:36:26 [bppp] => 150 [sppp] => 250 [discount] => 10 [service_charge] => 30 ) ) ) but when add table join left orders table returns duplicate entry. additionally join left 'action_bies' orders table follows:
$sql = "select orders.*,order_products.*,products.*,psettings.*,cities.*,locations.*,action_bies.* orders left join order_products on orders.id=order_products.order_id left join action_bies on orders.id=action_bies.order_id , action_bies.action='$action' left join products on products.id=order_products.product_id left join psettings on psettings.product_id=order_products.product_id left join cities on cities.id=orders.city_id left join locations on locations.id=orders.location_id orders.status = '$status' order orders.id asc "; this sql return duplicate data. here data:
array ( [orders] => array ( [id] => 12 [name] => abdus sattar bhuiyan [email] => sattar.kuet@gmail.com [mobile] => 01673050495 [alt_mobile] => 01818953250 [city_id] => 2 [location_id] => 5 [status] => confirmed [cashed] => 1115 [created] => 2015-07-02 01:07:18 [modified] => 2015-07-02 01:01:57 [comment] => 07/02/2015 06:00 ) [city] => array ( [id] => 2 [name] => comilla ) [location] => array ( [id] => 5 [city_id] => 2 [name] => homna ) [action] => array ( [0] => array ( [id] => 1 [action] => confirm [admin_id] => 30 [order_id] => 12 [created] => 0000-00-00 00:00:00 ) [1] => array ( [id] => 4 [action] => confirm [admin_id] => 30 [order_id] => 12 [created] => 2015-07-02 00:00:00 ) [2] => array ( [id] => 5 [action] => confirm [admin_id] => 30 [order_id] => 12 [created] => 2015-07-02 00:00:00 ) [3] => array ( [id] => 1 [action] => confirm [admin_id] => 30 [order_id] => 12 [created] => 0000-00-00 00:00:00 ) [4] => array ( [id] => 4 [action] => confirm [admin_id] => 30 [order_id] => 12 [created] => 2015-07-02 00:00:00 ) [5] => array ( [id] => 5 [action] => confirm [admin_id] => 30 [order_id] => 12 [created] => 2015-07-02 00:00:00 ) [6] => array ( [id] => 1 [action] => confirm [admin_id] => 30 [order_id] => 12 [created] => 0000-00-00 00:00:00 ) [7] => array ( [id] => 4 [action] => confirm [admin_id] => 30 [order_id] => 12 [created] => 2015-07-02 00:00:00 ) [8] => array ( [id] => 5 [action] => confirm [admin_id] => 30 [order_id] => 12 [created] => 2015-07-02 00:00:00 ) ) [order_products] => array ( [0] => array ( [id] => 10 [order_id] => 12 [product_id] => 1 [pieces] => 1 ) [1] => array ( [id] => 10 [order_id] => 12 [product_id] => 1 [pieces] => 1 ) [2] => array ( [id] => 10 [order_id] => 12 [product_id] => 1 [pieces] => 1 ) [3] => array ( [id] => 11 [order_id] => 12 [product_id] => 2 [pieces] => 1 ) [4] => array ( [id] => 11 [order_id] => 12 [product_id] => 2 [pieces] => 1 ) [5] => array ( [id] => 11 [order_id] => 12 [product_id] => 2 [pieces] => 1 ) [6] => array ( [id] => 12 [order_id] => 12 [product_id] => 3 [pieces] => 3 ) [7] => array ( [id] => 12 [order_id] => 12 [product_id] => 3 [pieces] => 3 ) [8] => array ( [id] => 12 [order_id] => 12 [product_id] => 3 [pieces] => 3 ) ) [products] => array ( [0] => array ( [id] => 1 [category_id] => 1 [name] => নভোযানের নাম সি প্রোগ্রামিং [writer] => engr. abdus sattar bhuiyan [created] => 2015-06-24 16:17:45 ) [1] => array ( [id] => 1 [category_id] => 1 [name] => নভোযানের নাম সি প্রোগ্রামিং [writer] => engr. abdus sattar bhuiyan [created] => 2015-06-24 16:17:45 ) [2] => array ( [id] => 1 [category_id] => 1 [name] => নভোযানের নাম সি প্রোগ্রামিং [writer] => engr. abdus sattar bhuiyan [created] => 2015-06-24 16:17:45 ) [3] => array ( [id] => 2 [category_id] => 1 [name] => resonance of creativity c++ [writer] => engr. abdus sattar bhuiyan [created] => 2015-06-26 07:32:52 ) [4] => array ( [id] => 2 [category_id] => 1 [name] => resonance of creativity c++ [writer] => engr. abdus sattar bhuiyan [created] => 2015-06-26 07:32:52 ) [5] => array ( [id] => 2 [category_id] => 1 [name] => resonance of creativity c++ [writer] => engr. abdus sattar bhuiyan [created] => 2015-06-26 07:32:52 ) [6] => array ( [id] => 3 [category_id] => 1 [name] => programming story c [writer] => hasibul hasan shanto [created] => 2015-06-26 07:35:57 ) [7] => array ( [id] => 3 [category_id] => 1 [name] => programming story c [writer] => hasibul hasan shanto [created] => 2015-06-26 07:35:57 ) [8] => array ( [id] => 3 [category_id] => 1 [name] => programming story c [writer] => hasibul hasan shanto [created] => 2015-06-26 07:35:57 ) ) [psettings] => array ( [0] => array ( [id] => 1 [category_id] => 1 [product_id] => 1 [img] => 1.jpg [desc] => description [created] => 2015-06-29 15:15:58 [bppp] => 165 [sppp] => 300 [discount] => 20 [service_charge] => 30 ) [1] => array ( [id] => 1 [category_id] => 1 [product_id] => 1 [img] => 1.jpg [desc] => description [created] => 2015-06-29 15:15:58 [bppp] => 165 [sppp] => 300 [discount] => 20 [service_charge] => 30 ) [2] => array ( [id] => 1 [category_id] => 1 [product_id] => 1 [img] => 1.jpg [desc] => description [created] => 2015-06-29 15:15:58 [bppp] => 165 [sppp] => 300 [discount] => 20 [service_charge] => 30 ) [3] => array ( [id] => 2 [category_id] => 1 [product_id] => 2 [img] => 2.jpg [desc] => [created] => 2015-06-26 07:33:41 [bppp] => 150 [sppp] => 250 [discount] => 20 [service_charge] => 30 ) [4] => array ( [id] => 2 [category_id] => 1 [product_id] => 2 [img] => 2.jpg [desc] => [created] => 2015-06-26 07:33:41 [bppp] => 150 [sppp] => 250 [discount] => 20 [service_charge] => 30 ) [5] => array ( [id] => 2 [category_id] => 1 [product_id] => 2 [img] => 2.jpg [desc] => [created] => 2015-06-26 07:33:41 [bppp] => 150 [sppp] => 250 [discount] => 20 [service_charge] => 30 ) [6] => array ( [id] => 3 [category_id] => 1 [product_id] => 3 [img] => 3.jpg [desc] => [created] => 2015-06-26 07:36:26 [bppp] => 150 [sppp] => 250 [discount] => 10 [service_charge] => 30 ) [7] => array ( [id] => 3 [category_id] => 1 [product_id] => 3 [img] => 3.jpg [desc] => [created] => 2015-06-26 07:36:26 [bppp] => 150 [sppp] => 250 [discount] => 10 [service_charge] => 30 ) [8] => array ( [id] => 3 [category_id] => 1 [product_id] => 3 [img] => 3.jpg [desc] => [created] => 2015-06-26 07:36:26 [bppp] => 150 [sppp] => 250 [discount] => 10 [service_charge] => 30 ) ) ) here should mentioned action_bies table has duplicate data follows: 
how can unique data in case. read large data.
you can select distinct on unique identifier have, orders_products.id likely.
example:
select distinct(orders_products.id) orders_products x = '$y'; i don't see group statement in original query.
Comments
Post a Comment