php - MySQL: Number of subsequent occurrences

570

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 - 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! =)

Answer

Solution:

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)

779

Answer

Solution:

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;

821

Answer

Solution:

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

$results = mysql_query($sql);

$FooSendNumber = mysql_num_rows($results);

$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.

People are also looking for solutions to the problem: php - Retrieving JSON data from webhook

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question