Jump to content

DISTINCT and TIMESTAMP


Daemoncraft

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:)?



Link to comment
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


Link to comment
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.
Link to comment
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...
Link to comment
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.
Link to comment
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
Link to comment
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!
Link to comment
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.