Member Avatar for unikorndesigns

Hi Guys....i am facing a small issue with an SQL statement here. Will you be able to help me out? I wanted to grab the page title, page author's name which is in the users table, the no of comments, and the page publish date.

"SELECT * FROM pages INNER JOIN users ON pages.page_author=users.users_id"

will do my job pretty neat and clean
the following is the schema of the pages and the users table

CREATE TABLE IF NOT EXISTS `pages` (
  `page_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `page_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `page_publish_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `page_title` text NOT NULL,
  `page_content` longtext NOT NULL,
  `page_excerpt` text NOT NULL,
  `page_status` varchar(20) NOT NULL DEFAULT 'publish',
  `page_comment_status` varchar(20) NOT NULL DEFAULT 'open',
  `page_password` varchar(20) NOT NULL DEFAULT '',
  `page_name` varchar(200) NOT NULL DEFAULT '',
  `page_modified_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `page_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `page_type` varchar(20) NOT NULL,
  `page_order` bigint(220) NOT NULL,
  `page_comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`page_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

CREATE TABLE IF NOT EXISTS `users` (
  `users_id` bigint(220) NOT NULL AUTO_INCREMENT,
  `users_user_type_id` int(220) NOT NULL,
  `users_name` varchar(220) NOT NULL,
  `users_username` varchar(220) NOT NULL,
  `users_password` varchar(32) NOT NULL,
  `users_email` varchar(220) NOT NULL,
  `users_lastlogin_date` date DEFAULT NULL,
  `users_avatar` int(220) DEFAULT NULL,
  `users_lastlogin_ip` int(30) DEFAULT NULL,
  PRIMARY KEY (`users_id`),
  UNIQUE KEY `users_email` (`users_email`),
  UNIQUE KEY `users_username` (`users_username`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

I wanted to display all the pages with page_status as draft with other info as mentioned above, page title, page author's name which is in the users table, the no of comments, and the page publish date.
i tried

SELECT * FROM pages WHERE page_status='".$type."' INNER JOIN users ON pages.page_author=users.users_id

but it doesn't seem to work.

Disclaimer: my usage of INNER JOIN with WHERE clause could be wrong, am not sure. I started using the JOINs very recently..

Thanks in advance for any help! :D

  • 2 Contributors
  • 2 Replies
  • 229 Views
  • 45 Minutes Discussion Span
  • Latest Post Latest Post by unikorndesigns
Member Avatar for adam_k 
SELECT * FROM pages INNER JOIN users ON pages.page_author=users.users_id 
WHERE page_status='".$type."'

WHERE clause has to go to the end. Think of it as JOIN produces a single table with the combined contents of the 2 tables specified.

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.