how to create subarray into main array [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed last year.

Improve this question

i have two table in sql database 1st is booking and it has customers details and another is booking_line it has purchased products history by customers now i want both results in a array like following image i just want an array named Customer has all purchased details customers wise

array(
        [0] => array(
                 [id] => 67
                 [name] => Neha
                 [mobile_no] => xxxxxxxxx
                 [gender] => Female
                 [Sale] => Array
                    (
                        [id] => 1
                        [booking_id] => 67
                        [barcode] => 1368
                        [product_name] => Custom Print Mattel Rakhi BE
                        [p_price] => 35
                        [price] => 100
                   )
                [Sale] => Array
                    (
                        [id] => 2
                        [booking_id] => 67
                        [barcode] => 1368
                        [product_name] => Custom Print Mattel Rakhi BE
                        [p_price] => 35
                        [price] => 100
                   )
         )
       [1] => array(
                     [id] => 68
                     [name] => Nehaxx
                     [mobile_no] => xxxxxxxxx
                     [gender] => Female
             )

Currently i got the output like this

    array(
        [0] => array(
                 [id] => 67
                 [name] => Neha
                 [mobile_no] => xxxxxxxxx
                 [gender] => Female
         )
        [1] => array(
           [Sale] => Array
                (
                    [id] => 1
                    [booking_id] => 67
                    [barcode] => 1368
                    [product_name] => Custom Print Mattel Rakhi BE
                    [p_price] => 35
                    [price] => 100
               )
         )
        [2] => array(
                 [id] => 68
                 [name] => Nehaxx
                 [mobile_no] => xxxxxxxxx
                 [gender] => Female
         )
         [1] => array(
           [Sale] => Array
                (
                    [id] => 1
                    [booking_id] => 67
                    [barcode] => 1368
                    [product_name] => Custom Print Mattel Rakhi BE
                    [p_price] => 35
                    [price] => 100
               )
         )
)

and my code is

$sqlA = "SELECT * FROM booking";
$resultA = $conn->query($sqlA);
$dataA = array();

while($rowA = $resultA->fetch_assoc()) {
    $sql = "SELECT * FROM booking_line WHERE booking_id=".$rowA["id"]."";
    $result = $conn->query($sql);
    $dataA[] = $rowA;
    
    while($row = $result->fetch_assoc()) {   
        $dataA[] = array("Sale" => $row);
    }
}
print_r($dataA);

Have you tried anything? It's unclear if the screenshot shows the output of your current code, or a desired output? Please provide a clear structure to your question: sample source data, expected result, your current code, and what the code currently outputs. Remember we can't easily run your code since we don't have access to your database. See also How to Ask and how to make a minimal reproducible example of your issue. You can edit your question. Thanks. — ADyson
– ADyson, Commented May 7, 2024 at 8:49
Ok. Well 1) what output do you get now? 2) what does the source data look like? 3) your code is incomplete, it only seems to show one query. Like I said above, you need to edit your question to show the end-to-end requirements and code properly. We only half have the information right now. Once we are fully informed we can help you a lot more easily. Thanks. — ADyson
– ADyson, Commented May 7, 2024 at 10:58
Making iterated trips to your database (nesting queries inside of loops is not recommended practice). You are tying up resources unnecessarily and should be using a table join to make only one trip to the database. This is related data. — mickmackusa
– mickmackusa ♦, Commented May 21, 2024 at 1:53

ADyson · Accepted Answer · 2024-05-07 12:14:49Z

1

The problem is that you're adding the second row data to the top level of the array, instead of as an item underneath the main row.

This should help:

while($rowA = $resultA->fetch_assoc()) {
    $sql = "SELECT * FROM booking_line WHERE booking_id = ?";
    $result = $mysqli->execute_query($sql, [$rowA["id"]]);
    
    while($row = $result->fetch_assoc()) {   
        $rowA["Sale"] = $row;
    }

    $dataA[] = $rowA;
}

However: If you expect there could be more than one booking line associated with a booking (which sounds likely), then you will need a slightly different approach, otherwise you would only ever see one of the booking_line records in your final result.

For example:

while($rowA = $resultA->fetch_assoc()) {
    $sql = "SELECT * FROM booking_line WHERE booking_id = ?";
    $result = $mysqli->execute_query($sql, [$rowA["id"]]);
    $rows = $result->fetch_all(MYSQLI_ASSOC);
    $rowA["Sales"] = $rows;
    $dataA[] = $rowA;
}

Live demo: https://phpize.online/sql/mysql80/388e1f35aca672c57025358804701bf4/php/php82/d66648a97e39b33c527ea35a33c90f2e/

P.S. I also paramterised your query correctly for you - it should not be written with variable data concatenated directly into the SQL string - see How to include a PHP variable inside a MySQL statement for more info.

edited May 7, 2024 at 12:14

answered May 7, 2024 at 12:05

ADyson

62.9k16 gold badges85 silver badges99 bronze badges

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

1 Comment

mickmackusa Over a year ago

If you find this question to be sufficiently clear, please recommend a JOINed query solution (and/or find a suitable dupe).

Collectives™ on Stack Overflow

how to create subarray into main array [closed]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related