Skip to content
Lode Claassen edited this page Apr 27, 2016 · 2 revisions

The mysql component lets you connect to the database.

Basics

Connecting

fem\mysql::connect();

This is needed before any other call. It is best placed in public_html/index.php.

Select a single record

$user = fem\mysql::select('row', "SELECT * FROM `users` WHERE `id` = %d;", [42]);

var_dump($user); // array('id' => 42, 'name' => '...')

Select multiple records

$sql = "SELECT * FROM `users` WHERE `status` = 'active';";
$users = fem\mysql::select('array', $sql);

var_dump($users); // array(array('id' => 42, 'name' => '...'), ..)
var_dump(fem\mysql::$num_rows); // int(42)

Note: $num_rows contains the full amount of rows, even if the select()'s $type is set to 'row' of 'field'.

Insert, update, delete

$sql = "INSERT INTO `users` SET `name` = '%s';";
$binds = ['Zaphod Beeblebrox'];
fem\mysql::query($sql, $binds);

var_dump(fem\mysql::$insert_id); // int(42)
$sql = "
	UPDATE `users`
	   SET `name` = '%s'
	 WHERE `id` = %d
;";
$binds = ['Zaphod Beeblebrox', 42];
fem\mysql::query($sql, $binds);

var_dump(fem\mysql::$affected_rows); // int(1)

Extras

Select a single value

$sql = "SELECT COUNT(`id`) FROM `users` WHERE `status` = 'active';";
$count = fem\mysql::select('field', $sql);

var_dump($count); // int(42)

Raw queries

fem\mysql::raw("SET SQL_MODE='STRICT_ALL_TABLES';")

Note: This method does not escapes values and is thus not safe for sql injection. Prefer to use query() instead.

Multiple databases

You can switch between multiple databases while keeping the connection open.

Gives the mysqli connection object. This gives the possibility to switch connections while kee

fem\mysql::connect();
$connection_of_db1 = fem\mysql::get_connection_object();

fem\mysql::connect($config_for_db2);
$connection_of_db2 = fem\mysql::get_connection_object();

fem\mysql::set_connection_object($connection_of_db1);
fem\mysql::query($sql_for_db1);

fem\mysql::set_connection_object($connection_of_db2);
fem\mysql::query($sql_for_db2);

Configure login

There are four ways to supply the login details.

Environment

Supply a APP_MYSQL environment variable with a DSN (data source name). I.e. mysql://username:password@hostname:3306/database.

File

Edit the config/mysql.ini with all login fields.

host = "localhost"
user = "username"
pass = "password base64 encoded"
name = "database"
port = 3306
(Re-)Connect

Supply an array with all details to connect().

$config = [
	'host' => $hostname,
	'user' => $username,
	'pass' => $password,
	'name' => $database,
	'port' => $port,
];

fem\mysql::connect($config);
Custom

Extend the get_config() method and return an array with the login details.

Make a application/helpers/mysql.php file with the following contents:

<?php

namespace projectname\helpers;
use alsvanzelf\fem;

class mysql extends fem\mysql {
	protected static function get_config() {
		// get your details from somewhere else
		
		return [
			'host' => $hostname,
			'user' => $username,
			'pass' => $password,
			'name' => $database,
			'port' => $port,
		];
	}
}

Error handling

When mysql returns an error fem\mysql throws an exception. The $message and $code supplied to the exception are the mysql error message and error number.

If you however need access to those values yourself, you can fetch them via properties.

$number  = fem\mysql::$error_number;
$message = fem\mysql::$error_message;

Syntax

void connect(array $config=null)
mixed select(string $type, string $sql, array $binds=null)
mysqli_result query(string $sql, array $binds=null)
mysqli_result raw(string $sql)
mysqli get_connection_object()
void set_connection_object(mysqli $connection);

Arguments

Argument Method Details
$type select() One of the constants:
  • AS_ARRAY: multi dimensional array with an array for each record
  • AS_ROW: single dimensional array for the first record
  • AS_STRING: single value of the first column of the first record
$sql select()
query()
raw()
The sql query to be executed.

Use a sprintf formatted string to use automatic escaping. I.e. use '%s' for adding strings and %d for integers and supply those, in order, via the $binds argument.
$binds select()
query()
Optional. An array containing all user supplied values you want to escape before adding to the query.

Remarks

Escaping

Queries are automatically escaped if you supply values via the $binds argument.

Safe queries protection

query() is protecting you from executing unsafe UPDATE and DELETE statements. If they don't use a WHERE or LIMIT clause, they are blocked and an exception is thrown.

If you need to execute a unsafe query, use raw().

MySQLi

Even though its name is mysql, it does use the mysqli extension.

You can’t perform that action at this time.