Working with the SQL...

[kevin433]

Hi,

I need help again This time it's about the SQLite in MTA. I started today with SQLite. What I want to try is to get an integer from the database and then show the integer for the Player in the textbox. That's how far I got:

function SQLData(source)
sourcename = getPlayerName ( source )
result = executeSQLQuery("SELECT Kevin433 FROM Geld WHERE Geld = '" .. usergeld .. "'")
outputChatBox("Du hast " .. usergeld .. "$" )
end
addCommandHandler("info", SQLData)

I have worked with MYSQL before, but not with SQLite. The thing is that when I write /info, nothing happens. The most of it is from the wiki pages and the forum.

[Aibo]

data fetched from the database is put into table "result" with the same key, so you'll have to access by it, like result[1].Geld

also fix your query, you're trying to get a value from column "Kevin433", where the Geld equals undefined variable "usergeld" *)

function SQLData(thePlayer)
  sourcename = getPlayerName ( thePlayer )
  result = executeSQLQuery("SELECT Geld FROM Geld WHERE Player = '" .. sourcename .. "'")
if #result > 0 then
outputChatBox("Du hast " .. result[1].Geld .. "$", thePlayer)
else
-- nothing found in the database
end   
end
addCommandHandler("info", SQLData)

(i dont know how you organized your Geld table, so i just put Player there, which is where you store the associated player's name or whatever)

Edited February 17, 2010 by Guest

[50p]

Please stop naming element variables "source". It will confuse you later when you'll have to deal with different types of elements and for functions which are attached to events it's even worse because MTA doesn't know which source to use and it will overwrite the hidden source passed to function call (the element from event) with the source from parameters list which can be everything depending on event.

If you expect player element call the variable "player", "thePlayer" or even "plr". If you expect vehicle name the variable "vehicle", "theVehicle" or "veh".

[Aibo]

totally agree, i somehow missed that source isn't the "hidden" source

[kevin433]

OK, the first thing I checked now was if it had created a table. I opened up the registry.db file with SQLiteadmin and it was empty I now decided to move over to the MYSQL module, I have already some experience using it in PHP, but I don't know how to create a table in my database with LUA In PHP it was mysql_query(CREATE TABLE table_name(..... . My server is already connected with the database.

I'm really confused about source, thePlayer etc... I changed everything with source where i excepted a player element with thePlayer (tried also player) and some things didn't work longer. I started with the "play" resource, and when i change everything to "thePlayer" or "player", then I can't even spawn

[Aibo]

i dont know what problems you've had, MySQL and SQLite have pretty much the same syntax.

that query you've wrote wouldn't work in MySQL either, as rememeber it. *)

as for changing player-thePlayer, you probably missed something there

[kevin433]

That's how it would be in PHP (copied it from a site, didn't remember the whole.)

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
 
// Create a MySQL table in the selected database
mysql_query("CREATE TABLE example(
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
name VARCHAR(30), 
age INT)")
or die(mysql_error());  
 
echo "Table Created!";
 
?>

So do I have to use the same syntax?

[Aibo]

almost, it would be something like this:

executeSQLQuery("CREATE TABLE 'example' ('id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 'name' TEXT, 'age' INTEGER)")

(that is if you use SQLite, of course. never tried MySQL module, but i guess there you'll have to use MySQL syntax and module's own query functions.)

[kevin433]

I finally made it!!

If someone has the same problem/question, here's the code:

function MYSQLData()
mysqlconnection = mysql_connect("hostname", "db_username", "db_password", "db_name",port (optional))
if(not mysqlconnection) then
outputServerLog("MYSQL Connection failed!")
else
outputServerLog("MYSQL Connection succeeded!")
	mysql_query(mysqlconnection, "CREATE TABLE User (Geld INT NOT NULL)")
end
end
 
addEventHandler ( "onResourceStart", resourceRoot, MYSQLData )

Thank you xbost and 50p (I will try it later again with player, thePlayer etc...)!

[kevin433]

Got one last question. When I try to "Select" the info from the table, the Server gives me this error:

ERROR: ...mods/deathmatch/resources/MYSQLSystem/Untitled 2.lua:9: attempt to get length of global 'result' (a userdata value)

I don't know what the error means

This is how my code is looking:

mysql_query(mysqlconnection, "CREATE TABLE IF NOT EXISTS User (Geld INT NOT NULL)")
	result = mysql_query(mysqlconnection, "SELECT Geld FROM User")
if #result > 0 then
outputServerLog("Geld:" .. result[1].Geld)
end

[Aibo]

as i told you, i don't know how exactly MySQL module works, since i never used it. the code i've posted was for SQLite.

erorr is caused by "if #result > 0 then", because "result" is not a table.

i think you need to use https://wiki.multitheftauto.com/wiki/Mod ... sql_result to get the data from "result" there.

personally, i'd stick with SQLite

[kevin433]

Yeah, I know that you said that don't know exactly the MYSQL module works, but I tried to ask if there is anyone in this Forum who knows how the MYSQL module work.

Thanks for the mysql_result function, I got it to work now. I forgot to add a row

Oh, yeah, forgot the code. For everyone who has the same problem:

function MYSQLData()
mysqlconnection = mysql_connect("hostname", "db_username", "db_password", "db_name",port(optional))
if(not mysqlconnection) then
outputServerLog("MYSQL Connection failed!")
else
outputServerLog("MYSQL Connection succeeded!")
	mysql_query(mysqlconnection, "CREATE TABLE IF NOT EXISTS User (Geld INT NOT NULL)")
	mysql_query(mysqlconnection, "INSERT INTO User (Geld) VALUES('500') ")
	result = mysql_query(mysqlconnection, "SELECT Geld FROM User")
outputServerLog("result"..mysql_result(result, 1, 1))
end
end
 
addEventHandler ( "onResourceStart", resourceRoot, MYSQLData )