TUTORIAL DATABASE

You can get all the porn you want on your iPhone

2011-12-31T07:16:00.000-08:00

Our society makes it very easy for a man regarded as mobile porn . The Internet is the greatest threat, and the best way to access pornography. He used to say that if someone wanted to watch pornography on the Internet requires a computer. No more! You can get all the porn you want on your iPhone, or dozens of other portable devices. It can be overwhelming for women who want to hear your loved on.

But now we need to know if you are looking for porn, and even mobile devices. Let's look at some pieces of Bang You Later things spyware can help you seek to strengthen faith.

First, the iPhone is a spy. This is a piece of material information is the recovery unit of the ipad porn and Apple. To retrieve a call history, contact information and images and view documents, maps, and many others have seen. It takes 20 minutes to scan your phone to see what he did.

If the second smartphone to see porn, so the phone is for you Recon. You can see the pictures, web pages, texts and calls, and do not need to connect to your phone. When you get the model number and serial number of your phone and connect to your computer. Even today, know where your phone! An amazing piece of spy equipment! The offer may not be the spy cell phone technology advanced Recon.

And then there is the detection of memory porn. Plug the flash drive into your computer and shows all the porn that used a computer. You can find and remove pornographic. And if it is not enough, you can download on a stick to detect pornography.

So do not feel you can not know if they are lying to their own porn. Now you have the tools for management.

A good shop espionage has a complete tool, you can imagine. I warn you to buy spy equipment abroad, or on the auction site. We have little or no recourse if you want to restore. If you buy a U.S. supplier, you can be sure to purchase a class of materials of espionage, and can pick up the phone and live help if you have any questions. And if you have problems, you know, they guarantee that you will have the honor of leading companies. I have a friend who bought a flashlight spy camera in China. I said I could get one. But he wanted to save money. Twelve weeks later, it was necessary. It took two days and broke. She flew away, knowing that the return would be almost impossible.

So, ladies, is the teaching of today's technology. Please see my link below to see all the cool things I spy mentioned above, and many others ....

A database dictionary

2011-03-16T23:04:00.000-07:00

database: A collection of related information stored in a structured format. Database is often used interchangeably with the term table (Lotus Approach, for instance, uses the term database instead of table). Technically, they’re different: A table is a single store of related information; a database can consist of one or more tables of information that are related in some way. For instance, you could track all the information about the students in a school in a students table. If you then created separate tables containing details about teachers, classes and classrooms, you could combine all four tables into a timetabling database. Such a multi-table database is called a relational database.

data entry: The process of getting information into a database, usually done by people typing it in by way of data-entry forms designed to simplify the process.

dbms: Database management system. A program which lets you manage information in databases. Lotus Approach, Microsoft Access and FileMaker Pro, for example, are all DBMSs, although the term is often shortened to ‘database’. So, the same term is used to apply to the program you use to organise your data and the actual data structure you create with that program.

field: Fields describe a single aspect of each member of a table. A student record, for instance, might contain a last name field, a first name field, a date of birth field and so on. All records have exactly the same structure, so they contain the same fields. The values in each field vary from record to record, of course. In some database systems, you’ll find fields referred to as attributes.

flat file: A database that consists of a single table. Lightweight database programs such as the database component in Microsoft Works are sometimes called ‘flat-file managers’ (or list managers) because they can only handle single-table databases. More powerful programs, such as FileMaker Pro, Access, Approach and Paradox, can handle multi-table databases, and are called relational database managers, or RDBMSs.

foreign key: A key used in one table to represent the value of a primary key in a related table. While primary keys must contain unique values, foreign keys may have duplicates. For instance, if we use student ID as the primary key in a Students table (each student has a unique ID), we could use student ID as a foreign key in a Courses table: as each student may do more than one course, the student ID field in the Courses table (often shortened to Courses.student ID) will hold duplicate values.

index: A summary table which lets you quickly look up the contents of any record in a table. Think of how you use an index to a book: as a quick jumping off point to finding full information about a subject. A database index works in a similar way. You can create an index on any field in a table. Say, for example, you have a customer table which contains customer numbers, names, addresses and other details. You can make indexes based on any information, such as the customers’ customer number, last name + first name (a composite index based on more than one field), or postal code. Then, when you’re searching for a particular customer or group of customers, you can use the index to speed up the search. This increase in performance may not be noticeable in a table containing a hundred records; in a database of thousands of records it will be a blessing.

key field: You can sort and quickly retrieve information from a database by choosing one or more fields to act as keys. For instance, in a students table you could use a combination of the last name and first name fields (or perhaps last name, first name and birth dates to ensure you identify each student uniquely) as a key field. The database program will create an index containing just the key field contents. Using the index, you can quickly find any record by typing in the student’s name. The database will locate the correct entry in the index and then display the full record.

Key fields are also used in relational databases to maintain the structural integrity of your tables, helping you to avoid problems such as duplicate records and conflicting values in fields (see primary key and foreign key).

normalisation: The process of structuring data to minimise duplication and inconsistencies. The process usually involves breaking down a single table into two or more tables and defining relationships between those tables. Normalisation is usually done in stages, with each stage applying more rigourous rules to the types of information which can be stored in a table. While full adherence to normalisation principles increases the efficiency of a particular database, the process can become so esoteric that you need a professional to create and understand the table design. Most people, when creating a database, don’t need to go beyond the third level of normalisation, called third normal form. And there’s no need to know the terminology: simply applying the principles is sufficient.

The first three levels in normalising a database are:

First Normal Form (1NF): There should be no repeating groups in a table.

For example, say you have a students table with the following structure:

student ID
name
date of birth
advisor
advisor’s telephone
student
course ID 1
course description 1
course instructor 1
course ID 2
course description 2
course instructor 2

The repeating course fields are in conflict with first normal form.To fix the problems created by such repeating fields, you should place the course information in a separate course table, and then provide a linking field (most likely student ID) between the students table and the course table.

Second Normal Form (2NF): No non-key fields may depend on a portion of the primary key.

For example, say we create a course table with the structure:

student ID
course ID
course description
course instructor

We can create a unique primary key by combining student ID + course ID (student ID is not unique in itself, as one student may take multiple courses; similarly, course ID is not unique in itself as many students may take the same course; however, each student will only be taking a particular course once at any one time, so the combination of student ID + course ID gives us a unique primary key).

Now, in 2NF, no non-key fields (course description, course instructor) may depend on a portion of the primary key. That, however, is exactly what we have here: the course instructor and course description are the same for any course, regardless of the student taking the course.

To fix this and put the database in second normal form, we create a third table, so our database structure now looks like this (with key fields in italics):

Student table

student ID
name
date of birth
advisor
advisor’s telephone

Student courses table

student ID
course ID

Courses table

course ID
course description
course instructor

Third Normal From (3FN): No fields may depend on other non-key fields. In other words, each field in a record should contain information about the entity that is defined by the primary key.

In our students table, for example, each field should provide information about the particular student referred to by the key field, student ID. That certainly applies to the student’s name and date of birth. But the advisor’s name and telephone doesn’t change depending on the student. So, to put this database in third normal form, we need to place the advisor’s information in a separate table:

Student table

student ID
name
date of birth
advisor ID

Student courses table

student ID
course ID

Courses table

course ID
course description
course instructor

Advisor table

advisor ID
advisor name
advisor telephone

primary key: A field that uniquely identifies a record in a table. In a students table, for instance, a key built from last name + first name might not give you a unique identifier (two or more Jane Does in the school, for example). To uniquely identify each student, you might add a special Student ID field to be used as the primary key.

query: A view of your data showing information from one or more tables. For instance, using the sample database we used when describing normalisation, you could query the Students database asking “Show me the first and last names of the students who take both history and geography and have Alice Hernandez as their advisor” Such a query displays information from the Students table (firstname, lastname), Courses table (course description) and Advisor table (advisor name), using the keys (student ID, course ID, advisor ID) to find matching information.

rdbms: Relational database management system. A program which lets you manage structured information stored in tables and which can handle databases consisting of multiple tables.

record: A record contains all the information about a single ‘member’ of a table. In a students table, each student’s details (name, date of birth, contact details, and so on) will be contained in its own record. Records are also known as tuples in technical relational database parlance.

relational database: A database consisting of more than one table. In a multi-table database, you not only need to define the structure of each table, you also need to define the relationships between each table in order to link those tables correctly.

report: A form designed to print information from a database (either on the screen, to a file or directly to the printer).

SQL: Structured Query Language (pronounced sequel in the US; ess-queue-ell elsewhere). A computer language designed to organise and simplify the process of getting information out of a database in a usable form, and also used to reorganise data within databases. SQL is most often used on larger databases on minicomputers, mainframes and corporate servers.

table: A single store of related information. A table consists of records, and each record is made up of a number of fields. Just to totally confuse things, tables are sometimes called relations. You can think of the phone book as a table: It contains a record for each telephone subscriber, and each subscriber’s details are contained in three fields – name, address and telephone.

Databasics III: Data entry design

2011-02-16T22:54:00.000-08:00

The look of your database is as important as its structure, because it’s the interface that affects how people work with it. Aim for a cleanly structured, consistent look with complimentary colours and few distractions, as in this sample main menu for a banjo tablature application.

It’s staggering how many shareware and commercial database applications have appalling data entry screens. Many developers seem to think that well-oiled inner workings are all that’s needed to sell an application, when any user knows that, when you get down to it, the interface is the app.

When you’re designing a database application, you’re taking on the role of a developer. As you do so, remember to keep the soul of a user. While the brain work in building a database comes during the design stage, the hard slog comes when you – or other people – start adding data to that structure, especially when there are copious amounts of data to add. You can alleviate much of the tedium of data entry by ensuring your data entry forms are logically organised, easy on the eyes and efficient. If you alleviate the tedium and keep the users of your system happy, you’ll automatically increase the accuracy of the data entered into the database.
Data entry guidelines

Exactly how you design your data entry screens will depend on the database program you’re using, the amount of data you’re dealing with, the needs and likes of the data entry personnel, and any application-specific requirements which may exist.

If you’re designing a database purely for your own use it’s still worth designing well-thought-out data entry screens. After all, why should you make life harder for yourself when a little effort in the design phase will make using your database easier to use for all time?

The following guidelines will help you design attractive and easy-to-use data entry screens.
i. Organise fields logically

Group related fields together and use boxes or colour coding to make it easy for users to zero in on information quickly.
ii. Don’t clutter

Space fields on the screen so users can easily spot the field they need to edit.
iii. Don’t force users to scroll

This database uses a tabbed data entry screen to group fields logically and avoid clutter. Note the tabs across the top (Title, Product Details, Requirements, etc.); data entry instructions included on the form; the use of asterisks to mark fields where data entry is compulsory; and the navigation bar at the bottom which is consistent between tabs.

If possible, make sure all your fields are visible simultaneously, so users don’t have to keep switching between the keyboard (for data entry) and the mouse (for scrolling), and so users can see all information at a glance. It’s also worth avoiding scrolled data entry screens as people have a tendency to forget about those invisible fields lurking off the bottom of the screen and leave them blank.

If you have a large number of fields in your database and you don’t want to create clutter but still want to avoid scrolling, try using a tabbed interface. With a tabbed interface, you can separate fields into logical groupings, with each group on its own tab. This approach avoids clutter while keeping everything on the screen.
iv. Go easy on the fonts

At the most, your data entry screens should use three fonts – one for the headings, one for field labels, and one for the field contents themselves.

For best readability, use sans serif fonts such as Arial, Verdana and Trebuchet MS (sans serif fonts lack the little ‘feet’ or extra small lines at the end of characters that you’ll find in serif fonts such as Times New Roman). If large amounts of text will be entered in text fields, you may wish to use Times New Roman or another serif font for those fields. Choose standard fonts, especially if your database will be used on a variety of computers. Make sure all text is of a readable size.
v. Go easy on the colours

Avoid lairy colours. Instead choose soft colours or those that provide good contrast for the fields and captions. If a number of people will be using your database application, keep in mind that some of them may be colour blind or suffer from other eyesight difficulties.
vi. Abandon the defaults

Dump the default data entry screens created by Access, Alpha and most other databases. They are usually poorly organised and either look boring or hard to read. Access is particularly bad in this respect, while FileMaker offers the best standard screens. This is particularly important if you’re using an older version of any of these database programs; more recent versions tend to provide a bit more spit and polish.

Use the default screens as a starting point and tweak them or, if you don’t mind the extra work, build your screens from scratch.
vii. Check your tab order

Ensure that Tab order is correct. During data entry, you move from field to field using the Tab key. It’s important you ensure the Tab order is consistent, so that the insertion point moves from the first field on the screen to the last without jumping about. Incorrect tab order will not only frustrate your users, but it may cause them to enter data into the wrong field.

In Access, open the form in Design View and choose Tab Order from the View Menu. If you’re using Access 2010, switch to Design View, click the Design tab and click Tab Order in the Tools group.
viii. Be consistent

If you application has more than one data entry screen, use the same look and the same organisational principles for each screen. If, for example, you place a Close button on one data entry screen so users can close the screen with a mouse click, include the same box in a similar location on other screens.
ix. Include help

On this data entry form, help is provided both on the form itself and in the form of pop-up tooltips. Microsoft Access makes it particularly easy to add this sort of help to data entry screens.

Add descriptive help where possible. Your data entry forms should be as self-explanatory as possible, with commonsense labels on each field.

You can make data entry even easier by providing help for each field. In Microsoft Access, for example, you can add a pop-up field tooltip (called a ControlTip in Access 2007 and 2010) which will appear when the user lets their cursor linger on a field.

If your database doesn’t support such tooltips, you can include instructions directly on the data entry form itself. For instance, if you want people to enter phone numbers without including any special characters (such as hyphens or parentheses) you can add a message to that effect beside the phone number field, thus: Enter numbers only, eg. 0291237745.
x. Provide shortcuts

You can speed up data entry dramatically by providing shortcuts such as drop-down boxes with predefined choices, auto-filled fields, multiple choice fields and so on. Some of these features you can add during the database definition stage, although we kept things simple in the previous tutorial and avoided such options. Others you can add when you design your data entry forms. Still others you can introduce by using lookup tables or related tables, which let users ‘look up’ information – such as the postal code for a city – and have it entered automatically.

Drop-down lists and pre-defined choices speed up data entry. These choices can be defined either during database definition or when you design the data entry form itself.

We’ll look at all these options in the future.
xi. Validate data as it’s entered

You should design your forms so they check for invalid data and give users a chance to correct mistakes they’ve made. Much of this validation can be done during database definition; some you’ll add during form design.

We’ll look at data validation in more detail in the future as well.
Experimenting with forms

Each database program provides different tools for designing data entry forms. These tools are sometimes very fiddly to work with, so it pays to practice. One of the best ways to do that is to use the standard data entry form created by the database and then mess around with it. Don’t do this experimenting on a ‘live’ database. Create a sample one from scratch, if you like, and work with it.

To do this in Microsoft Access 2000, select a table in the Tables section of the database objects window, click the New Object: Autoform button on the toolbar to create a new form, and then click the View button on the toolbar to switch to Design View. Click the Properties button on the toolbar and select different objects on the form to adjust the settings.

If you’re using Access 2007 or 2010, select a table from the Supporting Objects list, click the Create tab and click the Forms button to create a default form. Click the View button to switch to Design View, click Property Sheet in the Tools section and then click different objects on the form to see their properties.

Parameter queries with custom dialogs

2011-01-16T22:51:00.000-08:00

In the previous tutorial, we explored how to create simple parameter queries in Microsoft Access which prompt the user for criteria when run. These are real bread-and-butter queries you'll find yourself using over and over. The only drawback with such queries is that you have no control over the look of the prompt dialog boxes displayed and, with complex queries, you may end up presenting your users with prompt after prompt after prompt.
Your database application will be much more enjoyable to use if you eliminate this dialog box proliferation and spruce up the dialogs. To do so, you'll need to create a custom dialog box which consolidates all the prompts.

The task

We're going to use the Countries database we've used in past tutorials (you'll find a link to download a copy at the end of this article) and design a dialog box for the parameter query with which we ended the previous column.
That query was designed to elicit the following information:
Show me countries that joined the UN between date x AND date y AND which have a population over n.
If we create such a query without using a custom dialog box, we'll have to badger our users with three consecutive dialogs, the first asking Joined the UN after what date?, the second asking And joined before which date?, and the final prompting And has a population over? With our custom dialog, we can consolidate these questions and also gain control over the appearance of the dialog box itself.
We'll build the form first, then recreate a modified version of the query which takes advantage of the custom dialog box.
I'll describe the process using Access 2000. Access 97's interface is a little different, but you should be able to adapt these instructions with very few changes.

When a box is a form

As far as Access is concerned, a dialog box is simply another type of form, like the data entry forms you encountered early on in this series. So that's where you'll start – by creating a form:

Open the Countries database and, in the main database window, click the Forms button, then double-click Create Form In Design View. You'll see a blank form titled Form1: Form.
If it's not already displayed, display the Toolbox by clicking the Toolbox icon (a crossed hammer and spanner) on the toolbar. Make sure the Control Wizards are enabled (the Control Wizards icon at the top of the Toolbox should be depressed). Drag the toolbox to the side so it's easily accessible but doesn't obscure the form window. Take some time to run your cursor over each of the Toolbox icons so you can see what it contains.

Access provides an assortment of tools with which to customise forms. Click the image above to see a full-sized image and detailed caption.

We want to add a data entry box for each of our three prompts. So click the Text Box tool in the Toolbox, then click-and-drag on the form to create a rectangular text box (position it a little down from the top and about two-thirds of the way from the left edge). You'll see that a label is created to the left of your form. Click the label to select it and resize it by dragging the resizing handle at its left edge towards the left of the form. Click within the label, delete the existing text and type Show countries which joined the UN after:, then click the form outside the label. You'll see that the Text Box contains the word Unbound. This indicates that the text box is not connected (bound) to data in a field, as is the case with our data entry forms. In fact, the whole form is called an unbound form because it is not based on any existing data or data structure.
Repeat step 3 for the remaining two prompts, lining them up one beneath the other. Place the text and joined before: in the second prompt and and whose population exceeds: in the third prompt.
Each text box needs an easy-to-remember name so we can refer to it easily in our query, so right-click the first text box and choose Properties from the pop-up menu. This displays the Properties box, which lets you adjust every facet of your text box. Position the Properties box to the side, so you can see it and the Toolbox while also viewing your form.
In the Properties box, click the All tab and change the value of the Name property to FirstJoined.
Now do the same for the other two text boxes. Click the second text box (the Properties box will now reflect the settings for that box) and change the Name property to LastJoined. Click the third box and change its Name property to Population.

Save your form at this point giving it the name Criteria.

Querying the form

So far, all we've done is cosmetic. We've designed a rather boring looking form that will display three prompts. You can preview your form by clicking the View button on the toolbar. This form needs a Command Button that will tell it to run our date and population query, but before we can do that, we have to have a query to run!
To create a query that grabs its parameters from our custom form:

In the main database window, click the Queries button and double-click Create Query In Design View.
Select the Countries table and click Add to add it to the query, then close the Show Tables dialog.
Drag the fields Name and JoinedUN onto the query grid, then drag the Population field onto the grid to the right of the JoinedUN column.
In the Criteria cell of the JoinedUN column, type:

Between [Forms]![Criteria]![FirstJoined] AND [Forms]![Criteria]![LastJoined]
Eek! I know this looks scary, but that [Forms]![Criteria]![FirstJoined] stuff simply tells Access to go check its collection of forms, locate one called Criteria, and then find the value entered into the FirstJoined text box on that form. You can think of it as the equivalent of telling Access the address where it can find the data, with each part of the address enclosed in square brackets and separated by an exclamation mark. Note that instead of using greater than (>) and less than (<) symbols, we've used the shorthand BETWEEN…AND operator, which you can use when searching for dates.

You should be able to guess what's coming next. In the Criteria cell of the Population column, type:

>[forms]![Criteria]![Population]

What type of data?

Because each answer is being entered into a text box on our form, we need to tell Access whether the input is a date, number or something else by identifying its data type. That way, Access will know how to deal with the data:

Right-click in the grey area above the query grid and choose Parameters from the pop-up menu.
In the first Parameter cell, type:

[Forms]![Criteria]![FirstJoined]
then tab over to the Data Type cell and select Date/Time from the drop-down list.

In the second Parameter cell type:

[Forms]![Criteria]![LastJoined]
and select Date/Time data type.

In the third Parameter cell type:

[Forms]![Criteria]![Population]
and select Long Integer in the data type list.

Click OK and close the query, saving it with the name UNPopulation.

At your command

Now that we have our query, we're going to return to our form and put a Command Button on it that runs that query:

Open the Criteria form in Design view.
Click the Command Button tool in the toolbox then draw a rectangle about three centimetres wide and one centimetre high at the bottom of your form. The Command Button Wizard will be displayed. (If it isn't, it means you haven't enabled Control Wizards in the Toolbox. Select the button you just created and press the Delete key to erase it, then enable the Control Wizards and try again.)
In the Categories list select Miscellaneous and in the Actions list select Run Query, then click Next.
Select the UNPopulation query from the list and click Next.
Select the Text option, type Display Matches and click Next.
Name the button RunQuery and click Finish.
Save and close the form.

How'd we get here?

That's all the hard work out of the way. Let's summarise what we've done then take our customised parameter query for a spin:

We created a form containing a text box for each parameter in our query.
We gave each text box a recognisable name.
We created a query containing criteria which derive their parameters from the values typed into the text boxes on our custom form.
We assigned each parameter a data type to ensure Access knew how to deal with it.
We added to our form a command button which runs the query.

Rock and roll

Now it's time to run the query. In this case, we actually run the form which, in turn, calls the query via the command button:

Click Forms in the database window and double-click the Criteria form. (If the Properties box opens when you open the form, click its Close button to close it.)
Type the following values in the three boxes on the form:

1/1/1960
31/12/1969
20,000,000
and click the Display Matches button. (Note, if you use a different date format on your PC, such as MM/DD/YY, adjust the second value accordingly.)

Access will display a table containing the six countries which joined the United Nations during the sixties and whose population is greater than 20 million.

Our custom dialog box and the results of running the query. Click the image to see a full-sized image and detailed caption.

Database Driven Website

2010-11-16T22:27:00.000-08:00

A database driven website is a website that has most of its webpage content in a database. Therefore, the website content isn't actually sitting in files on the server, it is sitting in tables and columns in a database.

A website with its content stored on the file system is often referred to as a static website, whereas a database driven website is often referred to as a dynamic.

Content Management Systems

A website with dynamic content usually has a CMS (Content Management System) to assist the content providers in updating the website.

A CMS is usually provided in the form of an administration area where content providers need to log in before they can add content. Once logged in, they can create, update and delete articles. They may be able to upload files such as Word documents, PDF files etc. They might be able to upload images too.

All of this content can be stored in the database. Some may be stored on the file system too though. For example, although documents and images can be stored in the database, there are sometimes reasons to store them on the file system. Performance is often a key reason. Database size is another.
Discussion Forums and Blogs

Discussion forums and blogs have become a popular feature for many websites. Most, if not all, forums and blogs are database driven. Users can register their details, then add content. When the user clicks the "Submit" button, their details/content is inserted into the database. Then when someone decides to view this content, it is read from the database using SQL (Structured Query Language).
Combination of Static and Dynamic

Some websites have a combination of static content and dynamic content. There could be any number of reasons for this. Often, smaller websites will be static. There's little need to configure a database just to store a handful of webpages - much easier and cheaper to keep them as files on the server. Even websites like this might contain some added functionality such as a discussion forum, or a blog. In this case, the discussion forum or blog will need its content stored in a database.
Benefits of a Database Driven Website

Database driven websites can provide much more functionality than a static site can. Extended functionality could include:

Enabling many (potentially non-technical) users to provide content for the website. Users can publish articles on the website without needing to FTP them to a web server.

Shopping cart
You can provide advanced search functionality that enables users to filter the results based on a given field. They can then sort those results by a field - say "Price" or "Date".

Customized homepage
You can allow your users to perform tasks such as registering for a newsletter, post questions to your forums, provide comments on a blog, update their profile, etc.
Integration with corporate applications such as CRM systems, HR systems etc

Creating a Database

2010-09-15T09:05:00.000-07:00

With database management systems, many tasks can be done either via programatically or a user interface. Creating databases is no exception.

Option 1: Programatically

Many database administrators (DBAs) use Structured Query Language (SQL) to perform many of their database tasks. To enter SQL, you need to open an interface that allows you to enter your code. For example, if you use SQL Server, you would normally use Query Analyzer.
The following example is the basic code for creating a new database. Parameters can be added to this example if your requirements are more specific.

CREATE DATABASE MyDatabase

Note: This example assumes you know how to use your database system to run scripts like this. If you don't you, will probably find it easier to use the user interface method (below).

Option 2: User Interface

Most database systems make it very easy to create a database via a user interface. Generally, it's just a matter of selecting an option from a menu, then providing a name for your database.
The following examples demonstrate how to create a database in Microsoft Access.

From the "File" menu, click on "New Database":
Choose "Blank Database". (MS Access also gives you the ability to choose from a template, but we'll just use a blank database here):
Choose a location to save the database:

Your New Database

Once you've completed the above tasks, you should see a blank database, like this:
We know this database is blank because it doesn't have any tables. If it did, you would see these tables in the middle pane of the table tab. Now that we have our blank database, we can start adding some tables.

ColdFusion Database Queries (cfquery)

2010-07-01T22:29:00.000-07:00

In the previous lesson we created a datasource so that it could be used by ColdFusion to connect to a database.

Now that we have the datasource, we can perform a query against the database that the datasource is linking to. To perform a database query in ColdFusion, you use the cfquery tag.

Basic CFQUERY Example

This code is all you need to do in order to query a database in ColdFusion. The cfquery tag connects to and opens the database for you, all you need to do is supply it with the name of the datasource.

<cfquery datasource="Entertainment">
select *
from Movies
</cfquery>

Authentication

Many database configurations require authentication (in the form of a username and password) before you can query the database. You can supply these using the username and password attributes.

Note that the username and password can also be configured against the datasource in the ColdFusion Administrator. Supplying these details in your query overrides the username and password in the ColdFusion Administrator.

<cfquery datasource="Entertainment" username="webuser" password="letmein">
select *
from Movies
</cfquery>

Cached Queries

If you have a lot of traffic, you may find that performance of your website/application is affected. If so, you will need to look at ways of improving performance. One effective technique you can use is to cache some of your database queries.

A cached query is a query that has its results stored in the server's memory. The results are stored when the query is first run. From then on, whenever that query is requested again, ColdFusion will retrieve the results from memory.

For ColdFusion, it's much faster retrieving the results from memory than to perform another query from the database. When you query a database, ColdFusion has to wait whilst the database connection (often on another server) is established, the database is opened, the query is run, and the results are returned to ColdFusion. All this takes time, and it can impact on other ColdFusion requests occurring at the same time.

You can cache a query using the cachedAfter attribute. If the query was last run after the supplied date, cached data is used. Otherwise the query is re-run.

<cfquery datasource="Entertainment" cachedAfter="November 20, 2006">
select *
from Movies
</cfquery>

You can also cache a query using the cachedWithin attribute in conjunction with the CreateTimeSpan function.

In the following example, if the query's cached data is older than 1 hour, the query is re-run. Otherwise, cached data is used.

<cfquery datasource="Entertainment" cachedwithin="#CreateTimeSpan(0,1,0,0)#">
select *
from Movies
</cfquery>

Limiting the Number of Records Returned

You can limit the number of rows to be returned by using the maxrows attribute.

<cfquery datasource="Entertainment" maxrows="50">
select *
from Movies
</cfquery>

Timeouts

You can set a timeout limit using the timeout attribute. This can be useful in preventing requests running far longer than they should and impacting on the whole application as a result.

The timeout attribute sets the maximum number of seconds that each action of a query is allowed to execute before returning an error.

<cfquery datasource="Entertainment" timeout="30">
select *
from Movies
</cfquery>

PHP If Statements

2010-02-16T22:49:00.000-08:00

You use PHP if statements when you want your program to execute a block of code only if a particular condition is true. In other words, you can tell your program "if something is true, then execute this piece of code".

Syntax

if (condition)
  code to be executed if condition is true;

Example

In the following example, we create a variable called "favoriteFruit" and assign a value to it. We then compare the value with a string: "Pomegranate". If the two values are the same, we output some text.
The two equals signs (==) is a comparison operator (it compares the two values). If this condition is true, it displays the code within the curly braces ({ and }). The curly braces are only necessary if you're outputting multiple lines of code . If you're only outputting one line of code (like we are) they're optional.

$favoriteFruit = "Pomegranate";
if ( $favoriteFruit == "Pomegranate" )
  {
  echo "Your favorite fruit contains around 7% fibre.";
  }

The above example results in the following:

Your favorite fruit contains around 7% fibre.

If Else Statement

We can add an else to our if statement to make our application do something else if the condition is not true.
Example:

$favoriteFruit = "Fig";
  if ( $favoriteFruit == "Pomegranate" )
    {
    echo "Your favorite fruit contains around 7% fibre.";
    }
  else
    {
    echo "Sorry, I don't know how much fibre that fruit contains.";
    }

The above example results in the following:

Sorry, I don't know how much fibre that fruit contains.

If... Elseif Statement

Let's say we learn the fibre content of another fruit. We could then add an elseif to our if statement. That way, we could include a custom message for the new fruit. In fact, we could use elseif as many times as we like.
Example:

$favoriteFruit = "Lychee";
  if ( $favoriteFruit == "Pomegranate" )
    {
    echo "Your favorite fruit contains around 7% fibre.";
    }
  elseif ( $favoriteFruit == "Lychee" )
    {
    echo "Your favorite fruit contains around 1.5% fibre.";
    }
  else
    {
    echo "Sorry, I don't know how much fibre that fruit contains";
    }

The above example results in the following:

Your favorite fruit contains around 1.5% fibre.

About Database Tables

2009-09-15T09:07:00.000-07:00

Database tables will most likely be the area you'll become most familiar with after working with databases for a while. Now, before we go ahead and start adding tables to our new database, let's have a look at what a database table actually is.

What is a Table?

In database terms, a table is responsible for storing data in the database. Database tables consist of rows and columns.
In the following example, the second row is highlighted in black:
In the next example, the second column is highlighted in black. This column has been given a name of "FirstName":
A row contains each record in the table, and the column is responsible for defining the type of data that goes into each cell. Therefore, if we need to add a new person to our table, we would create a new row with the person's details.
OK, now lets go ahead and create a table.

PHP Database Driven Website

2008-10-16T22:32:00.000-07:00

You can use PHP, (in conjunction with SQL and HTML), to create database driven websites.

To create a database driven website in PHP, you need a database management system (DBMS). Common database systems include MySQL, Microsoft SQL Server, and Oracle.

Your DBMS can be located either on the same computer that the website is on, or on another server. It's good practice to separate your database server from your web server, but if you've only got one machine to develop on, sharing the same machine shouldn't cause any problems (as long as it's powerful enough to run a web server and database server etc).

Anyway, once you have a database with some tables and some data, you can connect to it and query it.

MySQL is a database system commonly used with PHP websites. The following examples demonstrate how to connect and query a MySQL database.
Connecting to the Database

Before you can query your database, you need to connect to the database server, then locate the database. Once you've done this, you can send in your SQL code to do your queries.

To connect to the database server:
mysql_connect("localhost", "web_user", "LetMeIn!") or die(mysql_error());

The above code uses the mysql_connect function to connect to the database server. We provide the following parameters: Server, Username, Password. PHP needs this info so that it knows which server to connect to. In this example we are just connecting to the local machine so we use "localhost" as the server.

We have also used the PHP die and mysql_error functions to be used in the event there's an error and PHP can't connect to the server. This will display the error message which can assist us in determining the cause of the problem.

To select the database:
mysql_select_db("MyDatabase") or die(mysql_error());

The above code uses the mysql_select_db function to select the database from the database server. You need to do this because, your database server could contain many databases. You need to tell PHP which database to use.

Again we use the die and mysql_error functions to be used in the event of an error.
Querying the Database

You can use the mysql_query function to send a SQL query to the database:
$result = mysql_query("SELECT * FROM Individual")
or die(mysql_error());

What we do here is, assign the results of a query to the $result variable. The query is acheived by passing a SQL statement to the mysql_query as a parameter. In this SQL statement, we are selecting all records from the "Individual" table.

Once again we use the die and mysql_error in case there's an error.
Displaying the Results

To display the results, you need to loop through the results of the query and display each record with each iteration of the loop:

while($row = mysql_fetch_array($result)){
echo $row['FirstName'] . " " . $row['LastName'];
echo "<br />";
}

Here we use a while loop to loop through the results of the query. The while loop keeps iterating until it finishes the last result. This means we can display each result as it iterates past that result. We display the result using $echo and the PHP $row variable, indicating which columns we want to display.

The Whole Code

Combining the above code, (and adding comments), results in something like this:

<?php
// Connect to the database server
mysql_connect("localhost", "web_user", "LetMeIn!") or die(mysql_error());

// Open to the database
mysql_select_db("MyDatabase") or die(mysql_error());

// Select all records from the "Individual" table
$result = mysql_query("SELECT * FROM Individual")
or die(mysql_error());

// Loop thru each record (using the PHP $row variable),
// then display the first name and last name of each record.
while($row = mysql_fetch_array($result)){
echo $row['FirstName']. " - ". $row['LastName'];
echo "<br />";
}
?>

Simple Database Design

2008-09-15T08:58:00.000-07:00

What is a Database?

A database is a collection of data. That may sound overly simplistic but it pretty much sums up what any database is.
A database could be as simple as a text file with a list of names. Or it could be as complex as a large, relational database management system, complete with in-built tools to help you maintain the data.
Before we get into dedicated database management systems, let's start with the basics - let's look at a simple text file example.

Text File

Imagine we have a text file called "Individual.txt", and that the contents look like this:
We could use this information to do things such as send an email to everyone on our list. We could do this because, due to the way we designed the list, we know that each row contains a different individual, and the information on that row is related to that individual. Also, the items in each row are separated by commas. Therefore, we know that the email address next to "Homer" is his email address. We could also call each row a record. Therefore, we currently have 4 records in our database.
With a small list like this, a text file may serve our purposes perfectly.

Spreadsheet

Another option would be to store it in a spreadsheet using spreadsheet software (for example, Microsoft Excel). That way, we could do some extra things with our list (such as format it, or sort by first name/surname etc).
A spreadsheet program like Excel makes these tasks relatively easy to do. Also, programs like Excel organize the data into rows and columns, making your data easier to comprehend. Something like this:

Database Software

A better option would be to store the data in a database table using specialized database software, such as Microsoft Access. Something like this:

So What's the Difference?

You may be wondering what the difference is between the last two examples (Excel vs Access). After all, both examples have the data organized into rows and columns.
There are many differences between spreadsheet software and database software. The rest of this tutorial will show you why database software is a much better option for creating databases.

PHP Form Variables

2007-10-16T22:47:00.000-07:00

Many website applications rely on HTML forms so that users can perform their tasks. For example, most Content Management Systems allow users to provide content for a website by entering the content into a textarea form field, then clicking a "Save" button. When the user clicks the Save button, the form is submitted to the action page. The action page is normally specified with the action attribute of the form tag.

If you're not familar with HTML forms, see the HTML forms section of the HTML tutorial, then return to this page.

Once a form has been submitted, the form fields are made available to the action page as a special type of variable. You, as the programmer, can read these form variables by using the appropriate syntax for form variables. Form variables are stored as an array. We will be covering arrays later, but for now all you need to know is how to read each form variable.

Forms can be submitted using one of two methods: get or post. By default, a form will be submitted using the "get" method. This results in each form variable being passed via the URL. If you decide to use the "post" method, you specify this using the method attribute ( method="post" ) of the form tag.
The Get Method

If you use the get method, your action page can access each form field using $_GET["variableName"] (where "variableName" is the name of the form field).
Example

Form page

Display:

Code: <form action="php_action_page.cfm" method="get">
<input type="text" name="firstName" /><br />
<input type="text" name="lastName" /><br />
<input type="submit" />

Action page (php_action_page.cfm):

Here, the action page outputs the contents of the form variables that were passed from the form.
<html>
<head>
<title>PHP Form Variables Example</title>
</head>
<body>
First Name: <$php echo $_GET["firstName"]; ?><br />
Last Name: <$php echo $_GET["lastName"]; ?>
<body>

The Post Method

If your form uses the post method, you use $_POST["variableName"].

Example

Form page

Display:

Code: <form action="php_action_page.cfm" method="post">
<input type="text" name="firstName" /><br />
<input type="text" name="lastName" /><br />
<input type="submit" />

Action page (php_action_page.cfm):

Here, the action page outputs the contents of the form variables that were passed from the form.
<html>
<head>
<title>PHP Form Variables Example</title>
</head>
<body>
First Name: <$php echo $_POST["firstName"]; ?><br />
Last Name: <$php echo $_POST["lastName"]; ?>
<body>

Database Management Systems

2007-09-15T09:03:00.000-07:00

A Database Management System (DBMS), is a software program that enables the creation and management of databases. Generally, these databases will be more complex than the text file/spreadsheet example in the previous lesson. In fact, most of today's database systems are referred to as a Relational Database Management System (RDBMS), because of their ability to store related data across multiple tables.
Some of the more popular relational database management systems include:

Microsoft Access
Filemaker
Microsoft SQL Server
MySQL
Oracle

Throughout this tutorial, you will become familiar with some of the key concepts of database management systems. These include:

Database creation
Tables
Adding data to your database
Querying a database
Relational database design

What Does a Database Management System Look Like?

Different database management systems look different, but generally, there are a number of common features that you'll usually see across most of them.

Microsoft Access

This is the main screen you'll see when opening up Access to view an existing database. The outer part is the database management system and it's menu, the middle part is the actual database. In this example, the database is called "dateSite" and has 20 tables. If you were to open a different database, the name of the database would be different and you would see different tables, but the available options would be the same (i.e. Tables, Queries, Forms, Reports, Macros, Modules, Open, Design, New).
Some of these options are common across all database management systems. All database systems allow you to create tables, build queries, design a new database, and open an existing database.

Microsoft SQL Server

Microsoft SQL Server is a more robust database management system than Access. While Access is better suited to home and small office use, SQL Server is more suited to enterprise applications such as corporate CRMs and websites etc.
The above screen is what you see when you open SQL Server through Enterprise Manager. Enterprise Manager is a built-in tool for managing SQL Server and its databases. In this example, there are 6 databases. Each database is represented down the left pane, and also in the main pane (with a "database" icon).

Which Database System to Use?

If you are using a database for home or small office use, Microsoft Access or Filemaker should be fine. If you need to create a database driven website, then you're better off using a more robust system such as SQL Server, Oracle, or MySQL.
The examples in this tutorial use Microsoft Access. If you don't have Microsoft Access, you should still be able to follow the examples. The tasks we perform are the same tasks you would need to perform regardless of which database management system you use. The key goal with this tutorial is to provide you with an overview of what is involved in creating and maintaining a database.

The Design Process

2005-02-16T22:56:00.000-08:00

This third article in the series delves into the database heartland by exploring relational database design.

One of the best ways to understand the importance of good database design is to start with an all-in-one, flat-file table design and then toss in some sample data to see what happens. By analysing the sample data, you’ll be able to identify problems caused by the initial design. You can then modify the design to eliminate the problems, test some more sample data, check for problems, and re-modify, continuing this process until you have a consistent and problem-free design.

Once you grow accustomed to the types of problems poor table design can create, hopefully you’ll be able to skip the interim steps and jump immediately to the final table design.

A sample design process

Let’s step through a sample database design process.

We’ll design a database to keep track of students’ sports activities. We’ll track each activity a student takes and the fee per semester to do that activity.

Step 1: Create an Activities table containing all the fields: student’s name, activity and cost. Because some students take more than one activity, we’ll make allowances for that and include a second activity and cost field. So our structure will be: Student, Activity 1, Cost 1, Activity 2, Cost 2

Step 2: Test the table with some sample data. When you create sample data, you should see what your table lets you get away with. For instance, nothing prevents us from entering the same name for different students, or different fees for the same activity, so do so. You should also imagine trying to ask questions about your data and getting answers back (essentially querying the data and producing reports). For example, how do I find all the students taking tennis?

Step 3: Analyse the data. In this case, we can see a glaring problem in the first field. We have two John Smiths, and there’s no way to tell them apart. We need to find a way to identify each student uniquely.

Uniquely identify records

Let’s fix the glaring problem first, then examine the new results.

Step 4: Modify the design. We can identify each student uniquely by giving each one a unique ID, a new field that we add, called ID. We scrap the Student field and substitute an ID field. Note the asterisk (*) beside this field in the table below: it signals that the ID field is a key field, containing a unique value in each record. We can use that field to retrieve any specific record. When you create such a key field in a database program, the program will then prevent you from entering duplicate values in this field, safeguarding the uniqueness of each entry.

Our table structure is now: ID, Activity 1, Cost 1, Activity 2, Cost 2

While it’s easy for the computer to keep track of ID codes, it’s not so useful for humans. So we’re going to introduce a second table that lists each ID and the student it belongs to. Using a database program, we can create both table structures and then link them by the common field, ID. We’ve now turned our initial flat-file design into a relational database: a database containing multiple tables linked together by key fields. If you were using a database program that can’t handle relational databases, you’d basically be stuck with our first design and all its attendant problems. With a relational database program such as Microsoft Access or FileMaker, you can create as many tables as your data structure requires.

The Students table would normally contain each student’s first name, last name, address, age and other details, as well as the assigned ID. To keep things simple, we’ll restrict it to name and ID, and focus on the Activities table structure.

Step 5: Test the table with sample data.

Step 6: Analyse the data. There’s still a lot wrong with the Activities table:

Wasted space. Some students don’t take a second activity, and so we’re wasting space when we store the data. It doesn’t seem much of a bother in this sample, but what if we’re dealing with thousands of records?
Addition anomalies. What if #219 (we can look him up and find it’s Mark Antony) wants to do a third activity? School rules allow it, but there’s no space in this structure for another activity. We can’t add another record for Mark, as that would violate the unique key field ID, and it would also make it difficult to see all his information at once.
Redundant data entry. If the tennis fees go up to $39, we have to go through every record containing tennis and modify the cost in each and every one.
Querying difficulties. It’s difficult to find all people doing swimming: we have to search through Activity 1 and Activity 2 to make sure we catch them all.
Redundant information. If 50 students take swimming, we have to type in both the activity and its cost each time.
Inconsistent data. Notice that there are conflicting prices for swimming? Should it be $15 or $17? This happens when one record is updated and another isn’t.

Eliminate recurring fields

Our simple Students table is fine, so we’ll keep it. But there’s so much wrong with the Activities table let’s try to fix it in stages.

Step 7: Modify the design. We can fix the first four problems by creating a separate record for each activity a student takes, instead of having one record for all the activities a student takes.

First we eliminate the Activity 2 and Cost 2 fields. Then we need to adjust the table structure so we can enter multiple records for each student. To do that, we redefine the key so that it consists of two fields, ID and Activity. As each student can only take an activity once, this combination gives us a unique key for each record.

Our Activities table has now been simplified to: ID, Activity, Cost. Note how the new structure lets students take any number of activities – they’re no longer limited to two.

Step 8: Test sample data.

Step 9: Analyse the data. We know we still have the problems with redundant data (activity fees repeated) and inconsistent data (what’s the correct fee for swimming?). We need to fix these things, which are both problems when it comes to data entry – editing or modifying records.

Eliminate data entry anomalies

As well, we should check that other data entry processes, such as adding or deleting records, will function correctly too.

If you look closely, you’ll find that there are potential problems when we add or delete records:

Insertion anomalies. What if our school introduces a new activity, such as sailing, at $50. Where can we store this information? With our current design we can’t until a student signs up for the activity.
Deletion anomalies. If Mark Antony (#219) transfers to another school, all the information about golf disappears from our system, as he is the only student taking this activity.

Step 10: Modify the design. The cause of all our remaining problems is that we have a non-key field (cost) which is dependent on only part of the key (activity). Check it out for yourself: The cost of each activity is not dependent on the student’s ID, which is part of our composite key (ID + Activity). The cost of tennis, for example, is $36 for each and every student who takes the sport – so the student’s ID has no bearing on the value contained in this field. The cost of an activity is purely dependent on the activity itself – it’s dependent on only part of the combined ID/activity key. This is a design no-no, and it’s causing us problems. By checking our table structures and ensuring that every non-key field is dependent on the whole key, we will eliminate the rest of our problems.

Our final design will thus contain three tables: the Students table (Student, ID), a Participants table (ID, Activity), and a modified Activities table (Activity, Cost).

If you check these tables, you’ll see that each non-key value depends on the whole key: the student name is entirely dependent on the ID; the activity cost is entirely dependent on the activity. Our new Participants table essentially forms a union of information drawn from the other two tables, and each of its fields is part of the key. The tables are linked by key fields: the Students table:ID corresponds to the Participants table:ID; the Activities table:Activity corresponds to the Participants table:Activity.

Step 11: Test sample data.

Step 12: Analyse the results. This looks good:

No redundant information. You need only list each activity fee once.
No inconsistent data. There’s only one place where you can enter the price of each activity, so there’s no chance of creating inconsistent data. Also, if there’s a fee rise, all you need to do is update the cost in one place.
No insertion anomalies. You can add a new activity to the Activities table without a student signing up for it.
No deletion anomalies. If Mark Antony (#219) leaves, you still retain the details about the golfing activity.

Keep in mind that to simplify the process and focus on the relational aspects of designing our database structure, we’ve placed the student’s name in a single field. This is not what you’d normally do: you’d divide it into firstname, lastname (and initials) fields. Similarly, we’ve excluded other fields that you would normally store in a student table, such as date of birth, address, parents’ names and so on.

A summary of the design process

Although your ultimate design will depend on the complexity of your data, each time you design a database, make sure you do the following:

Break composite fields down into constituent parts. Example: Name becomes lastname and firstname.
Create a key field which uniquely identifies each record. You may need to create an ID field (with a lookup table that shows you the values for each ID) or use a composite key.
Eliminate repeating groups of fields. Example: If your table contains fields Location 1, Location 2, Location 3 containing similar data, it’s a sure warning sign.
Eliminate record modification problems (such as redundant or inconsistent data) and record deletion and addition problems by ensuring each non-key field depends on the entire key. To do this, create a separate table for any information that is used in multiple records, and then use a key to link these tables to one another.

Sites Recomended

2003-09-04T05:05:00.000-07:00

Gourmonde

Fashionista

Woman Trick

Fashionade

Fashionista

Travelancer

My Girl Sasha

Horse Ranch

Extreme Hero

Travel Malay

planingtonextday

Lockers Room

Lose my Faith

blogs my baby

Foods & Culiner

Africa

Asia Travel

Business

Cheap Holiday

Cruise

Europe

Food Travel

Holidays

Hotels

India

Jobs

Luxury Holidays

PHP Switch Statements

2003-01-16T22:50:00.000-08:00

In the previous lesson we used a PHP if... elseif statement in order to execute different block of code for each different condition. As mentioned, we could use as many "elseif"s as we like.

If you have many conditions, PHP switch statements are a more efficient way of doing this. The server will execute a PHP switch statement quicker than multiple "elseif"s. Also, there's actually less code for the programmer to write.

To write a PHP switch statement, you start with the switch keyword followed by the expression to evaluate (for example, a variable name). You then follow that with a case for each condition (based on the expression).

Syntax

switch (expression)
  {
  case value1
     code to be executed if the expression is equal to value1;
     break;
  case value2
     code to be executed if the expression is equal to value2;
     break;
  case value3
     code to be executed if the expression is equal to value3;
     break;
  default
     (optional) code to be executed if none of the above conditions are true.
     break;
  }

Example

In the following example, we declare a variable called $favoriteVege and assign a value to it. We then execute a switch statement in order to display a different string depending on the value of the variable.

Don't forget to end each case with a break;. This is required, otherwise all cases would be displayed.

$favoriteVege = "Fennel";
switch ($favoriteVege)
  {
  case "Gourd":
     echo "Your favorite vegetable has around 40 kilojoules of energy!";
     break;
  case "Artichoke":
     echo "One of those has around 105 kilojoules of energy!";
     break;
  case "Cassava":
     echo "One of those has around 550 kilojoules of energy";
     break;
  case "Potato":
     echo "That would be around 460 kilojoules of energy";
     break;
  default:
     echo "Sorry, don't know how much energy that vegetable contains.";
     break;
  }

The above code results in the following:

Sorry, don't know how much energy that vegetable contains.