I am using codeigniter and I am trying to call 3 different tables and join them together into one array. This is what I have:
$this->db->select('so_alcohol.id,
so_alcohol.name as name,
so_alcohol.category as category,
so_alcohol.permName as permName,
so_alcohol.picture as alcoholPicture,
group_concat(so_user.firstName) as firstName,
group_concat(so_user.lastName) as lastName,
group_concat(so_user.picture) as userPicture,
group_concat(so_rating.idUser) as idUser,
group_concat(so_rating.overall) as overall,
group_concat(so_rating.description) as description');
$this->db->from('alcohol');
$this->db->order_by("id", "desc");
$this->db->join('so_rating', 'so_rating.idAlcohol = so_alcohol.id', 'LEFT');
$this->db->join('so_user', 'so_user.id = so_rating.idUser', 'LEFT');
$query = $this->db->get();
The output I would like is the following:
Array
(
[0] => stdClass Object
(
[id] => 1
[name] => Product Name
[category] => Product category
[permName] => Product-Name
[alcoholPicture] => http://image.com/1.jpg
[idUser] => 1,2,3
[overall] => 100,80,50
[description] => Review 1,Awesome review 2, Horrible review 3
[firstName] => name 1, name 2, name 3
[lastName] => last 1, last 2, last 3
[userPicture] => http://userimage.com/1.jpg, http://userimage.com/2.jpg, http://userimage.com/3.jpg
)
[0] => stdClass Object
(
[id] => 1
[name] => Product Name
[category] => Product category
[permName] => Product-Name
[alcoholPicture] => http://image.com/1.jpg
[idUser] => 1,2,3
[overall] => 100,80,50
[description] => Review 1,Awesome review 2, Horrible review 3
[firstName] => name 1, name 2, name 3
[lastName] => last 1, last 2, last 3
[userPicture] => http://userimage.com/1.jpg, http://userimage.com/2.jpg, http://userimage.com/3.jpg
)
)
My question is how do I make what I have work, right now there are two problems (that I have found) First it only outputs 1 result, I need it to output 40 and second I cant so_user.id = so_rating.idUser because I already group_concat(so_rating.idUser)Any Ideas? I am not even sure if this is the right way of doing it as I am still a php newbie.
EDIT:
Alcohol
id name category permName picture
1 Product #1 1 Product-1 http://someurl.com/1.jpg
2 Product #2 1 Product-2 http://someurl.com/2.jpg
3 Product #3 1 Product-2 http://someurl.com/3.jpg
4 Product #4 2 Product-2 http://someurl.com/4.jpg
Rating
id idUser idProduct overall description
1 1 2 100 Great Product Review 1
2 2 2 75 Great Product Review 2
3 1 3 100 Great Product Review 3
4 2 3 98 Great Product Review 4
User
id firstName lastName userPicture
1 first 1 last 1 http://someurlUserPicture.com/1.jpg
2 first 2 last 2 http://someurlUserPicture.com/2.jpg
3 first 3 last 3 http://someurlUserPicture.com/3.jpg
4 first 4 last 4 http://someurlUserPicture.com/4.jpg
can you provide some table schematics to work with? i also don't see any GROUP BY in your query. i assume you want to GROUP BY so_alcohol.id? if you want to group only by so_alcohol.id you will have to use an aggregate function on the remaining columns like max(so_alcohol.name) as name. further reading about max()
your second problem is really none. you can join on any available column even if you don't decide to have it in the result. example:
SELECT
MAX(customer.email) AS email,
MAX(customer.name) AS name,
GROUP_CONCAT(address.country) AS countries
FROM
customer
JOIN
/* customer.address_id is not in the SELECT-clause,
but you can still use the column to join */
address ON customer.address_id = address.id
GROUP BY
customer.id
if you are thinking about splitting the value of firstName into single names afterwards, don't. have mysql produce one user per row and do the grouping in PHP while outputting.
Edit1: Example on GROUP BY
TABLE product
id name
1 Product1
2 Product2
TABLE rating
rating_id product_id
1 1
2 1
3 2
Join
SELECT *
FROM product
JOIN rating ON product.id = rating.product_id
outputs
id name rating_id product_id
1 Product1 1 1
1 Product1 2 1
2 Product2 3 2
adding a GROUP BY id-clause
SELECT
id,
MAX(name) AS name,
COUNT(rating_id) AS rating_count
FROM product
JOIN rating ON product.id = rating.product_id
GROUP BY id
will put all tuples that have the same value in the column id into one group:
id name rating_count
1 Product1 2
2 Product2 1
you are not allowed to select columns in your result, which you do not group by or use aggregate functions on. but mysql will let this slip and provide you with a reasonable result anyway.
hope that mini-tutorial works. if not i suggest to get to know SQL better before you continue to use codeigniter. if you don't understand what codeigniter does behind the curtains, you will not be able to control your output.