SQL Checking if a data already exists

[Karuzo]

Hey Guys,

so i have a question about sql again.

How can i check that a data exists already in the databse ?

For example. To check if the player has bought already a car.

Hope you understood me.

My Code:

  
local connection  
local vehicle 
  
function vehicleCreat( player, preis,vId) 
    local account = getAccountName(getPlayerAccount(player)) 
    connection  = dbConnect( "sqlite", "cars.db", "", "", "share=0" ) 
    local playerm = string.gsub ( getPlayerName ( player ), '#%x%x%x%x%x%x', '' ) 
if connection then 
        local query = dbQuery ( connection , "CREATE TABLE IF NOT EXISTS car (id NUMERIC, owner TEXT, name TEXT, health NUMBERIC, paintjob NUMERIC, color NUMERIC, upgrades NUMERIC)" ) 
         
    if query then 
        if getPlayerMoney ( player ) >= math.abs(preis) then 
            local vName = getVehicleNameFromModel ( vId ) 
            local query = dbExec ( connection, "INSERT INTO car(id,owner,name,health,paintjob,color,upgrades) VALUES(?,?,?,?,?,?,?)", vId, tostring(account), tostring(vName), 1000, 0, 0, 0) 
                outputDebugString("query successfull") 
            vehicle = createVehicle ( vId, 545.33130, -1257.61816, 16.62232, 0, 0, -45 ) 
            warpPedIntoVehicle(player, vehicle) 
            takePlayerMoney ( player, math.abs(preis) ) 
            setElementData(player, "CarGot", "no") 
            outputChatBox("Du hast dir erfolgreich ein  " .. vName.. " gekauft!", player, 0, 125, 0, false) 
        else 
            outputChatBox("Du kannst dir dieses Auto nicht leisten!",player, 125, 0, 0, false) 
        end 
    else 
        outputDebugString("Datenbank-Verbindung(CARSYS) konnte nicht hergestellt werden!") 
    end 
end 
end 
addEvent( "CarBuy", true  ) 
addEventHandler( "CarBuy", root, vehicleCreat ) 
  

[cheez3d]

You made a typo: health NUMBERIC instead of NUMERIC

[Karuzo]

Oh thank you, didn't noticed that.

Could you help me with the other problem too ?

[cheez3d]

SELECT * FROM `sometable` WHERE `somecolum` IS NOT NULL

[Karuzo]

And how do i ask ,

if the column is null ?

Like :

  
local checkcar = dbQuery(connection, "SELECT * FROM car WHERE id IS NOT NULL") 
if checkcar == nil then 
.... 
  

?

[Wei]

local checkcar = dbQuery(connection, "SELECT * FROM car WHERE id IS NOT NULL") 
if checkcar[1].columnHERE then 
  
end 

if you mean if column value is not nil

local checkcar = dbQuery(connection, "SELECT * FROM car WHERE id IS NOT NULL") 
if #checkcar ~= 0 then  
  
end 

if there is any result

Edited March 2, 2014 by Guest

[Karuzo]

Could you explain to me what this [1].id means ?

[Wei]

Could you explain to me what this [1].id means ?

you need dbPoll to get the value. [1].columnName should be bacause it returns the table created that way

[1] -- will get the first value from table

.columnName -- will get the value of the column row

[Karuzo]

Oh now i understand.

So

  
    local checkcar = dbQuery(connection, "SELECT * FROM car WHERE id IS NOT NULL") 
    if #checkcar ~= 0 then 
      
    end 

Will check if the column is empty , right?

[Citizen]

How can i check that a data exists already in the databse ?

For example. To check if the player has bought already a car.

The players can only own one car ? If yes, then I would suggest you to set a UNIQUE index on the owner column.

This way, your query will fail if you want to insert an entry in this table with an owner that is already in the table (so it means that he already owns a car).

But dbExec doesn't tell you if the query has failed or not, you will have to use dbQuery and then dbPoll (the last one will return you the error code return by the database if any).

To create an UNIQUE index on a table column:

CREATE INDEX index_name ON table_name (column_name); 

[Wei]

This will check there is any value in the table. But in this query it is not needed

[Karuzo]

@Citizen:

What would be index_name ?

I mean lets say i INSERT INTO the owner column a owner, and i had a index on that column, and the owner has already a car.

Would that interrupt the INSERT and would do nothing ?

And how is that with dbPoll?

I'm pretty new to SQL.

[Citizen]

@Citizen:

What would be index_name ?

replace index_name by whatever you want. It's a name that will identify the index you are creating so you will be able to delete that index using that name if you need to allow multiple cars with the same owner later.

Be carefull, there are multiple index types (index are just constraints):

http://www.w3schools.com/sql/sql_constraints.asp

So usually, you will set a PRIMARY KEY on the id column (not the vehmodel, the id of the entry/line in the table) and add the AUTO_INCREMENT on that same column. It will just set a new id for you, so no need to set the id when using ur INSERT query.

And if you need to add a constraint on a column to be sure you won't store two times a same value on that column in that table, just add a UNIQUE index (UNIQUE is the type of that index).

I mean lets say i INSERT INTO the owner column a owner, and i had a index on that column, and the owner has already a car.

Would that interrupt the INSERT and would do nothing ?

Yeah it will ! The database won't insert it in your table and will return an errorCode and errorText (but the error code is enough)

According to this page, the error code returned will probably be 19 if the player already owns a car:

http://www.sqlite.org/c3ref/c_abort.html

And how is that with dbPoll?

Well we (Solidsnake and myself) already show you how to do a query with dbQuery and getting the result with dbPoll:

viewtopic.php?f=91&t=72059#p667863

Just replace line 2 by:

local result, affectedRowsOrErrCode = dbPoll( qh, -1 ) 

If affectedRowsOrErrCode is equal to 1, then it means that the row has been inserted (number of affected rows). But if it is equal to 19, then it means that it's the error code returned by sqlite (since you didn't try to insert 19 rows ).

See the 2nd example of the dbPoll wiki page.

[Karuzo]

Ok,

so i tried that with the IDs,

but i get always an error.

bad argument #1 to 'ipairs' (table expected, got userdata)

dunno why.

  
            local ID = dbQuery(connection,"SELECT id FROM car") 
            for i,v in ipairs(ID) do 
            carid = v.id+1 
            end 

and than i just INSERT that carid in the id column.

[Citizen]

Well we (Solidsnake and myself) already show you how to do a query with dbQuery and getting the result with dbPoll:

viewtopic.php?f=91&t=72059#p667863

Did you even read what I just said ? Did you check the wiki page of dbQuery and dbPoll ?Don't you remember how we were getting the result(s) from database with dbQuery and dbPoll ?

local qh = dbQuery(connect, "SELECT * FROM accounts WHERE username='?' AND password='?'", username, password) 
local result = dbPoll( qh, -1 ) 
if result and #result == 1 then 
    --Good login and password since the query returned a result 
    local datas = result[1] 
    --datas is a table with all columns of the accounts table (but only with the datas of the player) 
    -- datas.username contains the username in the database 
else 
    -- Wrong login and/or password 
end 

dbPoll is giving you a the result(s) as a table not dbQuery.

Please do an effort like you always did.

[Karuzo]

Yeah,

a friend helped me out:

  
    local account = getAccountName(getPlayerAccount(player)) 
        local checkcar = dbQuery(connection,"SELECT * FROM car WHERE owner='"..account.."'") 
        local result = dbPoll(checkcar,-1) 
    if #result >= 1 then 
        outputChatBox("Du hast bereits ein Auto!",player, 125,0,0,false) 
--... 

Thank you anyway Citizen

[Citizen]

Well, it's called database optimisation. A well made database and well constructed queries will reduce the CPU load a lot.

For example, even your query can be improuved:

"SELECT COUNT(*) FROM car WHERE owner='"..account.."'" 

This way, sqlite won't get the values in each column if there is an entry that match the WHERE clause.

Yeah because in your script, you don't care about what are the values in each columns of the car that the query will potentially find, you just want to check the number of entries/car it will find for that owner.

http://www.w3schools.com/sql/sql_func_count.asp

[WhoAmI]

Look on google for SQLite Database manager - it is addon to explorers. You can create there databases, getting values etc.. It will create .sqlite file, which mta supports. In this addon you can do a column with auto_increment - for ids. Until I'm using it, everything is easier.