Gmod Mysql ORDER BY Limit

Hi all… I’ve a problem… When I try to delet some rows (to stack 5 rows max) all rows are deleted … (maximum rows per player reached, so delet oldest).

No synthax warning was returned…

In Mysql photo table :
SourceID = ply steamID
ID = Primary autoinc key (int 10).

In Gmod lua channel :
NetReadShowSourceID = ply steamID
NetReadShowSourceName = ply Name

All of this variabl work when i test it with a print.



function DelO2dPhoto(NetReadShowSourceID,NetReadShowSourceName)		
		local Req = databaseObject:query( " DELETE FROM photo WHERE SourceID = '"..NetReadShowSourceID.."' ORDER BY ID DESC LIMIT 5 ")  -- if i  use 0,5 i got a synthax warning
		function Req:onSuccess( data ) print( "DelO2dPhoto Query successful! "..NetReadShowSourceName.."  "..NetReadShowSourceID ) end
		function Req:onError( err, sql ) print( "DelO2dPhoto Query errored! "..NetReadShowSourceName.."  "..NetReadShowSourceID ) print( "Query:", sql ) print( "Error:", err ) end
		Req:start()
end
DelO2dPhoto(NetReadShowSourceID,NetReadShowSourceName)


In this cod, all rows are deleted =/. Any idéa ?

Thanx n_n

order by for query result. As far as I know. Not for Delete or Create or Insert or Update or Alter commands.

ho… So how can i delete all rows (for a certain SteamID) on a table, but save only 5 last rows ?

I googled this question because I was intrigued. This answer is probably good: http://stackoverflow.com/questions/6989932/sql-delete-all-rows-except-some-ones

So, if i understoond, i need to delete rows without specified rows with a concatenated selec req… huhuhuhu, need to try ^^

mmmm maybe good but i’ve an other error message …

local Req = databaseObject:query( " DELETE FROM photo WHERE SourceID = ‘"…NetReadShowSourceID…"’ NOT IN ( SELECT LigneId FROM photo WHERE SourceID = ‘"…NetReadShowSourceID…"’ ORDER BY LigneId DESC LIMIT 10 ) ")
This version of Mysql dosen’t support ‘LIMIT & IN/ALL/ANY/SOME subquery’

So i think i need to find another way to delete old think…mm maybe a coute + add on table and importe table to the delete req with a lot of AND X)



DELETE FROM photo WHERE SourceID = '"..NetReadShowSourceID.."' AND ID NOT IN (
   SELECT ID FROM photo WHERE SourceID = '"..NetReadShowSourceID.."' ORDER BY ID DESC LIMIT 5
)


Deletes everything in photo for that SourceID, with the exception of “the items for that SourceID that were the 5 added most recently”.

Also, I don’t think I need to mention SQL injection…

If you can’t use limit or order by in a subquery, either update your database or perform the queries separately.

client don’t perform sql query. He have no choice of what he give to server.

Server receiv command and find here owne data.

(ply was send to server and server extract data from ply and if ply and NetReadShowSourceID are different : server don’t execute script )

And sv_interface isn’t included or added to client, so we can’t grab it withscript hooker.

(and humm… how can i include the data grabed by SELECT query to my DELET query ?)

You could store the results of the select query in a table and then build the delete query from that…



local part1 = "DELETE FROM photo WHERE SourceID = '"..NetReadShowSourceID.."' AND ID NOT IN ("
for k, v in ipairs(resultsOfSelect) do
   part1 = part1 .. tostring(v)
   if k ~= #resultsOfSelect then
      part1 = part1 + ", "
   end
end

local delete = part1 .. ")"

-- Execute the delete query.


^ Replace the spaces between the commas with the ids that you get from the SELECT part, then run the second part.