chrometweaks.org

I bought a web domain and hosting on iPage. What now?

Click Here To View All Answers...


Got a question... I bought a web domain and hosting on iPage. What now? Thanks in advance for any response. Second question of mine... Hi,.

I'm currently working with the contribution.

Article Manager.

I want to include related articles in my product info pages, based on the x-sell feature of.

Article Manager.

..

I've only recently discovered joining tables properly in MySQL, and I'm now putting it into practice, however, I'm getting the same results twice when executing this query:.

Select d.articles_name as name,.

       d.articles_id   as article_id,.

       au.authors_name as author,.

       au.authors_id   as authors_id.

From articles_xsell       as x,.

     articles_description as d,.

     articles             as ar,.

     authors              as au.

Where x.xsell_id = 8.

  and d.language_id = 1.

  and ar.articles_status = 1.

  and x.articles_id = d.articles_id.

  and au.authors_id = ar.authors_id.

I'm sure it's likely to be something simple, but I can't quite figure it out..

Thanks in advance,.

Steve...

Comments (88)

I'm stumped. I'm not so sure what is the right answer to your question. I'll do some investigation and get back to you if I discover an decent answer. You should email the people at iPage as they probably can help you..

Comment #1

And here's some data:.

#.

# Dumping data for table `articles`.

#.

INSERT INTO `articles` (`articles_id`, `articles_date_added`, `articles_last_modified`, `articles_date_available`, `articles_status`, `authors_id`) VALUES (3, '2004-02-18 11:54:23', '2004-02-20 11:11:38', NULL, 1, 1);.

INSERT INTO `articles` (`articles_id`, `articles_date_added`, `articles_last_modified`, `articles_date_available`, `articles_status`, `authors_id`) VALUES (4, '2004-02-26 00:00:00', '2004-02-20 11:11:52', NULL, 1, 1);.

#.

# Dumping data for table `articles_description`.

#.

INSERT INTO `articles_description` (`articles_id`, `language_id`, `articles_name`, `articles_description`, `articles_url`, `articles_viewed`, `articles_head_title_tag`, `articles_head_desc_tag`, `articles_head_keywords_tag`) VALUES (3, 1, 'A Bug\'s Life Review', 'Pur minuscule hero is Flik, an intelligent young ant who is forever inventing \r\nthings to try to make life less routine, much to the amusement of the rest of \r\nthe colony. When the grasshoppers\' share of the food is accidentally destroyed, \r\nit\'s up to Flik to figure out a way to save the colony.', 'www.pixar.com/featurefilms/abl/', 85, 'A Bug\'s Life Review', 'This story of an oppressed ant colony is portrayed by breathtaking computer animation.   The same people who brought us Toy Story, Pixar Studios via Disney are responsible for this insect epic.', '');.

INSERT INTO `articles_description` (`articles_id`, `language_id`, `articles_name`, `articles_description`, `articles_url`, `articles_viewed`, `articles_head_title_tag`, `articles_head_desc_tag`, `articles_head_keywords_tag`) VALUES (4, 1, 'Microsoft IntelliMouse Explorer Review', '<P><STRONG>Microsoft Optical Technology<BR></STRONG>The fastest, most accurate mouse on the market delivering optical technology. Superior tracking speed translates into precise cursor movement and accuracy.</P>', 'www.microsoft.com/hardware/mouseandkeyboard/productdetails.aspx?pid=003', 15, 'Microsoft IntelliMouse Explorer Review', 'Microsoft Intellimouse Explorer is Microsoft Corporation’s award-winning optical mouse. It features unique comfort enhancements and is supercharged with Microsoft Optical Technology.', '');.

#.

# Dumping data for table `articles_xsell`.

#.

INSERT INTO `articles_xsell` (`ID`, `articles_id`, `xsell_id`, `sort_order`) VALUES (8, 4, 26, 1);.

INSERT INTO `articles_xsell` (`ID`, `articles_id`, `xsell_id`, `sort_order`) VALUES (7, 3, 8, 1);.

#.

# Dumping data for table `authors`.

#.

INSERT INTO `authors` (`authors_id`, `authors_name`, `authors_image`, `date_added`, `last_modified`) VALUES (1, 'Joe Bloggs', NULL, '2004-02-19 15:43:01', '2004-02-19 16:16:52');.

Thanks,.

Steve...

Comment #2

For 4 tables you need three joins..

I think you only have two - you also need a third join between the two sets of tables...

Comment #3

You have:.

X,d joined.

Au,ar joined.

So you effectively have two tables still (x,d) and (au,ar) - so you need another join to make one table - (x,d,au,ar).

Sorry it is early I am still trying to work out the final join - but maybe you have the info you need?.

This post has been edited by.

Yesudo.com.

: 20 February 2004, 11:20..

Comment #4

Try.

Select d.articles_name as name,.

D.articles_id as article_id,.

Au.authors_name as author,.

Au.authors_id as authors_id.

From articles_xsell as x,.

Articles_description as d,.

Articles as ar,.

Authors as au.

Where x.xsell_id = 8.

And d.language_id = 1.

And ar.articles_status = 1.

And x.articles_id = d.articles_id.

And au.authors_id = ar.authors_id.

And ar.articles_id = d.articles_id.

Additonal join being between article_id in both articles and articles description..

This post has been edited by.

Yesudo.com.

: 20 February 2004, 12:43..

Comment #5

Wow!.

Thanks a million - You've not just got this working, but another piece of the MySQL puzzle's just clicked into place!.

I'd never looked at joins as actually connecting the tables - I always just thought they were getting data from eachother..

Thanks for the help, Emmett!.

Steve...

Comment #6

No probs..

Something I recently read is that the amount of joins required, for a multiple table join, is the amount of tables minus one. Came in helpful for me here..

This post has been edited by.

Yesudo.com.

: 20 February 2004, 21:38..

Comment #7

Suppose that makes sense if you try to visualize the joining:.

TABLE1 join TABLE2 join TABLE3 join TABLE4.

That way, their joins are all 'connectors', whereas previously I had:.

TABLE1 join TABLE2.

TABLE3 join TABLE4..

Comment #8

Hi,.

I'm currently working with the contribution.

Article Manager.

I want to include related articles in my product info pages, based on the x-sell feature of.

Article Manager.

..

I've only recently discovered joining tables properly in MySQL, and I'm now putting it into practice, however, I'm getting the same results twice when executing this query:.

Select d.articles_name as name,.

       d.articles_id   as article_id,.

       au.authors_name as author,.

       au.authors_id   as authors_id.

From articles_xsell       as x,.

     articles_description as d,.

     articles             as ar,.

     authors              as au.

Where x.xsell_id = 8.

  and d.language_id = 1.

  and ar.articles_status = 1.

  and x.articles_id = d.articles_id.

  and au.authors_id = ar.authors_id.

I'm sure it's likely to be something simple, but I can't quite figure it out..

Thanks in advance,.

Steve...

Comment #9

Here's the table structure for the four tables I'm accessing:.

# Table structure for table `articles`.

#.

CREATE TABLE `articles` (.

 `articles_id` int(11) NOT NULL auto_increment,.

 `articles_date_added` datetime NOT NULL default '0000-00-00 00:00:00',.

 `articles_last_modified` datetime default NULL,.

 `articles_date_available` datetime default NULL,.

 `articles_status` tinyint(1) NOT NULL default '0',.

 `authors_id` int(11) default NULL,.

 PRIMARY KEY  (`articles_id`),.

 KEY `idx_articles_date_added` (`articles_date_added`).

) TYPE=MyISAM;.

#.

# Table structure for table `articles_description`.

#.

CREATE TABLE `articles_description` (.

 `articles_id` int(11) NOT NULL auto_increment,.

 `language_id` int(11) NOT NULL default '1',.

 `articles_name` varchar(64) NOT NULL default '',.

 `articles_description` text,.

 `articles_url` varchar(255) default NULL,.

 `articles_viewed` int(5) default '0',.

 `articles_head_title_tag` varchar(80) default NULL,.

 `articles_head_desc_tag` text,.

 `articles_head_keywords_tag` text,.

 PRIMARY KEY  (`articles_id`,`language_id`),.

 KEY `articles_name` (`articles_name`).

) TYPE=MyISAM;.

#.

# Table structure for table `articles_xsell`.

#.

CREATE TABLE `articles_xsell` (.

 `ID` int(10) NOT NULL auto_increment,.

 `articles_id` int(10) unsigned NOT NULL default '1',.

 `xsell_id` int(10) unsigned NOT NULL default '1',.

 `sort_order` int(10) unsigned NOT NULL default '1',.

 PRIMARY KEY  (`ID`).

) TYPE=MyISAM;.

#.

# Table structure for table `authors`.

#.

CREATE TABLE `authors` (.

 `authors_id` int(11) NOT NULL auto_increment,.

 `authors_name` varchar(32) NOT NULL default '',.

 `authors_image` varchar(64) default NULL,.

 `date_added` datetime default NULL,.

 `last_modified` datetime default NULL,.

 PRIMARY KEY  (`authors_id`),.

 KEY `IDX_AUTHORS_NAME` (`authors_name`).

) TYPE=MyISAM;.

Steve..

Comment #10


This question was taken from a support group/message board and re-posted here so others can learn from it.