Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > PHP: Общие вопросы > Нужна помощь при разборе данных из бд mysql


Автор: jone31 12.7.2010, 22:01
всех приветствую.

при подсчете времени которое пользователь был онлайн, возникла следующая проблемма:
Есть БД MySQL, в ней таблица с логами входов/выходов юзеров, Далее я вытаскиваю из неё записи нужного мне юзера, за нужный промежуток времени, но вот есть трудность: запись входа пользователя обозначается как STAT_IN, а выхода STAT_OUT, и мне нужно получить промежуток времени между двумя этими записями, но как?

насколько мне известно узнать следующий ход цикла, не прерывая предыдущий нельзя. 

слышал про перебор массивов, но никогда ещё с ним не работал, может как то с помощью него можно реализовать это?

Автор: нуп 12.7.2010, 22:47
Если правильно понял  smile , но причем здесь массивы  smile 
Код

$str = strtotime('2010-07-10 11:45:01');
$str2 = strtotime('2010-07-10 12:45:01');
echo $str2 - $str;

Результат в милисекундах. Дальше сам переводи

Автор: CruorVult 13.7.2010, 09:41
Цитата(нуп @  12.7.2010,  22:47 Найти цитируемый пост)
Результат в милисекундах

а разве не в секундах?

Автор: jone31 13.7.2010, 10:02
Цитата(нуп @  12.7.2010,  22:47 Найти цитируемый пост)
Если правильно понял   , но причем здесь массивы   
нет, алгоритм вычисления времени я уже написал. проблеммма не в этом.

попробую ещё раз описать проблемму:

вот таблица mysql:
Код
CREATE TABLE `log` (
  `id` bigint(20) NOT NULL auto_increment,
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,   ---- здесь время которое мне нужно узнать
  `user_id` int(11) default NULL,
  `user_sn` varchar(50) default NULL, 
  `type` varchar(10) default NULL,   ---- здесь находятся значения STATE_IN и STATE_OUT
  `msg` text,
  `room` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
);
Мне нужно узнать промежуток времени(по полю `time`) между значениями поля `type` - STATE_IN и ближайшим к нему STATE_OUT

Автор: нуп 13.7.2010, 10:08
Цитата(CruorVult @  13.7.2010,  09:41 Найти цитируемый пост)
а разве не в секундах? 

Да, пардон ошибся.

Добавлено через 8 минут и 44 секунды
jone31, \ у Вас хранится по 2 записи в таблице, когда юзер пришел и когда он ушел? 

Автор: ksnk 13.7.2010, 14:16
jone31, 
Разумнее было бы делать время прихода - ухода в одной строке таблицы. Так проблем с вычислениями не было бы. соотвестственно, при выходе юзера - ведем поиск записи входа этого  с максимальным временем и вписываем ему время выхода. Или вообще при любом телодвижении юзера вписываем ему время выхода "time()+10 минут", а то реальный выход с сайта ловить достаточно нетривиально.

если на каждую запись "прихода" юзера есть соответствующая запись "ухода" - можно построить запрос с join'ом, который всю таблицу вернет в нужном виде. Что такое user_sn - сессия юзера? если так, то запрос может быть примерно такой
Код

SELECT log . time , log1 . time 
FROM  `log` 
LEFT JOIN  `log` AS log1 ON log.user_sn = log1.user_sn where log.type="STATE_IN" and log1.type="STATE_OUT"

Только в таблицу нужно будет добавить ключей - в user_sn  и в type? иначе будет тормозить ... 

Автор: jone31 13.7.2010, 20:25
ksnk, речь идет не о сайте, а о чат-боте. `user_sn` - это ICQ номер с которого выполнен вход(он тоже уникален, но у одного пользователя их может быть несколько, поэтому по `user_id` ловить легче) 

нуп, нет, пользователь вошел и в лог пишется что он зашел, если он сам набрал команду выхода то в лог об этом записали. Но есть такие записи, где есть запись входа, но нету записи выхода(т.к. пользователя выкинуло из ICQ, вышел из аськи не выходя из чата и т.д.), такие записи в расчет не берутся.  Берутся только парные записи, т.е. вход/выход.

вот небольшой(150 строк) кусочек таблицы(поля user_sn и msg вырезаны):
Код

INSERT INTO `log` (`id`, `time`, `user_id`, `type`, `room`) VALUES
(1352404, '2010-07-06 19:29:12', 1915, 'OUT', 0),
(1352405, '2010-07-06 19:29:13', 2846, 'OUT', 0),
(1352406, '2010-07-06 19:29:19', 3877, 'OUT', 0),
(1352407, '2010-07-06 19:29:30', 1915, 'OUT', 0),
(1352408, '2010-07-06 19:29:47', 3644, 'OUT', 0),
(1352409, '2010-07-06 19:29:48', 1944, 'OUT', 0),
(1352410, '2010-07-06 19:29:50', 2846, 'OUT', 0),
(1352411, '2010-07-06 19:30:08', 1915, 'OUT', 0),
(1352412, '2010-07-06 19:30:13', 3892, 'STATE_OUT', 0),
(1352413, '2010-07-06 19:30:27', 2846, 'OUT', 0),
(1352414, '2010-07-06 19:30:27', 1915, 'OUT', 0),
(1352415, '2010-07-06 19:30:30', 1944, 'OUT', 0),
(1352416, '2010-07-06 19:30:41', 3877, 'OUT', 0),
(1352417, '2010-07-06 19:30:43', 1954, 'OUT', 0),
(1352418, '2010-07-06 19:30:47', 3644, 'OUT', 0),
(1352419, '2010-07-06 19:30:48', 1915, 'OUT', 0),
(1352420, '2010-07-06 19:31:03', 2846, 'OUT', 0),
(1352421, '2010-07-06 19:31:06', 1954, 'OUT', 0),
(1352422, '2010-07-06 19:31:21', 3875, 'OUT', 0),
(1352423, '2010-07-06 19:31:23', 3644, 'OUT', 0),
(1352424, '2010-07-06 19:31:31', 3566, 'STATE_IN', 0),
(1352425, '2010-07-06 19:31:33', 3644, 'OUT', 0),
(1352426, '2010-07-06 19:31:35', 1944, 'OUT', 0),
(1352427, '2010-07-06 19:31:52', 2846, 'OUT', 0),
(1352428, '2010-07-06 19:32:03', 2846, 'STATE_OUT', 0),
(1352429, '2010-07-06 19:32:14', 3877, 'OUT', 0),
(1352430, '2010-07-06 19:32:16', 1954, 'OUT', 0),
(1352431, '2010-07-06 19:32:17', 1915, 'OUT', 0),
(1352432, '2010-07-06 19:32:28', 3878, 'STATE_IN', 0),
(1352433, '2010-07-06 19:32:32', 3644, 'OUT', 0),
(1352434, '2010-07-06 19:32:40', 3644, 'STATE_OUT', 0),
(1352435, '2010-07-06 19:32:43', 3877, 'OUT', 0),
(1352436, '2010-07-06 19:32:53', 1915, 'OUT', 0),
(1352437, '2010-07-06 19:32:58', 3566, 'OUT', 0),
(1352438, '2010-07-06 19:32:59', 3892, 'STATE_IN', 0),
(1352439, '2010-07-06 19:33:05', 3892, 'OUT', 0),
(1352440, '2010-07-06 19:33:14', 1944, 'OUT', 0),
(1352441, '2010-07-06 19:33:29', 3878, 'OUT', 0),
(1352442, '2010-07-06 19:33:31', 1954, 'OUT', 0),
(1352443, '2010-07-06 19:33:36', 1944, 'STATE_OUT', 0),
(1352444, '2010-07-06 19:33:44', 3875, 'OUT', 0),
(1352445, '2010-07-06 19:33:49', 3892, 'OUT', 0),
(1352446, '2010-07-06 19:33:54', 3922, 'OUT', 0),
(1352447, '2010-07-06 19:33:59', 3892, 'OUT', 0),
(1352448, '2010-07-06 19:34:03', 2204, 'STATE_IN', 0),
(1352449, '2010-07-06 19:34:07', 1915, 'OUT', 0),
(1352450, '2010-07-06 19:34:08', 1954, 'OUT', 0),
(1352451, '2010-07-06 19:34:23', 2204, 'OUT', 0),
(1352452, '2010-07-06 19:34:47', 3878, 'OUT', 0),
(1352453, '2010-07-06 19:34:55', 3875, 'OUT', 0),
(1352454, '2010-07-06 19:35:19', 1954, 'OUT', 0),
(1352455, '2010-07-06 19:35:21', 3923, 'STATE_OUT', 0),
(1352456, '2010-07-06 19:35:21', 1915, 'OUT', 0),
(1352457, '2010-07-06 19:35:26', 3566, 'STATE_OUT', 0),
(1352458, '2010-07-06 19:35:27', 3892, 'OUT', 0),
(1352459, '2010-07-06 19:35:48', 2204, 'OUT', 0),
(1352460, '2010-07-06 19:36:05', 1915, 'OUT', 0),
(1352461, '2010-07-06 19:36:11', 3878, 'OUT', 0),
(1352462, '2010-07-06 19:36:18', 205, 'STATE_IN', 0),
(1352463, '2010-07-06 19:36:20', 3828, 'STATE_IN', 0),
(1352464, '2010-07-06 19:36:27', 3922, 'OUT', 0),
(1352465, '2010-07-06 19:36:28', 3493, 'STATE_IN', 0),
(1352466, '2010-07-06 19:36:34', 3877, 'OUT', 0),
(1352467, '2010-07-06 19:36:36', 1915, 'OUT', 0),
(1352468, '2010-07-06 19:36:41', 1954, 'OUT', 0),
(1352469, '2010-07-06 19:36:43', 205, 'OUT', 0),
(1352470, '2010-07-06 19:36:48', 3892, 'OUT', 0),
(1352471, '2010-07-06 19:37:09', 3922, 'OUT', 0),
(1352472, '2010-07-06 19:37:28', 3493, 'OUT', 0),
(1352473, '2010-07-06 19:37:33', 1915, 'OUT', 0),
(1352474, '2010-07-06 19:37:33', 3878, 'OUT', 0),
(1352475, '2010-07-06 19:37:34', 1954, 'OUT', 0),
(1352476, '2010-07-06 19:38:18', 205, 'OUT', 0),
(1352477, '2010-07-06 19:38:21', 3828, 'OUT', 0),
(1352478, '2010-07-06 19:38:40', 3892, 'OUT', 0),
(1352479, '2010-07-06 19:38:48', 1915, 'OUT', 0),
(1352480, '2010-07-06 19:38:49', 3493, 'OUT', 0),
(1352481, '2010-07-06 19:39:00', 3878, 'OUT', 0),
(1352482, '2010-07-06 19:39:07', 1954, 'OUT', 0),
(1352483, '2010-07-06 19:39:16', 3892, 'OUT', 0),
(1352484, '2010-07-06 19:39:20', 205, 'OUT', 0),
(1352485, '2010-07-06 19:39:28', 2204, 'STATE_OUT', 0),
(1352486, '2010-07-06 19:39:42', 1685, 'STATE_IN', 0),
(1352487, '2010-07-06 19:39:42', 3493, 'OUT', 0),
(1352488, '2010-07-06 19:39:45', 1954, 'OUT', 0),
(1352489, '2010-07-06 19:39:53', 3878, 'OUT', 0),
(1352490, '2010-07-06 19:39:55', 3828, 'OUT', 0),
(1352491, '2010-07-06 19:40:04', 205, 'OUT', 0),
(1352492, '2010-07-06 19:40:07', 1915, 'OUT', 0),
(1352493, '2010-07-06 19:40:12', 1954, 'OUT', 0),
(1352494, '2010-07-06 19:40:16', 1685, 'OUT', 0),
(1352495, '2010-07-06 19:40:20', 1915, 'OUT', 0),
(1352496, '2010-07-06 19:40:22', 1954, 'OUT', 0),
(1352497, '2010-07-06 19:40:46', 3878, 'OUT', 0),
(1352498, '2010-07-06 19:40:53', 3828, 'STATE_OUT', 0),
(1352499, '2010-07-06 19:41:01', 3493, 'OUT', 0),
(1352500, '2010-07-06 19:41:12', 3875, 'OUT', 0),
(1352501, '2010-07-06 19:41:14', 3877, 'OUT', 0),
(1352502, '2010-07-06 19:41:31', 205, 'OUT', 0),
(1352503, '2010-07-06 19:41:32', 1954, 'OUT', 0);

STATE_IN и STATE_OUT - вход/выход
значение OUT  - сообщение

Автор: jone31 14.7.2010, 19:35
ну неужели это невозможное действие?

Автор: bazzjr 15.7.2010, 06:16
jone31, если я  правильно понял, то тебе нужно посчитать время из поля `time` до настоящего времени при `type`='STATE_OUT' ?

Если так то:

Код

SELECT `time`, DATE_FORMAT(time, '%d-%m-%Y %H:%i:%m') AS `time` FROM `log` WHERE `type`='STATE_OUT'


теперь имеем к примеру в переменной $timeUserIn значение из базы
дальше можно вычислить сколько он пробыл онлайн :

Код

$dateDiff = time()-strtotime($timeUserIn['date_enter']);

$fullDays    = floor($dateDiff/(60*60*24));
$fullHours   = floor(($dateDiff-($fullDays*60*60*24))/(60*60));
$fullMinutes = floor(($dateDiff-($fullDays*60*60*24)-($fullHours*60*60))/60);

echo "Дней - ".$fullDays."<br /> Часов- ".$fullHours."<br /> Минут- ".$fullMinutes;



Ну или же возьми две других даты из базы STAT_IN и STAT_OUT.   smile

Автор: bazzjr 15.7.2010, 06:32
Немного подумав... наверно решение ksnk будет правильным.

Чем оно не утраивает?

Автор: ksnk 15.7.2010, 09:37
bazzjr, тем, что поля в базе имеют другой смысл smile

jone31, Выбраная схема и дисциплина огранизации хранения данных не предполагает их реляционную обработку.  smile 
Я не смог состряпать разумно выглядящий запрос, который бы сам все делал и считал для пользоателя время, проведенное им на сайте.
Разве что  выбрать все STATE_OUT и STATE_IN для пользователя, а потом вручную отсортировав  по возрастанию времен, найти соответствующие пары и посчитать разницу. 

Хотя, возможно я просто не понял задачу  smile если нужно просто понять - сколько пользователь сидит в онлайне в эту сессию (от последнего незакрытого STATE_IN), то запрос относительно простой, хотя, тоже только полуавтомат
Код

SELECT UNIX_TIMESTAMP(time) as ts, `type` 
FROM `log` WHERE 
`type` in ('STATE_IN','STATE_OUT') and user_id=3892 
order by `time` DESC limit 1

результат нужно проверить. если первая строчка результата - type==STATE_IN, то все в порядке, сессия не закрыта. время в онлайне будет time()-`ts`

Автор: jone31 15.7.2010, 11:49
Цитата(ksnk @  15.7.2010,  09:37 Найти цитируемый пост)
jone31, Выбраная схема и дисциплина огранизации хранения данных не предполагает их реляционную обработку.   Я не смог состряпать разумно выглядящий запрос, который бы сам все делал и считал для пользоателя время, проведенное им на сайте.

а я и не просил и даже сам не думал обойтись одним sql.

Мой алгоритм поначалу был такой: вывести все STATE_IN и STATE_OUT в массив, и потом путем перебора массива сделать новый массив где они все разложены в пары, а лишнее все убрано. но пока что ничего не получается.

Автор: jone31 15.7.2010, 12:22
мне нужно узнать сколько он всего просидел в чате по данным лога.

Автор: ksnk 15.7.2010, 14:16
jone31, тот-же запрос, но limit 2.
Это даст нам пару значений - вход и выход юзера. если первая запись - вход - юзер еще сидит в онлайне. Если первая запись - выход, а вторая - вход, то сколько он сидел можно посчитать отняв первое значение времени от второго. При других значениях ответа база некорректна, надо бежать жаловаться создателю...  smile 

Автор: jone31 15.7.2010, 14:21
суть проблеммы неправильна понята.

там записей вход/выход тысячи. нужно их все сгрупировать по вход/выход, найти разницу во времени и все это сложить. smile  там получится онлайн у каждого пользовтаеля самое малое - 2 месяца(это я вручную прикинул)

Автор: нуп 15.7.2010, 15:08
Ну так чисти базу, заем тебе эти данные. И проще сделать 1 запись, кде будет 2 колонки - вход и выход  smile 

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)