I am creating a blog using PHP and SQL, I am trying to query everything in 1 SQL query so I can bring out all of the comments with the authors and all of the blogs with the authors. Each of them have the user.uuid
as a foreign key.
My Blog Table looks like this:
CREATE TABLE `blogs` ( `uuid` int(255) NOT NULL, `title` varchar(1024) NOT NULL, `detail` varchar(1024) NOT NULL, `user_id` int(255) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
My Blog Comments Table looks like this:
CREATE TABLE `blogs_comments` ( `uuid` int(255) NOT NULL, `blog_uuid` int(255) NOT NULL, `user_uuid` int(255) NOT NULL, `comment` varchar(250) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
My User Table looks like this:
CREATE TABLE `users` ( `uuid` int(255) NOT NULL, `username` varchar(15) COLLATE utf8_unicode_ci NOT NULL, `password` varchar(64) COLLATE utf8_unicode_ci NOT NULL, `foreName` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `surName` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `email` text COLLATE utf8_unicode_ci NOT NULL, `number` int(11) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `is_admin` tinyint(1) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Here is my current SQL:
SELECT b.uuid AS 'Blog ID', b.title AS 'Blog Title', b.detail AS 'Blog Body', u.uuid AS 'Blog Author ID', u.username AS 'Blog Author Username', (SELECT bc.comment WHERE u.uuid = bc.user_uuid) AS 'Comment', (SELECT u.uuid WHERE u.uuid = bc.user_uuid) AS 'Comment Author ID', (SELECT u.username WHERE u.uuid = bc.user_uuid) AS 'Comment Author Username' FROM blogs b INNER JOIN blogs_comments bc ON b.uuid = bc.blog_uuid INNER JOIN users u ON b.user_id = u.uuid
The query is working fine, however, it has an unexpected output and looks like this:
I would like the output to have all of the comments for the comment author in 1 row, then each row will be specific to the comment author. IE:
Comment Author Comment Comment Jaquarh TEST Lorem Ipsum
Advertisement
Answer
You’ll need to know how many comments you’ll have every time, but if you do, a pivot may give you what you need. I’m a little confused about your query structure, but I think it may look something like this with a pivot:
SELECT blog_id AS 'Blog ID', title AS 'Blog Title', detail AS 'Blog Body', blog_author_id AS 'Blog Author ID', username AS 'Blog Author Username', Comment_Author_ID AS 'Comment Author ID', Comment_Author_Username AS 'Comment Author Username', [0] AS 'Comment 1', [1] AS 'Comment 2', [2] AS 'Comment 3', [3] AS 'Comment 4', [4] AS 'Comment 5', [5] AS 'Comment 6' FROM (SELECT --A1 b.uuid as blog_id, b.title, b.detail, u.uuid as blog_author_id, u.username, (SELECT bc.comment WHERE u.uuid = bc.user_uuid) AS Comment, (SELECT u.uuid WHERE u.uuid = bc.user_uuid) AS Comment_Author_ID, (SELECT u.username WHERE u.uuid = bc.user_uuid) AS Comment_Author_Username, row_number() over(partition by b.uuid order by bc.comment) AS row_num FROM blogs b INNER JOIN blogs_comments bc ON b.uuid = bc.blog_uuid INNER JOIN users u ON b.user_id = u.uuid AND u.uuid = bc.user_uuid ) as A1 PIVOT (max([comment]) FOR row_num in ([0], [1], [2], [3], [4], [5])) AS pvt
This article might help you too: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017