Jump to content

Archived

This topic is now archived and is closed to further replies.

Daemoncraft

DISTINCT and TIMESTAMP

Recommended Posts

I cant get my stuff to work

[code]
<?php
$query = mysql_query("SELECT DISTINCT(user_id) FROM warnings ORDER BY time DESC");
while($w = mysql_fetch_array($query)){
$q = mysql_fetch_array(mysql_query("SELECT * FROM members WHERE id = '$w[user_id]'"));

$warnings = mysql_num_rows(mysql_query("SELECT * FROM warnings WHERE user_id = '$q[id]'"));
print $warnings;
print date("d M Y g:i A", $w['time']);

}
?>
[/code]

My "print date("d M Y g:i A", $w['time']);" prints this date 31 Dec 1969 4:00 PM

on every timestamp i try i get that date..

but when i remove
"SELECT DISTINCT(user_id) FROM warnings ORDER BY time DESC"
and change it to
"SELECT * FROM warnings ORDER BY time DESC"

then it works as it should...but i have to use the DISTINCT function, please help me:)?



Share this post


Link to post
Share on other sites
How is the time column defined in the table?

Whar do you get if you [code]echo $w['time'];[/code]

Share this post


Link to post
Share on other sites
int(11)

i have also tried with timestamp..but thats not the problem im 100% sure there..

bc it all works if i dont use the distnict

its the $query that got a problem...  but i cant find it

Share this post


Link to post
Share on other sites
It sounds like you are not storing valid timestamp values if they all come out with that date.

What, exactly, are you trying to output - 3 queries seems excessive?

Share this post


Link to post
Share on other sites
okay maybe i just don't get it.. if it works as it should - in other words, it works the way you WANT it to, [b]without[/b] the distinct, then why are you trying to use distinct? "if it ain't broke, don't fix it," comes to mind.

perhaps you need to further elaborate on your problem.

Share this post


Link to post
Share on other sites
i run an rpg, and on my forum i have a "warn" feature that lets u warna user for spamming..when u click warn it insert into a table named
"warnings"
but if i list it like this

CODE1
[code]
$query = mysql_query("SELECT * FROM warnings ORDER BY time DESC");
while($w = mysql_fetch_array($query)){
}
[/code]
it will show the user more than 1 time on the list since that is listing all the ppl..so 1 person can be listed like twice etc..

CODE2
[code]
$query = mysql_query("SELECT DISTINCT(user_id) FROM warnings ORDER BY time DESC");
while($w = mysql_fetch_array($query)){
[/code]

so if I user this one it will let me list the persons/users once(like I want to).

Timestamp works normal if I use CODE1 but then it lists all the queries(I just want one query of each user)

On CODE2 it list perfectly:) but the timestamp gets to this date "31 Dec 1969 4:00 PM"...

so can anyone help me


Share this post


Link to post
Share on other sites
so what are you really trying to output here, each individual warning, or just a list of people who have been warned, regardless of how many times they have been warned? because it seems to me that you are somehow trying to do both, in a manner that can't be done.  It seems to me that you are trying to make a query that will somehow list all the warnings with each timestamp, but at the same time, only pull the username once. It seems to me that if you:

query without distinct, you get this:
[code]
bob  time1
bob  time2
bob  time3
john  time1
matt  time1
matt  time2
kelly time1
joe  time1
greg  time1
greg  time2
[/code]
query with distinct, you get this:
[code]
bob  time1
john  time1
matt  time1
kelly time1
joe  time1
greg  time1
[/code]

but what you are trying to query and get, is this:

[code]
bob  time1
      time2
      time3
john  time1
matt  time1
      time2
kelly time1
joe  time1
greg  time1
      time2
[/code]

is this right? if not, then give an example of what you want the output to look like.

Share this post


Link to post
Share on other sites
OK. I've asked a couple of questions to help me solve your problem to which there has been no answer. I'm sorry, but if you won't help me to help you then I can't.

Share this post


Link to post
Share on other sites
sorry barand, that's probably more my fault than his.  it just [i]seems[/i] to me that since it seems to halfway work just fine for him, one way or the other, that in the end, it's really a formatting issue here, not a storage problem. unless he has some bug that is storing the same date over and over.. but anyways, it just [i]seems[/i] that he's trying to find the formatting middleground by altering the sql query, which i don't [i]think[/i] can be done the way he wants to. If i'm right about what he's trying to output, then he's going to have to accept the query without the distinct, and do some conditioning with php to format it the way he wants to, but he's going to have to chime in here and be more specific. Well.. again, I don't think you can get a result like that in sql. you're like 20 times better at sql than i am, so uh..can you? anyways, i do agree with you in that he needs to start answering some questions here... but just curious, what do [i]you[/i] suspect the problem is here? I'm trying to figure out your train of thought here...

Share this post


Link to post
Share on other sites
The first problem is the dates. If we can see what is being stored

echo $w['time'];

we can see if it is a valid timestamp - I suspect not, as it aleays gives the same wrong date.

The second is the general approach - using a query to get a value to use in a query, and then a third query. It looks as though a single joined query with GROUP BY might do, but I can't find out what the output should be, only how he's attempting to do it - which ain't working.

On thing's for sure, using DISTINCT is going to have no effect on the date values.

Share this post


Link to post
Share on other sites
[quote author=Crayon Violent link=topic=107478.msg431285#msg431285 date=1157827712]
so what are you really trying to output here, each individual warning, or just a list of people who have been warned, regardless of how many times they have been warned? because it seems to me that you are somehow trying to do both, in a manner that can't be done.  It seems to me that you are trying to make a query that will somehow list all the warnings with each timestamp, but at the same time, only pull the username once. It seems to me that if you:

query without distinct, you get this:
[code]
bob  time1
bob  time2
bob  time3
john  time1
matt  time1
matt  time2
kelly time1
joe  time1
greg  time1
greg  time2
[/code]
query with distinct, you get this:
[code]
bob  time1
john  time1
matt  time1
kelly time1
joe  time1
greg  time1
[/code]

but what you are trying to query and get, is this:

[code]
bob  time1
      time2
      time3
john  time1
matt  time1
      time2
kelly time1
joe  time1
greg  time1
      time2
[/code]

is this right? if not, then give an example of what you want the output to look like.
[/quote]

Im doing the one in the middle there..and that works fine:)

its the timestamp that is the error..
well the timestamp wont cooperate with the DISTNICT

Share this post


Link to post
Share on other sites
[quote author=Barand link=topic=107478.msg431303#msg431303 date=1157829928]
The first problem is the dates. If we can see what is being stored

echo $w['time'];

we can see if it is a valid timestamp - I suspect not, as it aleays gives the same wrong date.

The second is the general approach - using a query to get a value to use in a query, and then a third query. It looks as though a single joined query with GROUP BY might do, but I can't find out what the output should be, only how he's attempting to do it - which ain't working.

On thing's for sure, using DISTINCT is going to have no effect on the date values.
[/quote]

ITS VALID!

if u read all my post u will understand!

it works if i dont use the DISTNICT PART!

Share this post


Link to post
Share on other sites
Well i got it to work now:)

[code]
$query = mysql_query("SELECT DISTINCT user_id FROM warnings");
while($w = mysql_fetch_array($query)){
$rtime = mysql_fetch_array(mysql_query("SELECT * FROM warnings WHERE user_id = '$w[user_id]' ORDER BY time DESC limit 1"));
[/code]

Share this post


Link to post
Share on other sites
so..basically what you are wanting is a list of people with warnings, and their most recent warning, if there is more than 1?

Share this post


Link to post
Share on other sites
In which case
[code]<?php
$sql = "SELECT m.name, COUNT(*), MAX(w.time)
        FROM members m 
        INNER JOIN warnings w ON w.user_id = m.id
        GROUP BY m.name";
$res = mysql_query($sql) or die(mysql_error());
while (list($name, $total, $date) = mysql_fetch_row($res)) {
    echo "$name $total " . date ('d m Y g:ia', $date) . '<br>';
}
?>[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.