У меня есть 2 таблицы: продукт и тележка, я хочу объединить эти 2 таблицы и отобразить данные в массиве в соответствии с конкретным условием, а именно:
Все продукты под определенной категорией должны отображаться, и если конкретный пользователь приобрел какой-либо продукт среди данных продуктов, его детали также должны отображаться перед этим продуктом
Код, который я сделал до сих пор,
$catid = $_REQUEST['catid']; $userid = $_REQUEST['userid']; $sql = "select * from productsize where catid = '".$catid."' GROUP BY productid"; $result = mysqli_query($con, $sql); if (mysqli_num_rows($result) > 0) { while($row = mysqli_fetch_assoc($result)) { $rows['catid'] = $row['catid']; $rows['catname'] = $row['catname']; $rows['productid'] = $row['productid']; $rows['prodname'] = $row['prodname']; $rows['prodimg'] = $row['prodimg']; $row2[]=$rows; } } echo "<pre>"; print_r($row2); echo "</pre>"; Он дает такой массив
Array ( [0] => Array ( [catid] => 2 [catname] => C1 [productid] => 13 [prodname] => P1 [prodimg] => ) [1] => Array ( [catid] => 2 [catname] => C1 [productid] => 14 [prodname] => P1 [prodimg] => ) [2] => Array ( [catid] => 2 [catname] => C1 [productid] => 15 [prodname] => P3 [prodimg] => ) )
Но последний массив, который я хочу вместо указанного массива,
Array ( [0] => Array ( [catid] => 2 [catname] => C1 [productid] => 13 [prodname] => P1 [prodimg] => [size] => Array ( [0] => small [1] => medium [2] => large [3] => perpiece ) [cost] => Array ( [0] => 10 [1] => 20 [2] => 30 [3] => 12 ) [purchasedsize] => Array ( [0] => small [1] => 0 [2] => large [3] => 0 ) [purchasedquantity] => Array ( [0] => 2 [1] => 0 [2] => 1 [3] => 0 ) [userid] => 1 ) [1] => Array ( [catid] => 2 [catname] => C1 [productid] => 14 [prodname] => P1 [prodimg] => [size] => Array ( [0] => small [1] => medium [2] => large [3] => 0 ) [cost] => Array ( [0] => 15 [1] => 20 [2] => 25 [3] => 0 ) [purchasedsize] => Array ( [0] => 0 [1] => medium [2] => 0 [3] => 0 ) [purchasedquantity] => Array ( [0] => 0 [1] => 1 [2] => 0 [3] => 0 ) [userid] => 1 ) [2] => Array ( [catid] => 2 [catname] => C1 [productid] => 15 [prodname] => P3 [prodimg] => [size] => Array ( [0] => 0 [1] => medium [2] => 0 [3] => perpiece ) [cost] => Array ( [0] => 0 [1] => 20 [2] => 0 [3] => 18 ) [purchasedsize] => Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 ) [purchasedquantity] => Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 ) [userid] => 0 ) )
Вид таблицы продуктов (так как вы увидите, что таблица продуктов содержит продукт и под каждым продуктом имеется макс. 4 размера (их будет не больше 4))
id catid catname productid prodsize cost prodname prodimg 1 2 C1 13 small 10 P1 2 2 C1 13 medium 20 P1 3 2 C1 13 large 30 P1 4 2 C1 13 perpiece 12 P1 5 2 C1 14 small 15 P2 6 2 C1 14 medium 20 P2 7 2 C1 14 large 25 P2 8 2 C1 15 perpiece 18 P3 9 2 C1 15 medium 20 P3
Вид стола
id catid catname userid productid prodname prodsize quantity prodcost 1 2 C1 1 13 P1 large 1 30 2 2 C1 1 13 P1 small 2 10 3 2 C1 1 14 P2 medium 1 20
Может ли кто-нибудь помочь мне получить требуемый массив в результате?
Попробуй это
$catid = $_REQUEST['catid']; $userid = $_REQUEST['userid']; $sql= "SELECT p.catid, p.catname, p.productid, p.prodimg, GROUP_CONCAT(p.prodsize ORDER BY p.id ASC) as size, GROUP_CONCAT(p.cost ORDER BY p.id ASC) as cost, p.prodname, GROUP_CONCAT(c.prodsize,'-',c.quantity) as cart_details, GROUP_CONCAT(DISTINCT(c.userid)) as user_id FROM products p LEFT JOIN cart c ON(c.productid = p.productid AND c.userid = '$userid' AND p.prodsize = c.prodsize) WHERE p.catid ='$catid' GROUP BY p.productid ORDER BY user_id DESC, p.productid ASC"; $result = mysql_query($sql); if (mysql_num_rows($result) > 0) { $i = 0; while($row = mysql_fetch_assoc($result)) { $rows[$i]['catid'] = $row['catid']; $rows[$i]['catname'] = $row['catname']; $rows[$i]['productid'] = $row['productid']; $rows[$i]['prodname'] = $row['prodname']; $rows[$i]['prodimg'] = $row['prodimg']; $final_size = array_fill(0, 4, '0'); $final_cost = array_fill(0, 4, '0'); $size = explode(',', $row['size']); $cost = explode(',', $row['cost']); foreach($size as $k=>$sizecol) { switch($sizecol) { case 'small': $array_key = '0'; break; case 'medium': $array_key = '1'; break; case 'large': $array_key = '2'; break; case 'perpiece': $array_key = '3'; break; } $final_size[$array_key] = $sizecol; $final_cost[$array_key] = $cost[$k]; } $cart_details = explode(',', $row['cart_details']); $purchasedsize = array_fill(0, 4, '0'); //Since you displayed this array has 4 values only $purchasedquantity = array_fill(0, 4, '0'); foreach($cart_details as $cart) { if($cart != '') { $details = explode('-', $cart); $key = array_search($details[0], $size); $purchasedsize[$key] = $details[0]; $purchasedquantity[$key] = $details[1]; } } $rows[$i]['size'] = $final_size; $rows[$i]['cost'] = $final_cost; $rows[$i]['purchasedsize'] = $purchasedsize; $rows[$i]['purchasedquantity'] = $purchasedquantity; $rows[$i]['userid'] = $row['user_id']; $i++; } } echo "<pre>"; print_r($rows); echo "</pre>";
Выходной массив
Array ( [0] => Array ( [catid] => 2 [catname] => c1 [productid] => 13 [prodname] => P1 [prodimg] => [size] => Array ( [0] => small [1] => medium [2] => large [3] => perpiece ) [cost] => Array ( [0] => 10 [1] => 20 [2] => 30 [3] => 12 ) [purchasedsize] => Array ( [0] => small [1] => 0 [2] => large [3] => 0 ) [purchasedquantity] => Array ( [0] => 2 [1] => 0 [2] => 1 [3] => 0 ) [userid] => 1 ) [1] => Array ( [catid] => 2 [catname] => c1 [productid] => 14 [prodname] => P2 [prodimg] => [size] => Array ( [0] => small [1] => medium [2] => large [3] => 0 ) [cost] => Array ( [0] => 15 [1] => 20 [2] => 25 [3] => 0 ) [purchasedsize] => Array ( [0] => 0 [1] => medium [2] => 0 [3] => 0 ) [purchasedquantity] => Array ( [0] => 0 [1] => 1 [2] => 0 [3] => 0 ) [userid] => 1 ) [2] => Array ( [catid] => 2 [catname] => C1 [productid] => 15 [prodname] => P3 [prodimg] => [size] => Array ( [0] => 0 [1] => medium [2] => 0 [3] => perpiece ) [cost] => Array ( [0] => 0 [1] => 20 [2] => 0 [3] => 18 ) [purchasedsize] => Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 ) [purchasedquantity] => Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 ) [userid] => ) )