Retrieve latest result...

[Aureole]

I need to make a query that pulls the latest added news article so whichever one has the highest id, how do I do this?

[trq]

id's are not designed to be sorted by, they may not stay in order.

 

I would suggest you use a timestamp and sort by that, however, your query would be as simple as...

 

SELECT data FROM tbl ORDER by id DESC LIMIT 1;

[Aureole]

Ok thanks that kind of worked but now I have another problem I won't post the entire file but this should be enough for you to see what I mean.

 

<?php
if ($title && $short && $full)
    {
    $query = "INSERT INTO news (title, short, full, author, authorid, cat, date) 
VALUES ('$title', '$short', '$full', '$author', '$authorid', '$cat', NOW())"; 
    $result = @mysql_query($query);
if ($result)
    {
$show = "SELECT title, short, author, authorid, cat, id, date_format(date, '%e/%M/%Y - %H:%i') as prettydate FROM news ORDER by id DESC LIMIT 1";
$result = mysql_query($show) or die(mysql_error());
echo "<a href=\"http://www.veraci7y.net/news/view.php?id={$row['id']}\" title=\"View News Article\" class=\"newstitle\">{$row['title']}</a>";
            echo "    <a href=\"http://www.veraci7y.net/news/edit.php?id={$row['id']}\" target=\"blank\" title=\"Edit Article\" class=\"user\">Edit</a>  <a href=\"http://www.veraci7y.net/news/delete.php?id={$row['id']}\" target=\"blank\" title=\"Delete Article\" class=\"user\">Delete</a>";
        echo "<p class=\"newsinfo\">Posted by <a href=\"http://www.veraci7y.net/forums/index.php?showuser={$row['authorid']}\" class=\"user\" title=\"View Member's Profile\">{$row['author']}</a> <span class=\"newsdate\">{$row['prettydate']} EST</span></p><p class=\"newscontent\">";
        echo parseme("{$row['short']}");
	echo "</p><br />";
  }

else
    {
  echo '<p class="warning">News Article could not be added.</p>';
  }
}

else
    {
    echo '<p class="warning">All fields are required.</p>';
  }
}
?>

 

Cause I'm using ajax to send the form and I want it to show the news article that you just posted if it was successful.

[trq]

What is your question?

 

Also... when I said not to rely on id's staying in order, I meant it. There is no guarantee your latest entry will have the highest id.

[Aureole]

How isn't there it's auto increment so of course it will have the highest id  ???

 

Basically if the insert query was successful I want to echo the result but like...

 

Title  Edit  Delete

 

Posted by Name Date

 

Story

 

I'm submitting the form with ajax so basically it should look like when you are adding news it is updating in real time.

[trq]

How isn't there it's auto increment so of course it will have the highest id

 

OK... you know best.

[Aureole]

Obviously I don't, I just don't understand how but ok. Well is there a way I can use the date and retrieve the result with the highest date?

[trq]

Same query...

 

SELECT data FROM tbl ORDER by datefld DESC LIMIT 1;

[Aureole]

Well I have it working perfectly everything shows except the id.

 

<?php
$show = "SELECT id FROM news ORDER by id DESC LIMIT 1";
$result = mysql_query($show) or die(mysql_error());
$showid = $row['id'];

echo "<a href=\"news/view.php?id=$showid\" title=\"View News Article\" class=\"newstitle\">$title</a>";
echo "<a href=\"news/edit.php?id=$showid\" target=\"blank\" title=\"Edit Article\" class=\"user\">Edit</a><a href=\"news/delete.php?id=$showid\" target=\"blank\" title=\"Delete Article\" class=\"user\">Delete</a>";
echo "<p class=\"newsinfo\">Posted by <a href=\"forums/index.php?showuser=$authorid\" class=\"user\" title=\"View Member's Profile\">$author</a> <span class=\"newsdate\">a second ago.</span></p><p class=\"newscontent\">";
echo parseme("$short");
echo "</p><br />";
?>

[trq]

You never fetch the record from the resultset.

 

$row = mysql_fetch_assoc($result);