By Spiderpoison is licensed under a License Creative Commons Attribution-non-Commercial Share-ShareAlike 3.0 License based on a work at sqlui.sourceforge.net.

Feel free to use these scripts how to give in the tile!!!

1. FOREWORD

The SQLui was created to facilitate the work of developers working with JSON files. The sqlui.class.php class can be used in any script in PHP to faciliar creating and manipulating JSON data files. SQLui is an alternative for developers who do not want to use a conventional base or NoSQL data.

One minimal of knowledge about PHP, SQL and JSON is expected to interpret this manual.

2. GETTING STARTED

All SQL statements must be written in capital letters. All references to fields and tables must be carried out with lower case letters. All records should be treated as strings. There should be no line breaks in query.

The command will always return an array containing the key and the records, how to you can see in the example below.

Query:

    SELECT * FROM test

Return:

    Array(
        [0] => Array(
                [field_name] => value
                [field_name] => value
                ...
            )                           
        [1] => Array(
                [field_name] => value
                [field_name] => value
                ...
            )                           
        )

The class yet can return arrays with 'notice' or 'error' in the case of failures.

Query:

  CREATE TABLE users

Return:

    Array(
        [error] => Array(
            [0] => Table users already exists
        )
    )

or

Query:

    CREATE TABLE test

Return:

    Array(
        [notice] => Array(
            [0] => Command Successfully
        )
    )

3. HOW TO DO?

By placing the files on the server and run the first access, you will automatically create a directory "database" with JSON files "users.json" and "levels.json".

You must configure the permissions of access to server files to maintain content integrity.

If you want to change or enter a new user the password, field password must be encrypted as follows below:

  UPDATE users SET password=PASSWROD('your password') WHERE id='0'

or

  INSERT INTO users VALUES('id','name',PASSWROD('your password'))

After initial setup you can use the system via the web interface, posting commands directly into the bar, or load the class in your PHP script.

To load the class add this command below.

    $sqlui=false;
    if (!$sqlui) $sqlui = new SQLui();
    $sqlui->Connect('your_user','your_password');
    $sqlui->Database('your_database');

The Database method is optional , you can use TAKE command to select or change a database used.

    $sqlui=false;
    if (!$sqlui) $sqlui = new SQLui();
    $sqlui->Connect('your_user','your_password');
    $sqlui->Command('TAKE database_name');

Default user and password are respectively 'root' and 'pass'. All user validations will be made in databases/sqlui/users.json, so, to add or remove access to the system, manipulate this table.

    $command=$sqlui->Command("UPDATE users SET password=PASSWROD('Your new password') WHERE id='0'");

Return:

    $command=Array(
        [notice] => Array(
            [0] => Command Successfully
        )
    )

And to read the records:

    foreach($command as $value){
        echo $value[col_name];
    }

And this print

    Command Successfully

4. DEFINITIONS, STATEMENTS AND SYNTAX

Commands in brackets are optional. The items inside braces are required. All posted values should be treated as strings between single or double quotes.

4.1. SHOW Syntax

Display informations about databases or tables.

    SHOW {DATABASES|TABLES|TABLE {tbl_name}}

4.1.1. Display databases.

    SHOW DATABASES
    Array(
        [0] => Array(
            [0] => database_name
            [1] => database_name
        )
    )

4.1.2. Displays selected database.

    SHOW DATABASE
    Array(
        [0] => Array(
            [0] => database_name
    )

4.1.3. Displays database tables.

    SHOW TABLES
    Array(
        [0] => Array(
            [0] => table_name
            [1] => table_name
            ...
        )
    )

4.1.4. Displays tables fields.

    SHOW TABLE table_name
    Array(
        [0] => Array(
            [0] => field_name
            [1] => field_name
            ...
        )
    )
4.2. CREATE Syntax

Use this function to create tables or databases.

    CREATE {DATABASE {db_name}|TABLE {tbl_name({col[,col...]})}

4.2.1. Create Database.

    CREATE DATABASE test
    Array(
        [notice] => Array(
            [0] => Command Successfully
        )
    )

4.2.2. Creates a new table.

    CREATE TABLE test(id,name)
    Array(
        [notice] => Array(
            [0] => Command Successfully
        )
    )
4.3. DROP Syntax

You can delete your databases and tables.

    DROP [DATABASE {db_name}|TABLE {tbl_name}]

4.3.1. Delete database.

    DROP DATABASE test
    Array(
        [notice] => Array(
            [0] => Command Successfully
        )
    )

4.3.2. Delete a table.

    DROP TABLE test
    Array(
        [notice] => Array(
            [0] => Command Successfully
        )
    )
4.4. TAKE syntax

Select or change a database.

    TAKE db_name

Example:

    TAKE database
    Array(
        [notice] => Array(
            [0] => Command Successfully
        )
    )
4.5. ALTER TABLE Syntax

Changes a table structure.

    ALTER TABLE {tbl_name}
        {ADD|DROP col_name[col_name...]}|{CHANGE col_name new_name[,col_name new_name...]}

4.5.1. Add a field

    ALTER TABLE test ADD field

4.5.2. Delete a field

    ALTER TABLE test DROP field

4.5.3. Change name of a field

    ALTER TABLE test CHANGE field_1 new_name_1,field_2 new_name_2

All Return:

    Array(
        [notice] => Array(
            [0] => Command Successfully
        )
    )
4.6. TRUNCATE Syntax

Truncate a table.

    TRUNCATE {tbl_name}

Example:

    TRUNCATE test
    Array(
        [notice] => Array(
            [0] => Command Successfully
        )
    )
4.7. SELECT Syntax

Select the contents of a table.

    SELECT [DISTINCT] [COUNT] {tbl_name.col_name|col_name|*}
        FROM {tbl_name} [[LEFT]JOIN {join_tbl_name} ON {where_condition}]
            [WHERE {where_condition}]
                [ORDER BY {col_name}[ASC | DESC]]
                [LIMIT {[offset,]row_count}]
                [INTO 'file_name']

Example:

    SELECT tbl1.col,tbl2.col FROM tbl1 JOIN tbl2 ON tbl2.col=tbl1.col LIMIT 2
    Array(
        [0] => Array(
            [tbl1.col] => string
            [tbl2.col] => string
        )
        [1] => Array(
            [tbl1.col] => string
            [tbl2.col] => string
        )
    )
4.8. COUNT Syntax

Return a count matches a query.

    COUNT(col_name[,col_name...]|*)

Example:

        SELECT COUNT(*) FROM tbl1
    Array(
        [0] => Array (
            [count] => 1
        )
    )
4.9. WHERE Syntax

Used to filter records.

    WHERE {tbl_name.col_name|col_name}{operator}{'string'}
        [{AND|OR} {tbl_name.col_name|col_name}{operator}{'string'}...]

Where operators are.

OperatorResult
=equal
<>not equal
!=not equal
>greater than
<less than
>=greater than or equal
<=less than or equal
LIKEsearch for a pattern

Example

    SELECT col FROM tbl WHERE col='needle'
    Array(
        [0] => Array (
            [col] => needle
        )
        [1] => Array (
            [col] => needle
        )
    )
4.10. LIKE Syntax

Used to filter records using a pattern.

    [NOT] LIKE {'[operator]string[operator]'}

Where, operators are a signal %, and can be used this way.

OperatorFind word
'string%'starting with
'%string'ends
'%string%'contains

Example:

     SELECT col FROM tbl WHERE col LIKE 'ne%'
    Array(
        [0] => Array (
            [col] => needle
        )
        [1] => Array (
            [col] => never
        )
        [1] => Array (
            [col] => next
        )
    )
4.11. INSERT INTO Syntax

Insert new records in a table.

    INSERT INTO {tbl_name[(col_name,...)]}
        VALUES{('string'[,'string'...])}[,('string'[,'string'...])]

Example:

    INSERT INTO tbl(id,name) VALUES('0','name 0')
    Array(
        [notice] => Array (
            [0] => Command Successfully
        )
    )

When you insert more than one record, use this syntax to make the process faster.

    INSERT INTO tbl VALUES('0','name 0'),('1','name 1'),('2','name 2')
    Array(
        [notice] => Array (
            [0] => Command Successfully
        )
    )
4.12. UPDATE Syntax

Update records in a table.

    UPDATE {tbl_name} SET col_name1={'string'}[,col_name2={'string'}...]
        [WHERE {where_condition}]

Example:

    UPDATE tbl SET col1='string',col2="string" WHERE col1='test'
    Array(
        [notice] => Array (
            [0] => Command Successfully
        )
    )
4.13. DELETE Syntax

Delete records in a table.

    DELETE {col_name|*} FROM {tbl_name} [WHERE where_condition]

Example:

    DELETE col FROM tbl WHERE col='test'
    Array(
        [notice] => Array (
            [0] => Command Successfully
        )
    )
4.14. PASSWORD Syntax

Encrypts you text using SQLui Password.

    PASSWORD(col_name|'string')

4.14.1 Apply encryption in the selected field.

    SELECT PASSWORD(field) FROM tbl1
    Array(
        [0] => Array (
            [0] => 8c772c65c6e2f5a92cf18fb01688cd7b
        )
    )

4.14.2 Apply encryption on update or insert.

    INSERT INTO users VALUES('1','name',PASSWORD('password'))

or

    UPDATE users SET password=PASSWORD('password')
    Array(
        [notice] => Array (
            [0] => Command Successfully
        )
    )

4.14.3 Apply encryption on where clause.

    SELECT * FROM users WHERE password=PASSWORD('pass')
    Array(
        [0] => Array (
            [0] => 0
            [0] => user
            [0] => 8c772c65c6e2f5a92cf18fb01688cd7b
        )
    )

© 2015 SQLui. All rights reserved.