Datediff()
Een tabel met een registratie systeem, ook een veld met lastactive (datetime).
Ik doe bij mijn AdminCP een lijst met alle leden op de pagina printen en ook degene rood markeren die nog niet online zijn gekomen.
Wat ik nu ook nog wil is degenen markeren die al twee weken niet online zijn. Dit dacht ik op te lossen met datediff(), waarbij ik deze code nu heb:
Code (php)
1
2
3
4
5
2
3
4
5
<?php
$sql2 = mysql_query("SELECT DATEDIFF(CURDATE(), lastactive) AS dagen_offline FROM `".$db_tbl."` WHERE DATEDIFF(CURDATE(), lastactive) >= 14") or die(mysql_error());
$row2 = mysql_fetch_object($sql2);
echo $diff = htmlspecialchars($row2->dagen_offline);
?>
$sql2 = mysql_query("SELECT DATEDIFF(CURDATE(), lastactive) AS dagen_offline FROM `".$db_tbl."` WHERE DATEDIFF(CURDATE(), lastactive) >= 14") or die(mysql_error());
$row2 = mysql_fetch_object($sql2);
echo $diff = htmlspecialchars($row2->dagen_offline);
?>
Ik zal het vast wel fout hebben gedaan, omdat ik totaal niet weet hoe ik dit moet gebruiken en ik word ook niet wijs uit de MySQL documentation.
Dit is de output script:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
<?
$sql = "SELECT name,active,state,ip,lastactive FROM `".$db_tbl."` ORDER BY name ASC";
$query = mysql_query($sql);
# ! begin test omgeving
$sql2 = mysql_query("SELECT DATEDIFF(CURDATE(), lastactive) AS dagen_offline FROM `".$db_tbl."` WHERE DATEDIFF(CURDATE(), lastactive) >= 14") or die(mysql_error());
$row2 = mysql_fetch_object($sql2);
echo $diff = htmlspecialchars($row2->dagen_offline);
# ! einde test omgeving
while($row = mysql_fetch_object($query)) {
$name = htmlspecialchars($row->name);
$active = htmlspecialchars($row->active);
$state = htmlspecialchars($row->state);
$ip = htmlspecialchars($row->ip);
$lastactive = htmlspecialchars($row->lastactive);
$number = $number + 1;
if($active == 0) {
echo "<tr>\n";
echo "<td><font color=\"red\">".$number." NOT ACTIVE</font></td>\n";
echo "<td>".$name."</td>\n";
echo "<td></td>\n";
echo "<td></td>\n";
echo "<td><font color=\"red\">0000-00-00 00:00:00</font></td>\n";
echo "</tr>\n";
}elseif ($state == 1 && $lastactive != "0000-00-00 00:00:00"){
echo "<tr>\n";
echo "<td><font color=\"green\">".$number." ADMIN</font></td>\n";
echo "<td><a title=\"PM: ".$name."\" href=\"../pm/compose.php?id=".$name."\">".$name."</a></td>\n";
echo "<td><a title=\"View profile\" href=\"profile.php?id=".$name."\">View Profile</a></td>\n";
echo "<td>".$ip."</td>\n";
echo "<td>".$lastactive."</td>\n";
echo "</tr>\n";
}elseif ($state == 0 && $lastactive != "0000-00-00 00:00:00"){
echo "<tr>\n";
echo "<td>".$number."</td>\n";
echo "<td><a title=\"PM: ".$name."\" href=\"../pm/compose.php?id=".$name."\">".$name."</a></td>\n";
echo "<td><a title=\"View profile\" href=\"profile.php?id=".$name."\">View Profile</a></td>\n";
echo "<td>".$ip."</td>\n";
echo "<td>".$lastactive."</td>\n";
echo "</tr>\n";
}elseif ($state == 0 && $lastactive == "0000-00-00 00:00:00") {
echo "<tr>\n";
echo "<td>".$number."</td>\n";
echo "<td><a title=\"PM: ".$name."\" href=\"../pm/compose.php?id=".$name."\">".$name."</a></td>\n";
echo "<td><a title=\"View profile\" href=\"profile.php?id=".$name."\">View Profile</a></td>\n";
echo "<td>".$ip."</td>\n";
echo "<td><font color=\"red\">".$lastactive."</font></td>\n";
echo "</tr>\n";
}
}
?>
$sql = "SELECT name,active,state,ip,lastactive FROM `".$db_tbl."` ORDER BY name ASC";
$query = mysql_query($sql);
# ! begin test omgeving
$sql2 = mysql_query("SELECT DATEDIFF(CURDATE(), lastactive) AS dagen_offline FROM `".$db_tbl."` WHERE DATEDIFF(CURDATE(), lastactive) >= 14") or die(mysql_error());
$row2 = mysql_fetch_object($sql2);
echo $diff = htmlspecialchars($row2->dagen_offline);
# ! einde test omgeving
while($row = mysql_fetch_object($query)) {
$name = htmlspecialchars($row->name);
$active = htmlspecialchars($row->active);
$state = htmlspecialchars($row->state);
$ip = htmlspecialchars($row->ip);
$lastactive = htmlspecialchars($row->lastactive);
$number = $number + 1;
if($active == 0) {
echo "<tr>\n";
echo "<td><font color=\"red\">".$number." NOT ACTIVE</font></td>\n";
echo "<td>".$name."</td>\n";
echo "<td></td>\n";
echo "<td></td>\n";
echo "<td><font color=\"red\">0000-00-00 00:00:00</font></td>\n";
echo "</tr>\n";
}elseif ($state == 1 && $lastactive != "0000-00-00 00:00:00"){
echo "<tr>\n";
echo "<td><font color=\"green\">".$number." ADMIN</font></td>\n";
echo "<td><a title=\"PM: ".$name."\" href=\"../pm/compose.php?id=".$name."\">".$name."</a></td>\n";
echo "<td><a title=\"View profile\" href=\"profile.php?id=".$name."\">View Profile</a></td>\n";
echo "<td>".$ip."</td>\n";
echo "<td>".$lastactive."</td>\n";
echo "</tr>\n";
}elseif ($state == 0 && $lastactive != "0000-00-00 00:00:00"){
echo "<tr>\n";
echo "<td>".$number."</td>\n";
echo "<td><a title=\"PM: ".$name."\" href=\"../pm/compose.php?id=".$name."\">".$name."</a></td>\n";
echo "<td><a title=\"View profile\" href=\"profile.php?id=".$name."\">View Profile</a></td>\n";
echo "<td>".$ip."</td>\n";
echo "<td>".$lastactive."</td>\n";
echo "</tr>\n";
}elseif ($state == 0 && $lastactive == "0000-00-00 00:00:00") {
echo "<tr>\n";
echo "<td>".$number."</td>\n";
echo "<td><a title=\"PM: ".$name."\" href=\"../pm/compose.php?id=".$name."\">".$name."</a></td>\n";
echo "<td><a title=\"View profile\" href=\"profile.php?id=".$name."\">View Profile</a></td>\n";
echo "<td>".$ip."</td>\n";
echo "<td><font color=\"red\">".$lastactive."</font></td>\n";
echo "</tr>\n";
}
}
?>
Code (php)
1
2
3
4
5
2
3
4
5
<?php
$sql2 = mysql_query("SELECT lastactive AS dagen_offline FROM '".$db_tbl."' WHERE lastactive=(CURDATE() - INTERVAL 14 DAY)");
$row2 = mysql_fetch_object($sql2);
echo $diff = htmlspecialchars($row2->dagen_offline);
?>
$sql2 = mysql_query("SELECT lastactive AS dagen_offline FROM '".$db_tbl."' WHERE lastactive=(CURDATE() - INTERVAL 14 DAY)");
$row2 = mysql_fetch_object($sql2);
echo $diff = htmlspecialchars($row2->dagen_offline);
?>
?
Want als ik dat doe, dan zegt firefox:
Firefox heeft vastgesteld dat de server het verzoek voor dit adres doorverwijst op een manier die nooit zal eindigen.
Gewijzigd op 01/01/1970 01:00:00 door GaMer B
Er zit elders in jouw script een fout.
Tevens is het niet handig om mysql_fetch_object() te gebruiken, heeft niks met objecten en oop te maken en is zo snel als dikke **piep** door een trechter. Gebruik mysql_fetch_assoc(), dat werkt een stuk beter.
Waarom doe jij trouwens de aanname dat de query wel zal lukken? Je hebt de garantie dat dit niet het geval is. Controleer dus of de query wel is gelukt.
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT
lastactive
AS
dagen_offline
FROM
'".$db_tbl."'
WHERE
lastactive=(CURDATE() - INTERVAL 14 DAY
lastactive
AS
dagen_offline
FROM
'".$db_tbl."'
WHERE
lastactive=(CURDATE() - INTERVAL 14 DAY
aan:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''pcm' WHERE lastactive=(CURDATE() - INTERVAL 14 DAY)' at line 1
Maar hoe ziet de syntax er dan uit, want ik zou echt niet weten hoe ik dat allemaal moet gebruiken. En het enige dat MySQL documentation zegt is:
Code (php)
1
2
3
4
2
3
4
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31
Daarnaast heb je nog een fout in de datumfunctie, maar daar kwam MySQL nog helemaal niet aan toe!
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
SELECT
lastactive
AS
dagen_offline
FROM
".$db_tbl."
WHERE
DATEDIFF(CURDATE(), lastactive) > 14
lastactive
AS
dagen_offline
FROM
".$db_tbl."
WHERE
DATEDIFF(CURDATE(), lastactive) > 14
Niet getest...