Here is what I’m trying to get and I think I’ve been on it so long I have quit thinking logically: http://sourceuno.com/gridlayout.png
I have five tables I’m working from: pros, categories, subcategories, videos and questions
pros contains id, fname, lname, category, subcategory, thumbnail, views
They each answer questions which are assigned access levels (free, freemium and premium) which belong to a subcategory. The grid above is displayed by the four most recent video answers posted for that category. I am using a double inner JOIN to read this data into a row:
[php]$query=“SELECT videos.proid, videos.code, videos.views, pros.id, videos.qid,
questions.question, pros.fname, pros.lname FROM videos INNER JOIN pros
ON pros.id = videos.proid INNER JOIN questions ON videos.qid = questions.id
WHERE subcatid = ‘$subcatid’ LIMIT 4”;[/php]
This returns 4 video codes from the subcategory ID I am looking for and then stops. I need it to iterate through and give me all of the answers to all of the questions that person has answered so I can produce it in columns underneath their name AND break it up by printing the actual question text (from the questions database) in the rows I have colored blue. It’s difficult for me to explain exactly what I’m doing better than this.
So I’m looking for the 4 most recent videos added in this subcategory . Then I want to publish those professionals in a row of 4 across the top along with a link to their introductory video. Below that the number of views (clicks) each link to that video (thumbnail) has had. Then I want to select ALL of the questions which belong to this subcategory which have been answered by ANY of these four people and display those in COLUMNS beneath the professional’s thumbnail. A tricky part for me is only publishing the text of that question once as a divider (colspan 4) and IF the professional who’s column we are in answered it displaying a “View Now” link to view that video answer for that professional.
CREATE TABLE IF NOT EXISTS pros
(
id
int(11) NOT NULL AUTO_INCREMENT,
category
int(3) NOT NULL,
subcat
int(3) NOT NULL,
fname
varchar(36) NOT NULL,
lname
varchar(24) NOT NULL,
company
varchar(64) NOT NULL,
title
varchar(36) NOT NULL,
status
int(1) NOT NULL,
submitby
varchar(64) NOT NULL,
stuff
blob NOT NULL,
videoadded
varchar(16) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;
CREATE TABLE IF NOT EXISTS categories
(
id
int(3) NOT NULL AUTO_INCREMENT,
maincat
varchar(36) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
CREATE TABLE IF NOT EXISTS subcategories
(
id
int(3) NOT NULL AUTO_INCREMENT,
subcat
varchar(96) NOT NULL,
maincat
int(3) NOT NULL,
PRIMARY KEY (id
)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
CREATE TABLE IF NOT EXISTS videos
(
id
int(6) NOT NULL AUTO_INCREMENT,
code
text NOT NULL,
proid
int(5) NOT NULL,
views
int(9) NOT NULL,
maincatid
int(3) NOT NULL,
subcatid
int(3) NOT NULL,
when
int(16) NOT NULL,
qid
int(3) NOT NULL COMMENT ‘relevant question ID’,
PRIMARY KEY (id
)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;
CREATE TABLE IF NOT EXISTS questions
(
id
int(9) NOT NULL AUTO_INCREMENT,
qestioncat
varchar(255) NOT NULL,
question
tinytext NOT NULL,
PRIMARY KEY (id
)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
I know my JOIN statement is missing some data but writing the join isn’t the challenge it’s getting the data I need and publishing it in columns with those colspan4 dividers of the question text. Oh and only the one’s with access level 0 should be hyperlinked unless the user is logged in then levels 1 and 2 become active but first things first.