placing multiple records inside a single column of a <SOL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
PastorHank
Forum Contributor
Posts: 117
Joined: Sat Jun 03, 2006 7:58 am
Location: Texas Hill Country

placing multiple records inside a single column of a <SOL

Post 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
Last edited by PastorHank on Thu Oct 18, 2007 1:12 pm, edited 1 time in total.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
PastorHank
Forum Contributor
Posts: 117
Joined: Sat Jun 03, 2006 7:58 am
Location: Texas Hill Country

Post by PastorHank »

Yes, I'll post it a bit later...unfortunately I've got to be out of pocket until tomorrow.

thank you
PastorHank
Forum Contributor
Posts: 117
Joined: Sat Jun 03, 2006 7:58 am
Location: Texas Hill Country

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
PastorHank
Forum Contributor
Posts: 117
Joined: Sat Jun 03, 2006 7:58 am
Location: Texas Hill Country

Post 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....
PastorHank
Forum Contributor
Posts: 117
Joined: Sat Jun 03, 2006 7:58 am
Location: Texas Hill Country

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Glad you got it worked out.
Post Reply