Chapter 6, Page 192

[Libby]

Hi Guys,

 

I'm having some difficulty with the SQL to populate the messages table in Chapter 6, page 192.

The SQL is included below, together with a screenshot of the resulting table.

 

There are two questions relating to the records where the subject is "Database Design"

1) 

- message_id 2 by user 2 asking for help "I'm creating a new database..."

- message_id 3 is a reply to the question "The number of tables in your database..." and has the parent_id 2 to indicate that. However, should the user_id here be 3, otherwise the original poster appears to be answering themselves?

 

2)

The "Okay thanks" message is a reply to the reply, so should that have a parent_id of 3, or should it be linked to the overall parent message, message_id 2?  The parent_id value 0 in the book doesn't seem right as this would suggest a new message (?)

 

Many thanks

Libby

SELECT * FROM forums;
SELECT user_id, username FROM users;

INSERT INTO messages (parent_id, forum_id, user_id, subject, body, date_entered) VALUES

(0, 1, 1, 'Question about normalization.', 'I''m confused about normalization. For the second normal form (2NF), I read...', UTC_TIMESTAMP()),

(0, 1, 2, 'Database Design', 'I''m creating a new database and am having problems with the structure. How many tables should I have?...', UTC_TIMESTAMP()),

(2, 1, 2, 'Database Design', 'The number of tables your database includes...', UTC_TIMESTAMP()),

(0, 1, 3, 'Database Design', 'Okay, thanks!', UTC_TIMESTAMP()),
(0, 2, 3, 'PHP Errors', 'I''m using the scripts from Chapter 3 and I can''t get the first calculator example to work. When I submit the form...', UTC_TIMESTAMP());

[Antonio Conte]

You problem is that you are not escaping quotes. The sentence in bold I''m confused about normalization uses an apostrophe of the same type as your string declaration. You can't mix and max apostrophes of the same type in strings. "I'm confuced" and 'I "Love" spinach' will work because, while 'I'm confused' and "I "Love" spinach" will break.

 

You need to escape you input using mysqli_real_escape_string() or by using prepared statements. (Which is recommended. An hour spent googling is worth it)

 

Also, you'd have problems using these strings in PHP too. Keep that in mind.