Add additional values in an array

Question

(This post might get a big long but please bear with it and help me find the solution)

I have a product table that have various parameter, main parameter is its size and cost. Each products size is divided into 3 parts i.e small, medium and large and its cost also gets furtur divided into 3 parts according to the size of the product. For this purpose i created another table productsize that carries size and cost of that size for every product.

Small view of prodsize table is

I have another table cart, it holds the value of those product that a user has ordered

Small view of cart table is

Now I wish to combine these two tables and have an array that displays the size of each size and the quantity a user has ordered for every size of a particular product. for this purpose I wrote the code below

$sql = "SELECT catname,GROUP_CONCAT(prodsize) as sizes,GROUP_CONCAT(cost) as price,prodname,prodimg,catid,productid 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))
                {
                    $pid=$row['productid'];
                    $rows['catname'] = $row['catname'];
                    $rows['sizes'] = $row['sizes'];
                    $rows['price'] = $row['price'];
                    $rows['prodname'] = $row['prodname'];
                    $rows['prodimg'] = $row['prodimg']; 
                    $rows['catid'] = $row['catid'];     
                    $rows['productid'] = $row['productid'];

                    $user_sql = "SELECT group_concat(prodsize) as purchased_size,group_concat(quantity) as purchased_quantity,catid,userid FROM `cart` where userid='$userid' and catid='$catid' and productid='$pid'";

                    $user_result = mysqli_query($con, $user_sql);
                    if (mysqli_num_rows($user_result) > 0) 
                        {   
                            while($user_row = mysqli_fetch_assoc($user_result))
                                {
                                    $rows['purchased_size'] = $user_row['purchased_size'];
                                    $rows['purchased_quantity'] = $user_row['purchased_quantity'];
                                    $rows['userid'] = $user_row['userid'];
                                }
                        }

                    $post_rows[]=$rows;     
                }       
                echo "<pre>";
                print_r($post_rows);
                echo "</pre>";
        }

Array that I got through print_r($post_rows) is

Array
(
    [0] => Array
        (
            [catname] => Main Course
            [sizes] => small,medium,large
            [price] => 130,170,200
            [prodname] => pies
            [prodimg] => 
            [catid] => 2
            [productid] => 13
            [purchased_size] => small,large
            [purchased_quantity] => 1,1
            [userid] => 1
        )

    [1] => Array
        (
            [catname] => Main Course
            [sizes] => small,medium,large
            [price] => 50,90,110
            [prodname] => Squash
            [prodimg] => 
            [catid] => 2
            [productid] => 14
            [purchased_size] => medium
            [purchased_quantity] => 1
            [userid] => 1
        )

    [2] => Array
        (
            [catname] => Main Course
            [sizes] => small,medium
            [price] => 70,110
            [prodname] => corns
            [prodimg] => 
            [catid] => 2
            [productid] => 15
            [purchased_size] => 
            [purchased_quantity] => 
            [userid] => 
        )
)

Now the change that i want is in [purchased_size] and [purchased_quantity] . I want that if the user hasn't purchased anything for a particular product then the values in [purchased_size] and [purchased_quantity] should go 0,0,0 and if the user purchases one size of product as in the case of Squash, the user chooses medium then other values in [purchased_size] and [purchased_quantity] shouuld go 0,0 and if he has ordered 2 sizes then 3rd value in [purchased_size] and [purchased_quantity] should go 0

So the new array should look like the array given below (IMP point, the order for size and cost should be fixed i.e small, medium, large)

Array
(
    [0] => Array
        (
            [catname] => Main Course
            [sizes] => small,medium,large
            [price] => 130,170,200
            [prodname] => pies
            [prodimg] => 
            [catid] => 2
            [productid] => 13
            [purchased_size] => small,0,large
            [purchased_quantity] => 1,0,1
            [userid] => 1
        )

    [1] => Array
        (
            [catname] => Main Course
            [sizes] => small,medium,large
            [price] => 50,90,110
            [prodname] => Squash
            [prodimg] => 
            [catid] => 2
            [productid] => 14
            [purchased_size] => 0,medium,0
            [purchased_quantity] => 0,1,0
            [userid] => 1
        )

    [2] => Array
        (
            [catname] => Main Course
            [sizes] => small,medium
            [price] => 70,110
            [prodname] => corns
            [prodimg] => 
            [catid] => 2
            [productid] => 15
            [purchased_size] => 0,0,0
            [purchased_quantity] => 0,0,0
            [userid] => 
        )

)

I just don't know why you'd use GROUP_CONCAT() in the first place. It seems easier to handle the non-aggregated array directly in PHP — Strawberry
– Strawberry, Commented Sep 23, 2015 at 10:42

ajmedway · Accepted Answer · 2015-09-23 13:22:20Z

1

The problem here is in the way you are doing things and eventually you will run into some trouble as you are juggling around with data taken from your database and reformatting it to suit nuances of purpose instead of just storing & retrieving the data the correct way in the first place.

There should ideally be separate product ID's in a products table for each product/size you are handling so you can track stock levels etc and handle quantities in a cart/basket scenario on each tangible item... anyway to directly answer your question, this code makes the conversions needed for your specific purpose:

$rows['sizes'] = 'small,medium,large';
$available_sizes = explode(',', $rows['sizes']);
$rows['purchased_size'] = 'medium';
$purchased_sizes = explode(',', $rows['purchased_size']);
$rows['purchased_quantity'] = 1;
$purchased_quantity = explode(',', $rows['purchased_quantity']);

echo '<pre>';
echo '$purchased_sizes: ' . print_r($purchased_sizes, true);
echo '</pre>';
echo '<pre>';
echo '$purchased_quantity: ' . print_r($purchased_quantity, true);
echo '</pre>';

$rows['purchased_size'] = '';
$rows['purchased_quantity'] = '';
foreach ($available_sizes as $key => $size) {
    $rows['purchased_size'] .= in_array($size, $purchased_sizes) ? $size : 0;
    $rows['purchased_quantity'] .= in_array($size, $purchased_sizes) ? array_shift($purchased_quantity) : 0;
    if ($key != count($available_sizes)-1) {
        $rows['purchased_size'] .= ',';
        $rows['purchased_quantity'] .= ',';
    }
}

echo '<pre>';
echo '$rows[purchased_size]: ' . print_r($rows['purchased_size'], true);
echo '</pre>';
echo '<pre>';
echo '$rows[purchased_quantity]: ' . print_r($rows['purchased_quantity'], true);
echo '</pre>';

Output from the above:

$purchased_sizes: Array
(
    [0] => medium
)
$purchased_quantity: Array
(
    [0] => 1
)
$rows[purchased_size]: 0,medium,0
$rows[purchased_quantity]: 0,1,0

edited Sep 23, 2015 at 13:22

answered Sep 23, 2015 at 11:02

ajmedway

1,49214 silver badges29 bronze badges

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

4 Comments

ajmedway Over a year ago

But look how complex that has to become, you need to seriously rethink your data storage and overall strategy. Get a pen and paper and break it down into something simpler and more logical.

Sam Over a year ago

i see your point, it has become way to complicated, i came across a question that is quite similar to mine but it also does not have an answer, would appreciate if you could go through it. link is: stackoverflow.com/questions/32758649/…

ajmedway Over a year ago

@Sam OK sure, I will. Would you mind upvoting and clicking the tick to accept my answer? I have demonstrated that it answers this question, many thanks.

ajmedway Over a year ago

Why has this answer received 2 downvotes? Helps the guy asking the question and also serves to illustrate that this is not the best way to do things...

Collectives™ on Stack Overflow

Add additional values in an array

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related