Page 1 of 1

placing multiple records inside a single column of a <SOL

Posted: Wed Oct 17, 2007 2:21 pm
by PastorHank
I have three database tables - one with text, one with links to video and one with links to audio.
What I would like to do is place the results from my select statement for each inside a single column of a master table.
(i.e. all type a records in row 1 column 1, all type b in row 2 column 2, and all type c in row 3 column 3).

It's easy enough to create 3 tables, with a row for each record in the resultset, but for this client's desire I need one table with 3 rows of 1 column each with each row containing all records. Is this even possible? and if so, could someone please point me to the right place to look. I've googled it, but I may not be phrasing the question correctly.

thanks

Posted: Wed Oct 17, 2007 3:11 pm
by CoderGoblin
You may want to look at something called "views" (which I believe are a recent addition to MySQL). You do not mention any column names or what database you are using (I assume MySql which unfortunately I don't know that well). I know in Postgres you could join the three tables with a left outer join and "coalesce" the result to only get one "text" column which contains a value along with the "id".

The question is in some ways is why you need the three tables? Would you actually be better off with one table with an additional column of "type" or something to distinguish what they are ? The "type" column could be an integer value to aid in indexing.

Posted: Wed Oct 17, 2007 3:52 pm
by RobertGonzalez
Can you post the data you are talking about. Samples is really what we need to see. Samples of what is currently in there and samples of what you want that to look like.

Posted: Wed Oct 17, 2007 4:08 pm
by PastorHank
Yes, I'll post it a bit later...unfortunately I've got to be out of pocket until tomorrow.

thank you

Posted: Thu Oct 18, 2007 10:59 am
by PastorHank
OK....

first, the reason I use 3 tables is I come from the old school of database processing that says 3 smaller tables are faster to process than 1 large one plus, these aren't related in any way....

now in html I can could hard code what I need like this

Code: Select all


	<table border="0" width="600" id="table1" height="275">
		<tr>
			<td valign="top"><p align="center" valign="top" width='100'><b><i>Stories From The Heart<br>
			</i><a href="http://www.whathesees.net/samplepage1.htm">link 1</a><br>
			<a href="http://www.whathesees.net/samplepage2.htm">link 2</a><br>
			<a href="http://www.whathesees.net/samplepage3.htm">link 3</a></b></td>
			<td rowspan="3" width="400"><p align="right">
			<img border="0" src="http://www.whathesees.net/images/cardamom-mountains2.jpg" width="400" height="267"></td>
		</tr>
		<tr>
			<td valign="top"><p align="center" valign="top" width='100'><b><i>Video Stories<br>
			</i><a href="http://www.whathesees.net/moviepage1.htm">link 1</a><br>
			<a href="http://www.whathesees.net/moviepage2.htm">link 2</a><br>
			<a href="http://www.whathesees.net/moviepage3.htm">link 3</a></b></td>
		</tr>
		<tr>
			<td valign="top"><p align="center" valign="top" width='100'><b><i>Audio Stories<br>
			</i><a href="http://www.whathesees.net/audiopage1.htm">link 1</a><br>
			<a href="http://www.whathesees.net/audiopage2.htm">link 2</a><br>
			<a href="http://www.whathesees.net/audiopage3.htm">link 3</a></b></td>
		</tr>
	</table>

However I want to be able to dynamically retrieve my data - heres the php code for the first segment - 'stories'

Code: Select all

echo "<table border='0' cellspacing='2' width='400' align='center'>";

 while ($row = mysql_fetch_array($result)) {
  extract($row);
  	
  
echo "<tr>\n
		<td align='left'><i>$shortcaption<i></td>
 		</tr>";
}

echo "</table>\n";
This creates a table with a record on each row. (I've left off my link code, however it works so that's not an issue for me)

What I need to do is be able to place all my records in one row, in the same column, and I need the table to contain three rows with my results from the 3 datasets inside.

Posted: Thu Oct 18, 2007 11:16 am
by RobertGonzalez
I apologize in advance for my ignorance. I thought you meant tables as in database tables, not html tables.

So what you have is (without the images):

Code: Select all

----------------------
Stories From The Heart
link 1
link 2
link 3
----------------------
Video Stories
link 1
link 2
link 3
----------------------
Audio Stories
link 1
link 2
link 3
And you want to do this dynamically with data from database tables that are not related in any way?

Posted: Thu Oct 18, 2007 12:21 pm
by PastorHank
That was my fault....I didn't even think of differentiating the two....(another duh moment in my life...)


Yes, I want to pull the data from my database tables and place it in my html tables dynamically....

Posted: Thu Oct 18, 2007 1:12 pm
by PastorHank
This has been resolved. I've convince the client that eventually placing the captions inside an html table would become cumbersome and look really ragged. Instead we're going back to using a selection list box.

thanks anyway

Posted: Thu Oct 18, 2007 1:46 pm
by RobertGonzalez
Glad you got it worked out.