Jump to content
Larry Ullman's Book Forums

Recommended Posts

I added all the code from the chapter until I get the problem.. 

 

 

I made the code red where i get the problem..

 

the book says if I get a null result the names of my timezones are wrong or mysql hasnt loaded timezones.

 

 

 

 

CREATE DATABASE forum CHARACTER SET utf8 COLLATE utf8_general_ci;

 

USE forum;

 

 

CREATE TABLE forums (

forum_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,

name VARCHAR(60) NOT NULL,

PRIMARY KEY (forum_id),

UNIQUE (name)

) ENGINE = INNODB;

 

 

CREATE TABLE messages (

message_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

parent_id INT UNSIGNED NOT NULL DEFAULT 0,

forum_id TINYINT UNSIGNED NOT NULL,

user_id MEDIUMINT UNSIGNED NOT NULL,

subject VARCHAR(100) NOT NULL,

body LONGTEXT NOT NULL,

date_entered DATETIME NOT NULL,

PRIMARY KEY (message_id),

INDEX (parent_id),

INDEX (forum_id),

INDEX (user_id),

INDEX (date_entered)

) ENGINE = MYISAM;

 

 

CREATE TABLE users (

user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,

username VARCHAR(30) NOT NULL,

pass CHAR(40) NOT NULL,

first_name VARCHAR(20) NOT NULL,

last_name VARCHAR(40) NOT NULL,

email VARCHAR(60) NOT NULL,

PRIMARY KEY (user_id),

UNIQUE (username),

UNIQUE (email),

INDEX login (pass, email)

) ENGINE = INNODB;

 

 

CHARSET utf8;

 

 

INSERT INTO forums (name) VALUES 

('MySQL'), ('PHP'), ('Sports'), 

('HTML'), ('CSS'), ('Kindling'); 

 

INSERT INTO forums (name) VALUES ('Modern Dance');

 

 

INSERT INTO users (username, pass, first_name, last_name, email) VALUES 

('troutster', SHA1('mypass'), 'Larry', 'Ullman', 'lu@example.com'),

 

('funny man', SHA1('monkey'), 'David', 'Brent', 'db@example.com'),

 

('Gareth', SHA1('asstmgr'), 'Gareth', 'Keenan', 'gk@example.com');

 

INSERT INTO users (username, pass, first_name, last_name, email) VALUES 

('tim', SHA1( 'psych' ) , 'Tim', 'Canterbury', 'tc@example.com'),

('finchy', SHA1('jerk'), 'Chris', 'Finch', 'cf@example.com');

 

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());

 

SELECT message_id, subject, date_entered FROM messages ORDER BY date_entered DESC LIMIT 1;

 

SELECT message_id, subject, CONVERT_TZ(date_entered, 'UTC', 'America/New_York') AS local

FROM messages ORDER BY date_entered DESC LIMIT 1;

Link to comment
Share on other sites

mysql> SELECT message_id, subject, CONVERT_TZ(date_entered, 'UTC', 'America/New_York') AS local

    -> FROM messages ORDER BY date_entered DESC LIMIT 1;

+---------------+---------------+-------+

| message_id |     subject    | local |

+---------------+---------------+-------+

|                  5 | PHP Errors | NULL |

+---------------+--------------+--------+

1 row in set (0.00 sec)

Link to comment
Share on other sites

Very good. That's a lot more to work with. The problem seems to be missing DateTimeZones in your database. Here's a thread describing the very same problem. Here's also some tips on how to install timezones yourself.

 

That said, doing this using mysql is not the only available method. If you are using timezones for other purposes than learning, you might instead consider doing this using PHP instead. After all, a user will often be able to set his own timezone, so using something like DateTime instead might make sense.

// Some logic for getting user's timezone. Could be stored in a cookie/similar. 
$timezone = 'America/New_York';

// A slightly altered query
$query = "SELECT message_id, subject, date_entered, messages ORDER BY date_entered DESC LIMIT 1";

// Run query
$result = mysqli_query($dbc, $query);

// Get result
$data = mysqli_fetch_array($result, MYSQLI_ASSOC);
try {
   $date = new DateTime($data['date_entered'], new DateTimeZone($timezone));
   echo $date->format('Y-m-d H:i:s'); // TimeZoned dateTime.
}
catch ( Exception $e )
{
   echo $e->getMessage(); // An error occured.
}

Hope that either solves your problem or gives you some idea of creating an alternative version.

Edited by Antonio Conte
  • Upvote 1
Link to comment
Share on other sites

  • 4 weeks later...
 Share

×
×
  • Create New...