Table join problem Chapter 12, p 436 PHP-Beg

tomasz · August 31st, 2003, 09:21 AM

I'm practicing the queries from Chapter 12 in Beginning PHP, and all was going well until I tried the table join at the bottom of page 436, namely:

SELECT user.userid, page FROM user, access_log
WHERE user.userid = access_log.userid;

I've entered this query exactly as it appears in the book but all I get is an empty result set returning.

Any ideas what's happening?

Thanks,

Tom

richard.york · August 31st, 2003, 12:08 PM

Hi Tom,
Try poking in this code just after your query to see what, if any, error mysql is returning. This is of course assuming that you are making the query from a PHP script.

$query = mysql_query("SELECT user.userid, page FROM user, access_log
WHERE user.userid = access_log.userid");

if (empty($query)) {

echo mysql_error().": ";
echo mysql_errno();

}

I'll dig by book out here in a minute and have a look at the query.

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

richard.york · August 31st, 2003, 12:56 PM

Hi Tom,
Honestly I don't see anything wrong with your query. I created a simular example below and was able to execute it just fine. I would double check your database set-up and if you do in fact not only have data in each database table, but also be sure that you have userids in each table that would allow the join to yield a match. Or you can just try my example below which is output from my website.. with things like IPs, emails, etc changed to protect my users! Its actually very simular to the example on pg. 436.

Code:

CREATE TABLE user_ids (
  user_id int(7) NOT NULL auto_increment,
  user_name varchar(20) NOT NULL default '',
  password varchar(20) NOT NULL default '',
  email varchar(250) NOT NULL default '',
  access_priviledges varchar(50) NOT NULL default 'general',
  lastaccesstime timestamp(14) NOT NULL,
  password_question text NOT NULL,
  password_answer text NOT NULL,
  email_confirmation varchar(50) NOT NULL default '',
  activated int(1) NOT NULL default '',
  UNIQUE KEY user_id (user_id),
  UNIQUE KEY user_name (user_name)
) TYPE=MyISAM;

INSERT INTO user_ids VALUES ('558', 'dude123', password('mypass'), '[email protected]', 'general', null, 'What is my pass?', 'I dunno', 'h3jh45m345', '1');
INSERT INTO user_ids VALUES ('157', 'dauser', password('dapass'), '[email protected]', 'general', null, 'What is da pass?', 'dapass', 'axdfk34r', '1');
INSERT INTO user_ids VALUES ('460', 'littlechic098', password('mom'), '[email protected]', 'general', null, 'My birthday?', '08/30/78', 'adf345ddf', '1');
INSERT INTO user_ids VALUES ('559', 'john_lennon', password('thebeatles'), '[email protected]', 'general', null, 'Paul and the boys?', 'Sgt. Peppers', 'adf3245', '1');
INSERT INTO user_ids VALUES ('304', 'billy', password('anotherpassword'), '[email protected]', 'general', null, 'Who said ha ha he thinks I look alike?', 'Chico Marx', 'adf234sd', '1');

CREATE TABLE access_log (
  page text NOT NULL,
  user_id varchar(25) NOT NULL default '',
  user_agent text NOT NULL,
  referer text NOT NULL,
  remote_address text NOT NULL,
  remote_host text NOT NULL,
  query_string text NOT NULL,
  visitcount int(11) NOT NULL default '0',
  lastaccesstime timestamp(14) NOT NULL
) TYPE=MyISAM;

INSERT INTO access_log VALUES ('writing/post_a_submission3', '558', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)', 'http://www.personaltruths.com/index.php', '127.0.0.1', 'adsl-66-159-205-155.dslextreme.com', '', 2, 20030722040329);
INSERT INTO access_log VALUES ('registered_users/index', '558', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)', 'http://www.personaltruths.com/index.php', '127.0.0.1', 'adsl-66-159-205-155.dslextreme.com', '', 4, 20030722041104);
INSERT INTO access_log VALUES ('welcome/services', '558', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)', 'http://www.personaltruths.com/index.php', '127.0.0.1', 'adsl-66-159-205-155.dslextreme.com', 'content=341&PHPSESSID=4f7b1ee4bfdeb1ddd5e2a8d882ed6998', 1, 20030722041132);
INSERT INTO access_log VALUES ('welcome/search_all', '558', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows 98)', 'http://www.personaltruths.com/index.php?content=341&PHPSESSID=4f7b1ee4bfdeb1ddd5e2a8d882ed6998', '127.0.0.1', 'adsl-66-159-205-155.dslextreme.com', 'search=contests&search_action=all&PHPSESSID=4f7b1ee4bfdeb1ddd5e2a8d882ed6998', 1, 20030722041204);
INSERT INTO access_log VALUES ('registered_users/index', '157', 'Mozilla/4.0 (compatible; MSIE 6.0; AOL 9.0; Windows NT 5.0)', 'http://www.smilingsouls.net/index.php', '127.0.0.1', 'cache-da08.proxy.aol.com', 'site=registered_users&logout=true&PHPSESSID=26aac8e4c6ecbc013fd3365ba8281fa7', 75, 20030819215535);
INSERT INTO access_log VALUES ('writing/index', '157', 'Mozilla/4.0 (compatible; MSIE 6.0; AOL 9.0; Windows NT 5.0)', 'http://www.smilingsouls.net/index.php?content=280&PHPSESSID=26aac8e4c6ecbc013fd3365ba8281fa7', '127.0.0.1', 'cache-dm02.proxy.aol.com', 'site=writing&PHPSESSID=26aac8e4c6ecbc013fd3365ba8281fa7', 39, 20030819215345);
INSERT INTO access_log VALUES ('writing/directory', '157', 'Mozilla/4.0 (compatible; MSIE 6.0; AOL 9.0; Windows NT 5.0)', 'http://www.smilingsouls.net/index.php?site=writing&PHPSESSID=26aac8e4c6ecbc013fd3365ba8281fa7', '127.0.0.1', 'cache-dq05.proxy.aol.com', 'content=263&category=fear&category_id=62&global_id=15&PHPSESSID=26aac8e4c6ecbc013fd3365ba8281fa7', 40, 20030819215403);
INSERT INTO access_log VALUES ('writing/post_a_submission', '157', 'Mozilla/4.0 (compatible; MSIE 6.0; AOL 9.0; Windows NT 5.0)', 'http://www.smilingsouls.net/index.php?content=265&category_id=62&do_action=post&PHPSESSID=26aac8e4c6ecbc013fd3365ba8281fa7', '127.0.0.1', 'cache-dq01.proxy.aol.com', '', 93, 20030819215458);
INSERT INTO access_log VALUES ('registered_users/index', '460', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; DVD Owner)', 'http://smilingsouls.net/', '127.0.0.1', 'roc-24-169-212-77.rochester.rr.com', 'site=registered_users&PHPSESSID=1163d5f196e321ca6374c66409ae67af', 2, 20030725163629);
INSERT INTO access_log VALUES ('registered_users/edit_submissions', '157', 'Mozilla/4.0 (compatible; MSIE 6.0; AOL 9.0; Windows NT 5.0)', 'http://www.smilingsouls.net/index.php', '127.0.0.1', 'cache-dk07.proxy.aol.com', 'content=280&PHPSESSID=26aac8e4c6ecbc013fd3365ba8281fa7', 11, 20030819215322);
INSERT INTO access_log VALUES ('registered_users/index', '559', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)', 'http://www.smilingsouls.net/index.php', '127.0.0.1', 'cache3-blfs.server.ntli.net', '', 1, 20030723155409);
INSERT INTO access_log VALUES ('welcome/index', '559', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)', 'http://search.freeserve.com/servlet/search/?qt=b&p=_results&q=do+we+all+need+love%3F&s=a', '127.0.0.1', 'cache3-blfs.server.ntli.net', '', 1, 20030723155421);
INSERT INTO access_log VALUES ('registered_users/index', '304', 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)', '', '127.0.0.1', 'roach-tc1-147.link2000.net', '', 3, 20030723160316);

# Back ticks aren't really necessary, but I like to use them just the same

SELECT `user_ids`.`user_id`, `page`  FROM `user_ids`, `access_log` WHERE `user_ids`.`user_id` = `access_log`.`user_id`;

Outputs:

558 writing/post_a_submission3
558 registered_users/index
558 welcome/services
558 welcome/search_all
157 registered_users/index
157 writing/index
157 writing/directory
157 writing/post_a_submission
460 registered_users/index
157 registered_users/edit_submissions
559 registered_users/index
559 welcome/index
304 registered_users/index

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::