Trying to set up script for testing most recent record

[Specky]

Hello, I’m trying to set up a query in a PHP script that pulls data from MySQL for a series of dropdowns, based on how old the records are, for example older than a month, older than 3 months etc.

It works but back to front in that it bases the criteria on the date added for the oldest record in the collection, so in other words it sees that that record is older than a month so outputs the collection name in the dropdown. What I need it to do is look for the single most RECENT record in the collection, and if THAT record is older than the specified interval then so be it, the collection gets outputted in the dropdown. So if someone adds another record, then the collection has been updated today and should no longer be in the “older than 1 month” category.

I’ve tried changing ASC and DESC around in the query, and changing the >= to <= but to no avail. Any ideas?

//OLDER THAN 1 MONTH
echo '<p><strong>Older than 1 month: </strong> <form name="viewnotepad" method="post" action="view-notepad.html">';
 echo '<select name="_Notepad" size="1">';  
 
		$getnotepads = $mysqli->query("SELECT * FROM specnotepadproducts WHERE Username LIKE '$username%' AND `DateAdded` <= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) GROUP BY NotepadName DESC LIMIT 1"); 
			while ($row = $getnotepads->fetch_assoc()) {   
				$notepad = $row['NotepadName'];
				$dateadded = $row['DateAdded'];
        		echo '<option>'.$notepad.'</option>';   
			}
       echo '</select><input name="viewnotepad" type="submit" value="GO" class="button-notepad-short"></form></p>';
echo '<div class="clear"></div>';

[WebMachine]

If you only want to target the record with the most recent ‘DateAdded’ would it work if you add an ORDER BY DateAdded DESC and select only the first record retrieved?

[Specky]

Afraid that doesn’t work- tried:

$getnotepads = $mysqli->query("SELECT * FROM specnotepadproducts WHERE Username LIKE '$username%' AND `DateAdded` <= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) GROUP BY NotepadName ORDER BY DateAdded DESC LIMIT 1"); 

But when I echo the date it shows the date added for the oldest item still, rather than the most recent- I tried ASC and DESC but the same happened…???

[droopsnoot]

I’ve read something about this issue before, it’s down to the way that GROUP BY works. Have a look at this page and see if it helps: http://stackoverflow.com/questions/6572110/order-by-date-and-time-before-group-by-name-in-mysql

[Specky]

Well, I tried:

		$getnotepads = $mysqli->query("SELECT NotepadName, MIN(DateAdded) FROM specnotepadproducts WHERE Username LIKE '$username%' AND `DateAdded` <= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) GROUP BY NotepadName ORDER BY MIN(DateAdded) DESC LIMIT 1"); 
			while ($row = $getnotepads->fetch_assoc()) {   
				$notepad = $row['NotepadName'];
				$dateadded = $row['DateAdded'];
        		echo '<option>'.$notepad.'</option>';   
			}

-and that gave $dateadded as blank.

So I tried:

		$getnotepads = $mysqli->query("SELECT * FROM ( SELECT * FROM specnotepadproducts WHERE Username LIKE '$username%' AND `DateAdded` <= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ORDER BY DateAdded DESC LIMIT 1) GROUP BY NotepadName"); 
			while ($row = $getnotepads->fetch_assoc()) {   
				$notepad = $row['NotepadName'];
				$dateadded = $row['DateAdded'];
        		echo '<option>'.$notepad.'</option>';   
			}

This does the same except that it also breaks the rest of the page so nothing further on is outputted.

Any other ideas as to how this can be achieved? I tried a query of my own which at least outputted the date, HOWEVER it still only outputs the oldest date, not the most recent…

$getnotepads = $mysqli->query("SELECT NotepadName, MAX(DateAdded) AS MaxDate FROM specnotepadproducts WHERE Username LIKE '$username%' AND `DateAdded` <= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) GROUP BY NotepadName ORDER BY MAX(DateAdded) DESC LIMIT 1"); 
			while ($row = $getnotepads->fetch_assoc()) {   
				$notepad = $row['NotepadName'];
				$dateadded = $row['MaxDate'];
        		echo '<option>'.$notepad.'</option>';   
			}