Get highest value in database?

[Maurize]

so I have a database.

My question is, how can I get the highest value?

I mean I want to set an ID. The first player who registers gets ID 1, the second ID 2, but how is this possible?

[Castillo]

You are talking about SQL/MySQL?

[Gallardo9944]

if you use MySQL database, you can create an ID field with AUTO_INCREMENT parameter.

[Maurize]

I'm talking about SQL...

executeSQLCreateTable( "db_data", "name STRING, code STRING, id INT" ) 

[Gallardo9944]

executeSQLCreateTable( "db_data", "id INT NOT NULL AUTO_INCREMENT, name STRING, code STRING" ) 

Should be fine, untested though. (or it could be AUTOINCREMENT (without "_"), cause I have never worked with sqlite, only MySQL. SQLite wiki says it's AUTOINCREMENT)

[Maurize]

lol any troubles cause this:

executeSQLInsert( "db_data", ""0", '"..getPlayerName( source ).."', '"..code.."'" ) 

[Gallardo9944]

you have double " before 0 for some reason. Also, you shouldn't set id to anything if it's AUTO_INCREMENT - it's set automatically.

[Maurize]

But I must insert data into database first... otherwise it says error ...

[Gallardo9944]

Should be fine.

executeSQLQuery("INSERT INTO db_data (name,code) VALUES('"..getPlayerName(source).."','"..code.."')") 

[Maurize]

hmm, createDB is buggy... database doesn't get created...

addEventHandler( "onResourceStart", resourceRoot, 
function() 
    executeSQLCreateTable( "db_data", "id INT IDENTITY( 1,1 ) PRIMARY KEY, name STRING, code STRING, life INT, armor INT, skin INT, meele INT, gun INT, ammo INT, pX FLOAT, pY FLOAT, pZ FLOAT, rZ FLOAT, int INT" ) 
end ) 

this works. but id is not in table...

Edited January 26, 2014 by Guest

[Gallardo9944]

just use executeSQLQuery and CREATE TABLE in it. executeSQLCreateTable is very old and deprecated

[Maurize]

executeSQLQuery( "CREATE TABLE db_data( id INT NOT NULL AUTO_INCREMENT, name STRING, code STRING, life INT, armor INT, skin INT, meele INT, gun INT, ammo INT, pX FLOAT, pY FLOAT, pZ FLOAT, rZ FLOAT, int INT )" ) 
    executeSQLQuery( "INSERT INTO db_data( name, code, life, armor, skin, meele, gun, ammo, pX, pY, pZ, rZ, int ) VALUES( '"..getPlayerName( source ).."', '"..code.."', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0' )" ) 

error, database cannot be created. syntax error with auto increment.

[Castillo]

SQLite by default has a column called "rowid", you don't have to create another column to store the ID.

[Maurize]

Doesn't work...

addCommandHandler( "getid", 
function( player, cmd, target ) 
if ( getPlayerFromName( target ) ) then 
    outputChatBox( "Das Ziel trägt die ID: "..getSQLData( getPlayerFromName( target ), "db_data", "rowid" ), player, 255, 255, 0 ) 
    end 
end ) 

[Castillo]

Post the function "getSQLData".

[Maurize]

function getSQLData( element, db, row ) 
    local db = executeSQLSelect( db, "*", "name = '"..getPlayerName( element ).."'" ) 
if ( db and #db == 1 ) then 
    return db[1][row] 
    end 
end 

[Castillo]

function getSQLData ( element, db, row ) 
    local db = executeSQLQuery ( "SELECT * FROM ? WHERE name = ?", db, getPlayerName ( element ) ) 
    if ( db and #db == 1 ) then 
        return db [ 1 ] [ row ] 
    end 
end 

Try that one instead.

[Maurize]

Thanks Castillo, I'll try it later.

[Maurize]

Database query failed near "select": sytnax error...

[Castillo]

My bad, I forgot to rename the function, copy the code again, it works ( tested ).

[Maurize]

Ye it get's values...

addCommandHandler( "getid", 
function( player, cmd, target ) 
if ( getPlayerFromName( target ) ) then 
    outputChatBox( "Das Ziel trägt die ID: "..tonumber( getSQLData( getPlayerFromName( target ), "db_data", "rowid" ) ), player, 255, 255, 0 ) 
    end 
end ) 

error attempt to concatenate nil value

[Castillo]

And, is the name of the player in the table?

[Maurize]

of course it is.

  
executeSQLQuery( "CREATE TABLE IF NOT EXISTS db_data( id INT IDENTITY( 1,1 ) PRIMARY KEY, name STRING, code STRING, life INT, armor INT, skin INT, meele INT, gun INT, ammo INT, pX FLOAT, pY FLOAT, pZ FLOAT, rZ FLOAT, int INT )" ) 
... 
    executeSQLQuery( "INSERT INTO db_data( name, code, life, armor, skin, meele, gun, ammo, pX, pY, pZ, rZ, int ) VALUES( '"..getPlayerName( source ).."', '"..code.."', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0' )" ) 

[Castillo]

Have you tried getting another data name? such as "name" instead of "rowid".

[Maurize]

Data gets saved via getSQLData Function so it does work with values in table...