how to compare 2 mysql rows

MySQL database
#1
field1 field2

458 457
487 854
477 475

the primary id of this table is timestamp.

how can select the 2 top max timestamp and get get the difference between in that 2 column?

#2

Not sure I understand. If you just want the difference between those 2 fields.

SELECT ABS(`field1` - `field2`) AS `diff`
#3

no not the difference between those 2 fields but the differece between the 2 rows

field1 field2

458 457
487 854
477 475

i will run a query that will select 2 rows only and i want to compare the values in field1 for that 2 rows, on field1 compare 458 with 487 and the difference is 29 the same would be for field2 397

#4

Just select the 2 rows and do your comparison in PHP. If you wanted to do this with a query it would have 2 subqueries. (which equates to 3 queries)

#5

I think can select the 2 latest record with the following query.

select top 2 timestamp, field1,field2 from tablename

But how can i get difference in php?

Field1 and field2 are prices for sometimes items in my database. These items change prices everyday.

I dont know how to do it. I always try things by myself first but i dont even have an idea on how to do it.

#6

Well your timestamp should always increment, so you should be able to use ORDER BY

For example:

SELECT `timestamp`, `field1`, `field2` FROM `table` ORDER BY `timestamp` DESC LIMIT 2

Then in PHP you could just access the rows using the keys for each row (depending how you fetch them). For example:

[php]$diff = abs($row[0][‘field1’] - $row[1][‘field1’]);[/php]

#7

i tried to feed $row with mysql_fetch_row($result),mysql_fetch_array($result), mysql_fetch_assoc($result)

none work

#8

maybe i was doing something wrong Matt, but i manage to get the result i wanted using another method.

I came up with the following

[php]
$row1 = mysql_fetch_assoc(mysql_query(“SELECT super_attack, fish_mask FROM runescape_merching ORDER BY timestamp DESC LIMIT 1”)) or die(mysql_error());
$row2 = mysql_fetch_assoc(mysql_query(“SELECT super_attack, fish_mask FROM runescape_merching WHERE timestamp < (SELECT MAX(timestamp) FROM runescape_merching) ORDER BY timestamp DESC LIMIT 1”)) or die(mysql_error());

$diff = abs($row1[‘super_attack’] - $row2[‘super_attack’]);
[/php]

basically what im doing is get the latest record and on a second query i get the record before that lastest record.

Thanks Matt, gave u a Karma + for the query

#9

What you are doing here:

[php]
$row1 = mysql_fetch_assoc(mysql_query(“SELECT super_attack, fish_mask FROM runescape_merching ORDER BY timestamp DESC LIMIT 1”)) or die(mysql_error());
$row2 = mysql_fetch_assoc(mysql_query(“SELECT super_attack, fish_mask FROM runescape_merching WHERE timestamp < (SELECT MAX(timestamp) FROM runescape_merching) ORDER BY timestamp DESC LIMIT 1”)) or die(mysql_error());
[/php]

Is essentially the same thing if you were to do this:

[php]
$row1 = mysql_fetch_assoc(mysql_query(“SELECT super_attack, fish_mask FROM runescape_merching ORDER BY timestamp DESC LIMIT 0,1”)) or die(mysql_error());
$row2 = mysql_fetch_assoc(mysql_query(“SELECT super_attack, fish_mask FROM runescape_merching ORDER BY timestamp DESC LIMIT 1,1”)) or die(mysql_error());
[/php]

Without the subquery.

#10

yea haha exactly the same althought i think urs is faster, is it?

#11

Using less queries is always faster. You are doing 3 queries when you should be able to use one. For example:

[php]
$sql = mysql_query(“SELECT super_attack, fish_mask FROM runescape_merching ORDER BY timestamp DESC LIMIT 2”)or die(mysql_error());

$rows = array();
while($row = mysql_fetch_assoc($sql)) {
$rows[] = $row;
}
$diff = abs($row[0][‘field1’] - $row[1][‘field1’]);
[/php]

#12

shouldn’t it be?
[php]
$diff = abs($rows[0][‘field1’] - $rows[1][‘field1’]);
[/php]

#13

Yes sorry I copied my code from above :wink:

#14

thank you that worked pretty well.

Sponsor our Newsletter | Privacy Policy | Terms of Service