mysql.query¶

coronium.mysql.query( connection_table, query_string )

Calls a MySQL database with the given query string.

Parameters

Name	Details
connection_table	A database connection table. (Table, required)
query_string	The MySQL compliant query string. (String, required)

Returns

The result, if any, contained in a table array. If there is an error result will be nil.

Results

For SELECT queries, a table array of records is returned in the result key. Loop over the result set to gain access to the data.

Examples

local answer = coronium.mysql.query( { database = "app" }, "SELECT * FROM users" )
if not answer.error then
  for _, record in ipairs( answer.result ) do
    print( record.username )
  end
end

If the query is an INSERT, UPDATE, or DELETE the result will contain one of the following keys:

insert_id for INSERT statements
affected_rows for UPDATE statements
affected_rows for DELETE statements

local answer = coronium.mysql.query( { database = "app" }, "DELETE * FROM users" )
if not answer.error then
  print( answer.result.affected_rows )
end

Name	Details
.error	The error string, if any, from the last MySQL query. If there is no error, it will be nil.
.errorCode	The MySQL numerical error code, if any, for the error. If there is no error, it will be nil.

utils.mysqlString¶

coronium.utils.mysqlString( string )

Makes a string safe for MySQL.

Parameters

Name	Details
string	The string to make safe for MySQL. (String, required)

Returns

MySQL safe string.

Examples

local sql_safe_str = coronium.utils.mysqlString( str )

The Connection Table¶

To connect to a remote database, include the following keys:

connection_table = {
 database = "DATABASE",
 user = "DATABASEUSER",
 password = "DATABASEPASS",
 host = "DATABASEHOST",
 port = 3306
}

When using the local database on the Coronium instance, you simply pass the database key.

local answer = coronium.mysql.query( { database = "friends" },
"SELECT * FROM locations" )

if not answer.error then
 print( #answer.result )
else
 print( answer.error, answer.errorCode )
end

Building Queries¶

It is advised to use string.format and utils.mysqlString to build your MySQL query.

local in_data = coronium.input()
local username = coronium.utils.mysqlString( in_data.username )

--sanitize for MySQL
local query = string.format( "SELECT * FROM users WHERE username=%s", username )
local answer = coronium.mysql.query( { database = "app" }, query )

coronium.output( answer )

The local MySQL database it not accessible remotely, but you can connect to other remote hosts.

Video Tutorial¶

http://www.youtube.com/watch?v=ob8Yh8AsEd8