MySQL database

Example database table

user

id	name	firstname	lastname	country
1	admin	META	YOTA	USA
2	jay2021	Jason	Banks	USA
3	nash	Nash	Bird	DEU
4	sarah21	Sarah	Stone	GBR
5	barbara	Barbara	White	USA

Overview

Here you find all the functions you need to interact with the database. The connection to your database is initialized automatically. Just use these functions to insert, update and receive data from your database.

All database functions

// Sample data
$table = 'user'; // Name of the table
$where = array( 'active' => '1', 'country' => 'US' ); // only rows where the active is set to 1 and country set to US
$data = array( 'firstname' => 'Jerome', 'lastname' => 'Spencer', 'email' => 'jerome@gmail.com'); // sample data to update or insert 
$arguments = array( 'firstname' => 'Jerome' );
$sql = "SELECT * FROM user WHERE firstname=:firstname"; // SQL sring can have arguments
// Update
$db->update( $table, $data, $where );           // Update data in the database
// Insert
$db->insert( $table, $data );                   // Insert a new row
// Delete
$db->delete( $table, $where );                  // Delete all rows which match the $where
$db->deleteById( $table, $id );                 // Delete the row with the specified id value 
// Fetch
$row = $db->getByName( $table, $name );         // Get a row by name column
$row = $db->getById( $table, $id );             // Get row by id column
$row = $db->get( $table, $where );              // Get a row with the specified $where criteria 
$row = $db->getByQuery( $sql, $arguments );     // Get a row by executing the query with the specified arguments
$array = $db->getAll( $table, $where );         // Get all rows with the specified $where criteria
$array = $db->getAllByQuery( $sql, $args );     // Get all rows which match the query
// Execute
$row = $db->query( $sql, $args );               // Execute any type of query
// Access row data
echo $row['firstname']; // access a field from a database row

You do not need to initialize the database, since the this is done automatically by Metayota. The database is ready to use!

Fetch a single database row

`$db->get( $table, $where )`

Fetch a database row from the user-table with the username jay:

$userRow = $db->get( 'user', array( 'username' => 'jay' ) );
echo $userRow['email']; // print the users email address.

`$db->getByName( $table, $name )`

Fetch a row by the name column of the user-table:

$userRow = $db->getByName( 'user', 'jason' );

Instead of hard-coding ID's in your PHP-code, it is recommended to add an additional name-column to identify the row. You can not query like this other columns.

`$db->getById( $table, $id )`

Fetch a row by the id column of the user-table:

$userRow = $db->getById( 'user', 21 );

`$db->getByQuery( $query, $arguments )`

Fetch a row with a custom MySQL-query:

$db->getByQuery('SELECT * FROM house WHERE floors=:floors', array('floors' => 4) );

The data in the methods above is always delivered in an array, you can access your row like this $userRow['username']

Fetch multiple rows from a database table

`$db->getAll( $table, $where )`

Use the database insert function to add a new row in a table.

Insert a new row into the address table:

$where = array( 'status' => 'active', 'type' => 'admin' );
$array = $db->getAll( 'user', $where );

This query would fetch all users which are saved as type admin and with the status active.

Fetch multiple rows by a custom MySQL query

`$db->getAllByQuery( $query, $arguments )`

Fetch all rows from the user table where status is active:

$array = $db->getAllByQuery( "SELECT * FROM user WHERE status=:status", array('status' => 'active') );
foreach( $array as $databaseRow ) {
    echo $databaseRow['firstname'];
}

Insert a row into the database

`$db->insert( $table, $data )`

Use the database insert function to add a new row in a table.

Insert a new row into the address table:

$addressData = array( 'street' => 'Mainstreet 3', 'zipcode' => '21900', 'country' => 'us' );
$db->insert( 'address', $addressData );

Update a row in a database table

`$db->update( $table, $data )`

Use the update function to update an existing row in a database table.

Insert a new row into the address table:

$addressData = array( 'name' => 'Mainstreet 3', 'zipcode' => '21900', 'country' => 'us' );
$where = array( 'id' => 12 ); // Which rows should be updated
$db->update( 'address', $addressData, $where );

Custom MySQL query

`$db->query( $query, $arguments )`

Just use the query function to execute any custom MySQL query.

Insert a new row into the address table:

$arguments = array( 'country' => 'us' );
$row = $db->query( "SELECT * FROM address WHERE country=:country", $arguments );

Delete rows

`$db->delete( $table, $where )`

Delete all rows in the specified $table that match the $where criteria.

Delete rows in the address table:

$where = array( 'country' => 'CH' );
$db->delete( 'address', $where ); // Deletes all rows with country code CH

Delete row by id

`$db->deleteById( $table, $id )`

Deletes the row with the specified $id column.

Delete rows in the address table:

$db->deleteById( 'address', 41 ); // Deletes the row with id 41