We accept safe and secure Credit Card and PayPal Payments.
 
Perl Scripts

All Count
Attachment Mailer
Perl Scripts Build A FAQ Plus
Perl Scripts Clock In Center
eBackup Automated
Easy Poll
eSurvey
Fetch a File
Form Maker
Mailing List Server
MySQL Mate
PDF Creation
QCart
Quick Fix
Quote of the day
Speed Search
Task Manager
Traffic Pack
Upload Plus
Upload Gold
Upload Pro
Website Manager

 
Free Perl Downloads

Free Perl Scripts Main Page
Free Perl Scripts 404 Alerter
AccessLog Viewer
Build A FAQ
PHP Scripts eBackup
Free PHP Scripts ErrorLog Viewer
eVars - Server Info
HT Pass Creator
Upload Lite
Website Manager

 
JavaScripts

Free Java Scripts Alert Boxes
Free JavaScripts Browser Sniffer
Check email
Generators
Slide Show
Sudoku
Window Maker
More...

 
Extra Utilities

ASP Scripts Ascii Codes
Free ASP Scripts Color Picker
Font Finder
HT Pass Creator
Meta Cloak
Meta Magic
Pano Zooms
SlideShow
Server Size

 
Online Tutorials

Free HTML Scripts Glossary
Free HTML Scripts HTML
JavaScript
MySQL
SSI
Build Traffic
Other

 
Miscellaneous

About Us
Graphics
Testimonials
Installations
Latest versions

 
Hawk Eye in Tennis

Should Hawk Eye replace linesmen and lineswomen at all tennis tournaments?







 
View all Polls

Run Polls on your site

Run your own Surveys

 
Store Download FAQs Contact Us Support Programming Policies  
Tables     List of Categories

Page 1 Page 2

  1. Conventions used in this FAQ.

  2. How can I check if a mysql table exists?

  3. How do I create a mysql table?

  4. How do I add a new column to an existing table?

  5. How do I drop an existing column?

  6. Can I rearrange the structure of a table, change column positions by shifting it from one position to another?

  7. How do I rename a column?

  8. How do I repair a corrupted or damaged table?

  9. How do I redefine a column's definition?

  10. How do I drop a table?







  1. Conventions used in this FAQ.

    Any mysql statements or commands in this FAQ will appear in
    gray colored text using the monospace font.
    mySQL keywords will be in UPPERCASE, while variables such as tablenames will appear in lowercase.

    Top of Page


  2. How can I check if a mysql table exists?

    There are several methods available o test if a mysql database table actually exists, but no functions, I'm aware of, that can be used within a SELECT statement.

    1. You can call the mysql_list_tables() function in PHP :

    $result = mysql_list_tables(string database, resource [link_identifier]);
    

    Or you can call the listtables method in Perl :

    @tables = $VAR{db}->listtables;
    

    In both of the above cases, you then need to sift through the resulting array to see if the table name you're looking for is in the list. Additionally, both have been deprecated.

    2. You can use the 'SHOW TABLES' statement which is much quicker and easier to handle. In PHP you would use :

    $result = mysql_query("SHOW TABLES FROM database LIKE 'tablename'");
    
    Or in Perl:
    $VAR{q1} = qq~SHOW TABLES LIKE "tablename"~;
    $VAR{x1} = $DBCONN->query($VAR{q1});
    $VAR{n1} = $VAR{x1}->numrows;
    
    If $VAR{n1} is greater than or equal to 1, the table exists. If it equals 0 (zero) the table does not exist.

    Top of Page


  3. How do I create a mysql table?

    You first need the right to create tables, usually assigned to all users. A simple example is as follows :
    CREATE TABLE employees ( 
        id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, fname VARCHAR(255)
    )
    
    The above statement, once executed, would create a table named "employees". The table would contain 2 columns, id and fname. The first column, id, is the primary key and is auto incremented. That is, each time a new record is created, the value of that column, can not be null and increments by 1. When adding a new record, you would leave the value for the id column null and mySQL would automatically generate a unique value. The value would be treated as an integer that can be up to 11 digits in length.

    The second column, can contain null of blank values or values of up to 255 characters.

    Top of Page



  4. How do I add a new column to an existing table?

    ALTER TABLE employees ADD lname VARCHAR(250)
    
    The above command would add a new column to the employees table. The new column can contain up to 250 characters, which would cover most if not all last names. Each time you add a new column, the tablename, column name and column definition must be specified.

    Top of Page


  5. How do I drop an existing column?

    ALTER TABLE employees DROP lname 
    
    When dropping columns, all data within that column is permanently erased. Unless you have the table backed up, the data is irretrievable. Every row/record within the table loses the column you drop.

    Top of Page


  6. Can I rearrange the structure of a table, change column positions by shifting it from one position to another?

    Yes, the simplest way is to use the following statement :
    ALTER TABLE tablename MODIFY colname definition AFTER colname
    
    Or, to move it to the first position in the structure :
    ALTER TABLE tablename MODIFY colname definition FIRST
    


    Top of Page


  7. How do I rename a column?

    ALTER TABLE employees CHANGE oldcolname newcolname OLDDEFINITION
    
    oldcolname becomes newcolname. The employees table would no longer have a column named "oldcolname". You must also retain and include the old definition. e.g. VARCHAR(255)

    Top of Page


  8. How do I repair a corrupted or damaged table?

    Tables, especially on busy sites can often be corrucpted. Serv crashes, or any other unexpected termination of a process before it's completed can and does corrupt tables. When this happens, it's nice to know the following can quickly repair the damage.
    REPAIR TABLE tablename
    


    Top of Page


  9. How do I redefine a column's definition?

    ALTER TABLE employees MODIFY fname NEWDEFINITION
    
    Modifies the type of data stored in specified column. You may want to change it from VARCHAR(255) to INT(20) for example.

    Top of Page


  10. How do I drop a table?

    Dropping tables is not a common practice unless you're in the development or wind up stage. Once dropped, all data stored with the table is permanently lost, including the table structure itself. Use wisely.
    DROP TABLE tablename
    


    Top of Page


Page 1 Page 2

Was this page helpful?    
Speed Search our site
Survey Software
Create unlimited web based surveys on your website from your website

Linux Hosting Plans from $9.12 per month, includes Plesk Control Panel, MySQL databases, cgi-bin, crontab manager and 50 email accounts.

Discounted Scripts
Subscribe to our periodial newsletter to receive special offers.

Bathroom Hygiene
How often do you thoroughly clean your bathroom?








View all Polls

Run Polls just like this one on your website!


About us | Contact us | Script FAQs | Script Support | Website Hosting | Our Policies | Store | Testimonials
Subscribers log in here. Subscribe to our periodical newsletter for special offers on commercial scripts, hosting, exciting business opportunities, and industry news and events. Our Mailing List Software is powered by Consolidator    
HTML Plain text
©1999 - 2015 All content Copyright PerlScriptsJavaScripts.com Proudly hosted by LinuxHostingPlans.com