PHP - SQL question: delete last row from recordset

1

Here’s what I’m trying to achieve, hope somebody can help me out:

  • first make a selection out of a mySQL database
  • if the number of selected rows is 10 -> delete the last row
  • then insert a new row

my PHP looks like this:


$query = "SELECT * FROM db_rss_uri WHERE rss_category='$category' ORDER BY rss_rate DESC, rss_id ASC LIMIT 10";
$result = mysql_query($query);
$num = mysql_numrows($result);

if ($num == 10) {
  // delete last row
}

What would be the best way to do this? Is there a simple way to target the last row of my selection?

I hope I’m being clear. Thanks in advance. :slight_smile:

2

Do LIMIT 10,1.

That way you get the 10th row. If there isn’t a 10th row, the query won’t return any data.

3

maybe something like

if ( mysql_numrows($result) > 10 )
$query = "UPDATE table SET column = $value.. WHERE id = 10";
else 
$query = "INSERT INTO table... etc";
4

Thanks, I used your suggestion.

Strange thing though is that when I use LIMIT 10,1 like this:


$query = "SELECT * FROM db_rss_uri WHERE rss_category='$category' ORDER BY rss_rate DESC, rss_id DESC LIMIT 10,1";
$result = mysql_query($query) or die('Could not query');
if ($result){
  // delete last row
}
// then insert new row

then when the table contains exactly 10 rows, the if statement is still false.
So it looks like LIMIT 10,1 returns the 11th row?
Anyway, when I use LIMIT 9,1 everything works like I want it to, so I’m happy :smiley:

5

but why male models?

oh no wait meant, why is it 10,1 (or 9,1). I always wrote LIMIT 10 and that worked fine…

edit:you know what I’m an idiot. Just re-read the mysql statement and realized it’s getting two thins… forget it…

6

Sorry, my mistake. It should be LIMIT 9,1. I guess the way to read it is ‘skip nine rows and then give me one after that’. That gives you the 10th row.