CODEWITHSUNDEEP
php
user622378
user622378

Reputation: 2336

Combining two SQL queries to a array?

Is this the correct way to combine two SQL queries to a array?

Note: I am aware of SQL JOIN, but I need to use two querys.

See below:

    $query = "SELECT  * FROM categories WHERE takeawayID  = :TakeawayID";
    $statement = $this->db->prepare($query);
    $statement->bindValue(':TakeawayID', $takeawayID, PDO::PARAM_STR);
    $statement->execute();
    $data['rowCats'] = $statement->fetchall(PDO::FETCH_CLASS);
    $categories = array();
    foreach ($data['rowCats'] as $cat) {
        $temp_categories = array();
        $temp_categories['id'] = $cat->id;
        $temp_categories['name'] = $cat->name;

        $num = $cat->id;

        $query = "SELECT * FROM items WHERE category_id = :category_id";
        $statement = $this->db->prepare($query);
        $statement->bindValue(':category_id', $num, PDO::PARAM_STR);
        $statement->execute();
        $data['rowItem'] = $statement->fetchall(PDO::FETCH_CLASS);
        foreach ($data['rowItem'] as $Item) {
           $temp_categories['item']['name'][] = $Item->name;
        }
                       $categories[] = $temp_categories;
    }

$categories array is now can be pass on to view file (template)

In the view file I should be able to do something like this:

<?php foreach($categories as $category): ?>
    <table  border=0 Cellspacing='0'>
        <tr>
            <td>
              <?php echo $category['name']; ?>
            </td>
        </tr>
    <?php foreach ($category['items'] as $item): ?>     
        <tr>
            <td>
              <?php echo $item['name']; ?>
            </td>
        </tr>
    <?php endforeach; ?>
    </table>
<?php endforeach; ?>

Upvotes: 0

Views: 1160

Answers (1)

Kaivosukeltaja
Kaivosukeltaja

Reputation: 15735

Is there a reason why you're fetching the results as classes and not associative arrays? Changing FETCH_CLASS to FETCH_ASSOC will let you add the item names directly to the results of the first query without having to use temporary arrays.

In that foreach loop you semantically have only one item with multiple names. I think that's not what you're trying to do, so you should instead write it something like this:

    foreach ($data['rowItem'] as $Item) {
       $temp_categories['item'][] = array('name' => $Item->name);
    }

Upvotes: 2

Related Questions