Why does website need database? Because Database empowers website. Without database, website does not achieve its full potential and power.
At the core of myNet.Office is built in myNet.Dbase, a user friendly interface, that lets you, with non SQL
database programming background, can do Create, Update, Delete, Queries
all DBMS (Database Management System) online easily. myNet.Dbase is designed for easy to run on any platform Windows, Unix,
Linux. It is designed
specifically to work with very large, high volume sites that connect to
databases back end. myNet.Dbase can contain millions of
There are many web databases but it costs money for license, hard to maintain, required developers, and required big infrastructure. myNet.Dbase is easy to use, powerful, and affordable.
What can I do with myNet.Dbase?
Simply, myNet.Dbase helps you store data and manage data on the web. Then Process, Share and Collaborate data and information with customers, staffs, and others. Things you can do:
1) To Create new table like Employee_data with maximum 64 fields per table.
2) To Delete the table.
3) To Update or Edit the table.
4) To Backup table, Save table Employee_data As another copy table named BAK_Employee_data .
5) To Add extra column field, such as EMAIL in the table.
6) To Rename filed such as NAME become LAST_NAME and Add extra field FIST_NAME
7) To Search single table, multi tables for contact with the phone number is 416
8) To Insert a new contact record into Employee_data table.
9) To Input, Update, Delete, View... the data of table use table Data Entry
10) To Send Email, electronic invoice from table record.
11) Add extra advanced fields like ENUM, SET to an existing table.
12) Built in SQL language that allows users run Database Programming, and get results displayed in a table row/col format. This is all power you need for database.
Example: Select Fname, Lname, Phone from table_employees
13) Import/Export data to file.
14) View the display of table in different view.
15) Automatic generate Data Entry for the table.
16) It is Relational Database. Which means you can have multi tables having relationship of 1-to-1, 1-to-many, many-to-many, in order to create complex big database.
For example, the built-in table x_Customer has 1-to-Many relationship with table x_Invoice.
17) .. More ....
myNet.Dbase -> Main Screen
Picture: myNet.Dbase main screen. It is a scroll list. Thus even the table has 10000 records. It loads only 10 or 50 .. records per page at one time. Thus load faster.
To edit a record or insert, simply click on the link primary key of the first column or for Data Entry. Click send email quickly to that person.
|MAIN SCREEN COMPONENTS||DESCRIPTION|
|CODE AREA||Area in the left corner where you submit SQL database programming.|
|[Submit SQL Code] button||Click on this button to run the SQL programming code. The
result will display in table row/col format.
In this example the SQL code= Select * from _acm_employees. Which means read and display all data of table _acm_employees.
|[Save As Query] button||Will save the SQL programming code in library as Query. Query is a short database program. Later, you retrieve and run it like a script. No need to enter code again. Such as, you have the code to read and display: Total, Gst, Pst ... for the first quarter in library programmed by some developers. You can open and run the Query script any time.|
|[ Create New Table] button||Click on this button will take you the Create New Table Template Tool in order to create a new table.|
|[ Add New Entry] button||Click on this button will take you the Data Entry screen in order to create a new record for this opening table.|
|expand||This button lets you hide the top area so that the display area has bigger space. Click again will un-hide the top area that include Code Area and Admin Area.|
|ADMINISTRATION AREA||Area in the right corner where you select Table to display pull down list of all tables in order to do Action of database admin like: Insert, Update, View, Search, Delete... records.|
|4) DROP PRIMARY KEY
Delete the primary key of this opening table.
5) ADD A TABLE PRIMARY KEY
Add the primary key for this opening table. Make sure use DROP first.
|STEPS TO DO ADMIN ACTION LIST||To do Admin Action list:
1) Select Table: to open the table name such as: _acm_employees.
2) Select from the list of Action only one Action: such as DELETE A COLUMN, or ADD A COLUMN | ADD ENUM
3) Follow step by step prompted until all conditions satisfied and the RUN button from disables to enables. Click on [RESET] not continue.
4) Finally, click RUN button to run the command action. DONE!
|TABLE DISPLAY OUTPUT AREA||The table display output of table when ever you submit a SQL code or select a table from the list of table.|
|Record per Page select||Lets you select how many pages, min 5 pages, max 1000 pages you want to display the output. The smaller pages display the faster loading.|
|<< First||GO back to the first record|
|<< Previous||GO back to the previous record|
|<< Next||GO back to the next record|
|Last >>||GO to the last record|
|List All >>||List all records of the opening table.|
|Reset||Reset the table to initial opening.|
|(sort Up) (Down)||Sort any column UP or DOWN by clicking on the column heading.|
|If there are many columns display that is hard to read, slow to scroll. Delete that column display.|
|Table relation icon of this filed ex. EMAIL with other
tables that share the same EMAIL column. You must setup using Create
Table Relation Template. Click on that relation icon will display the
info of the other relational table. Mouse hover on that icon will display
the relation table.
For example, table x_customer shares the same EMAIL of customer = firstname.lastname@example.org column with table x_invoice. When click on the relation icon on table x_invoice, will display all info of the table x_customer record that shares the same EMAIL = email@example.com. It lets you access info of that person from related table x_customer. The relation between x_customer and x_invoice is 1 -> to Many.
|ACTION column||is an action commands that let you do on records of the opening table.|
|Edit||Edit, Update this record.|
|Delete||Delete this record. Before delete, a prompt asks to confirm.|
|Email the person by looking up the field EMAIL of this row record.|
|View||View entire info of this record. You can use View menu to setup the current view of table display if the table has many columns displayed.|
|eBilling||Send an electronic invoice from a database record.|
|myNET.DBASE SUBMENU||From main menu [Net.DBase] is a list of sub menu such as: Administration, Tables, Reports, Form, Queries... are called Database Objects.|
|Administration||Open the main screen myNet.Dbase. The above picture.|
|Tables||Open the Create New Table Template where you begin to create a new table using interface.|
|Reports||Run the table report.|
|Form||Make a web form data entry for the open table.|
|Queries||Open a Queries Library where stores all short SQL database programming code. Click on each Query will run the program and display data in the main screen interface.|
|View||Let you select the View of selected columns for the current
opening table in order to reduce the loading time, and view only selected
For example, table x_customer has more than 35 columns too big to display, and slow down the loading. There are many fields you are not interested. Use View to select only column you want to see. But you can always see all data for that record by click on View icon from Action column.
|Relationship||Open the Create Table Relationship Template where you begin to create a new table relationships using template interface.|
|Import/Export||Open an interface let you import raw or text file exported by other databases. It also let you export raw data text file in format like comma delimited so that other external third party tables can read.|
Tables used by myNet.Office are named beginning with
x_ like x_customer,
x_invoice. Please do not name the same format.
Do not delete.
Introduction to Database
|Basic Database Administration||Description|
|What is Database?||Is a data warehouse, where you store data and information. A database contains a lot of tables.|
|What is Web Database?||
- Unlike fixed PC database stored on PC you can't access from any where. Use
web databases, you can work, share with staff, people any where, any
time. Your staff, people from different multi locations, just login your
website and access the web database.
- Web database is no limited by how many license users can use the database like PC database.
- Think fixed PC database like Outlook email vs. web database like web based Hotmail, Yahoo.
|What is Admin Database ?||Is a set of operations or tasks that you do on the data, such as: INSERT, DELETE, UPDATE, SEARCH...|
Note: All tables used by myNet.Office for configuration begins with x_, such as: x_email_property table.
Do not name same format.
|A database is made up
many tables. Each table stores different category of information. Example
tables: Employee data, Contact, Sale Inquiries...
Table contains field or column name, i.e., L_NAME, TITLE, EMAIL ... to
define the table.
There are few important notes about table:
Table name is one word. Can't name separated by space like employee data, but OK with employee_data
Table name is case sensitive. You should name table in lower case so that it can work on both server UNIX, LINUX, or WINDOWS platform.
In order to get data from table, we need a key. Table should always have a key called Primary Key. If you don't know key then just add an ID auto inc key.
If you can't create table using code, select Create Table Template Tool wizard. It will take care the rest.
|Record or Row||Row contains data of all Fields of a table. myNet.Office's table can have hundred of thousands of records.|
|Field or Column|| Table contains
names, called Field. Such as table Sales_Inquiries has fields like
Phone, Name, Email..
Like name of table, name of field is one word. Can't name First Name, but First_Name is ok.
A field is defined by the data type ( i.e., varchar, or text, Int ...) and the size.
Example: EMAIL varchar (20) is defined as character (not number) and can contains 20 characters. It input over 20 characters will be truncated to 20 characters.
|Data Type or Attributes or Field Characteristics. Not case sensitive.||To define type of data for a field, such as a number (Integer, Float..), or a character word (String, Varchar..)|
|varchar (has maximum 250 characters)
Data type is not case sensitive.
Varchar, VARCHAR is same.
|is a string of many character. 'a', 'b' is a character. "hello" is a VARCHAR of 5 characters or varchar (5). To define a field called f_name you write f_name varchar (30). Thus First name with at least 30 characters. Don't need to fill all 30 characters. Can be blank or no character.|
(contain 64 thousands of characters, or few hundreds of pages)
For example, you may want to declare (select) type for field: Resume, Cover_Letter as blob or text for your web form Submit Resume
to define a field like DESCRIPTION in table Sales Inquiries can have tens
of thousand characters.
Blob: like Text data type that can contain tens of thousand words but in binary characters. Use Blob to store images files, word doc files... like for submit Resume form.
myNet.Office will automatically creates a Text Area ( big box) interface for input data or form for Tex, Blob data type.
Here is the data type for String, characters.
|int, long ( long is bigger value than int)|| int is an Integer
number. Exp: 1, 50, 13900. Maximum value is -35000< 0 < 35000.
To define a field called Age as an integer: Age int. ( see define of COMPANY_SIZE data type as an integer in the blue code of table Sales Inquiries)
long is also defined as integer number but bigger value than int
|float, double (double is bigger value than float)||for table field data
is a decimal
number. Exp: 1899.35, 26.00. To define a field called salary you write code:
double ( See define of TOTAL_REVENUE data type as a double in
the blue code of Sales Inquiries)
Here is the data type for Number.
( set of list of selection. Choose only one choice.)
SUBJECT Enum ('Product', 'Service', 'Other Helps' ). Use
Create Table Template Tool, you just need to select its Name Type as
myNet.Office when encounter ENUM data type, will convert it into an option list to choose just one.
To add an ENUM field in table = my_support_center, example SUBJECT after SUBMIT_DATE column, you run this code:
ALTER TABLE my_support_center ADD SUBJECT Enum ('Product', 'Service', 'Other Helps' ) not null AFTER Submit_Date
To Delete SUBJECT column (field) in table = my_support_center
Alter table my_support_center drop column SUBJECT
(Select many choices in a list selection)
LIST_PRODUCTS_SERVICES Set ( 'Cryptopology', 'Compaq Server XP 35', 'Perfect
Mail', 'Cryptocard', 'Ebusiness Development Services', 'Online Consulting
This is data type used when users can select many choices in a set of many options.
Example, you have a LIST_PRODUCTS_SERVICES of many products and services you want users to select as many as they wanted by click on the check boxes [v]
|DATE (yyyy-mm-dd) or DATETIME (yyyy-mm-dd HH:MM:SS)||Is for Date field : 2008-03-24 or
DATETIME for field has date with time : 2008-03-24 14:56:35. Example,
APPOINTMENT_ DATE is Datetime data type from table Sales_Inquiries.
myNet.Office when encounter field Date or Datetime will has a calendar for easy input data.
is an integer number auto + 1.
|Used for table Primary Key type like ID primary key. Table needs a key, if you don't know or don't need then just select Int auto_increment as its key. Every time insert 1 record, ID is automatically + 1. It can't be used for field only key.|
|Primary Key||An unique field to
identify that table record. It shouldn't be duplicated
2 records that have the same key. Exp: emp_id, like Social Insurance, is a
key because whole company, no employee has the same emp_id. If you don't
need each row record is unique in a table the use the key Auto Increment Key
like ID key of Sales Inquiries table.
Table should have a Primary key. Use Auto Increment Key like ID if don't know or don't need.
Primary key should always be in the first position of table.
Primary key is used to protect the integrity of data.
For example, the Employee table if you use people FIRST_NAME as a key. What happens there are maybe many employees with the same first name John. When you do an UPDATE operation change the salary of John Smith, it also updates the salary of John Doer, John Lang... Who ever has first name John. As a result, every John has the same salary. Therefore, make sure primary key is unique for table. Use Employee ID rather than use Employee first name.
Not all tables required a unique primary key like Sales Inquiries table but should have a key. If you are not sure, use an Int Auto Increment like ID of Sales Inquires table. Each record has a unique ID number auto increment when insert.
The most popular Primary key are: Email, Phone, ID auto increment, SIN, UserID, CustomerID. Email is unique and used common for online because no member or customer has the same email. It is also used to send info like Password, Invoice.. to people. So if you don't know to select Primary key, but see table has Email then use Email is Primary key.
Programming Language is SQL
Learn more: INTRODUCTION TO DATABASE SCRIPT SQL.
scripting language. You can use SQL to Create, Insert, Delete, Update...
database. A database that has built in the ability of SQL Programming like
myNet.Dbase will have the ultimate power of data manipulations and
relational database. Database that doesn't have the ability of SQL
Programming will depend on the many steps of gui and interfaces in order to
get data is a weak database. You
don't need to know but just in case the business logic required to get
complex data, SQL becomes handy.
Not all databases built-in database programming, and Client/Server, Relational Web database
|Example - The script to create a new database table. It helps you understand about||database data type, fields, and how to create a new database table using simple script.|
CREATE TABLE my_support_center (
ID int not null auto_increment primary key,
NAME varchar (50),
Email varchar (50),
Phone varchar (50),
Company varchar (50),
SUBJECT Enum ( 'Question', 'Feedback/Ask', 'Help', 'Others Interest' ),
LIST_PRODUCTS_SERVICES Set ( 'Hosting Server', 'Note Book', 'Hardware Product', 'Software Product', 'E-Development Services',
'Consulting Services', 'Training Services', 'Sales Inquiries'),
ANALYZE THE DATABASE SCRIPT CODE.
- CREATE TABLE = Syntax to create new table.
- my_support_center = Name of new table. Must be lower case.
- ID int not null auto_increment primary key = Name field = ID. Defined as Primary Key and integer number and auto increment.
- NAME varchar (50) : NAME = name of the field. varchar = Data type of varchar - (50) = Size of this field that can contains 50 characters for NAME of that person.
- Code is not case sensitive, like STAFF_EMAIL or Staff_Email is not important, space not important.
- Field name, like STAFF_EMAIL or STAFFEMAIL, must be 1 word. Can't be: STAFF EMAIL
- To Create this table, Copy and Paste the blue code in the [Code Area] of myNet.Dbase.
The click [Submit SQL Code] button. It
will create the table
- Next, enter data by clicking on the first column field or the primary key field of record.
- Code is not case sensitive. EMAIL or Email is same. - Table name should be small case.
- You can also create new table using Create New Database Table Using Gui Template Tool. But use Code is faster. It is like English-syntax language.
- See HOW TO CREATE NEW TABLE.
- This database my_support_center contains advanced Data Type like:
- SUBJECT Data type = Enum Because Enum data type is used when there is a selection of one option in many options.
- LIST_PRODUCTS_SERVICES data type = Set Because Set data type allows to select not on1 but many options. It is in the opposite with Enum.
- Submit_Date Data Type = Datetime Because it is format: yyyy-mm-dd HH:MM
- Office_Notes Data Type = Text Because it can contains text up to 65000 characters, at least few hundreds of pages.
- Click see the result New table my_support_center is created by simple database scrip
Picture: This is the database table = my_support_center just created using database script. After having created a table, you need to input data.
Click to see its Data Entry interface Data Entry of table my_support_center ( Click on picture to enlarge)
Create new table using interface Create Table Template no SQL programming involved
Login to your website as Admin -> Click on [Net. Dbase] topmenu -> Select [Tables] sub menu. It opens Create Table Template Tool Interface.
myNET.DBASE -> Data Entry Form
In order to input data into table, you open table Data Entry Form. It lets you: Edit, Delete, Update, Search, Insert new table records.
To open a row record for edit or insert, simply click on the link primary key of the first column to open Table Data Entry.
Picture: Table Data Entry for the first record EID =2. To Insert click [Clear] button first.
|DATA ENTRY BUTTON||DESCRIPTION|
|< Back DB Admin||GO back to mNet.Dbase main screen|
|||< PREV||Go back to the first record|
|< PREV||Go back to previous record|
|NEXT>||Go to next record|
|NEXT>||||Go to last record|
|UPDATE||Update the current record. NOTE Can't Update the first row primary key.|
|DELETE||Delete this record.|
|INSERT||Insert new record. If in Update mode Insert is disable.
NOTE To insert new record, click on CLEAR button first.
|FORMAT||Format this Data Entry to display 1 column per row longer. Or display 2 column per row shorter.|
|SEARCH||Search for particular info such as Phone, Email then load into the Data Entry Form.|
|VIEW||View the current record in HTML format or TEXT format.|
myNET.DBASE -> Advanced SQL Database
You can admin myNet.Dbase database by using database programming or SQL. You can learn more click on button [SQL Help] from the left area.
Unlike Java, C/C++, SQL or database language is easy to learn like English syntax. You can do almost advanced SQL Database Programming by Copy your SQL code in the left Code Area.
Then click on [Submit SQL Code] button to run the program. You will see the result at the bottom int the Output Area.
This is a few basic syntax SQL database programming. To run SQL code: Copy or Type the code in the [Code Area]-> Click [Submit SQL Code] button to run.
|SELECT||SELECT * from employee_data||Select every thing and display from table employee_data|
|CREATE|| CREATE TABLE employee_data
emp_id int unsigned not null auto_increment primary key,
|To Create a database table employee_data|
|INSERT||INSERT INTO employee_data
(f_name, l_name, title, age, yos, salary, perks, email)
values ("Manish", "Sharma", "CEO", 28, 4, 200000,
|Insert data into employee_data table|
|DELETE||DELETE FROM employee_data
WHERE f_name = 'Hassan';
|Delete first name='Hassan'|
|UPDATE||UPDATE employee_data SET
yos=yos+1 where f_name='Manish';
||Update year of service of Mr. Manish increasing to 1.|
|SELECT||SELECT f_name, l_name, age, salary from employee_data where age >= 33;||Selects the names, ages and salaries of employees who are more than or equal to 33 years of age..|
|SELECT||SELECT f_name, l_name, title from employee_data
title = 'Web Designer' OR title = 'System Administrator';
|To list employees who are Web Designers and System Administrators|
|SELECT||SELECT f_name, l_name, age from employee_data where age BETWEEN 32 AND 40;||BETWEEN is employed to specify integer ranges. Thus instead of age >= 32 AND age <= 40, we can use age BETWEEN 32 and 40.|
myNet.Dbase -> SQL Database Programming
SQL Database Programming is a universal database programming language. Any good database like Oracle, Ms SQL... supported SQL will run SQL programming.
Click here: http://www.webdevelopersnotes.com/tutorials/sql/mysql_tutorial_selecting_data_using_conditions.php3 for the Database Programming Tutorial. We have setup a sample table named employee_data matched with table name in the tutorial for you to exercise. All you need to do is Copy the code in this tutorial and Paste into myNet.Dbase->[ Code Area]. Then Click Submit Code button. You will see exactly the result matched with the result of this tutorial. It is good for learning SQL Database Programming.
|SELECT f_name, l_name
where salary > 70000 AND salary < 90000
This code will read the table employee_data and display First Name, Last Name of all employees who has salary greater than 70000 and salary less than 90000.
This is the out put result if run by MySQL on Unix, Linux, Windows server.
Picture: The same output result if run by myNet.Dbase
DESIGN A PRIVATE LINK DATABASE
DATA ENTRY FORM
- USE WITH EMAIL
When you want suppliers, customers, partners, or in-house staffs privately to submit forms that are not posted on your website. You don't need to create a web page with form. You don't need your in-house staffs to login as admin in the website in order to input data. You just need is create a table to hold form data. Then you use private link web database form. You send email with a link. The customers, the in-house staffs click on that link will take them directly to the private data entry form for that table.
To setup, all you need to do is:
1) Design a table for the form to hold data.
2) Send email this Url link = http://www.mycompany.com/pub/FormBuilder_Table.jsp?tb=_feedback . Click on this link will open _feedback table, and begin enter data.
- Where you change _feedback is the table name that will store form data.
- Change www.mycompany.com to your company website domain name.
myNet.Dbase is not only built in a web database but also it is built in a set of database features of Database Management System (DBMS) that allow you, either a non web database skills or a web database programmer, can admin and maintain database.
You should be able to:
- Create new web database tables using programming or interface template.
- Use the Table Data Entry to Update, Delete, Insert... data.
- Do advanced Program Database SQL by using Code Area.
- Manage and Maintain your database without any web skills.