ZIM BASE - Database Simplified

ZIM Base lets you easily query MySQL database tables with PHP.
It runs MySQLi prepared statements for security at about 1/3 the size of MySQLi.
Parameters are in (light), commands are in red, secondary commands are in grey.
These are followed by ZIM Base result properties where applicable.

Get the ZIP FILE

CONNECT

require_once('zim_base.php');
if ($base->error) {echo "error connecting"; exit;}

VARIABLES - (name1, name2, etc.)

$base->makeVars("id", "data");
This method makes global variables from GET or POST.
Note: if using ZIM Bind see $base->simplify() in zim_bind.php internal docs
In this case we have:
$id: "123"
$data: "my GET content"

INSERT - (table, variables, update, where, more)

$result = $base->insert("zim_base", ["id"=>$id, "data"=>$data]);
$result = $base->insert("zim_base", ["id"=>$id, "data"=>$data, "date"=>"NOW()"]); // if date
if ($result->error || $result->affected_rows < 1) {echo "error"; exit;}
$result->query: INSERT INTO zim_base (id, data) VALUES (?, ?) // for the non-date command above
$result->values: 123, my GET content (values used for the ? above)
$result->types: is (i for integer, s for string - types used for the ? above)
$result->success: 1
$result->error:
$result->affected_rows: 1 (1 if new, 2 if updated, 0 if repeat update, -1 if unsuccessful)

INSERT > UPDATE - (table, variables, update, where, more)

$result = $base->insert("zim_base", ["id"=>$id, "data"=>$data], ["data"=>$data]);
if ($result->error || $result->affected_rows < 0) {echo "error"; exit;}
$result->query: INSERT INTO zim_base (id, data) VALUES (?, ?) ON DUPLICATE KEY UPDATE data=?
$result->values: 123, my GET content, my GET content (values used for the ? above)
$result->types: iss (i for integer, s for string - types used for the ? above)
$result->success: 1
$result->error:
$result->affected_rows: 0 (1 if new, 2 if updated, 0 if repeat update, -1 if unsuccessful)

UPDATE - (table, update, where, more)

$result = $base->update("zim_base", ["data"=>"Client's new Data"], ["id"=>$id]);
$result = $base->update("zim_base", ["data"=>"increased score", "score"=>"\score+1"], ["id"=>$id]); // \ to escape binding
if ($result->error || $result->affected_rows < 1) {echo "error"; exit;}
$result->query: UPDATE zim_base SET data=? WHERE id=?
$result->values: Client's new Data, 123 (values used for the ? above)
$result->types: si (i for integer, s for string - types used for the ? above)
$result->success: 1
$result->error:
$result->affected_rows: 1

SELECT > SINGLE - (table, fields, where, more)

$result = $base->select("zim_base", ["id", "data"], ["id"=>$id]);
$result = $base->select("zim_base", "*", ["id"=>$id]);
if ($result->error || $result->num_rows < 1) {echo "error"; exit;}
$record = $result->record;
if ($result->success && $result->num_rows > 0) {
    $record = $result->record;
    echo $record["data"];
}
if ($result->error || $result->num_rows < 1) {echo "error"; exit;}
$row = $result->row;
if ($result->success && $result->num_rows > 0) {
    $row = $result->row;
    echo $row[1];
}

$result->query: SELECT id, data FROM zim_base WHERE id=?
$result->values: 123 (values used for the ? above)
$result->types: i (i for integer, s for string - types used for the ? above)
$result->success: 1
$result->error:
$result->num_rows: 1
$result->metadata->field_count: 2
$result->metadata->fetch_field()->name: id
$result->metadata->fetch_field()->name: data
$result->record - the first record as an associative array with field name and value:

Array
(
    [id] => 123
    [data] => Client's new Data
)

$result->row - the first record as an array of data:

Array
(
    [0] => 123
    [1] => Client's new Data
)

$result->json - first field value - assumes single-field JSON data:

$result->json_array - decodes json first fields and encodes array of their values:

[123]

$result->assoc - an array of records each as an associative array with field name and value:

Array
(
    [0] => Array
        (
            [id] => 123
            [data] => Client's new Data
        )

)

$result->array - an array of records each as an array with field values:

Array
(
    [0] => Array
        (
            [0] => 123
            [1] => Client's new Data
        )

)

SELECT > MULTIPLE - (table, fields, where, more)

$result = $base->select("zim_base", "*", ["status"=>"1"]);
$result = $base->select("zim_base", ["id", "data"], ["data"=>["NOT", "=", ""]], "ORDER BY id");
$result = $base->select("zim_base", ["id", "data"], ["data"=>["OR", null, ["option 1","option 2","option 3"]]], "ORDER BY id");
$result = $base->select("zim_base", ["data", "COUNT(*)"], ["id"=>[null, ">" 100]], "GROUP BY data"); // then use $record["COUNT(*)"]
$result = $base->select("zim_base", "*", ["status"=>"1"], "AND custom='override'");
$result = $base->select("zim_base, zim_users", "*", ["zim_base.id"=>$id, "zim_users.id"=>$id]); // join
$result = $base->select("zim_base", ["id", "data"]);
$result = $base->select("zim_base");
if ($result->error || $result->num_rows < 1) {echo "error"; exit;}
foreach ($result->assoc as $record) {
    echo $record["data"];
}

if ($result->success && $result->num_rows > 0) {
    foreach ($result->assoc as $record) {
        echo $record["data"];
    }
}

if ($result->error || $result->num_rows < 1) {echo "error"; exit;}
foreach ($result->array as $row) {
    echo $row[1];
}

if ($result->success && $result->num_rows > 0) {
    foreach ($result->array as $row) {
        echo $row[1];
    }
}

$result->query: SELECT * FROM zim_base
$result->values: (values used for the ? above)
$result->types: (i for integer, s for string - types used for the ? above)
$result->success: 1
$result->error:
$result->num_rows: 1
$result->assoc:

Array
(
    [0] => Array
        (
            [id] => 123
            [data] => Client's new Data
        )

)

$result->array:

Array
(
    [0] => Array
        (
            [0] => 123
            [1] => Client's new Data
        )

)

$result->json_array: - all the records as JSON (assuming JSON is stored in single first field)

[123]

$result->json: - just the first record as JSON (assuming JSON is stored in single first field)

$result->record: - just the first record as associative array

Array
(
    [id] => 123
    [data] => Client's new Data
)

DELETE - (table, where)

$last_id = $result->assoc[$result->num_rows-1]["id"];
$result = $base->delete("zim_base", ["id"=>$last_id]);
if ($result->error || $result->affected_rows < 1) {echo "error"; exit;}
$result->query: DELETE FROM zim_base WHERE id=?
$result->values: 123 (values used for the ? above)
$result->types: i (i for integer, s for string - types used for the ? above)
$result->success: 1
$result->error:
$result->affected_rows: 1

QUERY - (SQL)

$result = $base->query("TRUNCATE TABLE zim_base");
Not a prepared statement so no binding - and not really executed for this example
$result->query: TRUNCATE TABLE zim_base
$result->native: [original mysqli result of statement]
$result->success: 1
$result->error:

REPLY - (messageType, message)

$base->reply("error", "cannot get data"); // echoes JSON: {error:"cannot get data"} then exits
$base->reply("success", "data added"); // echoes JSON: {success:"data added"} then exits
$base->reply("success - data added"); // echoes String: "success - data added" then exits

SETLOCK - (table, id, lockid, idfield, lockfield)

$base->setLock("zim_base", $id, $lockid);
Assuming table has id and lockid fields - this will enforce record locking:
if ($lockid != "") $base->setLock("zim_base", $id, $lockid);