Jump to content
Larry Ullman's Book Forums

I'm Having Trouble With Sorting Query Results In Chapter 5... Plz Help!


Recommended Posts

Hello everyone,

 

This is what I'm having trouble with in Chapter 5 on the part 'Sorting Query Results' on page 146:

 

Show all of the non-Simpson users by
date registered C:
SELECT * FROM users
WHERE last_name != 'Simpson'
ORDER BY registration_date DESC;

This is how it turned out when I followed the example:

 

image.png

 

I don't understand why it's not turning out like the example in the book

& why the registration date turned out like this!

 

I followed the authors examples & downloaded the scripts that go along with the book.

 

I copied & pasted this -from the books scripts phpmysql4_scripts\sql.sql-

into the SQL form on phpMyAdmin:

CREATE DATABASE sitename;

USE sitename;

CREATE TABLE users (
user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(40) NOT NULL,
email VARCHAR(60) NOT NULL,
pass CHAR(40) NOT NULL,
registration_date DATETIME NOT NULL,
PRIMARY KEY (user_id)
);

INSERT INTO users 
(first_name, last_name, email, pass, registration_date) 
VALUES ('Larry', 'Ullman', 'email@example.com', SHA1('mypass'), NOW());

INSERT INTO users VALUES 
(NULL, 'Zoe', 'Isabella', 'email2@example.com', SHA1('mojito'), NOW());

INSERT INTO users (first_name, last_name, email, pass, registration_date) VALUES
('John', 'Lennon', 'john@beatles.com', SHA1('Happin3ss'), NOW()),
('Paul', 'McCartney', 'paul@beatles.com', SHA1('letITbe'), NOW()),
('George', 'Harrison', 'george@beatles.com ', SHA1('something'), NOW()),
('Ringo', 'Starr', 'ringo@beatles.com', SHA1('thisboy'), NOW());

INSERT INTO users (first_name, last_name, email, pass, registration_date) VALUES
('David', 'Jones', 'davey@monkees.com', SHA1('fasfd'), NOW()),
('Peter', 'Tork', 'peter@monkees.com', SHA1('warw'), NOW()),
('Micky', 'Dolenz', 'micky@monkees.com ', SHA1('afsa'), NOW()),
('Mike', 'Nesmith', 'mike@monkees.com', SHA1('abdfadf'), NOW()),
('David', 'Sedaris', 'david@authors.com', SHA1('adfwrq'), NOW()),
('Nick', 'Hornby', 'nick@authors.com', SHA1('jk78'), NOW()),
('Melissa', 'Bank', 'melissa@authors.com', SHA1('jhk,h'), NOW()),
('Toni', 'Morrison', 'toni@authors.com', SHA1('hdhd'), NOW()),
('Jonathan', 'Franzen', 'jonathan@authors.com', SHA1('64654'), NOW()),
('Don', 'DeLillo', 'don@authors.com', SHA1('asf8'), NOW()),
('Graham', 'Greene', 'graham@authors.com', SHA1('5684eq'), NOW()),
('Michael', 'Chabon', 'michael@authors.com', SHA1('srw6'), NOW()),
('Richard', 'Brautigan', 'richard@authors.com', SHA1('zfs654'), NOW()),
('Russell', 'Banks', 'russell@authors.com', SHA1('wwr321'), NOW()),
('Homer', 'Simpson', 'homer@simpson.com', SHA1('5srw651'), NOW()),
('Marge', 'Simpson', 'marge@simpson.com', SHA1('ljsa'), NOW()),
('Bart', 'Simpson', 'bart@simpson.com', SHA1('pwqojz'), NOW()),
('Lisa', 'Simpson', 'lisa@simpson.com', SHA1('uh6'), NOW()),
('Maggie', 'Simpson', 'maggie@simpson.com', SHA1('plda664'), NOW()),
('Abe', 'Simpson', 'abe@simpson.com', SHA1('qopkrokr65'), NOW());

I don't understand why this is turning out wrong!

 

I got the example -on page 145- to work:

 

To sort data:
 
1. Select all of the users in alphabetical
order by last name A:
 
SELECT first_name, last_name FROM
users ORDER BY last_name;

This is what mine turned out like A:

 

145_A_Mine.png

 

I also got the second example on page 145 to work:

 

2. Display all of the users in alphabetical
order by last name and then first name B:
 
SELECT first_name, last_name FROM
users ORDER BY last_name ASC,
first_name ASC;

This is how mine turned out B:

 

145_B_Mine.png

 

I don't understand. I'm confused. I tried both of the examples on page 145 & I got them to work.

But the example on page 146 DOES NOT...

 

 

Thanks in advance for any help, ---It will be much appreciated---

 

-- Devin

Link to comment
Share on other sites

Hello,

 

What is "wrong", according to you?

 

The registration date is the same for all users because you entered all of them at one go into the database, and therefore the timestamp corresponding to NOW() is the same for everyone. Because of that, ordering the results by registration_date DESC has no real meaning.

 

I hope this helps,

 

Emilie

  • Upvote 3
Link to comment
Share on other sites

Hello,

 

What is "wrong", according to you?

 

The registration date is the same for all users because you entered all of them at one go into the database, and therefore the timestamp corresponding to NOW() is the same for everyone. Because of that, ordering the results by registration_date DESC has no real meaning.

 

I hope this helps,

 

Emilie

Hi Emilie,

 

I was just judging by the examples in the book & thought what I did was supposed to match the examples exactly.

I was thinking that if the registration_date wasn't the same as the author's in the book, then I might run into problems later on in the book... So, do I need to edit the registration_date in phpMyAdmin to make the registration dates the same as the authors? I just thought I might run into issues if the registration dates didn't match the authors exactly. &, by the way, thanks for the quick reply & for explaining this!

Link to comment
Share on other sites

Hello,

 

I think you can leave them as they are for now. If there are exercises where you need the registration date, you can change the registration date for some of them then, or add users so as to have a diversity of registration dates.

 

Emilie

  • Upvote 2
Link to comment
Share on other sites

Hello,

 

I think you can leave them as they are for now. If there are exercises where you need the registration date, you can change the registration date for some of them then, or add users so as to have a diversity of registration dates.

 

Emilie

Thank you Emilie! I appreciate all of the help! & I also voted your answer up for answering what I needed to know & the quick reply...  :)

Link to comment
Share on other sites

 Share

×
×
  • Create New...