MySQL: Number of subsequent occurrences

I have a table with four columns: id, from, to, msg.

The rows can look like this:

1 - foo - bar - hello
2 - foo - bar - zup?
3 - bar - foo - hi
4 - bar - foo - going okay
5 开发者_如何学Go- bar - foo - you?

Now I wanna know how many times "bar" has tried to "talk" to "foo" without response. Like I wanna count the number of occurrences since "foo" was on the sending end.

In this example, before the next entry, it should return 3.

Is this possible in pure (My)SQL?

I'm using php on the server side.

Thanks for any tips and advice! =)

Give this a go.

It assumes a table name of convo and the id is autoincrementing.

This is just the mysql but the PHP calls can be added quite cleanly if you wrapped it in a function that passes in the from and to variables.

SELECT 
  count(*) AS unreplied
FROM
  `convo`
WHERE
  `convo`.`from` = 'bar' AND
  `convo`.`to` = 'foo' AND
  `convo`.`id` > (SELECT
                    id
                  FROM
                    `convo`
                  WHERE
                    `convo`.`from` = 'foo' AND
                    `convo`.`to` = 'bar'
                  ORDER BY
                    `convo`.`id` DESC
                  LIMIT 1)

var1 = select id from table where fromname = "user1" and toname = "user2" order by id desc limit 1
var2 = select id from table where fromname = "user2" and toname = "user1" order by id desc limit 1

return var2-var1;

$sql = "SELECT `fieldone` FROM `table` WHERE `fieldone` = 'foo'";

$results = mysql_query($sql);

$FooSendNumber = mysql_num_rows($results);

or

$FooSendNumber = mysql_num_rows(mysql_query($sql));

Oh wait, you want to see pure MySQL too.

Yeah, it's possible:

$sql = "SELECT MYSQL_CALC_FOUND_ROWS `fieldone` FROM `table` WHERE `fieldone` = 'foo';SELECT FOUND_ROWS();"

Should work.

Oh wait, crap, I see, you wanted the number of times without response.