Jump to content
mahenda

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
Posted (edited)

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 by ginerjm

Share this post


Link to post
Share on other sites
Posted (edited)
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 by mahenda

Share this post


Link to post
Share on other sites
Posted (edited)

only admin can post but user can comment and reply

Edited by mahenda

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Implicit join syntax???

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Posted (edited)
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 by mahenda

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

HTH!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Posted (edited)

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 by ginerjm

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.