EDIT: OUTPUT CODE AT BOTTOM OF QUESTION
I just posted a question, thinking my problem was the query, but it turns out it's my PHP code.
Here's the problem. I have a GoalChallenge class, which has numerous properties, one of which should be one, or an array of ProductService objects; see GoalChallenge class below (note I have stripped out the other getters and setters, and left the ones that relate to the ProductService class.
When I use GoalChallenge::findByPersonaId, a ProductService object is created and relates to the matching GoalChallenge object, but there should be 2 ProductService objects within the GoalChallenge->product_service property (the query should match 2 rows). Instead, a duplicate GoalChallenge object is created, containing the same property values for everything other than the product_service property, which contains the 2nd matching object from the query.
I need the two matching ProductService objects to be part of the same GoalChallenge object (as matched by the query) - how can I make this happen?
If you need anything else, please ask. Really appreciate any help! Code below;
GoalChallenge.class.php
<?php
class GoalChallenge
{
private $id;
private $persona_id;
private $title;
private $item_category;
private $description;
private $solution;
private $product_service;
private $research_checklist;
private $subtopics;
private $keywords;
private $status;
public function __construct(
$id = null,
$persona_id = null,
$title = null,
$item_category = null,
$description = null,
$solution = null,
ProductService $product_service = null,
$research_checklist = null,
$subtopics = null,
$keywords = null,
$status = null
) {
$this->id = $id;
$this->persona_id = $persona_id;
$this->title = $title;
$this->item_category = $item_category;
$this->description = $description;
$this->solution = $solution;
$this->product_service = $product_service;
$this->research_checklist = $research_checklist;
$this->subtopics = $subtopics;
$this->keywords = $keywords;
$this->status = $status;
}
public function getProductService()
{
return $this->product_service;
}
public function setProductService(ProductService $product_service)
{
$this->product_service = $product_service;
}
}
And my GoalChallengeMapper.class.php;
class GoalChallengeMapper
{
protected $dblayer;
public function __construct(PDO $dblayer)
{
$this->dblayer = $dblayer;
}
public function saveField($id, $field, $data)
{
try {
$this->dblayer->beginTransaction();
$stmt = $this->dblayer->prepare("UPDATE goals_challenges SET $field = :data WHERE id = :id");
$stmt->bindParam(':id', $id);
$stmt->bindParam(':data', $data);
$stmt->execute();
$this->dblayer->commit();
return $stmt->rowCount();
} catch(PDOException $e) {
$this->dblayer->rollBack();
echo $e->getMessage();
exit;
}
}
public function findByPersonaId($persona_id)
{
try {
$this->dblayer->beginTransaction();
$stmt = $this->dblayer->prepare("SELECT goals_challenges.*, products_services.id as psid, products_services.url, products_services.feature_benefit from goals_challenges LEFT JOIN products_services ON goals_challenges.id = products_services.goal_challenge_id WHERE goals_challenges.persona_id = :persona_id");
$stmt->bindParam(':persona_id', $persona_id);
$stmt->execute();
$this->dblayer->commit();
$result_set = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$result_set[] = $this->mapObject($row);
}
return $result_set;
} catch (PDOException $e) {
$this->dblayer->rollBack();
echo $e->getMessage();
exit;
}
}
public function mapObject(array $row)
{
$entry = new GoalChallenge();
$entry->setId($row['id']);
$entry->setPersonaId($row['persona_id']);
$entry->setTitle($row['title']);
$entry->setItemCategory($row['item_category']);
$entry->setDescription($row['description']);
$entry->setSolution($row['solution']);
$entry->setProductService(new ProductService($row['psid'], $row['id'], $row['url'], explode(',', $row['feature_benefit'])));
$entry->SetResearchChecklist($row['research_checklist']);
$entry->setSubtopics($row['subtopics']);
$entry->setKeywords($row['keywords']);
$entry->setStatus($row['status']);
return $entry;
}
}
And finally, my ProductService class (minus getters and setters)
class ProductService
{
private $id;
private $goal_challenge_id;
private $url;
private $feature_benefit = [];
public function __construct($id = null, $goal_challenge_id = null, $url = null, array $feature_benefit = null)
{
$this->id = $id;
$this->goal_challenge_id = $goal_challenge_id;
$this->url = $url;
$this->feature_benefit = $feature_benefit;
}
}
THIS IS THE OUTPUT
GoalChallenge Object
(
[id:GoalChallenge:private] => 173
[persona_id:GoalChallenge:private] => 14
[title:GoalChallenge:private] => Lead Gen
[item_category:GoalChallenge:private] => Business Challenge
[description:GoalChallenge:private] =>
[solution:GoalChallenge:private] => Advertising
[product_service:GoalChallenge:private] => ProductService Object
(
[id:ProductService:private] => 1
[goal_challenge_id:ProductService:private] => 173
[url:ProductService:private] => www.google.com
[feature_benefit:ProductService:private] => Array
(
[0] => good for testing
[1] => mobile
)
)
[research_checklist:GoalChallenge:private] => 0,0,0,0,0,0
[subtopics:GoalChallenge:private] =>
[keywords:GoalChallenge:private] => ,,,,
[status:GoalChallenge:private] => 1
)
GoalChallenge Object
(
[id:GoalChallenge:private] => 173
[persona_id:GoalChallenge:private] => 14
[title:GoalChallenge:private] => Lead Gen
[item_category:GoalChallenge:private] => Business Challenge
[description:GoalChallenge:private] =>
[solution:GoalChallenge:private] => Advertising
[product_service:GoalChallenge:private] => ProductService Object
(
[id:ProductService:private] => 3
[goal_challenge_id:ProductService:private] => 173
[url:ProductService:private] => www.test.com
[feature_benefit:ProductService:private] => Array
(
[0] => good for searching
[1] => well known
)
)
[research_checklist:GoalChallenge:private] => 0,0,0,0,0,0
[subtopics:GoalChallenge:private] =>
[keywords:GoalChallenge:private] => ,,,,
[status:GoalChallenge:private] => 1
)
mysql> SELECT goals_challenges.*, products_services.id as psid, products_services.url, products_services.feature_benefit FROM goals_challenges LEFT JOIN products_services ON goals_challenges.id = products_services.goal_challenge_id WHERE goals_challenges.persona_id = 14;
+-----+------------+----------+--------------------+-------------+-------------+-----------------+--------------------+-----------+----------+--------+------+----------------+--------------------------------+
| id | persona_id | title | item_category | description | solution | product_service | research_checklist | subtopics | keywords | status | psid | url | feature_benefit |
+-----+------------+----------+--------------------+-------------+-------------+-----------------+--------------------+-----------+----------+--------+------+----------------+--------------------------------+
| 173 | 14 | Lead Gen | Business Challenge | | Advertising | NULL | 0,0,0,0,0,0 | NULL | ,,,, | 1 | 1 | www.google.com | good for testing, mobile |
| 173 | 14 | Lead Gen | Business Challenge | | Advertising | NULL | 0,0,0,0,0,0 | NULL | ,,,, | 1 | 3 | www.test.com | good for searching, well known |
+-----+------------+----------+--------------------+-------------+-------------+-----------------+--------------------+-----------+----------+--------+------+----------------+--------------------------------+
2 rows in set (0.00 sec)
print_r($goals_challenges)
Array
(
[173] => Array
(
[id] => 173
[persona_id] => 14
[title] => Lead Gen
[item_category] => Business Challenge
[description] =>
[solution] => Advertising
[research_checklist] => 0,0,0,0,0,0
[subtopics] =>
[keywords] => ,,,,
[status] => 1
[psid] => 1
[url] => www.google.com
[feature_benefit] => good for testing, mobile
[product_services] => Array
(
[0] => Array
(
[0] => 1
[1] => www.google.com
[2] => good for testing, mobile
)
[1] => Array
(
[0] => 3
[1] => www.test.com
[2] => good for searching, well known
)
)
)
)