Skip to main content

Posts

Showing posts with the label Altering Tables in mysql

Database Authentication

The final, and most complete of the three PHP authentication methods, is the utilization of a database to maintain and manage the usernames and passwords used to access PHP files. This solution provides advanced capabilities in administering authentication systems but also provides incredible flexibility and scalability to incorporate the authentication system into the database system as a whole. The first step of the process involves creating the user tables that will be used to house the authentication data. Storing Authentication Data The following table will be used to manage the storage of the login information that will be used by PHP to manage logins: CREATE TABLE `customers` ( `customerEmail` VARCHAR(40) NOT NULL, `lname` VARCHAR(25) NOT NULL, `fname` VARCHAR(25) NOT NULL, `title` ENUM('Mr.', 'Mrs.', 'Miss', 'Ms.','Dr.'), `passwd` VARCHAR(30), PRIMARY KEY (`customerEmail`) ); The idea of using the customerEmail as the lo

PHP Functions for Retrieving Metadata

The most complete way to retrieve metadata is by using a SELECT statement against the INFORMATION_SCHEMA database in the MySQL server. However, there are times that is overkill for the data needing to be collected for a PHP application. With that said, PHP offers multiple functions that provide methods for retrieving a small amount of metadata about the data in the MySQL server. Database Metadata The mysql_list_dbs() function retrieves the name of all the databases found on the server that are accessible by the user requesting the information: <?php // Load variables used in mysql_connect and connect to server include "connect_info.php"; $linkID1 = mysql_connect($dbhost,$dbuser,$dbpass) or die("Could not connect to MySQL server"); // Retrieve the names of the databases accessible by the user $schema_results = mysql_list_dbs($linkID1); print "The databases available to the root user are:<br>\n"; while (list($schema) = mysql_fetch

Altering Tables ( Changing Columns )

The second way to alter a column definition is to use a CHANGE clause. The CHANGE clause enables  the modification of both the column's definition and its name. To use this clause, specify the CHANGE  keyword, followed by the column's existing name, its new name, and its new definition, in that order. Note  that this means the existing name must be specified twice to change only the column definition (and not the  name). For example, change the last_name column from CHAR(30) to CHAR(40) without renaming  the column as follows: ALTER TABLE head_of_state CHANGE last_name last_name CHAR(40) NOT  NULL; To change the name as well (for example, to Surname), provide the new name following the existing  name: ALTER TABLE head_of_state CHANGE last_name Surname CHAR(40) NOT NULL; To change a column at a specific position within a table row, use FIRST or AFTER col_name. The  default is to change the last column.

Altering Tables ( Modifying Columns )

One method to alter a column definition is to use a  MODIFY  clause.  The name of the column to be  changed must be specified, followed by its new definition. Assume that the ID column's data type must be  changed from  INT to  BIGINT, to allow the table to accommodate larger identification numbers.  In  addition, the column needs to be changed to  UNSIGNED to disallow negative values. The following  statement accomplishes this task:  ALTER TABLE eu_countries MODIFY new_population BIGINT(12) NOT NULL; That ALTER TABLE statement changes the table structure as follows:  +----------------------+-------------------+-------+------+----------+--------+  | Field                    | Type                | Null  | Key  | Default | Extra |  +----------------------+-------------------+-------+------+----------+--------+  | name                   | char(52)          | NO   |         | NULL    |          |  | new_population | decimal(12,0) | NO   |         | NULL    |          

Altering Tables ( Add Columns )

After creating a table, there might be a change of requirements that forces changes to the structure of the  table.  If that happens, the table's structure can be modified without having to re-create the table.  This  section addresses some of the methods available for modifying the table structure and data.  Add Columns  For example, to add a column named Id to the eu_countries table, the following statement can be issued:  ALTER TABLE eu_countries ADD Id INT(6) NOT NULL;  That ALTER TABLE/ADD statement changes the table structure as follows:  mysql> DESCRIBE eu_countries;  +----------------------+-------------------+------+-----+-----------+--------+  | Field                    | Type                | Null | Key | Default  | Extra |  +----------------------+-------------------+------+-----+-----------+--------+  | name                   | char(52)          | NO   |       | NULL    |           |  | new_population | decimal(12,1) | NO   |       | 0.0        |