MySQL notes

Jani Reinikainen,

10. March 2003

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";

# ./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);
}

?>

MySQL notes

Jani Reinikainen,

Datafiles

Related documents