Database Operations

Learn about Zera's behavior with databases

Zera has an active DB connection that is created by the main modules, to do any database operation you may use the Zera database functions.

• dbh_do
• last_insert_id
• selectrow_hashref
• selectall_arrayref
• selectall

Execute an SQL code

$self->dbh_do(“SQL”,{},$param1, $param2, $paramx);

This function is used to execute any SQL query on the database, most commonly used for inserting, update and delete operations. The $param1, $paramx… parameters are optional.

Example:

$self->dbh_do(“INSERT INTO categories (category_id, category_name) VALUES(?,?)”,{},”1”,”Category”);

Get the auto increment field value

$self->last_insert(“table_name”,”field_name”);

This function returns the value of the last inserted record in the especified field. Use this function after any insert SQL query to get the auto increment id.

Example:

$self->dbh_do(“INSERT INTO categories (category_name) VALUES(?)”,{},”Category”);
my $new_category_id = $self->last_insert(“categories”,”category_id”);

Get a table row

$self->selectrow_hashref(“SQL SELECT”,{},$param1, $param2, $paramx);

Returns a row of the query in the form of a hash, use the column names to access each column value.

my $category = $self->selectrow_hashref(“SELECT category_id, category_name FROM categories WHERE category_id=?”,{},”2”);
print "Category name: $category->{category_name};"

Get multiple table rows (array of arrays)

$self->selectall_arrayref(“SQL”,{},$param1, $param2, $paramx);

This function is used to get records from an SQL query. The $param1, $paramx… parameters are optional. You can read the data as an array of arrays.

my $arrayref = $self->selectall_arrayref(“SELECT category_id, category FROM categories WHERE category_id > ?”,{},”100”);

To get the row 2 category value use

my $category_in_row_two = $arrayref->[1]->[1];

To create a loop and access each row

foreach my $row (@$arrayref){
    my $current_row_category_id = $row->[0];
}

Get multiple table rows (array of hashes)

$self->selectall("SQL SELECT", $param1, $param2, $paramx);

Returns all the results of the query in the form of an array of hashes. Access each row by column name

my $selectall = $self->selectall(“SELECT category_id, category FROM categories WHERE category_id > ?”,”100”);

Create a loop and access each row:

foreach my $row (@$selectall){
    my $current_row_category_id = $row->{category_id};
}

Placeholders and SQL injection

As you can see, in the database SQL queries we use question marks “?” and we add a parameter in the end for each one. This is very important and is used to sanitize the SQL query and prevent SQL attacks.