[SOLVED] finding all records but displaying the latest by time then sorting by time

[xoligy]

Ok here is the code im working with and im having problems because im dumb and been out the loop for a while, i did post this on another forum but no response after a few days or anything!

 

Anyway here is what im trying to do, i need to get the information from the db and if there is more than 1 record for the same person display the "latest" record and then sort in time desending. Below is how it started out and then the next bit of code is what i then tried but becasue i "moved" time im no longer able to use the code i had to get the time out the db!

 

The only other thing i can come up with is maybe a second query but surely it can all be done in one? Anyway any help is appreciated!

 

Btw i only re-wrote the top half so i could be speaking jibberish after this text lol

 

mysql_query("SELECT `id`,`src`,`dest`,`msg`,`time`,`del`,`crew` FROM `$tab[mail]` WHERE dest='$id' AND inbox='attacks' AND del='no' and time >= '$before' ORDER BY `time` DESC")or die("Invalid query: " . mysql_error());

 

 

Now whats happening is its showing all records by the same id whre i just want the latest record by time (if that makes sense) There will be other results shown by other people too but as i said i want it all done by last inputted time.

 

Someone did suggest SELECT DISTINCT and it seemed to fail when i tried adding it to the begginging of the query so i changed it to:

 

mysql_query("SELECT DISTINCT `time` AND `id`,`src`,`dest`,`msg`,`del`,`crew` FROM `$tab[mail]` WHERE dest='$id' AND inbox='attacks' AND del='no' and time >= '$before' ORDER BY `time` DESC")or die("Invalid query: " . mysql_error());

 

 

Which did the job i want i believe but messed up the time because there is no time being pulled.

 

what i mean:

user 1 time

user 2 time

user 2 time

user 2 time

 

Above wrong

Below wight

 

user 1 time

user 2 time

 

Hope it makes sense :/ also all the other querys are needed!

[xoligy]

No-one able to help out im starting to think maye i need a second query for the time and add that into the equasion? So a query for the $time and then do the query shown above will hopefully order how i want. But i would of thought that it could of all been done in one query :/

[kickstart]

Hi

 

This is not complete (because I am not sure which fields are specific to an id, and which are specific to the latest time), but hopefully will give you the idea:-

 

mysql_query("SELECT `id`,`src`,`dest`,`msg`,`b.LatestTime`,`del`,`crew` 
FROM `$tab[mail]` a
JOIN (SELECT `id`, MAX(`time`) as LatestTime GROUP BY `id`) b
ON `a.id` = `b.id`
WHERE dest='$id' 
AND inbox='attacks' 
AND del='no' 
and time >= '$before' 
ORDER BY `time` DESC")or die("Invalid query: " . mysql_error());

 

All the best

 

Keith

[xoligy]

Unfortunatly it didnt work, neither did my idea but i guessed my way wouldnt lol

here is what i got:

 

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY `id`) b ON `a.id` = `b.id` WHERE dest='4' AND inbox='attacks' AND d' at line 3

[kickstart]

Hi

 

Doh. Missed the FROM in the sub query.

 

mysql_query("SELECT `id`,`src`,`dest`,`msg`,`b.LatestTime`,`del`,`crew` 
FROM `$tab[mail]` a
JOIN (SELECT `id`, MAX(`time`) as LatestTime FROM `$tab[mail]` GROUP BY `id`) b
ON `a.id` = `b.id`
WHERE dest='$id' 
AND inbox='attacks' 
AND del='no' 
and time >= '$before' 
ORDER BY `time` DESC")or die("Invalid query: " . mysql_error());

 

However, as I said this is almost certainly not going to give you exactly what you want on its own, it is just a pointer in the right direction.

 

What do the various fields in the select represent? Which field is the person?

 

All the best

 

Keith

[xoligy]

id is now ambigious.. (uses google lol)

 

id = is just your normal 1,2,3 for each insert

src = person from

dest = person to

msg = message

time = time sent

del = deleted or not

crew = crew message

 

I'll read up on grouping and joining when im more awake, see if i can make head or tails of it. Im trying to do too much going from if, elseif statments to mysql querys before i know everything lol think this is the hardest job for me till i learn about sessions.

 

Thanks for the replys btw

 

[kickstart]

Hi

 

OK, bit more of a look:-

 

mysql_query("SELECT `a.id`,`b.src`,`a.dest`,`a.msg`,`b.LatestTime`,`a.del`,`a.crew` 
FROM `$tab[mail]` a
SELECT `src`, MAX(`time`) as LatestTime FROM `$tab[mail]` WHERE  dest='$id' AND inbox='attacks' AND del='no' and time >= '$before'  GROUP BY `src`
ON `a.src` = `b.src`
AND `a.time` = b.LatestTime
WHERE dest='$id' 
AND inbox='attacks' 
AND del='no' 
and time >= '$before' 
ORDER BY `time` DESC")or die("Invalid query: " . mysql_error());

 

This uses a subselect to find the latest email from a person which is sent to $id, in inbox of "attacks" has a del of "no" and a time >= to $before.

 

It joins the results of this with a full query of the table to get the other fields required.

 

You could possibly simplify this. As it is using an outer join you could probably remove the second set of WHERE clauses, although this might cause an issue if someone had multiple messages at the same time but in different inboxes.

 

There is also the possible issue that as time is not unique you could possibly bring back multiple rows (not that likely really). Only real way round that would be to use the id field and make sure that it is sequential.

 

All the best

 

Keith

[xoligy]

Sorry taken a while to get back been busy with things, any way seem to be getting this:

 

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT `src`, MAX(`time`) as LatestTime FROM `r38_mailbox` WHERE dest='1' AND i' at line 3

 

im lost lol

[xoligy]

I asked someone else about it and they said to try a loop so gonna read up about loops and see how horribly wrong i go there lol unless someone says that wount work?

[kickstart]

Hi

 

Can barely remember doing this.

 

Loops will probably work fine but be far less efficient than letting MySQL do the work. Probably even worse if the database is on a seperate server to the php.

 

However looks like I made a mess pasting it:-

 

mysql_query("SELECT `a.id`,`b.src`,`a.dest`,`a.msg`,`b.LatestTime`,`a.del`,`a.crew` 
FROM `$tab[mail]` a
LEFT OUTER JOIN (SELECT `src`, MAX(`time`) as LatestTime FROM `$tab[mail]` WHERE  dest='$id' AND inbox='attacks' AND del='no' and time >= '$before'  GROUP BY `src`) b
ON `a.src` = `b.src`
AND `a.time` = b.LatestTime
WHERE dest='$id' 
AND inbox='attacks' 
AND del='no' 
and time >= '$before' 
ORDER BY `time` DESC")or die("Invalid query: " . mysql_error());

 

Haven't tried it as I don't have an example database set up.

 

All the best

 

Keith

[xoligy]

Thanks again Keith, still no joy unfortunatly. What you wrote to me looks like jibberish lol but no doubt makes sense to you :-/

 

Anyway here is what i got: Invalid query: Unknown column 'a.id' in 'field list'

[kickstart]

Hi

 

Can you post the actual layout of the table/s (ie, export the table declarations).

 

a.id should be fine (id is in $tab[mail] in your original post and a is just used as an abbreviation).

 

All the best

 

Keith

[xoligy]

Here is an sql dump:

 

- phpMyAdmin SQL Dump
-- version 2.10.2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Jul 08, 2009 at 01:20 PM
-- Server version: 5.0.45
-- PHP Version: 5.2.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- 
-- Database: `mob2`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `r37_mailbox`
-- 

CREATE TABLE `r37_mailbox` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `src` int(11) NOT NULL default '0',
  `dest` int(11) NOT NULL default '0',
  `msg` text NOT NULL,
  `time` int(12) NOT NULL default '0',
  `inbox` varchar(32) NOT NULL default '',
  `del` char(3) NOT NULL default 'no',
  `crew` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=52 ;

-- 
-- Dumping data for table `r37_mailbox`
-- 

 

 

[kickstart]

Hi

 

The back tics around columns are causing the issue (sorry, I virtually never use them).

 

Try this SQL:-

 

SELECT a.id,b.src,a.dest,a.msg,b.LatestTime,a.del,a.crew
FROM `r37_mailbox` a
LEFT OUTER JOIN (SELECT src, MAX(`time`) as LatestTime FROM `r37_mailbox` WHERE  dest='$id' AND inbox='attacks' AND del='no' and time >= '$before'  GROUP BY `src`) b
ON a.src = b.src
AND a.time = b.LatestTime
WHERE dest='$id' 
AND inbox='attacks' 
AND del='no' 
and time >= '$before' 
ORDER BY `time` DESC

 

All the best

 

Keith

[xoligy]

God damn it! lol

 

Its close Keith, its still shows all the records it seems, but only a few have the name of the attacker which are the latest ones here is a dump of that info:

 

attacked by 3 revenge expires in "23 hours, 58 minutes, 46 seconds"  	attack back
3 took a short cut through your street in 1 lolos. 3 of your boys became speedbumps. 5 of 3's guys were also killed.

attacked by 3 revenge expires in "23 hours, 58 minutes, 46 seconds" 	attack back
3 took a short cut through your street in 1 lolos. 3 of your boys became speedbumps. 5 of 3's guys were also killed.

attacked by revenge expires in "-14432 days, -12 hours, -43 minutes, -8 seconds" 	attack back
3 took a short cut through your street in 1 lolos. 3 of your boys became speedbumps. 5 of 3's guys were also killed.

attacked by revenge expires in "-14432 days, -12 hours, -43 minutes, -8 seconds" 	attack back
3 took a short cut through your street in 1 lolos. 3 of your boys became speedbumps. 5 of 3's guys were also killed.

attacked by revenge expires in "-14432 days, -12 hours, -43 minutes, -8 seconds" 	attack back
3 took a short cut through your street in 1 lolos. 3 of your boys became speedbumps. 5 of 3's guys were also killed.

attacked by revenge expires in "-14432 days, -12 hours, -43 minutes, -8 seconds" 	attack back
3 took a short cut through your street in 1 lolos. 3 of your boys became speedbumps. 5 of 3's guys were also killed.

attacked by revenge expires in "-14432 days, -12 hours, -43 minutes, -8 seconds" 	attack back
3 took a short cut through your street in 1 lolos. 3 of your boys became speedbumps. 5 of 3's guys were also killed.

attacked by 2 revenge expires in "3 hours, 29 minutes, 31 seconds" 	attack back
2 took a short cut through your street in 1 lolos. 1 of your boys became speedbumps. 1 of 2's guys were also killed.

 

I know two 3's appeared but that shouldnt happen when its life *fingers crossed* plus i have a session to add to it when i read up about them.

[kickstart]

Hi

 

If you want just the latest ones try changing the "LEFT OUTER JOIN" to just a "JOIN".

 

All the best

 

Keith

[xoligy]

Your a genius! I dont care what other people say lol From what ive seen it works

 

Thanks very mutch Keith its much appreciated!