The class jDbSchema allow you to manipulate the structure of the database. It have some methods to create a table, to retrieve the structure of a table, to add/modify/remove columns etc. It uses some other classes: jDbTable and jDbColumn.

You can retrieve a jDbSchema object by calling the method schema() of a jDbConnection object.

See the API documentation.

If you want to execute a SQL script containing several queries, you can call the execSQLScript() method of the object jDbTools. You retrieve this object by calling the method tools() of a connection object.

  $conn = jDb::getConnection();
  $conn->tools()->execSQLScript('/path/to/a/script.sql');

In order to use the table prefix indicated in the profile, it is strongly recommended to use the %%PREFIX%% tag before each name of tables. It will be replaced by the prefix (or by nothing if there is no prefix).

UPDATE %%PREFIX%%product.....;
INSERT .....;

The tool class for Postgresql has two methods to manipulate value for field having the array type: decodeArrayValue() et encodeArrayValue(). They allow to convert the Postgresql value into a PHP array and vice-versa.

    // we expect to have a connection to a Postgresql database here
    $conn = jDb::getConnection();
    $pgTools = $conn->tools();

    // example of decoding
    $record = $conn->query('SELECT myarrayfield FROM mytable')->fetch();

    // if the value of myarrayfield is '{{"meeting", "lunch"}, {"training", "presentation"}}'
    // phpValue will be 'array(array("meeting", "lunch"), array("training", "presentation"))'
    $phpValue = $pgTools->decodeArrayValue($record->myarrayfield);

    // example of encoding
    $phpValue = array("lunch", "presentation");
    $stmt = $conn->prepare("INSERT INTO mytable (myarrayfield) VALUES(:arr)");
    $stmt->execute(array( 'arr' => $pgTools->encodeArrayValue($phpValue, jDbPgsqlTools::ARRAY_VALUE_TYPE_TEXT)));
    // the value into myarrayfield will be '{"lunch","presentation"}'

You must give the type of the array values to encodeArrayValue, with the constants:

• jDbPgsqlTools::ARRAY_VALUE_TYPE_TEXT if the array contain strings
• jDbPgsqlTools::ARRAY_VALUE_TYPE_INT if the array contain integers
• jDbPgsqlTools::ARRAY_VALUE_TYPE_FLOAT if the array contain floats

If PHP values have not the indicated type, they will be converted to this type.

jDbWidget is a class providing useful methods.

  $dbw = jDb::getDbWidget(); // instead of getConnection()

  $record = $dbw->fetchFirst("SELECT name, first_name FROM user");
  
  $liste = $dbw->fetchAll("SELECT name, first_name FROM user");

For other methods of jDbWidget, see the API reference.