Jump to content
mactron

Multiple returns from a fetch

Recommended Posts

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

 

Share this post


Link to post
Share on other sites

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;

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
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.)

  • Thanks 1

Share this post


Link to post
Share on other sites

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!!

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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
                        )

                )

        )
*/ 

 

  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.