Join advice needed

[donboe]

For a website I am working on I have different widgets. For this section I use three tables:

• widgets
• widget_content
• widget_photos

In most cases things are quite straightforward like this:

• tbl widgets: widget_name
• tbl widget_content: heading & content
• tbl widget_photos: photo

But in some cases the situation is slightly different. One widget, for example, has three rows in the widget_content tbl and three in the widget_photos tbl where each photo is related to a certain row in the widget_content tbl. Here is the table structure to illustrate what i mean

CREATE TABLE IF NOT EXISTS `site_widgets` (
  `widget_id` smallint(6) NOT NULL AUTO_INCREMENT,
  `widget_name` varchar(64) NOT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`widget_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `site_widgets` (`widget_id`, `widget_name`, `isActive`) VALUES
(1, 'Season dates', 1);

CREATE TABLE IF NOT EXISTS `widget_content` (
  `content_id` smallint(6) NOT NULL AUTO_INCREMENT,
  `widget_id` smallint(6) NOT NULL,
  `heading` varchar(128) DEFAULT NULL,
  `sub_heading` varchar(255) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`content_id`),
  KEY `widgetId` (`widget_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `widget_content` (`content_id`, `widget_id`, `heading`, `content`) VALUES
(1, 1, 'Spring season', 'This spring we will be open from March 15 till June 15'),
(2, 1, 'Summer season', 'The summer season we are open for tandem flights & visiting pilots after prior communication.'),
(3, 1, 'Autumn season', 'In fall we will be open from the 1st of September till the 17th November.');

CREATE TABLE IF NOT EXISTS `widget_photos` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `rel_id` tinyint(4) DEFAULT NULL,
  `photo` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `relId` (`rel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `widget_photos` (`id`, `rel_id`, `photo`) VALUES
(1, 1, 'flying_paradise_spring.jpg'),
(2, 2, 'flying_paradise_summer.jpg'),
(3, 3, 'flying_paradise_fall.jpg');

So widget Season dates has 3 related rows in the table widget_content related by widget_id => widget_id and each row in widget_content has a related photo in the widget_photos tbl related by content_id => rel_id

My question i how the query hould look like so I can loop over them in the View in PHP so that the related content and photos are displayed in panels like this:

Thank you in advance

[r937]

SELECT ... FROM site_widgets LEFT OUTER JOIN widget_content ON widget_content.widget_id = site_widgets.widget_id LEFT OUTER JOIN widget_photos ON widget_photos.rel_id = widget_content.widget_id

[donboe]

@r937 That works great Rudi as usual . I have one aditional question about this matter. This part was/is for the front-end. For the back-end (CMS) I actually need the same query for the edit page. The only problem is, like i said before, that not all widgets have the same structure. Most have the widget name (tbl site_widgets) a heading and content (tble widget_content) and a photo (tbl widget_photos), some have, next to the widget name like i said in the opening post, three rows in the tbl widget_content and three in yhe tbl widget_photos. And then there are widget that have just the widget_name and heading and content, but not a photo. Still it would be great If i could use the same select query for redirection to the actual edit page. I quess it should some sort of conditional query? But I have no idea how it hould look like?

[r937]

@donboe

Still it would be great If i could use the same select query for redirection to the actual edit page

no idea what this means, sorry

[donboe]

@r937 Hi Rudi. I think it has to do with the fetch and/orfetchAll in PHP. Like I explained most widgets have one row in the main table (tbld site_widgets) one row in the content table (tbl widget_content) and one row in the photos table (tbl widget_photos). A few widgets though have multiple rows in the content and photos tables. For the widgets with multiple rows I would like to use a foreach loop in PHP to show the separate rows:

<?php foreach ($details as $detail){
    echo $detail['heading'];
   // more variables
} ?>

Where for the widgets with a singe row I just ue the entire variable

<?php echo $details['heading']; ?>

For that to happen I use a if else statement in PHP. The problem is that the query as you gave me in the first place is working fine for the widgets with a singe row in each table when I use fetch() I can’t use that for the widgets with multiple rows because then I get the Illegal string offset message. And fetchAll () does not work for either of the two. So the question is. Is there a way to use the same query and still get a result set for both scenarios.

Edit:

I made some screenshots. This is what i get when I use fetch() for a widget with a singe row:

A you can see is this one ok. This is what I get when I use fetch() for a widget with multiple rows:

This with fetchAll for the single row widgets:

and this for the multiple rows using fetchAll

[Mittineague]

This is a PHP thing, not a database thing. The problem is because you are fetching both numerical and associative keys, but using only the associative keys without dealing with the numerical keys.

If you don’t need numerical keys, don’t get them. I you need to use both, wrap the assign lines in conditional checks.