assignment to make some page where only people who log in can leave a message

[ginerjm]

So to avoid confusion  Why not call them user_id and news_id and in the comment table call it comment_id to identify each comment belonging to that user_id?

Trying to make sense out of you columns here.

Edited April 30, 2022 by ginerjm

[aleX2]

ok, I will change that now....

[aleX2]

I made a change to id colums

[ginerjm]

Write a little script to make a connection to your database and run this script for each of your tables so we can see what you/we are dealing with.

//  set a table name to be shown
$tbl_name = '25_man_pool_names';

//  run an sql command to dump the structure
$q = "describe $tbl_name";
$qrslts = $pdo->query($q);

//  display the results
echo "<table border=1>";
echo "<caption>Table: $tbl_name</caption>";
while($row=$qrslts->fetch(PDO::FETCH_NUM))
	echo "<tr><td>{$row[0]}</td><td>{$row[1]}</tr>";
echo "</table>";

Cut and paste the results here using the <> tags of course

[aleX2]

Table: comment

comment_id int(11)

user_id int(11)

news_id int(11)

name varchar(50)

comment text

time timestamp

 

Table: users

user_id int(11)

name varchar(100)

email varchar(50)

username varchar(50)

password varchar(300)

status enum('admin','user')

 

Table: news

news_id int(11)

title varchar(200)

text text

category varchar(20)

time timestamp

author varchar(50)

 

 

[ginerjm]

What is the diff between name and username?

And why not use varchar for the text fields?

Suggestion - do not use values that could be reserved words such as date or time or name as column names.  Avoid.

And I also suggest using datetime rather than timestamp.

[aleX2]

Diff between name(is for first and lastname) and username{nickname).

For text fields they told us to put text and not varchar.So we don't have to worry about word limit.

Thanks for suggestion, it is a good one. In school they told us to use timestamp. As I told you I am a begginer and I don't know a lots of things.

That is why I came here in first place.

[ginerjm]

Then why not CALL them such Lastname and Firstname and Nickname  instead of leaving up to us to solve it?  (and don't use the word 'name')

So when you make these last updates can we see a new dump of your table structures?  And then we can get back to solving your original problem(s) now that we know what you are talking about?

 

[aleX2]

It's done...

Table: users

user_id int(11)

firstname varchar(100)

email varchar(50)

password varchar(300)

status enum('admin','user')

 

Table: comment

comment_id int(11)

user_id int(11)

news_id int(11)

firstname varchar(50)

comment text

time timestamp

 

Table: news

news_id int(11)

title varchar(200)

text text

category varchar(20)

time timestamp

author varchar(50)

 

 

 

Edited May 1, 2022 by aleX2

[Barand]

"comment" table should not contain the user's name (that should only be in the "users" table) - you have the user_id therefore you know the name.

I'd go with

CREATE TABLE `news` (
  `news_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `item_text` text,
  `category` varchar(20) DEFAULT NULL,
  `item_update` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `author` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(300) DEFAULT NULL,
  `status` enum('admin','user') DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `comment` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `news_id` int(11) DEFAULT NULL,
  `comment` text,
  `comment_update` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`comment_id`),
  KEY `idx_comment_user_id` (`user_id`),
  KEY `idx_comment_news_id` (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

[aleX2]

Thank you for suggestions how to create tables... I will use that in the future.