Daemoncraft Posted September 9, 2006 Share Posted September 9, 2006 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 PMon 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:)? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 9, 2006 Share Posted September 9, 2006 How is the time column defined in the table?Whar do you get if you [code]echo $w['time'];[/code] Quote Link to comment Share on other sites More sharing options...
Daemoncraft Posted September 9, 2006 Author Share Posted September 9, 2006 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 distnictits the $query that got a problem... but i cant find it Quote Link to comment Share on other sites More sharing options...
Barand Posted September 9, 2006 Share Posted September 9, 2006 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? Quote Link to comment Share on other sites More sharing options...
.josh Posted September 9, 2006 Share Posted September 9, 2006 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. Quote Link to comment Share on other sites More sharing options...
Daemoncraft Posted September 9, 2006 Author Share Posted September 9, 2006 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 thisCODE1[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 Quote Link to comment Share on other sites More sharing options...
.josh Posted September 9, 2006 Share Posted September 9, 2006 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 time1bob time2bob time3john time1matt time1matt time2kelly time1joe time1greg time1greg time2[/code]query with distinct, you get this:[code]bob time1john time1matt time1kelly time1joe time1greg time1[/code]but what you are trying to query and get, is this:[code]bob time1 time2 time3john time1matt time1 time2kelly time1joe time1greg time1 time2[/code]is this right? if not, then give an example of what you want the output to look like. Quote Link to comment Share on other sites More sharing options...
Barand Posted September 9, 2006 Share Posted September 9, 2006 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. Quote Link to comment Share on other sites More sharing options...
.josh Posted September 9, 2006 Share Posted September 9, 2006 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... Quote Link to comment Share on other sites More sharing options...
Barand Posted September 9, 2006 Share Posted September 9, 2006 The first problem is the dates. If we can see what is being storedecho $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 Link to comment Share on other sites More sharing options...
.josh Posted September 9, 2006 Share Posted September 9, 2006 okay i'll buy that. I see where you're comin' from now. Quote Link to comment Share on other sites More sharing options...
Daemoncraft Posted September 9, 2006 Author Share Posted September 9, 2006 [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 time1bob time2bob time3john time1matt time1matt time2kelly time1joe time1greg time1greg time2[/code]query with distinct, you get this:[code]bob time1john time1matt time1kelly time1joe time1greg time1[/code]but what you are trying to query and get, is this:[code]bob time1 time2 time3john time1matt time1 time2kelly time1joe time1greg 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 Quote Link to comment Share on other sites More sharing options...
Daemoncraft Posted September 9, 2006 Author Share Posted September 9, 2006 [quote author=Barand link=topic=107478.msg431303#msg431303 date=1157829928]The first problem is the dates. If we can see what is being storedecho $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! Quote Link to comment Share on other sites More sharing options...
redarrow Posted September 9, 2006 Share Posted September 9, 2006 distnict wont do nothink for you in this situation sorry. Quote Link to comment Share on other sites More sharing options...
Daemoncraft Posted September 9, 2006 Author Share Posted September 9, 2006 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] Quote Link to comment Share on other sites More sharing options...
.josh Posted September 9, 2006 Share Posted September 9, 2006 so..basically what you are wanting is a list of people with warnings, and their most recent warning, if there is more than 1? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 9, 2006 Share Posted September 9, 2006 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] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.