FREE MySQL tutorials

MySQL – Constraints

MySQL – Constraints

Tested on MySQL 5.5

Constraint: NOT NULL

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.


CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Constraint: UNIQUE

The UNIQUE constraint uniquely identifies each record in a database table
A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it


/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

/* Modify an existing table */
ALTER TABLE Persons
ADD UNIQUE (P_Id)

/* Modify an existing table */
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

/* Drop constraint */
ALTER TABLE Persons
DROP INDEX uc_PersonID

Constraint: PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.


/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

/* Modify an existing table */
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

/* Modify an existing table */
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

/* Drop constraint */
ALTER TABLE Persons
DROP PRIMARY KEY

Constraint: FOREIGN KEY

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
It is used to prevent actions that would destroy links between tables.

Example:
“Persons” table: P_Id LastName FirstName Address City
“Orders” table : O_Id OrderNo P_Id

Note that the “P_Id” column in the “Orders” table points to the “P_Id” column in the “Persons” table.


/* Create new table */
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

/* Create new table */
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

/* Modify an existing table */
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

/* Modify an existing table */
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

/* Drop constraint */
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

Constraint: CHECK

The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.


/* Create new table */
/* "P_Id" must only include integers greater than 0 */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

/* Modify an existing table */
ALTER TABLE Persons
ADD CHECK (P_Id>0)

/* Modify an existing table */
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

/* Drop constraint */
ALTER TABLE Persons
DROP CHECK chk_Person

Constraint: DEFAULT

The default value will be added to all new records, if no other value is specified.


/* Create new table */
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

/* Create new table */
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

/* Modify an existing table */
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

/* Drop constraint */
ALTER TABLE Persons
ALTER City DROP DEFAULT

By Andrea Tonin|MySQL, Web Design|Commenti disabilitati

PHP MySQL code to extract data from MySQL table and display as JSON

MySQL -> PHP -> JSON Layer -> JS/HTML

To prevent database password sniffing you must not get a direct access to database (sending database access datas).
The best way is to call a PHP engine and after, the PHP engine will render simple JSON data structure.
In the end you can use JScript to write, on the fly, the final HTML code.

json structure

We need store this simple datas:

—

Users:

Andrea Tonin January, 12 2012
Riccardo Santato April, 28 2010

—

We will create this json structure:

{"users":[
        {
            "firstName":"Andrea",
            "lastName":"Tonin",
            "joined": {
                "month":"January",
                "day":12,
                "year":2012
            }
        },
        {
            "firstName":"Riccardo",
            "lastName":"Santato",
            "joined": {
                "month":"April",
                "day":28,
                "year":2010
            }
        }
]}

Create database

Open phpMyAdmin

On the top localhost> mydatabase

Use phpMyAdmin to create:

Left Column> ‘Crea tabella’> MyISAM

Table name: ‘users’

PhpMyAdmin> In Alto> linguetta Struttura> aggiungere i campi

Campo: id
Tipo: INT
Lunghezza: 20
Predefinito: Nessuno
Null: deselezionato
Indice: PRIMARY
AUTO_INCREMENT: selezionato

Campo: firstName
Tipo: VARCHAR
Lunghezza: 255
Predefinito: Nessuno
Null: deselezionato
Indice: nessuno
AUTO_INCREMENT: deselezionato

Campo: lastName
Tipo: VARCHAR
Lunghezza: 255
Predefinito: Nessuno
Null: deselezionato
Indice: nessuno
AUTO_INCREMENT: deselezionato

Campo: month
Tipo: VARCHAR
Lunghezza: 20
Predefinito: Nessuno
Null: deselezionato
Indice: nessuno
AUTO_INCREMENT: deselezionato

Campo: day
Tipo: INT
Lunghezza: 2
Predefinito: Nessuno
Null: deselezionato
Indice: nessuno
AUTO_INCREMENT: deselezionato

Campo: year
Tipo: INT
Lunghezza: 4
Predefinito: Nessuno
Null: deselezionato
Indice: nessuno
AUTO_INCREMENT: deselezionato

The final result inside phpMyAdmin:

PhpMyAdmin> In Alto> linguetta Inserisci>

Aggiungere i campi:

Andrea Tonin January, 12 2012
Riccardo Santato April, 28 2010

colonna di sinistra PhpMyAdmin> click su ‘users’>

PHP json translator – php-json.php

<?php
$host="localhost"; //lasciare com'è se utilizzate bluehost
$username="lucedigi_user"; 
$password="mypassword"; 
$db_name="lucedigi_testphp"; // database name
$tbl_name="users"; //indicate la tabella presente nel database a cui si deve collegare 
  
// Connetti al server e seleziona il database
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("DB non connesso");
 
// JSON TRANSLATOR CODE START ###################################
 
$sql = "select * from users"; //indicate la tabella presente nel database a cui si deve collegare 
$result = mysql_query($sql);
$json = array();
if(mysql_num_rows($result)){
while($row=mysql_fetch_row($result)){
$json['users'][]=$row;  //indicate la tabella presente nel database a cui si deve collegare 
}
}
mysql_close($db_name);
echo json_encode($json); // json_encode() PHP Function
 
// JSON TRANSLATOR CODE END ####################################
?>

Notice:

echo json_encode($json); // json_encode() PHP Function

If you execute php-json.php, you will see:

{“users”:[[“1″,”Andrea”,”Tonin”,”January”,”12″,”2012″],[“3″,”Riccardo”,”Santato”,”April”,”28″,”2010″]]}

JScript Render – js-json.php

<!DOCTYPE html>
 
<html>
<head> 
</head>
 
<body>

    <!-- Write with PHP include function -->
    Write with PHP include function: <br>
    <?php include("php-json.php"); ?>
     
    <script>
	// Write with Javascript
	var jsoncontent='<?php include("phpjson.php"); ?>';
	document.write('<br>Write with JavaScript - simple variable:<br>');
	document.write(jsoncontent);
    </script>
	
</body>
 
</html>

If you execute js-json.php, you will see:

Write with PHP include function:
{“users”:[[“1″,”Andrea”,”Tonin”,”January”,”12″,”2012″],[“3″,”Riccardo”,”Santato”,”April”,”28″,”2010″]]}
Write with JavaScript – simple variable:
{“users”:[[“1″,”Andrea”,”Tonin”,”January”,”12″,”2012″],[“3″,”Riccardo”,”Santato”,”April”,”28″,”2010″]]}

You can store the data inside a javascript object:

<!DOCTYPE html>
  
<html>
<head> 
</head>
  
<body> 
    <script>
    // Write with Javascript
    var jsoncontent='<?php include("php-json.php"); ?>';
    var obj = JSON.parse(jsoncontent);
    alert(JSON.stringify(obj, null, 4));
    </script>   
</body>
  
</html>

Notice: JSON.stringify function to print out Javascript objects

The result is:

By Andrea Tonin|JavaScript, JSON, MySQL, PHP, Web Design|Commenti disabilitati

MySQL injection protection

MySQL injection protection

How to prevent MySQL injection

What is SQL injection?
SQL injection is the attempt to issue SQL commands to a database through a website interface, to gain other information. Namely, this information is stored database information such as usernames and passwords.

The code to prevent injection:

// collect data from HTML form
$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];
 
//Prevent MySQL injection
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

// Now you can send to DB secure data
$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);

Notice:
stripslashes —> Un-quotes a quoted string
mysql_real_escape_string —> Escapes special characters in a string for use in an SQL statement

By Andrea Tonin|MySQL, Web Design|Commenti disabilitati

My SQL – Data Types

My SQL – Data Types

Text types:

CHAR(size)
Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters

VARCHAR(size)
Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters. Note: If you put a greater value than 255 it will be converted to a TEXT type

TINYTEXT
Holds a string with a maximum length of 255 characters

TEXT
Holds a string with a maximum length of 65,535 characters

BLOB
For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data

MEDIUMTEXT
Holds a string with a maximum length of 16,777,215 characters

MEDIUMBLOB
For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data

LONGTEXT
Holds a string with a maximum length of 4,294,967,295 characters

LONGBLOB
For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data

ENUM(x,y,z,..)
Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.
Note: The values are sorted in the order you enter them.
You enter the possible values in this format: ENUM(‘X’,’Y’,’Z’)

SET
Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Number types:

TINYINT(size)
-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis

SMALLINT(size)
-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis

MEDIUMINT(size)
-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis

INT(size)
-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis

BIGINT(size)
-9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis

FLOAT(size,d)
A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

DOUBLE(size,d)
A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

DECIMAL(size,d)
A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.

Date types:

DATE()
A date. Format: YYYY-MM-DD
Note: The supported range is from ‘1000-01-01’ to ‘9999-12-31’

DATETIME()
*A date and time combination. Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’

TIMESTAMP()
*A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC). Format: YYYY-MM-DD HH:MM:SS
Note: The supported range is from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC

TIME()
A time. Format: HH:MM:SS
Note: The supported range is from ‘-838:59:59’ to ‘838:59:59’

YEAR()
A year in two-digit or four-digit format.
Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

*Even if DATETIME and TIMESTAMP return the same format, they work very differently. In an INSERT or UPDATE query, the TIMESTAMP automatically set itself to the current date and time. TIMESTAMP also accepts various formats, like YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.

By Andrea Tonin|MySQL|Commenti disabilitati

PHP – MYSQL – Insert Records using a HTML Form

I Records sono le righe della tabella.

insert-form.php (the HTML Form)

<html>
<body>

<!-- Send data to insert.php -->
<form action="insert-engine.php" method="post">
Firstname: <input type="text" name="firstname">
Lastname: <input type="text" name="lastname">
Age: <input type="text" name="age">
<input type="submit">
</form>

</body>
</html>

insert-engine.php (the PHP engine)

Syntax:

INSERT INTO table_name
VALUES (value1, value2, value3,…)

INSERT INTO table_name (column1, column2, column3,…)
VALUES (value1, value2, value3,…)

<?php
// Create connection
// Statement: mysqli_connect(host,username,password,dbname)
// NOTICE: se lo script è installato nello stesso server del Data Base, host->localhost
$con=mysqli_connect("localhost","lucedigi_user","mypassword","lucedigi_testphp");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
else
  {
  echo "Great! Connect to MySQL!";
  }
// Aggiungere nuovi record alle colonne con POST - START
$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";

if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }
echo "1 record added";
// Aggiungere nuovi record alle colonne con POST - END

mysqli_close($con); 
echo "Great! Connection Closed!"; 
?>

By Andrea Tonin|MySQL, PHP|Commenti disabilitati

Previous 123 Next