Select where date is closest to now

[davidb52]

Hello i have this query:

$query = mysql_query("SELECT dbfrom,message,dbdate,dbread FROM dbtext WHERE dbto='" . mysql_real_escape_string( $id ) . "' GROUP BY dbfrom ORDER BY dbdate DESC");

but it take's the first entry, and i want the one with dbdate closest till now.like:13-06-2011 15:50:3313-06-2011 15:56:3313-06-2011 15:59:33now it select 13-06-2011 15:50:33 but i want 13-06-2011 15:59:33.this didn't work:

$query = mysql_query("SELECT dbfrom,message,dbdate,dbread FROM dbtext WHERE dbto='" . mysql_real_escape_string( $id ) . "' AND dbdate<=(now) GROUP BY dbfrom ORDER BY dbdate DESC");

(now but than the hole code).it works but still takes 13-06-2011 15:50:33 but i want select only 13-06-2011 15:59:33

[justsomeguy]

What are you trying to use GROUP BY for?

[Ingolme]

Just order by date from newwst to oldest and limit the selection to 1: ORDER BY dbdate DESC LIMIT 1

[davidb52]

i only want to show 1 row, and there could be more of dbfrom(like more message) so i select only 1 message from dbfrom.like:dbfrom message datedavid test1 14-6-2011 16:44:05david test2 14-6-2011 16:45:05david test3 14-6-2011 16:49:05cecile testfromcecile 14-6-2011 16:51:05cecile testfromcecile2 14-6-2011 16:52:05james testjames 14-6-2011 16:13:05Whit the code i only want to show:david test3 14-6-2011 16:49:05cecile testfromcecile2 14-6-2011 16:52:05james testjames 14-6-2011 16:13:05for every from show only 1 where date is nearest to now, but how do i do that?

[Ingolme]

According to the MySQL manual, you can sort the GROUP BY results using DESC. From what I understood in the manual, uses the field defined by "ORDER BY" but you can choose the order with ASC or DESC.SELECT dbfrom,message,dbdate,dbread FROM dbtext WHERE dbto='" . mysql_real_escape_string( $id ) . "' GROUP BY dbfrom DESC ORDER BY dbdate DESC

[davidb52]

Thanks that you guys helping me, but it still don't work.If i group dbrom DESC or ASC it just does this:dbfrom message datececile testfromcecile 14-6-2011 16:51:05david test1 14-6-2011 16:44:05james testjames 14-6-2011 16:13:05orjames testjames 14-6-2011 16:13:05david test1 14-6-2011 16:44:05cecile testfromcecile 14-6-2011 16:51:05it just order by name, so sorry but that doesn't work to.

[Ingolme]

Oh, it looks like it's sorting by the field being grouped. Hmm, I'll need to look into it more. Feel free to research the MySQL Manual until I have some time to look into it.

[davidb52]

i found this (it's the same)http://stackoverflow.com/questions/3449757...from-each-groupbut, i can't get the code working in mine (from first awnser), mayby you could?I also found something about distinct but that doesn't work (i think that function has some problems with GROUP BY)and i also found thishttp://www.w3schools.com/sql/sql_func_last.aspBut i never work with AS so how i echo that query?

[Ingolme]

How is your implementation of the query they have there?

[davidb52]

$query = mysql_query("SELECT LAST(dbfrom) ,message,dbdate,dbread AS Last FROM dbtext WHERE dbto='$id'  GROUP BY dbfrom ORDER BY dbdate DESC");while($row = mysql_fetch_array($query)){$from = ['dbfrom'];$message = ['message'];$dbdate = ['dbdate'];echo "$from $message $date"}

or

$query = mysql_query("SELECT DISTINCT dbfrom,message,dbdate,dbread FROM dbtext WHERE dbto='" . mysql_real_escape_string( $id ) . "' GROUP BY dbfrom ORDER BY dbdate DESC");

and i maybe filled the wrong name's in by the firstlink, but that gave also a errorbut is there no (easy) way to just say 'SELECT * WHERE dbdate is nearist now()' or something?

[Ingolme]

There isn't an easy way if you're grouping by poster.Your query doesn't resemble the solution given in the link you showed. Their solution looks like this:

SELECT a.* FROM test a WHERE a.attendence = 1 AND NOT EXISTS (select 1 from test where name = a.name and id > a.id and attendence = 1)GROUP BY name

[Ingolme]

I just thought of something that might work:SELECT dbfrom,message,dbdate,dbread FROM dbtext WHERE dbto='" . mysql_real_escape_string( $id ) . "' GROUP BY dbfrom HAVING LAST(dbdate) = dbdate ORDER BY dbdate DESC

[davidb52]

Yeah that's the one i think gonne work, but what i said i don't now how to user it.

SELECT * FROM dbtextWHERE dbto = '$id' AND NOT EXISTS (select 1 from dbtext where dbdate = dbdate and dbdate > dbdate)GROUP BY name

that's not gonne work (i think)I don't now how to use this code because i never worked with a.(something).Could give me a link or explain it?a friend gave me this: (but it's also with .(something)

$query = mysql_query("SELECT dbfrom, message, dbdateFROMdbtextINNER JOIN(SELECT dbfrom, MAX(dbdate) FROM dbtext GROUP BY dbfrom) AS newest_dateON newest_date.dbfrom=message.dbfrom AND dbdate=newest_date.dbdate;");

[Ingolme]

Well, I could try to adapt it to your query, but I thought of a simple solution that might work which I posted just before.

[davidb52]

thx,because we posted same time i didn't saw that message.but that gives an error:Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in text.php on line 17this doesn't work

  $query = mysql_query("SELECT dbfrom,message,dbdate,dbread FROM dbtext WHERE dbto='" . mysql_real_escape_string( $id ) . "' GROUP BY dbfrom HAVING LAST(dbdate) = dbdate ORDER BY dbdate DESC");

without work:

$query = mysql_query("SELECT dbfrom,message,dbdate,dbread FROM dbtext WHERE dbto='" . mysql_real_escape_string( $id ) . "' GROUP BY dbfrom ORDER BY dbdate DESC");

I'm going to do some research why that don't work.

[Ingolme]

Hmm, it appears that the query has an error... You could print out the value of mysql_error() to see what's wrong with it.To apply the other query to your data would be like this:

SELECT a.`dbfrom`,a.message,a.dbdate,a.dbreadFROM dbtext aWHERE dbto = '$id' AND NOT EXISTS (select 1 from dbtext where `dbfrom` = a.`dbfrom` and dbdate > a.dbdate and dbto = '$id')GROUP BY dbfrom

[davidb52]

THANKS ALOT!, but i think it's not going to work. This is the last question because if this not gonne work i quit.( ! ) Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\mydabo.comSandbox\apps\text.php on line 20Call Stack# Time Memory Function Location1 0.0235 730168 {main}( ) ..\text.php:02 0.0493 784848 mysql_fetch_array ( ) ..\text.php:20

   $query = mysql_query("SELECT a.dbfrom,a.message,a.dbdate,a.dbreadFROM dbtext aWHERE dbto = '$id' AND NOT EXISTS (select 1 from dbtext where dbfrom style='color:orange'>= a.dbfrom and dbdate > a.dbdate and dbto = '$id')GROUP BY dbfrom");   while($row1 = mysql_fetch_array($query)){   $data[] = $row1;   foreach ($data as $row1);   $from = $row1['dbfrom'];   $message = $row1['message'];   $date = $row1['dbdate'];   $read = $row1['dbread'];}

line 20:

while($row1 = mysql_fetch_array($query)){

[Ingolme]

THANKS ALOT!, but i think it's not going to work. This is the last question because if this not gonne work i quit.( ! ) Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\mydabo.comSandbox\apps\text.php on line 20Call Stack# Time Memory Function Location1 0.0235 730168 {main}( ) ..\text.php:02 0.0493 784848 mysql_fetch_array ( ) ..\text.php:20

   $query = mysql_query("SELECT a.dbfrom,a.message,a.dbdate,a.dbreadFROM dbtext aWHERE dbto = '$id' AND NOT EXISTS (select 1 from dbtext where dbfrom style='color:orange'>= a.dbfrom and dbdate > a.dbdate and dbto = '$id')GROUP BY dbfrom");   while($row1 = mysql_fetch_array($query)){   $data[] = $row1;   foreach ($data as $row1);   $from = $row1['dbfrom'];   $message = $row1['message'];   $date = $row1['dbdate'];   $read = $row1['dbread'];}

line 20:

while($row1 = mysql_fetch_array($query)){

Did you notice that the forum software added this to the query? "style='color:orange'>"I edited my post when I saw it. It seems to have an error parsing "from" in the "dbfrom" field so I changed that.Also, when it tells you it found a boolean is means that your mysql_query() statement failed. Add this before your while() loop:

if(!$query) {  echo mysql_error();  exit;}

[davidb52]

Yeah I notice that, but I was thinking the good result would be in a color so I could see what you did.But I use that option also sometime like <span <?php if(){ echo 'style="color:red;"'; } else { echo 'style="color:green;"'; } ?>>green or red</span>you now?BUT THAAAAAAAAANNNNNNNNNNKKKKKKKKKKSSSSSSSSSS!!!!!!!!!!!! it finnaly work! thanks thanks thanks!