MySQL (PDO) - SQL Results

[Wstar]

I had a question about the SELECT statement. I was wondering if it can return a multidimensional array for a row. For example

• First Name

• Last Name

• Email

• Username

• PostID

  • Post ID I

  • Post ID 2

  • Post ID 3

The row would return a list of all the post ids (from a blog). This example is something I madeup to keep it simple. I have a very involved forum and newsfeed I created. I was just trying to find a way to minimize the database connections and sql statements being sent. normally I would do this in two SELECT statements. One to get the user details and the other would grab the blog id's which they created.

SELECT first, last, email, user FROM members WHERE id = 2 LIMIT 1
SELECT id FROM blog WHERE createdBy = 2

I hope this makes sense, if not, I can explain this in other ways. Thanks for any suggestions.

[NogDog]

Maybe something like this?

SELECT 
  m.first,
  m.last,
  m.email,
  CONCAT_WS(',', b.id) AS blog_ids
FROM members m
LEFT JOIN blog b ON m.id = b.createdBy
WHERE m.id = 2
GROUP BY m.id
LIMIT 1

[Wstar]

Thanks for the quick reply. But the blog_ids field is empty or returns only one blog id. Here is the test im working with.

Table user
id------first------last-------email
1------Jerrod----Davenport--123
2------Nick------Musick------456

Table blog
id--------user_id--------content
1--------1--------------Blog 1
2--------1--------------Blog 2

The SQL
SELECT
u.first,
u.last,
u.email,
CONCAT_WS(',', b.id) AS blog_ids
FROM user u
LEFT JOIN blog b ON u.id = b.user_id
WHERE u.id = 1
GROUP BY b.id
LIMIT 1

Results
First, Last, Email, Blog ID
Jerrod, Davenport, 123, 1

Am I missing anything?

[NogDog]

Maybe the LIMIT 1 is resulting in only one match being processed?

If it's necessary to have that LIMIT, then you may have to put the concat into a sub-query, something like:

SELECT 
  m.first,
  m.last,
  m.email,
  (
    SELECT CONCAT_WS(',', b.id)
    FROM blog b
    WHERE b.createdBy = m.idAS
    GROUP BY b.createdBy
  ) AS blog_ids
FROM members m
WHERE m.id = 2
LIMIT 1

However, assuming members.id is unique, the LIMIT 1 would seem redundant?