[SOLVED] using 2 loops with MAX()/MIN()

[rbragg]

Good morning! I've been on vacation since last Thursday afternoon so I'm sorry for the delayed reply. I get the same result using DISTINCT ... the empty "last reply: ".

[rbragg]

I've managed to get the results that I would like for $queryLastReply:

 

<?php 
# this query groups subjects together and displays the last post of each thread to show the last reply
$queryLastReply = "
SELECT actionID, name, subjectID, subject, timestamp
FROM onetable
WHERE subjectID = '". $first['subjectID'] ."'
ORDER BY actionID DESC
LIMIT 1
";
?>

 

This successfully gives me the last post of each thread.

 

However, I'm having trouble with consistently getting the first post for each thread with $queryFirst. Most show the first post in the thread but a few will show the last post. I cannot find the common denominator between those threads:

 

<?php 
# this query groups subjects together and displays the first post of each thread
$queryFirst = "
SELECT MIN(actionID), name, subjectID, subject, timestamp
FROM onetable
GROUP BY subjectID
ORDER BY subjectID DESC 
";
?>

[rbragg]

This was on the 3rd or 4th page and I see boo_lolly on today. 

[boo_lolly]

how's it goin rbragg? see if this works for ya:

$queryFirst = 
                "SELECT
                        actionID,
                        name,
                        subjectID,
                        subject,
                        timestamp
                FROM
                        onetable
                WHERE
                        subjectID = '". $first['subjectID'] ."'
                ORDER BY
                        actionID
                ASC
                LIMIT 1
";

[rbragg]

Thanks for sticking with me.  This gives me a completely blank loop. Echoing the query gives me Resource id #7.

[Barand]

Does this help?

 

http://www.phpfreaks.com/forums/index.php/topic,137413.msg581594.html#msg581594

[rbragg]

Hi Barand! I have only one table. I have tried implementing this idea, though, with no luck.

[Barand]

What is your table structure and the sql query you used?

[rbragg]

Good afternoon!

 

My queries are here: http://www.phpfreaks.com/forums/index.php/topic,136600.msg581456.html#msg581456

 

Again, $queryLastReply is functioning properly.

 

My table structure is here:

http://www.phpfreaks.com/forums/index.php/topic,136600.msg577549.html#msg577549

 

 

[Barand]

None of the queries posted resemble the code I suggested to get first and last post

SELECT t.name, t.subject, t.msg  FROM mytablename t
INNER JOIN 
           (SELECT subject, MIN(timestamp ) as date FROM mytablename GROUP BY subject
            UNION 
            SELECT subject, MAX(timestamp ) as date FROM mytablename GROUP BY subject) as a
ON t.subject= a.subject and t.timestamp = a.date
ORDER BY t.subject, t.timestamp

[rbragg]

I did try this. However, since the result was nowhere close to working I reverted back to what seemed to be very close to working.

 

ON t.subject= a.subject and t.timestamp = a.date

 

I have only one table so am not sure how to approach this part of the query. Also, I am finding the MIN/MAX actionID instead of timestamp/date.

[Barand]

"subject" in the above query should be "subjectID", sorry. (Only one table is used)

[rbragg]

I'm sorry, I was assuming that the t. and a. were referencing separate tables. I was being stubborn about that. Here was my attempt at this join:

 

<?php
$queryPosts = "
SELECT t.name, t.subject, t.timestamp FROM onetable t
INNER JOIN
  (SELECT subjectID, MIN(actionID) as id FROM onetable GROUP BY subjectID
  UNION
  SELECT subjectID, MAX(actionID) as id FROM onetable GROUP BY subjectID ) as a
ON t.subjectID = a.subjectID and t.actionID = a.id
ORDER BY t.subjectID DESC
";
$postResults = mysql_query($queryPosts) or die('The get posts query failed: ' . mysql_error());

echo "<form name='form_display' method='POST' action='forumDetail.php'>";
echo "<table width='100%' border='0' cellspacing='2' cellpadding='2'>
<tr bgcolor='#616161'><td width='8%'><input name='view' type='submit' class='style3' value='view'></td>
<td class='style6'>Message</td><td class='style6'>Started by</td><td class='style6'>Date & Time Started</td></tr>"; 

while ($post = mysql_fetch_array($postResults))
{
  echo "<tr><td align='center' class='style3'>";
  ?>
  <input type='radio' name='selected' value='<?php echo $post['subjectID'];?>'>
  <?php
  echo "</td><td class='style8'>";
  echo $post['subject'];
  echo "</td><td class='style3'>";
  echo $post['name'];
  echo "</td><td class='style3'>";   
  echo $post['timestamp'];
  echo "</td></tr>";
}
echo "</table>";
echo "</form>";
?>

 

This gives the results that it is indeed asking for. This type of query is new to me so I'm still trying to wrap my brain around it.

 

I only have a small problem, now, of trying to think up a cosmetic trick. Of course, I would like first and last posts to be grouped together with an indention before the last post. Also, all of the posts have radiobuttons in front of them and I'd like for only the first post to have a radiobutton.  I'm not sure of how I can achieve these effects since they are being displayed within a loop. ???

[Barand]

Fairly simple (pseudocode below)

prevSubject = '';

while (fetch next row) {

       IF subjectID != prevSubject
             output radio button in first cell
             prevSubject = subjectID
       else
            putput blank in first cell
      end if

      output rest of cells in row

end while
             
       

[rbragg]

Good morning, Barand. I get the general idea except for the $prevSubject variable. I'm not sure how I would set it:

 

<?php
$prevSubject = '';

while ($post = mysql_fetch_array($postResults))
{
  if ($post['subjectID'] != $prevSubject)
  {
    echo "<tr><td align='center' class='style3'>";
    ?>
    <input type='radio' name='selected' value='<?php echo $post['subjectID'];?>'>
    <?php
    echo "</td>";
  }
  else
  {
    echo "<tr><td align='center' class='style3'> </td>";
  }
  echo "<td class='style8'>";
  echo $post['subject'];
  echo "</td><td class='style3'>";
  echo $post['name'];
  echo "</td><td class='style3'>";   
  echo $post['timestamp'];
  echo "</td></tr>";
  } # close while
?>

[Barand]

You can set it just about anywhere in the loop AFTER you have tested its value

 

while ($post = mysql_fetch_array($postResults))
{
  if ($post['subjectID'] != $prevSubject)
  {
    echo "<tr><td align='center' class='style3'>";
    ?>
    <input type='radio' name='selected' value='<?php echo $post['subjectID'];?>'>
    <?php
    echo "</td>";
  }
  else
  {
    echo "<tr><td align='center' class='style3'> </td>";
  }
  $prevSubject = $_POST['subjectID'];                 // such as here
  echo "<td class='style8'>";
  echo $post['subject'];
  echo "</td><td class='style3'>";
  echo $post['name'];
  echo "</td><td class='style3'>";   
  echo $post['timestamp'];
  echo "</td></tr>";
  } # close while
?>

[rbragg]

Ah. $subjectID is not yet stored in the POST array. This is the first page in the process. It just displays all of the current threads and a glimpse of the last reply to each thread. I have to find a way to set $prevSubject.

 

I hope I am following you.

[Barand]

Correction

 

$prevSubject = $post['subjectID'];  // NOT the $_POST array but your "$post" from the mysql_fetch_array call

[rbragg]

I thought that may have been the case. I tried that and I get rbs before every subject. How would $prevSubject know whether or not to get the subjectID from the MIN or MAX?

 

I've attached a screencap:

 

[attachment deleted by admin]

[Barand]

Post the code that's now giving the above output

[rbragg]

Sure:

 

<?php
include 'dbConnect.php';

$queryPosts = "
SELECT t.name, t.subject, t.timestamp FROM onetable t
INNER JOIN
  (SELECT subjectID, MIN(actionID) as id FROM onetable GROUP BY subjectID
  UNION
  SELECT subjectID, MAX(actionID) as id FROM onetable GROUP BY subjectID ) as a
ON t.subjectID = a.subjectID and t.actionID = a.id
ORDER BY t.subjectID DESC
";
$postResults = mysql_query($queryPosts) or die('The post query failed: ' . mysql_error());

echo "<form name='form_display' method='POST' action='forumDetail.php'>";
echo "<table width='100%' border='0' cellspacing='2' cellpadding='2'>
<tr bgcolor='#616161'><td width='8%'><input name='view' type='submit' class='style3' value='view'></td>
<td class='style6'>Message</td><td class='style6'>Started by</td><td class='style6'>Date & Time Started</td></tr>"; 

while ($post = mysql_fetch_array($postResults))
{
  $prevSubject = $post['subjectID'];

  if ($post['subjectID'] != $prevSubject)
  {
    echo "<tr><td align='center' class='style3'>";
    ?>
    <input type='radio' name='selected' value='<?php echo $post['subjectID'];?>'>
    <?php
    echo "</td>";
  }
  else
  {
    echo "<tr><td align='center' class='style3'> </td>";
  }

  echo "<td class='style8'>";
  echo $post['subject'];
  echo "</td><td class='style3'>";
  echo $post['name'];
  echo "</td><td class='style3'>";   
  echo $post['timestamp'];
  echo "</td></tr>";
}
echo "</table>";
echo "</form>";
mysql_close;
?>

[Barand]

Made a couple of changes, commented

<?php
include 'dbConnect.php';

$queryPosts = "
SELECT t.subjectID, t.name, t.subject, t.timestamp FROM onetable t      
INNER JOIN
  (SELECT subjectID, MIN(actionID) as id FROM onetable GROUP BY subjectID
  UNION
  SELECT subjectID, MAX(actionID) as id FROM onetable GROUP BY subjectID ) as a
ON t.subjectID = a.subjectID and t.actionID = a.id
ORDER BY t.subjectID DESC
";                                                                              //added subjectID to selection ***
$postResults = mysql_query($queryPosts) or die('The post query failed: ' . mysql_error());

echo "<form name='form_display' method='POST' action='forumDetail.php'>";
echo "<table width='100%' border='0' cellspacing='2' cellpadding='2'>
<tr bgcolor='#616161'><td width='8%'><input name='view' type='submit' class='style3' value='view'></td>
<td class='style6'>Message</td><td class='style6'>Started by</td><td class='style6'>Date & Time Started</td></tr>"; 

$prevSubject = '';						
while ($post = mysql_fetch_array($postResults))
{

  if ($post['subjectID'] != $prevSubject)
  {
    echo "<tr><td align='center' class='style3'>";
    ?>
    <input type='radio' name='selected' value='<?php echo $post['subjectID'];?>'>
    <?php
    echo "</td>";
  }
  else
  {
    echo "<tr><td align='center' class='style3'> </td>";
  }

  $prevSubject = $post['subjectID'];                          // moved to here   ***

  echo "<td class='style8'>";
  echo $post['subject'];
  echo "</td><td class='style3'>";
  echo $post['name'];
  echo "</td><td class='style3'>";   
  echo $post['timestamp'];
  echo "</td></tr>";
}
echo "</table>";
echo "</form>";
mysql_close;
?>

[rbragg]

Awesome, awesome, awesome! Thanks so much!! That small change works perfectly. Again, thanks for sticking with me.