Andrew McDonough

Please note: This page is imported from my wiki, which hasn't been updated in over 10 years. Some of the formatting was lost during the import. I'll try to get around to fixing it someday.

Getting started with MySQL

The following are examples of how to get started on a database of registrations, called registrations.

Creating a new database

mysqladmin -u root -p create registrations

Logging into MySQL as root

mysql -u root -p

Creating a user for your new database

grant all privileges on registrations.* to ‘registrations’@‘localhost’ identified by ‘somepassword’; flush privileges;

Show all databases

show databases;

Opening your database

use registrations;

Showing database’s tables

show tables;

Creating your first table

CREATE TABLE registrations ( id int(11) NOT NULL auto_increment, name varchar(255), email varchar(255), address text, PRIMARY KEY (id) ) TYPE=MyISAM;

Showing the structure of this table

DESC registrations;

Removing this table if you want to start again

DROP TABLE registrations;

Inserting data into this table

INSERT INTO registrations SET name=‘Andrew McDonough’, email=‘andrew@andrew.com’, address=‘1 High Street’;

Getting data out of the table

SELECT * FROM registrations; SELECT id,name FROM registrations; SELECT id,name FROM registrations where id=1; SELECT * FROM registrations where name like ‘%ndrew%‘;

Useful field types

  • int(11) - Integer (1,2,3…)
  • varchar(255) - String up to 255 characters
  • text - String of unlimited length
  • date - Date
  • datetime - Date and time
  • enum(‘Yes’,‘No’) - Enumeration only allowing the values Yes and No

Connecting to MySQL from PHP

Creating a database handle

To connect to MySQL from PHP, you will need to connect to the database and get a database handle. I tend to assign this to the variable $dbh $dbh = @mysqlpconnect(DBHOST, DBUSER, DBPASS);

Opening the database

@mysqlselectdb(DB_NAME,$dbh);

Inserting a record into the database

$query = “INSERT INTO registrations SET name=‘Andrew’, email=‘andrew@andrew.com’, address=‘1 South Street’”; $result = @mysql_query($query,$dbh);

Fetching a record from the database

$query = “SELECT * FROM registrations WHERE id=‘1’”; $result = @mysqlquery($query,$dbh); $record = @mysqlfetch_array($result);

Fetching all query results into an associative array

$query = “SELECT * FROM registrations”; $result=@mysqlquery($query,$dbh); $result = array(); while($row=@mysqlfetcharray($result)){ arraypush($result,$row); }


Andrew McDonough

Andrew McDonough is a consultant CTO and software developer, currently based between Berlin and London.

Follow Andrew on Twitter or Connect on LinkedIn