Columns from rows

Printing a row is simple - select your data and echo it out so that you get something like:

USER1 | U1ARTICLE1 | U1ARTICLE2 | U1ARTICLE3 | U1PROFILE
USER2 | U2ARTICLE1 | U2ARTICLE2 | U2ARTICLE3 | U2PROFILE
USER3 | U3ARTICLE1 | U3ARTICLE2 | U3ARTICLE3 | U3PROFILE

What I’m trying to get is columns instead of rows something like:

USER1 | USER2 | USER3
U1ARTICLE1 | U2ARTICLE1 | U3ARTICLE1
U1ARTICLE2 | U2ARTICLE2 | U3ARTICLE2
U1ARTICLE3 | U2ARTICLE3 | U3ARTICLE3
U1PROFILE | U2PROFILE | U3PROFILE

I am reading the data in with a join from multiple tables and I get the data I am looking for but cannot think of how to print it in columns like above. Any pointers? I’m using a simple while($row = mysql_fetch_array($result)){ echo “$row[0] $row[1] $row[2] $row[3]”; }

I have no idea what you are trying to do. Don’t you already know the column names?

Can you show the table structure?

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.

Sponsor our Newsletter | Privacy Policy | Terms of Service