[SOLVED] Is there any way

[almightyegg]

to select the highest number ina specific column, eg.

ROW | COL 1

1 12

2 400

3 2

4 94

etc...

 

I want to select the row with the highest number, so in this case I would select row2. Can it be done??

[trq]

SELECT MAX(fld) FROM tbl;

[Barand]

If you want the whole row which contains the max value

 

SELECT * FROM tablename

WHERE col1 = (SELECT MAX(col1) FROM tablename)

[almightyegg]

Sorry about the long reply, I haven't been able to get to a computer.

This is what I'm using it for, there is no errors echoed out but it wasn't going into the table correctly so I decided I would echo $max itself. It shows 'Resource id #20'.

<?
$max = mysql_query("SELECT MAX(id) FROM topics WHERE fid = '$f'") or die(mysql_error());
echo "$max";
$fn = is_numeric($f);
if($fn == TRUE){
$update = mysql_query("UPDATE users SET sboard = '$max' WHERE username = '{$mem['username']}'");
}else{
$update = mysql_query("UPDATE users SET $f = '$max' WHERE username = '{$mem['username']}'");
}
?>

 

If you could help me resolve this I would be extremely thankful

[Barand]

$res= mysql_query("SELECT MAX(id) FROM topics WHERE fid = '$f'") or die(mysql_error());
$max = mysql_result($res, 0,0);
echo "$max";

[almightyegg]

Great that worked But now, there's an error elsewhere....

 

$count = $mem['$forumid'];

$count = mysql_query("SELECT * FROM topics WHERE id > '$count' AND fid = '$forumid'") or die(mysql_error());

$count = mysql_num_rows($count);

 

This code is to see how many new messages there has been since the user last looked at the board by counting the amount of row ids that are greater than the number stored in the user's row (the highest id from the last time the board was viewed). So basically, if the highest id when the board was last viewed by me was 342 and now it is 396 it would say 'Board Title 54 NEW'.

Now the problem is that the last viewed id has been stored successfully, which is 8 on my tester board and it's still 8 so it should say 'Board Title' but instead it says 'Board Title 8 NEW'. I don't understand why though. I hope somebody can shed some light onto this problem for me

 

Sorry if I've confused you with my babbling  :-X

[Barand]

Remove the quotes around '$forum id' - the code you have gives a count = 0;

 

That's a very inefficient way of doing it. Also better strategy would be to store the DATETIME of last visit and count posts after that time.

 

$time = $lastvisited;

$res = mysql_query("SELECT COUNT(*) FROM topics WHERE dateposted > '$time' AND fid = '$forumid' ");

$count = mysql_result($res,0,0);

 

That way the query only needs to fetch a single value, not 54 rows of data.

[almightyegg]

I had tried that previously but was unable to make it work...

 

and stil this way isn't working...

$count = $mem['$forumid'];

$count = mysql_query("SELECT * FROM topics WHERE id>'$count' AND fid=$forumid") or die(mysql_error());

$count = mysql_num_rows($count);

It now displays 'Unknown column 'gdis' in 'where clause'' but that doesn't make sense as I'm not naming a column as gdis (gdis being $forumid) am calling the column called fid and looking for rows which have that column filled as gdis  ??? I'm so confused :S

[Barand]

$count = $mem[$forumid];  <-- No quotes!

 

or

 

$count = $mem['forumid'];  <-- No $

 

One or the other - I have no way of knowing which you really mean.

[almightyegg]

Still get this: Unknown column 'gdis' in 'where clause'

I've been playing around with it, putting 's and `s around, but to no avail

 

$count = $mem[$forumid];
$count = mysql_query("SELECT * FROM topics WHERE `id` > '$count' AND `fid` = $forumid") or die(mysql_error());
$count = mysql_num_rows($count);

 

I can't see anything wrong. If I remove the quotes from around the $count in line 2 it makes no difference.... ???

[dg]

$count = $mem[$forumid];

$count = mysql_query("SELECT * FROM topics WHERE id > $count AND fid = $forumid") or die(mysql_error());

$count = mysql_num_rows($count);

[almightyegg]

No change...

[almightyegg]

Bump^^^

[Barand]

if "gdis" is the forum id, then you need quotes around $forumid as it is a string value

 

mysql_query("SELECT * FROM topics WHERE `id` > '$count' AND `fid` = '$forumid' ") or die(mysql_error());