Multiple returns from a fetch

[mactron]

Me again.. I'm fetching data from MySQL but each person in people.php is displayed two times instead one.. The issue must be inside models_category_tbl table, because each person have more then one result. Any advice is appreciated. What should I do?

I would like to display people something like that:

1 John Doe 1 IT developer, 3 mechanic

models/model = person

category = occupation/skills

MySQL:

model_index table
+---------+------------+-------------+-------------------+---------------+----------------+
|model_id | model_name | model_title | model_description | model_country | model_slug	  |
+---------+------------+-------------+-------------------+---------------+----------------+
| 1 	  | John Doe   | Johns title | Johns desc        | Hungary       | john-doe 	  |	
+---------+------------+-------------+-------------------+---------------+----------------+

models_category_tbl table
+-------------+---------------------+---------------------+
| category_id | model_category_name | model_category_slug |
+-------------+---------------------+---------------------+
| 1           | it developer        | it-developer        |
| 2           | photographer	    | photographer        |
| 3           | mechanic            | mechanic            |
+-------------+---------------------+---------------------+

models_category_ids table
+----+----------+-------------+
| id | model_id | category_id |
| 1  | 1        | 1           | 
| 1  | 1        | 3           |
+----+----------+-------------+

 

PHP function to fecth data:

        public function getAllModels1 () {

        $sql = "SELECT * FROM models_index  JOIN models_category_ids ON models_index.model_id=models_category_ids.model_id JOIN models_category_tbl ON  models_category_tbl.category_id=models_category_ids.category_id" ;
            $stmt = $this->conn->prepare($sql);
            $stmt->execute();
            $result = $stmt->fetchAll((PDO::FETCH_OBJ));
            
            return $result;            
    }

 

people.php

<?php 
declare(strict_types=1);
ob_start();
include "includes/connect.php";
include "includes/functions.php";
 

   	$model = new ModelsData();
	$models = $model->getAllModels1();


	foreach ($models as $model) { ?> 


	<a href="model.php?id=<?= $model->model_id ?>"><img src="<?= $model->model_img_path ?><?=$model->model_img_name ?>"></a>	 
 
	<?php echo $model->model_id . " " .  $model->model_name . " " . $model->category_id .  " " . $model->model_category_name  ; ?>
	

	<h3><?= $model->model_name ?></h3>

 
<?php }  ?>

 

Output:

 1 John Doe 1 IT developer 
 1 John Doe 3 mechanic

 

[mactron]

Should I write another query for categories?

[Barand]

One way is with GROUP_CONCAT()

SELECT i.model_name
     , GROUP_CONCAT(c.category_id,' ',c.model_category_name SEPARATOR ', ') as cats
FROM model_index i 
     JOIN
     models_category_ids USING (model_id)
     JOIN
     models_category_tbl c USING (category_id)
GROUP BY i.model_id;

 

[mactron]

Now I'm getting error in PHP::

Notice: Undefined property: stdClass::$category_id in C:\laragon\www\sys\admin\people.php on line 19
Notice: Undefined property: stdClass::$model_category_name in C:\laragon\www\sys\admin\people.php on line 19

 

[Barand]

Which is line 19?

[mactron]

	echo  $model->category_id .  " " . $model->model_category_name  ; ?>

 

[Barand]

3 hours ago, Barand said:

SELECT i.model_name ,

          GROUP_CONCAT(c.category_id,' ',c.model_category_name SEPARATOR ', ') as cats

My query returns two output columns

• model_name
• cats

Try 

echo model->model_name . " " . $model->cats . "<br>";

(of course, without seeing the code that actually runs  the query and gets the results, that's just a guess.)

[mactron]

Works like a charm, but I can't set the link pointed to the each category based on category id.

Among GROUP_CONCAT(), what is another way to resolve the issue? I would be thankful to you if you could provide me some reference. Thanks!!

[Barand]

4 hours ago, mactron said:

<a href="model.php?id=<?= $model->model_id ?>"><img src="<?= $model->model_img_path ?><?=$model->model_img_name ?>"></a>

The link in your code is to the model_id

Add "i.model_id" to the selected columns in the query, then the output will have $model->model_id

If you need individual category_ids, revert your query to that getting several rows for each model and store the results in an array, indexed by model, so each model has an array of categories.

[mactron]

Yes I know, but I would like to link to categories also...

 

[Barand]

51 minutes ago, Barand said:

If you need individual category_ids, revert your query to that getting several rows for each model and store the results in an array, indexed by model, so each model has an array of categories.

E.G.

$res = $conn->query("SELECT i.model_id
                         , i.model_name
                         , c.category_id as cat_id
                         , c.model_category_name as cat_name
                    FROM model_index i 
                         JOIN
                         models_category_ids USING (model_id)
                         JOIN
                         models_category c USING (category_id)
                         ");
$data = [];
foreach ($res as $r) {
    if (!isset($data[$r['model_id']])) {
        $data[$r['model_id']] = [ 'name' => $r['model_name'],
                                  'cats' => []
                                ];
    }
    $data[$r['model_id']]['cats'][$r['cat_id']] = $r['cat_name'];
}
/*
$data = Array
        (
            [1] => Array
                (
                    [name] => John Doe
                    [cats] => Array
                        (
                            [1] => IT Devloper
                            [3] => Mechanic
                        )

                )

            [2] => Array
                (
                    [name] => Laura Norder
                    [cats] => Array
                        (
                            [2] => Photographer
                        )

                )

        )
*/