Hacking Google Maps and Google Earth (ExtremeTech)

84 Part I — Basics

Listing 5-19: Creating a SQL Table

create table restaurants (id int auto_increment not null primary key,

lng float,

lat float,

name varchar(80));

The example in Listing 5-19 shows the creation of a table with an ID field with an auto_

incrementoption. Each time a record is inserted into the database, the ID field will

automatically be populated with the next ID in the sequence, providing a unique reference

for that row in the database.

To create this table, add data to it, or retrieve previously stored data requires using a database

interface.

Interfacing to the Database

Whether you are creating an interface for building the database or writing the script that

extracts the information from the database in XML format for Google Maps to process, you

will need to interface to the underlying RDBMS.

Depending on the language you are using, a number of different interfaces are available; gener-

ally, it is best to use the one with which you are most familiar or, if this is your first time, the

one most widely supported, because this increases the available resources and support.

SQL Quick Guide

SQL statements for manipulating data can be divided into three types, SELECT, INSERT, and

UPDATE:

■SELECTretrieves data from the database (selecting it). You must specify the fields you want

to retrieve, the tables you want to retrieve the information from, an optional criteria state-

ment (that is, specify the precise rows from the table you want), and the rules for matching

data between tables (joins).

■INSERTadds rows to the table. You must specify the table and either a list of values to be

inserted (according to the order of the fields in the database) ora list of the fields and their

values.

■UPDATEmodifies one or more rows in a table. You must specify the table, the updated

fields and their values, and the criteria to use when selecting which existing rows should be

updated.

With one or two exceptions, 99 percent of the interaction with an RDBMS will be through one

of these three statements.