web development

With the INT data type, both positive and negative numbers are generally allowed. With the attribute UNSIGNED, the range can be restricted to the positive integers. But note that then subtraction returns UNSIGNED integers, which can lead to deceptive and confusing results.

With TINYINT, numbers between -128 and +127 are allowed. With the attribute UNSIGNED, the range is 0 to 255. If one attempts to store a value above or below the given range, MySQL simply replaces the input with the largest or, respectively, smallest permissible value.

Optionally, in the definition of an integer field, the desired column width (number of digits) can be specified, such as, for example, INT(4). This parameter is called M (for maximum display size) in the literature. It assists MySQL as well as various user interfaces in presenting query results in a readable format.

AUTO_INCREMENT Integers

With the optional attribute AUTO_INCREMENT you can achieve for integers that MySQL automatically inserts a number that is 1 larger than the currently largest value in the column when a new record is created for the field in question. AUTO_INCREMENT is generally used in the definition of fields that are to serve as the primary key for a table.

The following rules hold for AUTO_INCREMENT:

• This attribute is permitted only when one of the attributes NOT NULL, PRIMARY KEY,or UNIQUE is used as well.

• It is not permitted for a table to possess more than one AUTO_INCREMENT column.

• The automatic generation of an ID value functions only when in inserting a new data record with INSERT, a specific value or NULL is not specified. However, it is possible to generate a new data record with a specific ID value, provided that the value in question is not already in use.

• If you want to find out the AUTO_INCREMENT value that a newly inserted data record has received, after executing the INSERT command (but within the same connection or transaction), execute the command SELECT LAST_INSERT_ID( ).

• If the AUTO_INCREMENT counter reaches its maximal value, based on the selected integer format, it will not be increased further. No more insert operations are possible. With tables that experience many insert and delete commands, it can happen that the 32-bit INT range will become used up, even though there are many fewer than two billion records in the table. In such a case, use a BIGINT column

Of all the MySQL numeric data types, you will likely use INT most often. You can run into problems if you define your fields to be smaller than you actually need; for example, if you define an id field as an unsigned TINYINT, you won’t be able to successfully insert that 256th record if ID is a primary key (and thus required).

Note that for each column, in addition to the data type, you can specify additional attributes. The following list names the three most important of these that are available for all data types:

• NOT NULL: The column may not contain the value NULL. This means that in saving a new data record an explicit value must be given (unless there is a default value for the column).

• NULL: The column is allowed to contain the value NULL. This is the default setting if when the table was created, neither NOT NULL nor NULL was specified.

• DEFAULT n or DEFAULT ‘abc’: If no value is specified for a data record, MySQL automatically provides the value n or the character string ‘abc’. (Here is a tip for advanced users: If you would like MySQL to insert the default value for an INSERT command, you must specify for the column either no value at all, or an empty character string. It is not permitted to supply the value NULL. In that case, MySQL stores NULL or returns an error message if NULL is not allowed.)

Even if you do not explicitly provide a default value, in many instances, MySQL will itself provide one: NULL if NULL is allowed, and otherwise, 0 for a numerical column, an empty character string for VARCHAR, the date 0000-00-00 in the case of dates, the year 0000 for YEAR,or the first element of an ENUM enumeration.

The configuration with $cfg[’Servers’][$i][’auth_type’] = ‘config’ is simple, but it is inflexible if various users wish to manage their own databases, and insecure because user names and passwords appear in plain text in config.inc.php.

 If as an Internet service provider wish to allow many users uniform access to phpMyAdmin, you should consider the two configuration variants described here. 

You must have administrator privileges for the MySQL server.

Creating a MySQL User for phpMyAdmin

Before http or cookie authentication can be used, a MySQL user must be created that can read mysql.user, mysql.db, and mysql.tables_priv (that is, it must be provided with the SELECT privilege).

The MySQL administrator can create such a user with the following SQL commands. The name of this user is irrelevant. For the following example, the name pmaUser will be used:

GRANT USAGE ON mysql.* TO pmaUser@localhost   

IDENTIFIED BY ‘xxxx’ GRANT SELECT

(Host, User, Select_priv, Insert_priv, Update_priv,

   Delete_priv, Create_priv, Drop_priv, Reload_priv,

 Shutdown_priv,     Process_priv, File_priv, Grant_priv,

 References_priv, Index_priv,     Alter_priv)

ON mysql.user TO pmaUser@localhost;

GRANT SELECT ON mysql.db TO pmaUser@localhost

 GRANT SELECT (Host, Db, User, Table_name, Table_priv,

Column_priv)

 ON mysql.tables_priv TO pmaUser@localhost

http authentication

In order to be able to use phpMyAdmin http authentication, make the changes shown in boldface to the file config.inc.php:

# in config.inc.php $cfg['Servers'][$i]['controluser'] = 'pmaUser‘;

// Read-only-User $cfg['Servers'][$i]['controlpass'] = 'xxx‘;

 // password $cfg['Servers'][$i]['auth_type']   = 'http‘;

// http authentication $cfg['Servers'][$i]['user']        = '';

 // empty! $cfg['Servers'][$i]['password']    = '';

// empty!

Now, every time that you want to use phpMyAdmin, the web browser’s login dialog prompt appears.

cookie Authentication

For cookie authentication, you must change only the auth_type line in config.inc.php:

# in config.inc.php $cfg['Servers'][$i]['controluser'] = 'pmaUser‘;

// Read-only-User $cfg['Servers'][$i]['controlpass'] = 'xxx‘;

  // password $cfg['Servers'][$i]['auth_type']   = 'cookie‘;

// cookie authentication $cfg['Servers'][$i]['user']        = '';

  // empty! $cfg['Servers'][$i]['password']    = '';

 // empty!

In the simplest case, both phpMyAdmin and MySQL run on the same local computer (that is, Apache, PHP, MySQL, and the scripts for phpMyAdmin are all locally installed).

Configuration of MySQL Access

The complete configuration of phpMyAdmin is accomplished in the file config.inc.php.In the simplest case, it suffices to set the following variables:

# configuration file config.inc.php . . .

// must contain the web address of the phpMyAdmin directory

$cfg['PmaAbsoluteUri'] = 'http://computer.name/phpmyadmin directory/'; . . .

$cfg['Servers'][$i]['host']      = 'computer.name‘;

// or 'localhost' . . .

$cfg['Servers'][$i]['auth_type'] = 'config';

 // must be 'config' $cfg['Servers'][$i]['user']      = 'root‘;

  // MySQL user $cfg['Servers'][$i]['password']  = 'xxxx‘;

// MySQL password …

If the MySQL server is running on the same computer as the web server that is executing the phpMyAdmin script files with PHP, then you give localhost as the computer name. Optionally, with $cfgServers[1][’port’] the TCP/IP port over which the link is established can be specified. That is necessary only if the default port 3306 is not the one to be used.

Servicing Several MySQL Servers with phpMyAdmin

Host, user, and password entries for multiple MySQL servers are possible in config.inc.php. If you make such entries, then on the start page of MySQL there appears a listbox with the names of the computers whose MySQL servers can be managed. Selecting one of these names establishes a link to that server.

The variable $cfg[’ServerDefault’] determines the server that phpMyAdmin uses to establish a link at startup. If the variable is set to 0, then at startup phpMyAdmin initially sets no link, but merely presents a listbox with the selection of servers.

If you do not wish to have simply the computer name (array variable host) displayed in the listbox, then you can use $cfg[’Servers’][$i][’verbose’] to display a different character string.

Additional Configuration Possibilities

In addition to the MySQL access data in config.inc.php, you can also set various options. The following list describes only the most important of these, together with their associated variables:

• $cfg[’Confirm’] = true/false: This variable specifies whether a confirmation query should be displayed before data are deleted (default: true).

• $cfg[’MaxRows’] = n: Specifies how many data records should be displayed per page as the result of a query (default is 30).

• $cfg[’ExecTimeLimit’] = n: Specifies how long the execution of the PHP code will be allowed to run (default is 300 seconds). The time is relevant primarily for time-intensive import/export commands.

• $cfg[’ShowBlob’]=true/false: Specifies whether the contents of fields of type xxxBLOB are to be displayed (default: false).

• $cfg[’ProtectBinary’] = false/’blob’/'all’: Specifies whether the contents of BLOB fields are to be write protected. By default (‘blob’), BLOBs cannot be changed. The prohibition against change to any table content (regardless of data type) is effected by ‘all’, while ‘false’ permits everything to be changed.

• $cfg[’LeftWidth’]=n: Specifies the width of the left column of the main window (by default, 150 pixels). In this column are displayed the listbox for database selection and the list of tables in the current database.

 It is what is displayed if the image
cannot be displayed - i.e. a not-graphic browser, the image is not found, etc.  Some browsers also show the alt tag when the mouse is placed over the image.

<img src=”filename.jpg” mce_src=”filename.jpg” alt=”Summer Vacation” title=”My trip to the
Grand Canyon” width=”640″ height=”480″>

It’s an attribute, not a tag.

It is displayed when the browser is incapable of displaying images. In the absence of any alt text, the image file name is usually selected by the client browser and displayed instead.

 The alt displays when images are turned off, the title
displays upon a mouseover. They are different things.

<?php // mysql-test.php ?>
<html><head>
<title>Test PHP and MySQL</title>
</head><body>
<?php
  $mysqluser=”root”;           // user name
  $mysqlpasswd=”xxx”;         // password
  $mysqlhost=”localhost”;    // computer name
  $connID = mysql_connect($mysqlhost, $mysqluser, $mysqlpasswd);
  $result=mysql_list_dbs();
  echo “<p>Databases at the local MySQL server<p>\n”;
  echo “<p>”;
  while($row = mysql_fetch_row($result)) {
    echo “<br><i>$row[0]</i>\n”;}
  echo “<p>”;
?> </body></html>

MySQL versions are identified by the attributes alpha, beta, gamma, and production:

• Alpha means that the version is in the throes of the development process and that new functions and even incompatible changes are to be expected. Although an alpha version is not published until it contains no known errors, it is highly probable that many undiscovered errors still lurk within. Loss of data during testing of an alpha version is quite possible!

• Beta means that this version is largely complete, but it has not been thoroughly tested. Major changes are not expected.

• Gamma means that the beta versions have become more or less stable. The goal now is discover errors and resolve them. Gamma versions are well suited to application developers who want to try out new functions on a test system.

• Production means that the MySQL development community has the impression that the version is mature and stable enough that it can be used for mission-critical purposes

ANSI SQL/92 is a standardized definition of the database query language SQL. Many commercial database systems are largely compatible with this standard and also offer many extensions.

Earlier version of Mysql ( lower than 5)  subSELECTs, views, triggers, stored procedures were examples of the lack of ANSI-SQL/92 compatibility.The lack of subSELECTs, views, SPs, and triggers does not greatly restrict the possibilities open to client programmers, but it does lead to a situation in which the program logic is transferred from the server level to the client level. The result is more complex or expensive client programming than would otherwise be the case, leading to redundancies in code and problems with maintenance and alteration of code.

In the context of a database system, a transaction means the execution of several database operations as a block. The database system ensures that either all of the operations are correctly executed or none of them. This holds even if in the middle of a transaction there is a power failure, the computer crashes, or some other disaster occurs. Thus, for example, it cannot occur that a sum of money is withdrawn from account A but fails to be deposited in account B due to some type of system error. Transactions also give programmers the possibility of interrupting a series of already executed commands (a sort of revocation). In many situations this leads to a considerable simplification of the programming process.

In spite of popular opinion, MySQL has supported transactions for a long time. One should note here that MySQL can store tables in a variety of formats. The default table format is called MyISAM, and this format does not support transactions. But there are a number of additional formats that do support transactions

Tables usually contain their data in no particular order (more precisely, the order is usually that in which the data have been entered or modified). However, for efficient use of the data it is necessary that from these unordered data a list can be created that is ordered according to one or more criteria. It is frequently useful for such a list to contain only a selection of the data in the table. For example, one could obtain a list of all of one’s customers, ordered by ZIP code, who have ordered a rubber ducky within the past twelve months.

To create such a list, one formulates queries. The result of the query is again a table; however, it is one that exists in active memory (RAM) and not on the hard drive.

To formulate a query one uses SQL instructions, which are commands for selecting and extracting data. The abbreviation SQL stands for Structured Query Language, which has become a standard in the formulation of database queries. Needless to say, every producer of a database system offers certain extensions to this standard, which dilutes the goal of compatibility among various database systems.

When tables get large, the speed at which a query can be answered depends significantly on whether there is a suitable index giving the order of the data fields. An index is an auxiliary table that contains only information about the order of the records. An index is also called a key.

An index speeds up access to data, but it has disadvantages as well. First, every index increases the amount of storage on the hard drive necessary for the database file, and second, the index must be updated each time the data are altered, and this costs time. (Thus an index saves time in the reading of data, but it costs time in entering and altering data. It thus depends on the use to which the data are to be put whether an index is on the whole a net plus or minus in the quest for efficiency.)

A special case of an index is a primary index,or primary key,which is distinguished in that the primary index must ensure a unique reference to a record. Often, for this purpose one simply uses a running index number (ID number). Primary indexes play a significant role in relational databases, and they can speed up access to data considerably