Jump to content


Photo

DISTINCT and TIMESTAMP


  • Please log in to reply
16 replies to this topic

#1 Daemoncraft

Daemoncraft
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 09 September 2006 - 05:59 PM

I cant get my stuff to work

<?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']);

} 
?>

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





#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 09 September 2006 - 06:12 PM

How is the time column defined in the table?

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Daemoncraft

Daemoncraft
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 09 September 2006 - 06:17 PM

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

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 09 September 2006 - 06:21 PM

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?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 09 September 2006 - 06:21 PM

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, without 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.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#6 Daemoncraft

Daemoncraft
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 09 September 2006 - 06:30 PM

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
$query = mysql_query("SELECT * FROM warnings ORDER BY time DESC");
while($w = mysql_fetch_array($query)){
}
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
$query = mysql_query("SELECT DISTINCT(user_id) FROM warnings ORDER BY time DESC");
while($w = mysql_fetch_array($query)){

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




#7 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 09 September 2006 - 06:48 PM

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:
bob   time1
bob   time2
bob   time3
john  time1
matt  time1
matt  time2
kelly time1
joe   time1
greg  time1
greg  time2
query with distinct, you get this:
bob   time1
john  time1
matt  time1
kelly time1
joe   time1
greg  time1

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

bob   time1
      time2
      time3
john  time1
matt  time1
      time2
kelly time1
joe   time1
greg  time1
      time2

is this right? if not, then give an example of what you want the output to look like.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 09 September 2006 - 07:05 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#9 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 09 September 2006 - 07:15 PM

sorry barand, that's probably more my fault than his.  it just seems 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 seems that he's trying to find the formatting middleground by altering the sql query, which i don't think 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 you suspect the problem is here? I'm trying to figure out your train of thought here...
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#10 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 09 September 2006 - 07:25 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#11 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 09 September 2006 - 07:33 PM

okay i'll buy that. I see where you're comin' from now.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#12 Daemoncraft

Daemoncraft
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 09 September 2006 - 07:42 PM

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:

bob   time1
bob   time2
bob   time3
john  time1
matt  time1
matt  time2
kelly time1
joe   time1
greg  time1
greg  time2
query with distinct, you get this:
bob   time1
john  time1
matt  time1
kelly time1
joe   time1
greg  time1

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

bob   time1
      time2
      time3
john  time1
matt  time1
      time2
kelly time1
joe   time1
greg  time1
      time2

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


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

#13 Daemoncraft

Daemoncraft
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 09 September 2006 - 07:44 PM

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.


ITS VALID!

if u read all my post u will understand!

it works if i dont use the DISTNICT PART!

#14 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 09 September 2006 - 07:46 PM

distnict wont do nothink for you in this situation sorry.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#15 Daemoncraft

Daemoncraft
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 09 September 2006 - 07:55 PM

Well i got it to work now:)

$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"));


#16 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 09 September 2006 - 07:59 PM

so..basically what you are wanting is a list of people with warnings, and their most recent warning, if there is more than 1?
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#17 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 09 September 2006 - 08:44 PM

In which case
<?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>';
}
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users