Mysql from two tables into 1 array

Question

MySQL DB

Product

id        name               
1         Product #1            
2         Product #2                
3         Product #3            
4         Product #4

Review

id        idUser    idProduct  Rating
1         1         1          A Long Boring Review that is up to 500 characters
2         1         2          A Long Boring Review that is up to 500 characters   
3         2         4          A Long Boring Review that is up to 500 characters
4         1         1          A Long Boring Review that is up to 500 characters

What would be the best way of pulling info from both these databases and arranging them as such:

[0] => stdClass Object
        (
            [id] => 1
            [name] => Product #1
            [reviews] => Array(
                [0]=>
                    (
                        [id] => "1"
                        [idUser] => "1"
                        [idProduct] => "1"
                        [Rating] => "A Long Boring Review that is up to 500 characters"
                    )
                [1] = >
                    (...
            )
        )
[1] => stdClass Object
        (
            [id] => 2
            [name] => Product #2
            [reviews] => Array(
                [0]=>
                    (
                        [id] => "1"
                        [idUser] => "1"
                        [idProduct] => "2"
                        [Rating] => "A Long Boring Review that is up to 500 characters"
                    )
                [1] = >
                    (...
            )
        )

I was thinking about using GROUP_CONCAT but wont that cause a lot of performance issues later on? Also doesn't that have a character limit?

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

You cannot get the desired data without first iterating over the result of Product, as it is a One to Many relationship

See this Answer Displaying data from two tables with many-to-many relation using PHP/CodeIgniter

It is for codeigniter, but you can get the idea

edited May 23 '17 at 12:04

Community

1
1

answered Apr 12 '12 at 04:35

Broncha

3,794
1
24
34

Isn't it bad to run a query in a loop though? – Jarritos Apr 12 '12 at 04:40
For your requirement, there is no other option. Not possible in One query as there are many rows for each row in Product – Broncha Apr 12 '12 at 05:05
What if I did a different format would that allow for doing it without a loop? – Jarritos Apr 12 '12 at 09:55
No, whatever the structure is.. iterating is the best approach.. You could do lazy loading (using some ORM's) but to get a multi dimensional result like you require.. You have to iterate. – Broncha Apr 12 '12 at 10:00

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

This will give you a one dimensional result if there is only one review:

SELECT `id`,`name`,(SELECT `Review`.`Rating` FROM `Review` WHERE `Review`.`idProduct` = `id`) as `rating` FROM `Product` WHERE 1;

For multiple reviews you'll need to loop through and build the structure for each product.

For another approach, see the answer for this question: How to create relationships in MySQL

edited May 23 '17 at 11:49

Community

1
1

answered Apr 12 '12 at 04:45

Resist Design

4,462
3
22
35

For the other approach aren't I already doing that via idProduct? THat is the foreign key. Also there are multiple results for rating. It seems to me that looping it would be a bad thing. – Jarritos Apr 12 '12 at 09:54
I'm sorry, I thought you might be using MyISAM and just making your own foreign key rather than leveraging the abilities that come with InnoDB's built-in foreign key support. – Resist Design Apr 12 '12 at 15:44

Mysql from two tables into 1 array

2 Answers2