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.
