I have managed to work out how to join two tables together with MySQL using the following code:

SELECT v.*, u.*
FROM video AS v, user AS u
WHERE v.user_id = u.user_id
ORDER BY v.user_id

Which returns a list of all fields in both tables so long as the video table has a record that matches the users_id.

I can then list all the records in a standard repeat region fine. However I would like to group all the video records for a particular user together.

User Name 1
- Video 1
- Video 2
User Name 2
- Video 1
- Video 2

The problem need help on is getting the video repeat region to work while maintaining the overall Next Record order. I get limited success from the following code, but some records just seem to disappear. If anyone has time to comment and give me pointers I would appreciate it.

<pre id=code><font face=courier size=2 id=code>
&lt;?php while (($Repeat1__numRows-- != 0) && (!$rs_uservideoss-&gt;EOF))
{
$lastuser = $rs_uservideoss-&gt;Fields("user_id";
$nextuser = $rs_uservideoss-&gt;Fields("user_id";
?&gt;
&lt;table width="700" border="0"&gt;
&lt;tr&gt;
&lt;td colspan="2"&gt; &lt;font face="Arial, Helvetica, sans-serif"&gt;
&lt;font size="2"&gt; &lt;b&gt;
&lt;?php echo $rs_uservideoss-&gt;Fields("peudonym"?&gt;
of
&lt;?php echo $rs_uservideoss-&gt;Fields("base"?&gt;
&lt;/b&gt;&lt;/font&gt;&lt;/font&gt; &lt;/td&gt;
&lt;/tr&gt;
&lt;?php while ($lastuser == $nextuser)
{
?&gt;
&lt;tr&gt;
&lt;td width="200" align="center" valign="middle"&gt;
&lt;div align="center"&gt;&lt;img src="&lt;?php echo $rs_uservideoss-&gt;Fields("thumburl"?&gt;" width="160" height="120" alt="Click to play" border="0"&gt;&lt;/div&gt;
&lt;/td&gt;
&lt;td width="250" align="center" valign="middle"&gt;
&lt;div align="center"&gt;&lt;font size="2"&gt; &lt;/font&gt;
&lt;table width="350" border="0" align="center"&gt;
&lt;tr&gt;
&lt;td width="50"&gt;&lt;font size="2" face="Arial, Helvetica, sans-serif"&gt;Title:&lt;/font&gt;&lt;/td&gt;
&lt;td width="200"&gt;&lt;font face="Arial, Helvetica, sans-serif"&gt;&lt;font size="2"&gt;
&lt;?php echo $rs_uservideoss-&gt;Fields("title"?&gt;
&lt;/font&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="100"&gt;
&lt;div align="center"&gt;&lt;font face="Arial, Helvetica, sans-serif"&gt;
&lt;font size="2"&gt; &lt;/font&gt;&lt;/font&gt; &lt;/div&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td width="50"&gt;&nbsp;&lt;/td&gt;
&lt;td width="200"&gt;&nbsp;&lt;/td&gt;
&lt;td width="100"&gt;
&lt;div align="center"&gt;&lt;a href="javascript:;" onClick="GP_AdvOpenWindow('/members/playvideo.php?video_id=&lt;?php echo $rs_uservideoss-&gt;Fields("video_id"?&gt;','PlayVideo','fullscreen=no,toolbar=no,location=no,status=no,menubar=no,scrollbars=no,resizable=no',375,425,'center','ignoreLink','alwaysOnTop',300,'borderless');return document.MM_returnValue"&gt;&lt;img src="/images/buttons/100x20/playvideo.gif" width="100" height="20" alt="Play Video" onMouseDown="GP_AdvOpenWindow('/members/playvideo.php?video_id=&lt;?php echo $rs_uservideoss-&gt;Fields("video_id"?&gt;','PlayVideo','fullscreen=no,toolbar=no,location=no,status=no,menubar=no,scrollbars=no,resizable=no',375,425,'center','ignoreLink','alwaysOnTop',300,'borderless');return document.MM_returnValue" border="0"&gt;&lt;/a&gt;&lt;/div&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td width="50"&gt;&lt;font face="Arial, Helvetica, sans-serif" size="2"&gt;Length:&lt;/font&gt;
&lt;/td&gt;
&lt;td width="200"&gt;&lt;font face="Arial, Helvetica, sans-serif"&gt;&lt;font size="2"&gt;
&lt;?php echo $rs_uservideoss-&gt;Fields("length"?&gt;
Seconds &lt;/font&gt;&lt;/font&gt;&lt;/td&gt;
&lt;td width="100"&gt;
&lt;div align="center"&gt;&lt;font face="Arial, Helvetica, sans-serif"&gt;
&lt;font size="2"&gt; &lt;/font&gt;&lt;/font&gt;&lt;/div&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;/div&gt;
&lt;/td&gt;
&lt;?php
$Repeat2__index++;
$lastuser = $rs_uservideoss-&gt;Fields("user_id";
$rs_uservideoss-&gt;MoveNext();
$nextuser = $rs_uservideoss-&gt;Fields("user_id";
}
?&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&nbsp;&lt;/td&gt;
&lt;td&gt;&nbsp;&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;?php
$Repeat1__index++;
$rs_uservideoss-&gt;MoveNext();
}
?&gt;
</font id=code></pre id=code>

Unfortunately it is not (yet) possible to do nested repeated region (to my knowledge)

My suggestion would be to add a little piece of code that would detect if the User Name has changed in the recordset, and then print it, otherwise don't print it.

Here's how I do that :

<pre id=code><font face=courier size=2 id=code>
&lt;?php
$oldUserName="";
?&gt;
&lt;?php while (($Repeat1__numRows-- != 0) && (!$rs_uservideoss-&gt;EOF)) {
?&gt;
<font color=red>.
.
.</font id=red>
&lt;?php
if($oldUserName!=$rs_uservideoss-&gt;Fields("user_id"){
echo $rs_uservideoss-&gt;Fields("user_id";
}
?&gt;
<font color=red>.
.
.</font id=red>
&lt;?php
$oldUserName = $rs_uservideoss-&gt;Fields("user_id";
?&gt;
// End of loop for repeat region 1
&lt;?php $Repeat1__index++; $rs_uservideoss-&gt;MoveNext();}?&gt;
</font id=code></pre id=code>

So the first step is to initialize $oldUserName to a value you're sure not having in your database

Next you start the loop for the repeat region, and print the user_id only if it is different from the precedent row.

Hope it helps.

Bruno


--- Better to die trying, than never try at all ---