4

I have 2 tables: product and cart, I wish to combine these 2 tables and display the data in array according to a particular condition, as follows:

All the products under a specific category should get displayed and if a particular user have purchased any product among the given products then its details should also get displayed in front of that product

The code that I have done until now is

$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>";

It gives an array like this

 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] => 
        )

)

But the final array that I want in place of the above array is

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
        )

)

View of product table (As you will see the product table carries a productid and under each productid there are max 4 size (there won't be more than 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

View of cart table

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

Can anyone please help me to get the required array as the result?

Improve this question

1 Answer 1

Reset to default
0

Try this

 $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>";

The output array

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] => 
    )

)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

although i get an array but the values in it are not correct
@kavi: Now the array seems to be output as you want. Check my modified code
I gave the output which I got is similar to the one you posted.
rest of the array seems to be fine but the cost of different size is still coming wrong, eg for the product id 13, the cost of size is opp, it should be 10,20,30 and your o/p is showing 30,20,10. Would really appreciate if you could check this
@kavi: Thanks for pointing that. I have changed GROUP_CONCAT(p.cost) as cost to GROUP_CONCAT(p.cost ORDER BY p.id ASC) as cost. Now its solved

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.