Getting the latest id field out of a database...

[rboycey]

Hi,

I have a sort of latest articles thingy on the front of my website. It gets the article out of a database and then displays everything before the [esum] tag (esum == end summary).

However at the mo it isn't working properly because I can't figure out how I get the latest id from the DB rather than the one I specify.

I.e: I need to be able to do this

$latid = "Latest ID NO from the DB"; // Latest ID no

 // First Article
    // Select the First Article Summary
    $article1 = mysql_query("SELECT title, body, id FROM articles WHERE id='$latid'");
    // Break up the article variables
    $article1 = mysql_fetch_array($article1);
    // Start the items
    $a1_id = $article1["id"]; // Article ID for link
    $a1_title = $article1["title"]; // Article Title
    $a1_pre_body = $article1["body"]; // The Article Body (Pre Split)
    $a1_body_arrray = split("\[esum]",$a1_pre_body); // Split up the Article
    $a1_body = $a1_body_arrray[0]; // Get the first part of the article for summary
    $a1_link = "Full Article"; // Get the article link text 

Please Help

Also, and I know this must be sound dumb but I often get confused about stuff like this.

How do you say and in the WHERE clause of the Select sql query - i.e. SELECT cnt, title FROM articles WHERE id='$latid' and auth='allow' Is that it or is there something different?

Also how do you use the UPDATE query - i.e.

$sql = mysql_query("UPDATE ~~~~~ IN ~~~~~~~~ WHERE id='$id'")

Many Thanks
Rick

[weirdbeardmt]

Hmm, one solution surely is ORDER by ID desc LIMIT 1? Or add a date field and just pull out the latest date?

Your syntax for AND in the SELECT is correct.

For full use of the syntax.. the easiest thing is to check out www.mysql.com -- they explain the syntax in full as well as giving helpful examples.

[revium]

Rick,

If your id field is an auto increment type field then you can use the mysql_insert_id() function to return the id of the most recent insertion to the database.

$latid=mysql_insert_id();
$article1 = mysql_query("SELECT title, body, id FROM articles WHERE id='$latid'");

[freddydoesphp]

revium that will only work in the same process that inserted the record I do believe. So if you tried to run the function a few days later the result would be empty.

[revium]

thanks freddy. i was wondering about that since in the php manual it says:

If link_identifier isn't specified, the last opened link is assumed.

[veronicay]

Hmm, one solution surely is ORDER by ID desc LIMIT 1? Or add a date field and just pull out the latest date?

If the numbers are sequential, wouldn't "SELECT MAX(id) ..." do??

[freddydoesphp]

revium,

I have certianly been wrong before and I suppose if the last link id established contained an INSERT query you could still call the function, but once another is established I think it would lose its value.

[rboycey]

1 > The table is auto-increment but I had tried the idea that revium suggested and as freddydoesphp said it only really works if your using it after you have exectuted a SQL command - I am using it in the admin area to get the id of the row that was just added for the content people to be able to quote links - i.e. website.com/article.php?action=disp&aid=112233 (hmmmmm like we will ever get that no. of articles )

2 > I will try the 2 suggestions so far :
- "SELECT MAX(id) ..."
- ORDER by ID desc LIMIT 1
But if a, you could suggest more ideas I would be very grateful and also if Veronica or someone could please xplain the SELECT MAX(id) ... query more, i.e. like what else can be used in it, and can it be used in a select query i.e. SELECT id, cnt, name FROM articles WHERE id='MAX(id)' or something like that or do i have to use it like
$id = mysql_query("SELECT MAX(id)");
$article1 = mysql_query("SELECT cnt, name, title FROM articles WHERE id='$id'");
etc

Many Thanks
Rick

[revium]

Rick,

The MAX function returns the highest value in a column. NULL values are not included in the calculation.

so you could use

SELECT MAX(id), cnt, name FROM articles

i have only ever used the max function to draw the value from a single column in a select query, (i.e. SELECT MAX(id) FROM articles) but i assume it would also work to retrieve multiple columns in one query. keep in mind that it will only return one record, which i believe is what you are after

[freakysid]

Originally posted by freddydoesphp
revium,

I have certianly been wrong before ...

This might be true, but even if it is, the best thing is never to admit it! However, on this occasion, I believe you are correct.

Regarding the MAX() function mentioned earlier in the thread, this is one of the GROUPING functions and so only can be used with GROUP BY or on the entire result set (in which case all this will give you is the value of the maximum ID - not much use here). So Revium your query in your last post will not work! you will get an error saying that the group by clause is missing (or something) from the mysql server.

Personally, I always prefer to use a date or time type field for this type of thing! I mean, when you think about it the type is self evident. Think about what you are modelling, "I want to get the latest article". See if you think about what you are modelling - what it is you want to know, you can often design a better data model, that, when you need to extend your application later and query the database differently (say to retrieve articles submitted in the last 30 days), your sound data modelling can accomodate this.

I personally like the TIMESTAMP time type, because although in its raw form it is not very human readable, with all the date and time functions available (see the manual) you can manipulate a TIMESTAMP to return just about anything you might want in your query.

Eg,

CREATE TABLE uselessTable (insertTime TIMESTAMP);

SELECT DAYOFMONTH(insertTime)as day, 
       MONTHNAME(insertTime) as month,
       YEAR(insertTime) as year
FROM uselessTable
WHERE MONTH(inserTime) = 3;

That will return a nicely formatted result set of the date of all records where insertdate is the month of March.

Wow, sorry for rambling, what was I saying, oh yeah use a date or time type. Although it should not be a problem in practice, a unique identifier field such as auto-increment field is just that - a unique identifier. In general, with RDMS, you should not relly on a unique identifier field to maintain order between records.

[revium]

freakysid,

thanks for clearing up the mistake with my query.

[veronicay]

Regarding the MAX() function mentioned earlier in the thread, this is one of the GROUPING functions and so only can be used with GROUP BY or on the entire result set (in which case all this will give you is the value of the maximum ID - not much use here). So Revium your query in your last post will not work! you will get an error saying that the group by clause is missing (or something) from the mysql server.

It's true that Revium's way won't work -- but you can still (theoretically) use MAX. The solution below actually won't help with MySQL, because I don't think it supports subqueries :-( -- but in standard SQL you could do:

select id,title from articles where id=(select max(id) from articles)

In the absence of this feature, weirdbeardmt's suggestion would work. Or, less efficiently, you could run one query to get the MAX(id), put that value in a variable and then run the second query passing the id, i.e.

"select id,article from articles where id = " . $maxid

Having said that, I think freakysid's solution is probably the easiest to work with. And as he says, it can be used for other purposes as well.

HTH

HTH

[kevin_tremblay]

MAX(ID) is the solution, I just did this with my Cold Fusion project and works great.