MySQL Tutorial

Willyke · #1 (**permalink**) 03-25-2007, 10:36 AM

MySQL is one of the most used database.
With MySQL you don't make websites like some people think but you can save data in it.

Before you can work with a database you need to connect to it and you do it like this:

Code:

<?php
$username= "root"; // Username that has access to your database
$password= ""; //Password that has access to your database
$host = "localhost"; // The host where the database is (mostly this is localhost)
$db = "databasename"; // The name of the database where you want to work with

//Now we going to connect to the database

mysql_connect($host,$username,$password);
mysql_select_db($db);
?>

Now we're going to see the following statements:

- CREATE
- SELECT
- INSERT
- UPDATE
- DROP
- ALTER
- DELETE

CREATE:
Before you can work with a table in a certain database you need to create it with the statement CREATE. You do that like this:

Code:

CREATE TABLE tablename(
fieldname datatype,
fieldname datatype,
);

These are the different datatypes you can choose from:

Datatype / Explanation

TINYINT Integer from -128 tot 127
SMALLINT Integer from -32.768 tot 32.767
MEDIUMINT Integer from -8.388.608 tot 8.388.607
INT Integer from -2.147.483.648 tot 2.147.483.647
BIGINT Integer from -9.223.372.036.854.775.808 till 9.223.372.036.854.775.807
FLOAT number with a ','
DOUBLE number with a ',' but with is more specifier
DATE UNIX-date
DATETIME UNIX-date and time
CHAR String with a number of characters
VARCHAR String with a number of characters
TINYBLOB Little BLOB-field
TINYTEXT Little TEXT-field
TEXT Textfield without restrictions, comes in handy for big documents
BLOB (Binairy Large OBject) The same as TEXT but only with BINARY DATA
LONGBLOB Huge BLOB field
LONGTEXT Huge TEXT field
ENUM No explanation
SET No explanation

Here is an example of a simple table

Code:

CREATE TABLE data(
id int(10) not null auto_increment PRIMARY KEY,
firstname varchar(200) not null,
surname varchar(255) not null,
age varchar(10) not null,
location varchar(255) not null)

But you can also make your table with PHPMyAdmin but that isn't installed on every server so its easy if you can do it without!

Code:

<?php
$username= "root"; // Username that has access to your database
$password= ""; //Password that has access to your database
$host = "localhost"; // The host where the database is (mostly this is localhost)
$db = "databasename"; // The name of the database where you want to work with

//Now we going to connect to the database

mysql_connect($host,$username,$password);
mysql_select_db($db);


$table= "CREATE TABLE data(
id int(10) not null auto_increment PRIMARY KEY,
firstname varchar(200) not null,
surname varchar(255) not null,
age varchar(10) not null,
location varchar(255) not null)";

mysql_query($table) or die(mysql_error());
echo "The table is succesfully added!";
?>

If the table has been added you will see the sentence:
The table is succesfully added!

SELECT:

SELECT is one of the most used statements because you get the data out your database witht this statement

You can do this on different ways

Code:

<?php
SELECT * FROM tablename
// Now you select everything

SELECT sub-table FROM tablename
// now you select sub-table(by example: name) from tablename

SELECT * FROM tablename WHERE firstname='Wesley'
// now you select everything out the table where the first name is Wesley

SELECT sub-table FROM tablename ORDER BY id DESC
// now it wil put the new data at the top

SELECT * FROM tablename ORDER BY firstname ASC
// now he will put the firstnames in alphabetical order

SELECT * FROM tablename WHERE id <= '5'
// Now he selects everything where the id is 5 or smaller
?>

There are much more like that but i think i gave enough examples.

Code:

<?php
//Make a connection with the database
//i don't put it here anymore, you will need to put it here by yourself (its on the top of the topic)

$sql = "SELECT * FROM data";
$query = mysql_query($sql);
//Now we are going to make a loop
while ($show = mysql_fetch_object($query))
{
echo "<b>Firstname: </b> $show->firstname<br>";
echo "<b>Surname: </b> $show->surname<br>";
echo "<b>Age: </b> $show->age<br>";
echo "<b>Location: </b> $show->location<br>";
}
?>

Now it will give the output:
Firstname:
Surname:
Age:
Location:

This is because there isn't anything in the tabel yet.

INSERT:

With the statement INSERT you can save data in your database

Code:

<?php
INSERT INTO tablename(sub-table 1, sub-table 2, sub-table 3, sub-table 4) VALUES  
('this comes in sub-table 1','this comes in sub-table 2','this comes insub-table 3','this comes in sub-table 4')
?>

This was the statement INSERT, now i'll give an example

form.html

Code:

<html>
<head>
<title>Add data into the database</title>
</head>
<body>

<form method="post" action="add.php">
<input type="text" name="firstname">
<input type="text" name="surname">
<input type="text" name="age">
<input type="text" name="location">
<input type="submit" name="submit" values="Add into database">
</form>

</body>
</html>

add.php

Code:

<?php
//Connect to the database first

$insert = "INSERT INTO data (id, firstname, surname, age, location) VALUES
('','$firstname,'$surname,'$age,'$location)";
mysql_query($insert) or die(mysql_error());
echo "The data has been added succesfully!";
?>

UPDATE:

If you want to change something lik your age we use the statement UPDATE.

This is how you do that:

Code:

UPDATE table SET sub-table='new data'

Think about it that you always need to use mysql_query(); to let the query work.

DROP
You need to watch out with this statement because it can delete whole tables.

Code:

DROP DATABASE databasename// Delete a whole Database
DROP TABLE tablename// Delete a database table

DELETE

Code:

DELETE FROM tablename

<?php
//an example
$delete = "DELETE FROM data WHERE firstname= 'Wesley'";  
mysql_query($delete) or die(mysql_error());  
echo "It is succesfully deleted!!";
?>

ALTER
With ALTER you can add/change/delete sub-tables in a table

Code:

<?php
// lets rename the table data to members
ALTER TABLE data RENAME AS members  
// Now we add the column password
ALTER TABLE members ADD COLUMN password varchar(255)  
// now we delete the column  surname
ALTER TABLE members DROP COLUMN surname
?>