db
Packages
Interfaces
Classes
Files

Class: Sql

Source Location: /db/mysql.class.php [line 55]

Class Overview


This class manages all the database access done by PHPBoost. It currently manages only one DBMS, MySQL, but we made it as generic as we could. It doesn't support ORM (Object Relationnal Mapping). On PHPBoost, all the table which are used contain a prefix which enables for example to install several instances of the software on the same data base. When you execute a query in a table, concatenate the PREFIX constant before the name of your table. Notice also that the kernel tables can have their name changed. You must not use their name directly but the constants which are defined in the file /kernel/db/tables.php. If you encounter any problem when writing queries, you should search what you need in the MySQL documentation, which is very well done: http://dev.mysql.com/doc/

Author(s):



Class Details

This class manages all the database access done by PHPBoost. It currently manages only one DBMS, MySQL, but we made it as generic as we could. It doesn't support ORM (Object Relationnal Mapping). On PHPBoost, all the table which are used contain a prefix which enables for example to install several instances of the software on the same data base. When you execute a query in a table, concatenate the PREFIX constant before the name of your table. Notice also that the kernel tables can have their name changed. You must not use their name directly but the constants which are defined in the file /kernel/db/tables.php. If you encounter any problem when writing queries, you should search what you need in the MySQL documentation, which is very well done: http://dev.mysql.com/doc/

Tags:

[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]


Class Methods

constructor Sql [line 60]

Sql Sql( )
Builds a MySQL connection.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method affected_rows [line 340]

int affected_rows( resource $resource, [string $query = ''])
Returns the number of the rows which have been affected by a request.

Tags:

  • return: The number of the rows affected by the specified resource.

Parameters:

resource   $resource   Resource corresponding to the request. The resource is given by the method which execute some queries in the data base.
string   $query   Deprecated field. Don't use it.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method auto_connect [line 129]

void auto_connect( )
Connects automatically the application to the DBMS by reading the database configuration file whose path is /kernel/db/config.php. If an error occures while connecting to the server, the script execution will be stopped and the error will be written in the page.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method close [line 392]

bool close( )
Closes the current MySQL connection if it is open.

Tags:

  • return: true if the connection could be closed, false otherwise.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method concat [line 297]

string concat( string $param)
Generates the syntax to use the concatenation operator (CONCAT in MySQL). The MySQL fields names must be in a PHP string for instance between simple quotes: 'field_name' The PHP variables must be bordered by simple quotes, for example: '\'' . $my_var . '\''

Tags:

  • return: The MySQL syntax to use.

Parameters:

string   $param   Element to concatenate. Repeat this argument for each element you want to contatenate, in the same order.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method connect [line 84]

int connect( string $sql_host, string $sql_login, string $sql_pass, $base_name $base_name, [$errors_management $errors_management = EXPLICIT_ERRORS_MANAGEMENT])
This method enables you to connect to the DBMS when you have the data base access informations.

Tags:

  • return: If you chose to manage the errors by a return value (ERRORS_MANAGEMENT_BY_RETURN), it will return the state of the connection:
    • CONNECTED_TO_DATABASE if the connection succed
    • UNEXISTING_DATABASE if the host could be joined but the data base on which PHPBoost must work doesn't exists
    • CONNECTION_FAILED if the host is unreachable or the login and the password weren't correct
    Otherwise, it won't return anything.

Parameters:

string   $sql_host   Name or IP address of the server on which is the DBMS you want to use.
string   $sql_login   Login enabling PHPBoost to connect itselft to the DBMS (the MySQL login).
string   $sql_pass   Password enabling PHPBoost to connect itself to the DMBS.
$base_name   $base_name   string Name of the data base PHPBoost must join an work on.
$errors_management   $errors_management   bool The way according to which you want to manage the data base connection errors :
  • ERRORS_MANAGEMENT_BY_RETURN will return the error
  • EXPLICIT_ERRORS_MANAGEMENT will stop the script execution and display the error message
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method count_table [line 268]

int count_table( string $table, int $errline, int $errfile)
Counts the number of the row contained in a table.

Tags:

  • return: The rows number of the table.

Parameters:

string   $table   Table name
int   $errline   The number of the line at which you call this method. Use the __LINE__ constant. It is very interesting when you debug your script and you want to know where is called the query which returns an error.
int   $errfile   The file in which you call this method. Use the __FILE__ constant. It is very interesting when you debug your script and you want to know where is called the query which returns an error.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method create_database [line 616]

string create_database( string $db_name)
Creates a data base on the DBMS at which is connected the current object.

Tags:

  • return: The name of the database created

Parameters:

string   $db_name   Name of the data base to create
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method date_diff [line 372]

string date_diff( string $field)
Generates the MySQL syntax which enables you to compute the number of years separating a date in a data base field and today.

Tags:

  • return: the syntax which will compute the number of years.

Parameters:

string   $field   Name of the field against which you want to compute the number of years.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method drop_tables [line 682]

void drop_tables( string[] $table_array)
Drops some tables in the data base.

Parameters:

string[]   $table_array   List of the tables to drop.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method escape [line 628]

string escape( string $value)
Escapes the dangerous characters in the string you inject in your requests.

Tags:

  • return: The protected string

Parameters:

string   $value   String to escape
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method fetch_assoc [line 316]

string[] fetch_assoc( resource $result)
Browses a MySQL result resource row per row. When you call this method on a resource, you get the next row.

Tags:

  • return: An associative array whose keys are the name of each column and values are the value of the field. It returns false when you are at the end of the rows.

Parameters:

resource   $result   MySQL result resource to browse. The resource is provided by the query_while method.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method fetch_row [line 328]

string[] fetch_row( resource $result)
Browses a MySQL result resource row per row. When you call this method on a resource, you get the next row.

Tags:

  • return: An array whose values are the value of the field. The fields are indexed according to the order they had in the select query. It returns false when you are at the end of the rows.

Parameters:

resource   $result   MySQL result resource to browse. The resource is provided by the query_while method.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method get_data_base_name [line 589]

string get_data_base_name( )
Returns the name of the data base which with the object is connected.

Tags:

  • return: the base name
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method get_dbms_version [line 580]

string get_dbms_version( )
Gets the version of MySQL used.

Tags:

  • return: The version used.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method get_executed_requests_number [line 512]

int get_executed_requests_number( )
Returns the number of request executed by this object.

Tags:

  • return: Number of request executed.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method insert_id [line 362]

int insert_id( [$query $query = ''])
Gets the ID generated from the previous INSERT operation.

Tags:

  • return: The ID generated for an AUTO_INCREMENT column by the previous INSERT query on success, 0 if the previous query does not generate an AUTO_INCREMENT value.

Parameters:

$query   $query  
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method limit [line 284]

string limit( int $start, [int $num_lines = 0])
Builds the MySQL syntax used to impose a limit in your row selection.

Tags:

  • return: The MySQL syntax for the limit instruction.

Parameters:

int   $start   Number of the first row (0 is the first).
int   $num_lines   Number of the rows you want to retrieve.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method list_databases [line 599]

string[] list_databases( )
Lists the existing data bases on the DBMS at which the object is connected. Only the data bases visible for the user connected will be returned.

Tags:

  • return: The list of the data bases
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method list_fields [line 410]

string[] list_fields( string $table)
Lists all the columns of a table.

Tags:

  • return: list of the fields of the table.

Parameters:

string   $table   Name of the table.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method list_tables [line 445]

string[] list_tables( )
Lists the tables (name and informations relative to each table) of the data base at which is connected this SQL object. This method calls the SHOW TABLE STATUS MySQL query, to know more about it, see http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Tags:

  • return: Map containing the following structure: for each table: table_name => array( 'name' => name of the table, 'engine' => storage engine of the table, 'row_format' => row storage format, 'rows' => number of rows, 'data_length' => the length of the data file, 'index_length' => the length of the index file, 'data_free' => the number of allocated but unused bytes, 'collation' => the table's character set and collation, 'auto_increment' => the next AUTO_INCREMENT value, 'create_time' => when the table was created, 'update_time' => when the data file was last updated )
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method num_rows [line 351]

int num_rows( resource $resource, $query $query)
Returns the number of rows got by a selection query.

Tags:

  • return: The number of rows contained in the resource.

Parameters:

resource   $resource   Resource corresponding to the result of the query.
$query   $query   Deprecated field. Don't use it.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method optimize_tables [line 648]

void optimize_tables( string[] $table_array)
Optimizes some tables in the data base.

Parameters:

string[]   $table_array   List of the tables to optimize.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method parse [line 474]

void parse( string $file_path, [string $tableprefix = ''])
Parses a SQL file. The SQL file contains the name of the tables with the prefix phpboost_.

Parameters:

string   $file_path   Path of the file.
string   $tableprefix   prefix The prefix you want to work with.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method query [line 156]

string query( string $query, int $errline, int $errfile)
Sends a simple selection query to the DBMS and retrieves the result. A simple query selects only one field in one row.

Tags:

  • return: The result of your query (the value at the row and column you chose).

Parameters:

string   $query   Selection query
int   $errline   The number of the line at which you call this method. Use the __LINE__ constant. It is very interesting when you debug your script and you want to know where is called the query which returns an error.
int   $errfile   The file in which you call this method. Use the __FILE__ constant. It is very interesting when you debug your script and you want to know where is called the query which returns an error.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method query_array [line 186]

void query_array( string $table, string $field, string $clause, int $errline, int $errfile)
This method makes automatically a query on several fields of a row. You tell it in which table you want to select, which row you want to use, and it will return you the values. It takes a variable number of parameters.

Parameters:

string   $table   Name of the table in which you want to select the values
string   $field   Name of the field for which you want to retrieve the value. If you want to work on several fields, you have to repeat this parameter for each field you want to select.
string   $clause   Where clause which will enable the method to know in which row it must select the values. It must respect the MySQL syntax and start off with 'WHERE '.
int   $errline   The number of the line at which you call this method. Use the __LINE__ constant. It is very interesting when you debug your script and you want to know where is called the query which returns an error.
int   $errfile   The file in which you call this method. Use the __FILE__ constant. It is very interesting when you debug your script and you want to know where is called the query which returns an error.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method query_close [line 382]

bool query_close( resource $resource)
Frees the memory allocated for a resource.

Tags:

  • return: true if the memory could be disallocated and false otherwise.

Parameters:

resource   $resource   Resource you want to desallocate.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method query_inject [line 234]

resource query_inject( string $query, int $errline, int $errfile)
This method enables you to execute CUD (Create Update Delete) queries in the database, and more generally, any query which has not any return value.

Tags:

  • return: The MySQL resource corresponding to the result of the query.

Parameters:

string   $query   The query you want to execute
int   $errline   The number of the line at which you call this method. Use the __LINE__ constant. It is very interesting when you debug your script and you want to know where is called the query which returns an error.
int   $errfile   The file in which you call this method. Use the __FILE__ constant. It is very interesting when you debug your script and you want to know where is called the query which returns an error.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method query_while [line 251]

resource query_while( $query $query, int $errline, int $errfile)
This method enables you to execute a Retrieve query on several rows in the data base.

Tags:

  • return: MySQL resource containing the results. You will browse it with the sql_fetch_assoc method.

Parameters:

int   $errline   The number of the line at which you call this method. Use the __LINE__ constant. It is very interesting when you debug your script and you want to know where is called the query which returns an error.
int   $errfile   The file in which you call this method. Use the __FILE__ constant. It is very interesting when you debug your script and you want to know where is called the query which returns an error.
$query   $query   The query you want to execute
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method repair_tables [line 660]

void repair_tables( string[] $table_array)
Repairs some tables in the data base.

Parameters:

string[]   $table_array   List of the tables to repair.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

method truncate_tables [line 672]

void truncate_tables( string[] $table_array)
Trucates some tables in the data base.

Parameters:

string[]   $table_array   List of the tables to truncate.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

static method clean_database_name [line 714]

static The clean_database_name( string $db_name)
Cleans the data base name to be sure it's a correct name

Cleans the data base name to be sure it's a correct name

Tags:

  • return: clean name

Parameters:

string   $db_name   Name to clear
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

static method highlight_query [line 523]

static string highlight_query( string $query)
Highlights a SQL query to be more readable by a human.

Tags:

  • return: HTML code corresponding to the highlighted query.

Parameters:

string   $query   Query to highlight
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]

static method indent_query [line 556]

static string indent_query( string $query)
Indents a MySQL query.

Tags:

  • return: The indented SQL query.

Parameters:

string   $query   Query to indent.
[ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]


Class Variables

[line 736]   string  $base_name  =  ''

Tags:

    [ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]
    [line 732]   bool  $connected  =  false

    Tags:

      [ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]
      [line 724]   resource  $link 

      Tags:

        [ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]
        [line 728]   int  $req  =  0

        Tags:

          [ Top ] - [ Class Details ] - [ Methods ] - [ Variables ]
          Documentation generated on Tue, 28 Jul 2009 22:43:40 +0200 by phpDocumentor 1.4.1