txtSQL 2.2 Final Readme

View official documentation
View Changelog

Welcome to the txtSQL 2.2 readme. This page should give you a heads up on how to get started in using txtSQL. If you are done with this page and still require some help, try posting at our forums

  1- Extracting txtSQL
  2- Configuring the class file
      2.1- Directory Structure
  3- Including the class file
      3.1- Instantiating the class
      3.2- Connecting to txtSQL
      3.3- Changing the root password
      3.4- Selecting a database
  4- Executing Commands
      4.1- List of commands
      4.2- Printing Results
  5- Disconnecting from txtSQL
  6- Error handling
  7- Public txtSQL Functions

1. Extracting txtSQL

 When you open the .zip file up, you will notice that there are two files; txtSQL.class.php, and txtSQL.core.php. Extract the two files to the same directory.
Create a folder with an arbitrary name; usually, it is named 'data'. This will be the folder containing the databases.
It can be located anywhere on the server, but usually it is located in the same folder as where you extracted the two files.
Make sure this directory is CHMOD'ed to 0755 or higher.
Now go back to the .zip archive and search for the file 'txtsql.MYI', and extract it to the data directory you just created.

OR

You can just copy the 'data' folder inside the .zip archive to your local harddrive.

2. Configuring the class file

Step one in using txtSQL, is to configure the class file so that it may be included into the php files that may require it. First, you must open the file 'txtSQL.class.php' in your favorite text editor; the one that I am currently using is TextPad which is freeware, and it has many tiny little features here and there. Once opening the file up you will notice a copyright statement, followed by some other stuff. Following the comments is a line that contains the following (line 30 by default): 

   30. include_once('./txtSQL.core.php');
What this line does is it includes the core functions and classes that are the workhorse of the txtSQL module. In order for php to be able to find the core file, you must edit the argument, the text enclosed in the single quotes, and have it point to the 'txtSQL.core.php' file.
  OR
Instead of having to alter the txtSQL.class.php file to point to the txtSQL.core.php, you can define a constant which will automatically do it for you. 
   <?php
   define('TXTSQL_CORE_PATH', 'PATH/TO/FOLDER/WITH/txtSQL_CORE_FILE');
   include_once('./txtSQL.class.php');
   ?>
This way, if the two scripts reside in two different folders, txtSQL will successfully be able to locate it with this constant.

2.1- Directory Structure

 A valid data directory structure consists of the following format:
+ datafolder ( folder with all the databases in them )
  + database_name
    + table.FRM (column definitions)
    + table.MYD (row data)
  + txtsql
    + txtsql.MYI (included with package)
Basically, a database is a folder inside of the main datafolder.

Also inside the datafolder is the txtsql database, with the txtsql.MYI that comes with the package.

Inside every database, a table is made up of two files; table.FRM, and table.MYD. The .FRM is the column definitions, and the other is the rows of data.

3. Including the class file

 Now that we are done configuring txtSQL 2.2, we can start working with it. First create a blank php document using your favorite text editor. As I said before, I am currently using TextPad. Save it as 'example.php'.

To make things less complicated, make sure that you save it in the same directory as where the txtSQL scripts are in.

Now we must include the php class, so type this in the newly created document 
   <?php
   include('./txtSQL.class.php');
   ?>

3.1- Instantiating the class

 In the world of object oriented programming (OOP for short), when classes are instantiated, or a copy of the class is created, a special type of variable called an object is automatically created.

The object is a pointer to many more variables or objects that are grouped in a special way. For more information on object oriented programming in php, see http://www.liquidpulse.net/articles/125.

We need to create an object that points to the txtSQL class and it's variables, so add this to the document 
   <?php
   include('./txtSQL.class.php');
   $sql = new txtSQL('./data');
   ?>
The argument, the text enclosed in the single quotes, is the path to the data directory containing all of the databases.

This folder must contain a folder called 'txtsql' (case-sensative), and inside that folder should be a file called 'txtsql.MYI'. This file contains all of the user/password combinations needed to operate with txtSQL.

This folder & file come packaged with txtSQL. Once the path is correct, you may move on to the next section.

3.2- Connecting to txtSQL

 We must now connect to txtSQL, with a correct username and password combination that will allow us to use txtSQL.

The default username for txtSQL is 'root', and the default password for it is blank (It is recomendded that you change the password later on). Now, add the following to the text file 
   <?php
   include('./txtSQL.class.php');
   $sql = new txtSQL('./data');
   $sql->connect($username, $password); // default is $sql->connect('root', '');
   ?>
txtSQL officially recognizes you as one of its users, and will allow you to query databases and table.

Refer to the documentation for a list of commands you may use

3.3- Changing the root password

 If you want to change the root password, which is reccommended, you can do so using the grant_permissions() function. The grant_permission() function call looks like this 
   <?php
   include('./txtSQL.class.php');
   $sql = new txtSQL('./data');
   $sql->connect($username, $password); // default is $sql->connect('root', '');
   $sql->grant_permissions($action, $user, $pass [, $newpass]);
   ?>
where $action can be either add, drop, or edit. The $newpass is only required if you want to edit a user.

$user is the username we are working with, and $pass is the password for that user.

For example, if we wanted to change the user 'root's password to 'bar' (assuming that the password is already blank), we would do the following 
   <?php
   include('./txtSQL.class.php');
   $sql = new txtSQL('./data');
   $sql->connect($username, $password); // default is $sql->connect('root', '');
   $sql->grant_permissions('edit', 'root', '', 'bar');
   ?>
OR

to create a new user named 'foo' with password 'bar' 
   <?php
   include('./txtSQL.class.php');
   $sql = new txtSQL('./data');
   $sql->connect($username, $password); // default is $sql->connect('root', '');
   $sql->grant_permissions('add', 'foo', 'bar');
   ?>
OR

to drop a user named 'foo' who has a password 'bar' 
   <?php
   include('./txtSQL.class.php');
   $sql = new txtSQL('./data');
   $sql->connect($username, $password); // default is $sql->connect('root', '');
   $sql->grant_permissions('drop', 'foo', 'bar');
   ?>
NOTE- you __CANNOT__ drop the user 'root'
         - you must supply the correct password for that user inorder to gain access

3.4- Selecting a database

 Just like mySQL, before doing anything with a table, you must first tell txtSQL which database the table is located in. This step is not crucial, because you can define a database in most queries alone.

We tell txtSQL what database we are working with by using the following: 
   <?php
   include('./txtSQL.class.php');
   $sql = new txtSQL('./data');
   $sql->connect($username, $password); // default is $sql->connect('root', '');
   $sql->selectdb('test'); // database 'test' is now selected

4- Executing Commands

 In order to, you have to call the respective function through the $sql object.

An example of 'selecting' data: 
   <?php
   include('./txtSQL.class.php');
   $sql = new txtSQL('./data');
   $sql->connect($username, $password); // default is $sql->connect('root', '');
   $sql->selectdb('test'); // database 'test' is now selected

   $results = $sql->select(array(
           'db'    => 'test', // This line is not necessary, we have already selected a database
           'table' => 'test',
           'where' => array('id = 10', 'and', 'name =~ John Smith'),
           'limit' => array(0, 100)
           ));
   ?>

4.1- List of commands

 The list of the more common commands that will work with txtSQL version 2.2 Final are listed below.

Before you can execute any of these commands you must first be connected, or an error will show. Click the commands for a more detailed explanation on their syntax, followed by a description and an example. See the documentation for a full list of functions

  • showdbs()
  • createdb()
  • dropdb()
  • renamedb()
  • select()
  • insert()
  • update()
  • delete()
  • showtables()
  • createtable()
  • droptable()
  • altertable()
  • describe()

    • 4.2- Printing results

     The $results variable now contains all of the rows of information inside the table 'test'. Table 'test' was defined as inside database 'test'.

    The only rows returned are the ones which fit the credentials that you give it ( The where clause ). You can use a foreach loop in this case to loop through the results 
       <?php
   include('./txtSQL.class.php');
   $sql = new txtSQL('./data');
   $sql->connect($username, $password); // default is $sql->connect('root', '');
   $sql->selectdb('test'); // database 'test' is now selected

   $results=
   $sql->execute('select',
           array('select' => array('id', 'name'),
                 'db'     => 'test',
                 'table'  => 'test',
                 'where'  => array('id = 10', 'and', 'name =~ John Smith'),
                 'limit'  => array(0, 100))));

   foreach ( $results as $key => $row )
   {
      print "ID: $row[id], NAME: $row[name]<BR>\n";
   }
   ?>

    5- Disconnecting from txtSQL

     At the end of every script should appear the following line to disconnect from txtSQL. It doesn't stop the script from working if it doesn't appear, it's merely present for development purposes. 
       <?php
   include('./txtSQL.class.php');
   $sql = new txtSQL('./data');
   $sql->connect($username, $password); // default is $sql->connect('root', '');
   $sql->selectdb('test'); // database 'test' is now selected

   $results=
   $sql->execute('select',
           array('select' => array('id', 'name'),
                 'db'     => 'test',
                 'table'  => 'test',
                 'where'  => array('id = 10', 'and', 'name =~ John Smith'),
                 'limit'  => array(0, 100))));

   foreach ( $results as $key => $row )
   {
      print "ID: $row[id], NAME: $row[name]<BR>\n";
   }

   $sql->disconnect();
   ?>

    6- Error Handling

     txtSQL comes pre-built with error handling capabilities. They are listed below, click a function for example usage and syntax

  • strict()
  • get_last_error()
  • last_error()
  • errordump()

    7- Public txtSQL Functions