how to retrive userName,comment, and createdAt from users and comments table while I have used userId as a Foreign key on the comment table

[mahenda]

CREATE TABLE posts (
postId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,	
title VARCHAR(255) NOT NULL,
author VARCHAR(24) NOT NULL,	
description TEXT NOT NULL,       
createdAt TIMESTAMP,
PRIMARY KEY (postId)    
);
CREATE TABLE comments(
commentId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,    
comment  TEXT NOT NULL,
postId INT(11),
userId INT(11),
createdAt TIMESTAMP,	
PRIMARY KEY (commentId),
FOREIGN KEY (userId) REFERENCES users(userId),
FOREIGN KEY (postId) REFERENCES posts(postId)   
);
CREATE TABLE replies (
repId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,   
reply  TEXT NOT NULL,
userId INT(11),
commentId INT(11), 	
createdAt TIMESTAMP,     
PRIMARY KEY (repId),
FOREIGN KEY (userId) REFERENCES users(userId),
FOREIGN KEY (commentId) REFERENCES comments(commentId)     
);
CREATE TABLE users (
userId INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
userName VARCHAR(100) NOT NULL,,	          
email VARCHAR(100) NOT NULL,
PRIMARY KEY (userId)
);

how to retrive userName,comment, and createdAt from users and comments table while I have used userId as a  Foreign key on the comment table

if it isn't correct, correct me please

[ginerjm]

With a 'select' statement perhaps?

 

Roughly - untested and with no regard for your capitlization:

$q = "select u.userName, c.comment, c.createdAt 
		from Users as u, Comments as c
		where u.userId = c.userId";

Have to ask - How are the Posts entries related to the rest of this db?  Is author related to users in any way?       Doesn't appear to be.  And the 'title' field seems rather large.

Edited June 5, 2019 by ginerjm

[mahenda]

2 hours ago, ginerjm said:

With a 'select' statement perhaps?

 

Roughly - untested and with no regard for your capitlization:

$q = "select u.userName, c.comment, c.createdAt 
		from Users as u, Comments as c
		where u.userId = c.userId";

Have to ask - How are the Posts entries related to the rest of this db?  Is author related to users in any way?       Doesn't appear to be.  And the 'title' field seems rather large.

only admin can post but user can comment and reply

Edited June 5, 2019 by mahenda

[mahenda]

only admin can post but user can comment and reply

Edited June 5, 2019 by mahenda

[Barand]

12 minutes ago, mahenda said:

#1054 - Unknown column 'c.comment' in 'field list'

If you are getting that error then the create statements you posted earlier must be incorrect. It says your "comments" table does not have a column "comment".

I created tables using your create code and ginerjm's query (although I don't agree with with his implicit join syntax) doesn't give an error.

[ginerjm]

Implicit join syntax???

[Barand]

FROM A,B WHERE A.xxx = B.xxx

Much better to use explicit joins ...

FROM A INNER JOIN B ON A.xxx = B.xxx

• The joins then reflect the structure of the data.
• The WHERE clause isn't cluttered with these structure details
• Implicit syntax can only be used for INNER JOINS so no code consistency with other join types
• Optimization seems improved now but there used to be a distinct speed advantage when using explicit joins in older versions of mysql

[mahenda]

1 hour ago, Barand said:

If you are getting that error then the create statements you posted earlier must be incorrect. It says your "comments" table does not have a column "comment".

I created tables using your create code and ginerjm's query (although I don't agree with with his implicit join syntax) doesn't give an error.

it is ok!

Edited June 5, 2019 by mahenda

[mahenda]

1 hour ago, Barand said:

If you are getting that error then the create statements you posted earlier must be incorrect. It says your "comments" table does not have a column "comment".

I created tables using your create code and ginerjm's query (although I don't agree with with his implicit join syntax) doesn't give an error.

it's ok it work

[ginerjm]

HTH!

[mahenda]

Now

i want to insert reply on replies table 

i.e INSERT INTO replies(reply,commentId,userId) VALUES ('reply here',Id of the current comment, id of the current user); 

i'm using $_SESSION['userId']  to get  current user  info.but how to get id of the current comment where the current user want to comment

 

 

any idea please

[ginerjm]

I'm assuming that the comment that is being replied to is currently being shown to the user as they type in their reply to it.  You should be showing the comment id on that screen, perhaps as a hidden field inside the form on the page so that when the user submits his reply the hendling script (the updater) gets not only the reply text they will also get the comment id that was hidden on the page. 

Edited June 6, 2019 by ginerjm

[mahenda]

14 hours ago, ginerjm said:

I'm assuming that the comment that is being replied to is currently being shown to the user as they type in their reply to it.  You should be showing the comment id on that screen, perhaps as a hidden field inside the form on the page so that when the user submits his reply the hendling script (the updater) gets not only the reply text they will also get the comment id that was hidden on the page. 

thanks