display results from a query by most recent date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rathlon
Forum Commoner
Posts: 45
Joined: Sun Nov 10, 2002 8:07 pm

display results from a query by most recent date

Post by rathlon »

Question 1.
I have a table with multiple records in it. Each record has it's own timestamp. How do I select the most recent update based on the most recent timestamp?

Question 2.
Some of my users keep putting " ' " in there record updates. I have a form written in php that allows them to view and update certain info. For instance...they update a projects name with the following:

"George's project".

Since that is the projects name, I use that info on another form (passed through a link) to query for information. Problem is when I query the following:

"select description from projects where project.name='George's project';"

it tells me that my query produced no results. If I try to stripslashes(project.name) <---- php function or addslashes(project.name) it still doesn't work. But, if I use:

"select * from projects"

I can see that the projects name actually is "George\''s project". Why then can't I select the description (or any other info) for that project by matching the project's name?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

1) SELECT .... ORDER BY fldName DESC

2) then you've done a bit too much ;) e.g.

Code: Select all

$value = "it's a test";
$value = mysql_escape_string($value);
echo $value;
$query = "INSERT INTO texts VALUES('".$value."')";
mysql_query($query, $conn);
it will echo it''s a test but mysql stores it's a test. just like

Code: Select all

$value = "you have to escape "s in here";
echo $value;
although there is a \" the actual value is only " and \ only escapes the special meaning of " in a double quoted string.
rathlon
Forum Commoner
Posts: 45
Joined: Sun Nov 10, 2002 8:07 pm

Post by rathlon »

what exactly does

select ... order by fldname desc

mean?

What I want to do is select the latest record out of a group of records that are related to the same subject. Will your suggestion do that? Please explain...
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

http://www.mysql.com/doc/en/Sorting_rows.html explains it .
If you want only the most recent entry you might limit the result set
e.g.

Code: Select all

SELECT * from table WHERE cat=5 ORDER BY tmInserted DESC LIMIT 1
http://www.mysql.com/doc/en/SELECT.html
rathlon
Forum Commoner
Posts: 45
Joined: Sun Nov 10, 2002 8:07 pm

Post by rathlon »

I read the info in the links but i still have a few questions...

what does the cat=5 mean?

I understand the order by...but the tminserted?? What is that?

desc = descending...understand that...and the limit is limiting the return to only 1 result....right?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

right, only one record in the resultset.
The select-statement was only an example to show you the right order of directives within the query.
cat and tmInserted were meant to be fictive fields in the (also fictive) table table ;)
tmInserted would be the timestamp.
Post Reply