Member Avatar for djcritch

heres the deal, i have created a table called tvprograms on a mySQL database and have added the fields id, program and comments and have populated the database.

The first thing i need to do is list just the programs in a php form but not the comments.

I then need to allow the user to select one of these programs and then display programs comments.

And finaly i need to allow the user to add a new program and comment to the database.

Can anyone help me with this as my coding is pretty poor!

Thanks guys

Merry Christmas!

  • 3 Contributors
  • 2 Replies
  • 118 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by jyshr

Dani AI

Generated

A normalized two-table design is the simplest, most future-proof solution for this thread. As pointed out, keep program metadata in one table and store each comment as its own row in a second table that references the program id. If existing data currently places a single comment column inside the program row, migrate those values into the new comments table so multiple comments can be supported later.

Typical workflow: list only program titles (select only id + title) and render each as a link that carries a program id (validate/cast the id before use). On the program detail page, load the program row and then load all comments that match the program id. For adding data, handle creation via POST: insert the program, get its inserted id, then insert the comment row (wrap both in a transaction so both inserts succeed or both roll back). ’s idea of looping through query results to make links is correct in concept; modern code should use prepared statements and escape output when printing HTML.

A concise PDO example (replace credentials and table/column names as needed):

$pdo = new PDO('mysql:host=localhost;dbname=db;charset=utf8mb4','user','pass',[
  PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION
]);

// fetch list
$stmt = $pdo->query('SELECT id,title FROM programs ORDER BY id DESC');
$list = $stmt->fetchAll(PDO::FETCH_ASSOC);

// fetch comments
$show = $pdo->prepare('SELECT author,body,created_at FROM comments WHERE program_id = ?');
$show->execute([ (int)$id ]);
$comments = $show->fetchAll(PDO::FETCH_ASSOC);

// insert program + comment atomically
$pdo->beginTransaction();
// prepare/execute inserts...
$pdo->commit();

Security and ops notes: avoid the old mysql_* API (removed in modern PHP); use PDO or mysqli with prepared statements to prevent SQL injection; always escape output (htmlspecialchars), use utf8mb4 charset, enable InnoDB + foreign keys for integrity, and enable exception-based error reporting during development. For migration or exact SQL patterns, apply backups and test on a copy first.

Recommended Answers

All 2 Replies

Member Avatar for FireNet

If you need multiple comments, use 2 tables. One for the program details, and one for the comments. Use an id tag (preferably the unique key from the program details)

CREATE TABLE tvprograms (
p_id int(11) NOT NULL UNIQUE AUTO INCREMENT,
p_name char(40),
p_desc tinytext,
p_misc tinytext
);

CREATE tvprograms_comment(
pc_id int(11) NOT NULL UNIQUE AUTO INCREMENT,
p_id int(11) NOT NULL,
pc_text text
);

-- SQL QUERY MAY CONTAIN ERRORS

Use simple select queries to list the programs. Then use the p_id value to list single program and the comments for the program.

SELECT * FROM tvprograms ORDER BY p_id DESC LIMIT 5; -- to list last 5 programs

SELECT * FROM tvprograms WHERE p_id=' $X '; --- to select one program

SELECT * FROM tvprograms_comment WHERE p_id=' $X '; -- to select all the comments for a single program.

I hope you get the idea how the 2 tables are being used to do what you need. One stores all the program details and the second stores all the comments, with each comment being tagged with an id number from the program details table.

Member Avatar for jyshr

to get the programs
you can try this
$aq = " select * from tvprograms";
$sh = mysql_query($aq);

$n = mysql_numrows($sh);
for($i = 0; $i < $n; $i++)
{$pro = mysql_result($sh,$i,"fieldname");
//here give link
}

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.