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

rbragg · April 18, 2007

I am developing a simple messageboard that will list threads. There will be the first post of the thread and directly underneath will be the last reply to that thread. My problem is that instead of the last reply, the last post in the db is being displayed under all of the threads.

actionID is the auto incremented primary key. subjectID is an integer that is the same for a post and all of its replies. A new post will have the next incremented integer.

<?php
include 'dbConnect.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 timestamp DESC
";
$firstResults = mysql_query($queryFirst) or die('The first query failed: ' . mysql_error());

# this query groups subjects together and displays the last post of each thread to show the last reply
$queryLastReply = "
SELECT MAX(actionID), name, subjectID, subject, timestamp
FROM onetable
GROUP by subjectID
";
$lastResults = mysql_query($queryLastReply) or die('The last reply query failed: ' . mysql_error()); 
while ($last = mysql_fetch_array($lastResults))
{
  $reply = "<b>" . $last['subject'] . "</b> by " . $last['name'] . " at " . $last['timestamp'];;
}

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 ($first = mysql_fetch_array($firstResults))
{
  echo "<tr><td align='center' class='style3'>";
  ?>
  <input type='radio' name='selected' value='<?php echo $first['subjectID'];?>'>
  <?php
  echo "</td><td class='style8'>";
  echo $first['subject'];
  echo "</td><td class='style3'>";
  echo $first['name'];
  echo "</td><td class='style3'>";   
  echo $first['timestamp'];
  echo "</td></tr>";  
  echo "<tr><td colspan='4' align='right' class='style3'><span class='style7'>last reply: </span>$reply</td></tr>";
  echo "<tr><td></td></tr>";
}
echo "</table>";
echo "</form>";
mysql_close;
?>

How do I get the last reply for each thread (the highest actionID for that subjectID) to display instead of the last reply in the db (the highest actionID in the db)? I thought my query was accurate.

schme16 · April 18, 2007

maybe something like

$queryLastReply = "
SELECT MAX(actionID), name, subjectID, subject, timestamp
FROM onetable
GROUP by subjectID, actionID
";

but i'm not sure, i'm used to using order rather than group...but esentialy the look to be doing the same thing.

boo_lolly · April 18, 2007

change this:

$queryLastReply = "
SELECT MAX(actionID), name, subjectID, subject, timestamp
FROM onetable
GROUP by subjectID
";

to this:

$queryLastReply = "
SELECT MAX(actionID), name, subjectID, subject, timestamp
FROM onetable
GROUP by subjectID
ORDER BY timestamp
ASC
";

mpharo · April 18, 2007

I think you need to add a WHERE clause in your SQL statement on the subjectID, the query you have looks correct except it is looking for all results and returning the last one, what you want if I am understanding is the last one for this thread or subjectID....

I could be wrong, but this is what it looks like without seeing your table structure and how the threads are being inserted...

boo_lolly · April 18, 2007

I think you need to add a WHERE clause in your SQL statement, the query you have looks correct except it is looking for all results and returning the last known one, what you want if I am understanding is the last one for this thread....

I could be wrong, but this is what it looks like without seeing your table structure and how the threads are being inserted...

you may be right, but i'm pretty sure the MAX(actionID) replaces the need for a WHERE clause.

mpharo · April 18, 2007

The MAX function returns the maximum value of an expression.

If it is an auto increment field which is not associated with an id for a thread, the max will just give the highest value on that row, which will be the last entry....I think he has it setup like this....

New Thread

actionID=1 subjectID=1, etc..

new Reply

actionID=2 subjectID=1, etc..

New Thread

actionID=3 subjectID=2, etc...

So if you do a max(actionID) it will give the highest value (the last row) in the table, where if you do a WHERE subjectID=1 it will give a highest value for that post...i would restructure your table a little if this is how it is setup, but I could be completly wrong...

rbragg · April 18, 2007

Thanks for all of your replies!

change this:

I initially tried this and it gives me the first post in the db rather than the last post in each subjectID.

rbragg · April 18, 2007

I think he has it setup like this....

New Thread

actionID=1 subjectID=1, etc..

new Reply

actionID=2 subjectID=1, etc..

New Thread

actionID=3 subjectID=2, etc...

"She", by the way. You are exactly right about how I have it set up. I was afraid I was being unclear. I had started a WHERE clause in $queryLastReply but had no idea where I was going with it.

boo_lolly · April 18, 2007

something like this:

$queryLastReply = "
SELECT MAX(actionID), name, subjectID, subject, timestamp
FROM onetable
WHERE subjectID = '$var'
";

$var is the subjectID of that particular thread.

rbragg · April 18, 2007

That makes good sense. I'm just unsure of how I would use $var in the loop?

rbragg · April 18, 2007

I've tried defining $var as $first but without the GROUP BY clause in $queryLastReply I get this message:

"The last reply query failed: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause"

boo_lolly · April 18, 2007

i think i see what you want to do now. what you need is a nested while loop. can i see the structure of your database tables with a few example values in the table cells? i could write it for ya real quick if i knew what your database looked like.

rbragg · April 18, 2007

Sure. I started out with 2 tables but normalized to 1:

actionID name subjectID subject msg ip timestamp

1 robin 1 test of onetable test 2007-04-17 10:38:22

2 robin 1 reply to test of onetable test 2007-04-17 10:51:41

3 Betty 2 Peaches We are losers. 2007-04-17 14:08:22

mpharo · April 18, 2007

Are you trying to display the last reply for all subjects or just a single subject?

rbragg · April 18, 2007

I'd like to display the last reply for each individual subject under that subject.

[attachment deleted by admin]

boo_lolly · April 18, 2007

there's something similar going on in a different thread... check it out, you may get something out of it...

http://www.phpfreaks.com/forums/index.php/topic,136603.msg577611.html#msg577611

mpharo · April 18, 2007

In this case you need to pass in the subjectID for the last reply you wanna get, im not sure if this is a main page or a refering page but this is somewhat what it should look like....

//Coming from one page or another depends on how this gets populated, if it is the same page it can be done like this but if it is from a refering page you need to either use a $_GET or $_POST to populate it

$var=$subjectID;

$queryLastReply = "

SELECT MAX(actionID), name, subjectID, subject, timestamp

FROM onetable

WHERE subjectID='$var'

GROUP by subjectID

";

this is untested but it should work properly, otherwise you can simplify this greater by doing something like this:

$var=$subjectID;

$queryLastReply = "

SELECT actionID, name, subjectID, subject, timestamp

FROM onetable

WHERE subjectID='$var'

ORDER BY actionID DESC

";

this will order it by actionID and then sort it by the last result first, you can then do a single mysql_fetch_array() outside of a loop to get the single result...

rbragg · April 18, 2007

boo_lolly, I studied the other thread and incorporated a nested while loop into my code:

<?php
include 'shared/dbConnect.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 timestamp DESC
";
$firstResults = mysql_query($queryFirst) or die('The first 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 ($first = mysql_fetch_array($firstResults))
{
  echo "<tr><td align='center' class='style3'>";
  ?>
  <input type='radio' name='selected' value='<?php echo $first['subjectID'];?>'>
  <?php
  echo "</td><td class='style8'>";
  echo $first['subject'];
  echo "</td><td class='style3'>";
  echo $first['name'];
  echo "</td><td class='style3'>";   
  echo $first['timestamp'];
  echo "</td></tr>";  
  echo "<tr><td colspan='4' align='right' class='style3'><span class='style7'>";

  # setting up nested while
  # this query groups subjects together and displays the last post of each thread to show the last reply
  $queryLastReply = "
  SELECT MAX(actionID), name, subjectID, subject, timestamp
  FROM onetable
  WHERE subjectID = '". $first['subjectID'] ."'
  GROUP BY subjectID                                                            
  ";
  $lastResults = mysql_query($queryLastReply) or die('The last reply query failed: ' . mysql_error()); 

  while ($last = mysql_fetch_array($lastResults))
  {
    $reply = "<b>" . $last['subject'] . "</b> by " . $last['name'] . " at " . $last['timestamp'];
  }

  echo "last reply: </span>$reply</td></tr>";
  echo "<tr><td></td></tr>";
}
echo "</table>";
echo "</form>";

mysql_close;
?>

Now, both $queryFirst & $queryLastReply give me the last reply. Please note the example in example_messagedisplay.jpg.

[attachment deleted by admin]

boo_lolly · April 18, 2007

thankyou for providing such detailed illustrations. =) that really helps me figure out what's going on.

try this:

<?php
include 'shared/dbConnect.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 timestamp DESC
";
$firstResults = mysql_query($queryFirst) or die('The first 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 ($first = mysql_fetch_array($firstResults))
{
  echo "<tr><td align='center' class='style3'>";
  ?>
  <input type='radio' name='selected' value='<?php echo $first['subjectID'];?>'>
  <?php
  echo "</td><td class='style8'>";
  echo $first['subject'];
  echo "</td><td class='style3'>";
  echo $first['name'];
  echo "</td><td class='style3'>";   
  echo $first['timestamp'];
  echo "</td></tr>";  
  echo "<tr><td colspan='4' align='right' class='style3'><span class='style7'>";

  # setting up nested while
  # this query groups subjects together and displays the last post of each thread to show the last reply
  $queryLastReply = "
  SELECT actionID, name, subjectID, subject, MAX(timestamp)
  FROM onetable
  WHERE subjectID = '". $first['actionID'] ."'
  LIMIT 1                                                         
  ";
  $lastResults = mysql_query($queryLastReply) or die('The last reply query failed: ' . mysql_error()); 

  while ($last = mysql_fetch_array($lastResults))
  {
    $reply = "<b>" . $last['subject'] . "</b> by " . $last['name'] . " at " . $last['timestamp'];
  }

  echo "last reply: </span>$reply</td></tr>";
  echo "<tr><td></td></tr>";
}
echo "</table>";
echo "</form>";

mysql_close;
?>

that may work.

mpharo · April 18, 2007

$queryFirst = "

SELECT MIN(actionID), name, subjectID, subject, timestamp

FROM onetable

GROUP by subjectID

ORDER by timestamp DESC

";

You are ordering DESC which is last result first...I think you are trying to do too much in a single query with the results...try breaking it up into 2 queries...one to get the first/last result and another to get the data for that result....like this...

$queryFirst=mysql_query("SELECT MIN(actionID) , subjectID FROM onetable GROUP BY subjectID");

while($sqlFirst=mysql_fetch_array($queryFirst)) {

$queryFirstData=mysql_query("SELECT actionID, name, subjectID, subject, timestamp FROM onetable WHERE actionID='$sqlFirst[actionID]' AND subjectID='$sqlFirst[subjectID]'");

$sqlFirstData=mysql_fetch_array($queryFirstData);

echo "FIRST DATA";

echo "actionID: " . $sqlFirstData[actionID] . "subjectID: " . $sqlFirstData[actionID] . "subject: " . $sqlFirstData[subject] . "timestamp: " . $sqlFirstData[timestamp];

}

$queryLast="SELECT MAX(actionID) , subjectID FROM onetable GROUP BY subjectID";

while($sqlLast=mysql_fetch_array($queryLast)) {

$queryLastData=mysql_query("SELECT actionID, name, subjectID, subject, timestamp FROM onetable WHERE actionID='$sqlLast[actionID]' AND subjectID='$sqlLast[subjectID]'");

$sqlLastData=mysql_fetch_array($queryLastData);

echo "LAST DATA";

echo "actionID: " . $sqlLastData[actionID] . "subjectID: " . $sqlLastData[actionID] . "subject: " . $sqlLastData[subject] . "timestamp: " . $sqlLastData[timestamp];

}

Changed the code, I relaized in the min/max the () are in wrong spot

rbragg · April 18, 2007

thankyou for providing such detailed illustrations. =) that really helps me figure out what's going on.

If you are offering your help I'd like to be as clear as possible. It must be annoying to read such vague posts.

I've tried this method with the timestamp and again, without the GROUP BY clause I get:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

*reading mpharo's post*

rbragg · April 18, 2007

Thanks for your continued help, mpharo I've tried your method and the fetch_array does not seem to work at any point with my db structure. I see where you are heading but I don't think this method will work with the type of data I'm trying to extract.

boo_lolly · April 18, 2007

thankyou for providing such detailed illustrations. =) that really helps me figure out what's going on.

If you are offering your help I'd like to be as clear as possible. It must be annoying to read such vague posts.

you have no idea how much it pisses me off

anyway, i'm sorry i missed the GROUP BY clause... try this:

<?php
include 'shared/dbConnect.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 timestamp DESC
";
$firstResults = mysql_query($queryFirst) or die('The first 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 ($first = mysql_fetch_array($firstResults))
{
  echo "<tr><td align='center' class='style3'>";
  ?>
  <input type='radio' name='selected' value='<?php echo $first['subjectID'];?>'>
  <?php
  echo "</td><td class='style8'>";
  echo $first['subject'];
  echo "</td><td class='style3'>";
  echo $first['name'];
  echo "</td><td class='style3'>";   
  echo $first['timestamp'];
  echo "</td></tr>";  
  echo "<tr><td colspan='4' align='right' class='style3'><span class='style7'>";

  # setting up nested while
  # this query groups subjects together and displays the last post of each thread to show the last reply
  $queryLastReply = "
  SELECT actionID, name, subjectID, subject, MAX(timestamp)
  FROM onetable
  WHERE subjectID = '". $first['actionID'] ."'
  GROUP BY subjectID
  LIMIT 1                                                         
  ";
  $lastResults = mysql_query($queryLastReply) or die('The last reply query failed: ' . mysql_error()); 

  while ($last = mysql_fetch_array($lastResults))
  {
    $reply = "<b>" . $last['subject'] . "</b> by " . $last['name'] . " at " . $last['timestamp'];
  }

  echo "last reply: </span>$reply</td></tr>";
  echo "<tr><td></td></tr>";
}
echo "</table>";
echo "</form>";

mysql_close;
?>

all i did was add a GROUP BY clause to the second query.

rbragg · April 19, 2007

In my many desperate attempts, I did add the GROUP BY clause. I just tried it a second time and it does not echo any values. I just get "last reply: ". Since I'm not getting the "by" or "at" that are in the 2nd while statement I assume the problem is still in $queryLastReply. :'(

boo_lolly · April 19, 2007

see what this gives you:

<?php
include 'shared/dbConnect.php';

# this query groups subjects together and displays the first post of each thread
$queryFirst = "
	SELECT DISTINCT
		subjectID,
		actionID,
		name,
		subject,
		timestamp
	FROM
		onetable
	ORDER BY
		timestamp
	DESC
";
$firstResults = mysql_query($queryFirst) or die('The first 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($first = mysql_fetch_array($firstResults)){
  		echo "
		<tr><td align='center' class='style3'>
  			<input type='radio' name='selected' value='". $first['subjectID'] ."'></td>
		<td class='style8'>". $first['subject'] ."</td>
		<td class='style3'>". $first['name'] ."</td>
		<td class='style3'>". $first['timestamp'] ."</td></tr>
  			<tr><td colspan='4' align='right' class='style3'><span class='style7'>
	\n";

	# this query groups subjects together and displays the last post of each thread to show the last reply
	$queryLastReply = "
		SELECT
			actionID,
			name,
			subjectID,
			subject,
			MAX(timestamp)
		FROM
			onetable
		WHERE
			subjectID = '". $first['actionID'] ."'
		GROUP BY
			subjectID
		LIMIT 1
	";
	$lastResults = mysql_query($queryLastReply) or die('The last reply query failed: ' . mysql_error());

	while($last = mysql_fetch_array($lastResults)){
		$reply = "<b>" . $last['subject'] . "</b> by " . $last['name'] . " at " . $last['timestamp'];
	}
	echo "last reply: </span>$reply</td></tr>";
	echo "<tr><td></td></tr>";
}
echo "</table>";
echo "</form>";

mysql_close;
?>

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information