Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: [mysql] jak wybrac rekordy "z pomiedzy"
Forum PHP.pl > Forum > PHP
AndyPSV
5.11.2010, 12:34:37
Jak wybrac te rekordy "spomiedzy" logd: 1 do 1?

[SQL] pobierz, plaintext 
  1. 	id_u	date 	ip	logd
  2. 			1	2010-11-05 12:17:34	127.0.0.1	1 /**** to pominac
  3. 			1	2010-11-05 12:17:29	127.0.0.1	0 /*** OD TAD
  4. 			1	2010-11-05 12:17:19	127.0.0.1	0
  5. 			1	2010-11-05 12:17:18	127.0.0.1	0
  6. 			1	2010-11-05 12:17:17	127.0.0.1	0
  7. 			1	2010-11-05 12:17:16	127.0.0.1	0
  8. 			1	2010-11-05 12:17:15	127.0.0.1	0
  9. 			1	2010-11-05 12:17:14	127.0.0.1	0
  10. 			1	2010-11-05 12:17:13	127.0.0.1	0
  11. 			1	2010-11-05 12:17:13	127.0.0.1	0 **** DO TAD WYBRAC TE REKORDY */
  12. 			1	2010-11-05 12:17:12	127.0.0.1	1
[SQL] pobierz, plaintext
markonix
5.11.2010, 12:40:16
Próbowałeś z between lub po prostu WHERE date > $data AND date < $data_2 ?

Edit: chyba nie zrozumiałem o co ma się opierać. Jeśli o logd to po prostu zwykłe WHERE logd = 0
pedro84
5.11.2010, 12:43:53
Czyli pomijać pierwszy i ostatnia?

PS. Stary: odtąd, dotąd - bo to wstyd.
AndyPSV
25.11.2010, 11:28:52
@markonix: to nie jest "sztywne", tzn. ze sytuacja sie zmienia, wiec daty nie wchodza w gre
@pedro84: tak, pomijac - chodzi o wybranie wszystkich rekordow "odtad", "dotad", tych pomiedzy 1 : 1, tak jak napisalem w poscie

w sytuacji, takiej - dalej ma wybierac te z wartoscia "0", ale TYLKO do pierwszego wystapienia "1" (znowu):

id_u date ip logd
[SQL] pobierz, plaintext 
  1.                         1	2010-11-05 12:17:34	127.0.0.1	1
  2.                         1	2010-11-05 12:17:34	127.0.0.1	1
  3.  
  4. 			1	2010-11-05 12:17:34	127.0.0.1	1 /**** to pominac
  5. 			1	2010-11-05 12:17:29	127.0.0.1	0 /*** ODTAD
  6. 			1	2010-11-05 12:17:19	127.0.0.1	0
  7. 			1	2010-11-05 12:17:18	127.0.0.1	0
  8. 			1	2010-11-05 12:17:17	127.0.0.1	0
  9. 			1	2010-11-05 12:17:16	127.0.0.1	0
  10. 			1	2010-11-05 12:17:15	127.0.0.1	0
  11. 			1	2010-11-05 12:17:14	127.0.0.1	0
  12. 			1	2010-11-05 12:17:13	127.0.0.1	0
  13. 			1	2010-11-05 12:17:13	127.0.0.1	0 **** DOTAD WYBRAC TE REKORDY */
  14. 			1	2010-11-05 12:17:12	127.0.0.1	1
  15.  
  16.                         1	2010-11-05 12:17:34	127.0.0.1	1
  17.                         1	2010-11-05 12:17:34	127.0.0.1	0
[SQL] pobierz, plaintext


takie cos, mi wstawili na forum mysql:

[SQL] pobierz, plaintext 
  1. $q = q('select i.id_u, i.`date`, i.ip, i.logd
  2.   from `'.PRFX.'logd` i
  3.     join (select min(`date`) minDate, max(`date`) maxDate
  4.             from `'.PRFX.'logd`
  5.             where logd = 1) m
  6.       on i.`date` > m.minDate
  7.         and i.`date` < m.maxDate;'); IF(n_r($q) > 0) $logd = f($q); var_export($logd);
[SQL] pobierz, plaintext


jednak zawsze wyswietla sie tylko 1 rekord, moze ktos wie jak to poprawic?

After doing the query, as you've supplied (sorry for the very late reply, but I was focused on more important things):

BTW. I want to get that effect, I've done an image: http://img255.imageshack.us/img255/8615/issue.gif

$q = q('select i.id_u, i.`date`, i.ip, i.logd
from `'.PRFX.'logd` i
join (select min(`date`) minDate, max(`date`) maxDate
from `'.PRFX.'logd`
where logd = 1) m
on i.`date` > m.minDate
and i.`date` < m.maxDate;'); if(n_r($q) > 0) $logd = f($q); var_export($logd);

I get:


array (
'id_u' => '1',
'date' => '2010-11-05 11:54:14',
'ip' => '127.0.0.1',
'logd' => '0',
)

----

Maybe I'm doing something wrong? (badly updated the "table names" or something)?

----
HERE IS MY TABLE

CREATE TABLE `chcur_logd` (
`id_u` int(11) NOT NULL,
`date` datetime NOT NULL,
`ip` varchar(255) NOT NULL,
`logd` tinyint(1) NOT NULL,
KEY `id_u` (`id_u`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;

--
-- Dumping data for table `chcur_logd`
--

INSERT INTO `chcur_logd` (`id_u`, `date`, `ip`, `logd`) VALUES
(1, '2010-11-05 11:03:32', '127.0.0.1', 0),
(1, '2010-11-05 11:03:52', '127.0.0.1', 0),
(1, '2010-11-05 11:03:56', '127.0.0.1', 1),
(1, '2010-11-05 11:54:14', '127.0.0.1', 0),
(1, '2010-11-05 11:54:17', '127.0.0.1', 1),
(1, '2010-11-05 12:16:50', '127.0.0.1', 0),
(1, '2010-11-05 12:16:51', '127.0.0.1', 0),
(1, '2010-11-05 12:16:52', '127.0.0.1', 0),
(1, '2010-11-05 12:16:53', '127.0.0.1', 0),
(1, '2010-11-05 12:16:54', '127.0.0.1', 0),
(1, '2010-11-05 12:16:54', '127.0.0.1', 0),
(1, '2010-11-05 12:16:55', '127.0.0.1', 0),
(1, '2010-11-05 12:16:55', '127.0.0.1', 0),
(1, '2010-11-05 12:16:56', '127.0.0.1', 0),
(1, '2010-11-05 12:16:57', '127.0.0.1', 0),
(1, '2010-11-05 12:16:57', '127.0.0.1', 0),
(1, '2010-11-05 12:16:58', '127.0.0.1', 0),
(1, '2010-11-05 12:16:58', '127.0.0.1', 0),
(1, '2010-11-05 12:16:59', '127.0.0.1', 0),
(1, '2010-11-05 12:17:00', '127.0.0.1', 0),
(1, '2010-11-05 12:17:02', '127.0.0.1', 0),
(1, '2010-11-05 12:17:03', '127.0.0.1', 0),
(1, '2010-11-05 12:17:03', '127.0.0.1', 0),
(1, '2010-11-05 12:17:04', '127.0.0.1', 0),
(1, '2010-11-05 12:17:05', '127.0.0.1', 0),
(1, '2010-11-05 12:17:06', '127.0.0.1', 0),
(1, '2010-11-05 12:17:06', '127.0.0.1', 0),
(1, '2010-11-05 12:17:07', '127.0.0.1', 0),
(1, '2010-11-05 12:17:08', '127.0.0.1', 0),
(1, '2010-11-05 12:17:09', '127.0.0.1', 0),
(1, '2010-11-05 12:17:10', '127.0.0.1', 0),
(1, '2010-11-05 12:17:11', '127.0.0.1', 0),
(1, '2010-11-05 12:17:12', '127.0.0.1', 1),
(1, '2010-11-05 12:17:13', '127.0.0.1', 0),
(1, '2010-11-05 12:17:13', '127.0.0.1', 0),
(1, '2010-11-05 12:17:14', '127.0.0.1', 0),
(1, '2010-11-05 12:17:15', '127.0.0.1', 0),
(1, '2010-11-05 12:17:16', '127.0.0.1', 0),
(1, '2010-11-05 12:17:17', '127.0.0.1', 0),
(1, '2010-11-05 12:17:18', '127.0.0.1', 0),
(1, '2010-11-05 12:17:19', '127.0.0.1', 0),
(1, '2010-11-05 12:17:29', '127.0.0.1', 0),
(1, '2010-11-05 12:17:34', '127.0.0.1', 1),
(1, '2010-11-05 13:25:42', '127.0.0.1', 1),
(1, '2010-11-05 16:10:59', '127.0.0.1', 1),
(1, '2010-11-06 03:42:21', '127.0.0.1', 1),
(1, '2010-11-06 03:47:18', '127.0.0.1', 1),
(1, '2010-11-06 03:47:26', '127.0.0.1', 0),
(1, '2010-11-06 03:47:28', '127.0.0.1', 0),
(1, '2010-11-06 03:47:29', '127.0.0.1', 0),
(1, '2010-11-06 03:47:30', '127.0.0.1', 0),
(1, '2010-11-06 03:47:33', '127.0.0.1', 1),
(1, '2010-11-06 09:48:23', '127.0.0.1', 1),
(1, '2010-11-06 09:53:30', '127.0.0.1', 1),
(1, '2010-11-06 10:26:45', '127.0.0.1', 1),
(1, '2010-11-06 11:51:30', '127.0.0.1', 1),
(1, '2010-11-06 17:22:20', '127.0.0.1', 1),
(1, '2010-11-07 09:48:50', '127.0.0.1', 1),
(1, '2010-11-07 10:49:17', '127.0.0.1', 1),
(1, '2010-11-07 10:51:59', '127.0.0.1', 1);

dostalem odpowiedz na oficjalnym forum mysql; sprawa wyglada tak:

pytanie jest: jak to wrzucic w zapytanie mysql_query();

[SQL] pobierz, plaintext 
  1. SET @row = 0;
  2. SET @GROUP = -1;
  3. SET @login = -1;
  4. SET @lastFail = -1;
  5.  
  6. SELECT groupId
  7.   FROM (SELECT @row := case logd when @login then @row + 1 else 0 end rowId,
  8.             @login := logd logd,
  9.             @GROUP := case @row when 0 then @GROUP + 1 else @GROUP end groupId
  10.           FROM chcur_logd
  11.           ORDER BY `date` DESC) v
  12.   WHERE logd = 0
  13.   GROUP BY groupId
  14.   HAVING max(rowId) > 3
  15.   ORDER BY groupId
  16.   LIMIT 1
  17.   INTO @lastFail;
  18.  
  19. SET @row = 0;
  20. SET @GROUP = -1;
  21. SET @login = -1;
  22.  
  23. SELECT id_u, `date`, ip, logd
  24.   FROM (SELECT @row := case logd when @login then @row + 1 else 0 end rowId,
  25.             @login := logd logd,
  26.             @GROUP := case @row when 0 then @GROUP + 1 else @GROUP end groupId,
  27.             id_u, `date`, ip
  28.           FROM chcur_logd
  29.           ORDER BY `date` DESC) v
  30.   WHERE groupId = @lastFail;
[SQL] pobierz, plaintext
thek
25.11.2010, 11:32:44
Najlepiej w takim wypadku poczytaj o procedurach i funkcjach w mysql, bo to najprostsze rozwiązanie pytania "Jak to wrzucić?"
phpion
25.11.2010, 11:34:46
Jak powyżej, albo po prostu każde polecenie (SET i SELECT w Twoim przypadku) wykonywać oddzielnie w mysql_query czy czym tam masz.
AndyPSV
25.11.2010, 23:05:46
sorry, ale jak to zrobic, bo nie moge cos znalexc rozwiazania??

[SQL] pobierz, plaintext 
  1. $unsucc = ('CREATE PROCEDURE unsuccessful_attempts() BEGIN
  2.  
  3. set @row = 0;
  4. set @group = -1;
  5. set @login = -1;
  6. set @lastFail = -1;
  7.  
  8. select groupId
  9.   from (select @row := case logd when @login then @row + 1 else 0 end rowId,
  10.             @login := logd logd,
  11.             @group := case @row when 0 then @group + 1 else @group end groupId
  12.           from chcur_logd
  13.           order by `date` desc) v
  14.   where logd = 0
  15.   group by groupId
  16.   having max(rowId) > 3
  17.   order by groupId
  18.   limit 1
  19.   into @lastFail;
  20.  
  21. set @row = 0;
  22. set @group = -1;
  23. set @login = -1;
  24.  
  25. select id_u, `date`, ip, logd
  26.   from (select @row := case logd when @login then @row + 1 else 0 end rowId,
  27.             @login := logd logd,
  28.             @group := case @row when 0 then @group + 1 else @group end groupId,
  29.             id_u, `date`, ip
  30.           from chcur_logd
  31.           order by `date` desc) v
  32.   where groupId = @lastFail;');
  33.  
  34. 			$q = q('CALL unsuccessful_attempts()'); IF(n_r($q) > 0) $logd = f($q); var_export($logd);
[SQL] pobierz, plaintext
CuteOne
26.11.2010, 00:54:12
skoro nie radzisz sobie z procedurami, może uzyjesz zwykłego if() w php??

[PHP] pobierz, plaintext 
  1.  
  2. $select = mysql_query("SELECT * FROM groupID");
  3.  
  4. $f = false;
  5. while($row = mysql_fetch_array($select)) {
  6.  
  7. if(!$f && $row['logd'] == 1) {
  8.    //$r[$row['id_u']]= $row['date']; pomijamy
  9.    $f = true;
  10. }
  11. elseif($f && $row['logd'] != 1) {
  12.    $r[$row['id_u']] = $row['date']
  13.  
  14. elseif($f && $row['logd'] == 1) {
  15.    //$r[$row['id_u']] = $row['date']; pomijamy
  16.    break;
  17. }
  18. }
  19.  
  20. echo '<pre>'.print_r($r).'</pre>';
  21.  
  22. // jeżeli chcesz wycinać kilka takich "pakietów" zamiast break; wstaw $f=false  ;]
[PHP] pobierz, plaintext
AndyPSV
28.11.2010, 07:52:44
ten kod mi wyswietla 1, co nie jest prawidlowa wartoscia (powinno byc 4)
jak wstawic ta "procedure"? wszedzie szukalem ale pewnie cos zle robie, bo nikt tych "procedur" w kodzie php nie podaje;


pomozcie mi to zrobic, bo juz mnie



-----------------------------------------------------------------------------

w koncu

[SQL] pobierz, plaintext 
  1. $q = q('SELECT m.*
  2.      , COUNT(n.id_u) total
  3.   FROM
  4.      (
  5. SELECT a.date AS start
  6.      , MIN(c.date) end
  7.      , a.logd
  8.      , a.ip
  9.   FROM 
  10.      ( SELECT x.date
  11.             , x.ip
  12.             , x.logd
  13.             , COUNT(*) id 
  14.          FROM chcur_logd x
  15.          JOIN chcur_logd y
  16.            ON y.date <= x.date
  17.         GROUP BY x.date
  18.      ) AS a
  19.   LEFT JOIN 
  20.      ( SELECT x.date
  21.             , x.ip
  22.             , x.logd, COUNT(*) id 
  23.          FROM chcur_logd x
  24.          JOIN chcur_logd y
  25.            ON y.date <= x.date
  26.         GROUP BY x.date
  27.      ) AS b ON a.id = b.id + 1 AND b.logd = a.logd AND b.ip = a.ip
  28.   LEFT JOIN  
  29.      ( SELECT x.date
  30.             , x.ip
  31.             , x.logd
  32.             , COUNT(*) id 
  33.          FROM chcur_logd x
  34.          JOIN chcur_logd y
  35.            ON y.date <= x.date
  36.         GROUP BY x.date
  37.      ) c ON a.id <= c.id AND c.logd = a.logd  AND c.ip = a.ip
  38.   LEFT JOIN 
  39.      ( SELECT x.date
  40.             , x.ip
  41.             , x.logd
  42.             , COUNT(*) id 
  43.          FROM chcur_logd x
  44.          JOIN chcur_logd y
  45.            ON y.date <= x.date
  46.         GROUP BY x.date
  47.      ) AS d ON c.id = d.id - 1 AND d.logd = c.logd AND d.ip = c.ip
  48.  WHERE b.id IS NULL 
  49.    AND c.id IS NOT NULL 
  50.    AND d.id IS NULL
  51.  GROUP 
  52.     BY start
  53.      ) m
  54.   JOIN chcur_logd n
  55.     ON n.date BETWEEN m.start AND m.end
  56.  GROUP
  57.     BY m.start
  58. HAVING m.logd = 1
  59.  ORDER
  60.     BY m.start LIMIT 1;');
  61. 			IF(n_r($q) > 0) $logd = f($q); var_export($logd);
[SQL] pobierz, plaintext
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2025 Invision Power Services, Inc.