Page 1 of 1

display results from a query by most recent date

Posted: Wed Dec 04, 2002 4:25 pm
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?

Posted: Wed Dec 04, 2002 6:20 pm
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.

Posted: Wed Dec 04, 2002 6:39 pm
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...

Posted: Wed Dec 04, 2002 6:52 pm
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

Posted: Wed Dec 04, 2002 7:34 pm
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?

Posted: Wed Dec 04, 2002 7:40 pm
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.