Creating databases (replace name with desired database name):
CREATE DATABASE name;
Selecing which database to use (replace name with desired database
name):
use name;
Creating tables:
CREATE TABLE harrier_runs ( run_id integer NOT NULL auto_increment, real_name varchar(60), hash_name varchar(60), run_date datetime NOT NULL, starting_point varchar(255), pbook_map varchar(20), tel_num varchar(150), PRIMARY KEY (run_id) );
Auto increment means it will automatically assign a sequence number to that
field – you insert NULL or don’t even name it in your INSERT statement and it
will automatically put a number 1 greater than the last entry. It’s a unique
identifier. Primary key means… well a unique identifier. It must be unique and
it will be indexed automatically. A normal key is indexed but may contain
duplicate values.
Set password/privileges to a database like this (of course change “password”
to your desired password):
GRANT ALL PRIVILEGES ON database.* TO webuser@localhost IDENTIFIED BY
"password";
Set your MySQL root user password like this (of course, change “password” to
your desired password):
GRANT ALL PRIVILEGES ON * TO root@localhost IDENTIFIED BY "password";
OR
# ./mysqladmin -u root password myMySQLpassWord
Import tables from a file:
$ ./mysql -p yourdatabase > yourfile.sql
Datafiles
When using datafiles to store data instead of, for example, a MySQL database,
it’s important to note in which format you create the datafile, since it can be
quite difficult to alter the format later on. If you want datafiles that can be
later imported to a MySQL database (MySQL compatible datafiles), create them in
this format:
bob 603 NE 4th Ave sue 303 NW 56th Ave
I.e. one record per line and fields that are separated by tabs. These kind of
datafiles can be parsed with the following PHP code snippet:
<?php
// FIRST READ ENTIRE FILE TO A VARIABLE
$fh = fopen("$datafile","r");
$text = fread($fh, filesize($datafile));
fclose($fh);
// SPLIT FILE BY NEWLINE
$splitted = split("\n", $text);
// SPLIT EACH LINE BY TABS
for ($i=0;$i<count($splitted)-1;$i++) {
$line_$i = split("\t", $splitted);
}
?>
Otherwise, for non-MySQL compatible datafiles, this is a quite common format:
bob|603|NE|4th|Ave sue|303|NW|56th|Ave
I.e. one record per line and fields that are separated by pipe symbols.
These kind of datafiles can be parsed with the following PHP code snippet:
<?php
// FIRST READ ENTIRE FILE TO A VARIABLE
$fh = fopen("$datafile","r");
$text = fread($fh, filesize($datafile));
fclose($fh);
// SPLIT FILE BY NEWLINE
$splitted = split("\n", $text);
// SPLIT EACH LINE BY PIPE SYMBOL
for ($i=0;$i<count($splitted)-1;$i++) {
$line_$i = split("|", $splitted);
}
?>
Related documents
Tags: mysql