Jump to content

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

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

Link to comment
Share on other sites

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

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
Link to comment
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.

Link to comment
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
Link to comment
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 is ok!

Edited by mahenda
Link to comment
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

Link to comment
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

Link to comment
Share on other sites

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
Link to comment
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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.