Database query help

The Database query cgi-bin is a Web interfaced perl scrypt (dbquery.pl) that allows you to query and manage data in any served Databases.

Index:

• 1) Database selection
• 2) Search method
  • 2.1) Normal
    • 2.1.1) Field to search in
    • 2.1.2) Search type
    • 2.1.3) Search string
  • 2.2) Advanced
• 3) Output format
  • 3.1) Define Custom
• 4) Output for printer
• 5) Submit
• 6) Add a new record
• 7) SQL query
• 8) Batch edit

• 1) Database selection

  When you call the scrypt in your browser, the served databases and related tables are listed at the beginning in a coloured table that appears to be as the following:

  Databases	DB 1	DB 2			DB ...	DB n
  Tables	Table 1	Table 2 a	Table 2...	Table 2 m	Table ...	Table n a	Table n b

  You can click on all the Table names you need, to select the database and the table of which you want to query and manage data.

  The selected database and table are specified below the horizontal row as:

  Database:	Database n
  Table:	Table n m

  ---

• 2) Search method:

  You can select Normal or Advanced search method. To toggle the search method, when Normal method is selected, the Advanced button is shown and when Advanced method is selected, the Normal button is shown.

  ---

  • 2.1) Normal

    With the Normal search method, quering data to the selected database and table you get the result records selected using a simple criterion for which only one user inserted string, Search string, had to match with the content of one selected field Field to search in per each query (or with the content of all the fields together) using Exact or Substring Search type.

    ---

    • 2.1.1) Field to search in

      The Field to search in is the field name whose content has to match with the Search string to get the record in the result of the query

      ---

    • 2.1.2) Search type

      The Search type is the type of research. You can select Exact or Substring type. In the first case you get only those records in which the Search string matches exactly with the content of the Field to search in. In the second case you get all the records in which the Field to search in contains the Search string.

      ---

    • 2.1.3) Search string

      The Search string is the string to which the content of Field to search in is compared.

      You can insert also a logical operator followed by the Search string (without spaces between operator and Search string).
      Allowed logical operators are:

      Operator(1)	meaning	possible search type
      !	not equal	Exact/Substring
      >	greater than (alphabetical)(2)	Exact
      >=	greater than or equal to (alphabetical)(2)	Exact
      <	less than (alphabetical)(2)	Exact
      <=	less than or equal to (alphabetical)(2)	Exact

      Note (1): if you search a string starting just with a "\" (backslash) or starting with a character used for the operators ( !, >, < ), you must insert a backslash as first character in the search string field. The second charater of the string, if it follows a "\" (or also in the third character of the string, if it follows "<\" or ">\") is not interpreted as a logical operator, but as part of the search string.

      Note (2): to get always a consistent answer to a database query based on the alphabetical order selection (via the logical operators), a simple suggestion is to insert always all the fields in the same format, following the rule that the most significant characters be located before the less significant. For example the date field must be inserted always in the same format <YYYYMMDD>; a complete name field must be inserted always in the same format <SURNAME GIVENNAME>.

      ---

  • 2.2) Advanced

    With the Advanced search method, you can query data to the selected database inserting one Search string for each field name of the specified table. You get as result all the records whose fields content match simultaneously with the corresponding user inserted string (logical ANDed match) using always the Substring Search type.

    For each search string you can insert also a logical operator followed by the Search string (without spaces between operator and Search string) following the rules in the above table.
    

    ---

• 3) Output format

  You can select the format of the query result. Generally the Full format is available to get a complete list of the content of all the fields of the matching records. Except for the Custom format you will get an output table for each matching record. With the Custom format you will get only one table for all matching records.

  ---

  • 3.1) Define custom

    You can customize the Custom output format table. If you click on Define Custom you can choise the field names to show in the output custom table. This choise is recorded as a cookie variable, so you don't need to reselect at the next query, neither if you restart your browser (it is written in the browser cookie file if your security advanced settings don' t deny it).

    ---

• 4) Output for printer

  If you select the checkbox Output for printer, you will get an output result more usefull for a print command (without all the buttons and the interactive parts). So, after this kind of query, to make another one query, you need to go back clicking on the browser Back button.

  ---

• 5) Submit

  This button submit the query with the above set parameters.

  ---

• 6) Add a new record

  This button allows you to add a new record into the selected database and table.
  If you have not the privileges to write into the database, an automatic e-mail request has sent to the database manager, that will update the database in a next time.

  ---

• 7) SQL query

  This Button is reserved to the root database manager.
  It allows you to make directly SQL query to the selected Database. You can insert both the name of a SQL scrypt file in the SQL command file field name and SQL commands directly into the SQL statements textarea.
  In both cases (in the textarea and into the scrypt file), separate SQL commands with a ";" (colon) followed by a <return> character.
  First the SQL scrypt file will be executed and then the SQL commands in the textarea.

  ---

• 8) Batch edit

  This Button is reserved to the root database manager and it is shown only if at least one update request of a managed database was previously made by one or more users.
  You can process the pending requests, accepting, modifying or discarding them, one at a time.