Connection to DataBase



    I must sorry for my English, I'm only learn it, so I realize that I can write and say some wrong grammatical sentences :)
But, Than more we talk than less we mistake...

Anatolij

Entry

Today I want write shot article about connection to Data Base. But, first let look What is this Data Base? What is it mean? Why we need use this thing? What is MySQL? What is SQL?

Today We all use modern technologies, million and million people use Internet for their own demands. Someone looks movie, someone read books, someone open net-shop, someone chat in social Net, forums, games, sites and many other things. Of course many internet resourses use different data; like logins, passwords, name goods, price's, and many others,- all item of this data stored in Data Base or Data Base Server (next DB). Many web-resourses could not be exist without DB.
    A DB is an organized collection of data.

Net sites

Picture.1. Different application of Data Base resourses


Access to this data is usually provided by a "database management system" (DBMS) consisting of an integrated set of computer software that allows users to interact with one or more databases and provides access to all of the data contained in the database . Database management systems are computer software applications that interact with the user, other applications, and the database itself to capture and analyze data.

A general-purpose DBMS provides various functions that allow entry, definition, creation, querying, update, storage and retrieval of large quantities of information as well as provide ways to manage how that information is organized. Well-known DBMSs include MySQL, MS Access, SQL Server, PostgreSQL, Microsoft SQL Server, Oracle and other.

database

Picture.2. Example of using DBMS


What is MySQL?

MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation.

The MySQL Web site (http://www.mysql.com/) provides the latest information about MySQL software.

MySQL is a database management system.

database

Picture.3. Data exchange


A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server. Since computers are very good at handling large amounts of data, database management systems play a central role in computing, as standalone utilities, or as parts of other applications.

MySQL databases are relational.

A relational database stores data in separate tables rather than putting all the data in one big storeroom. The database structures are organized into physical files optimized for speed. The logical model, with objects such as databases, tables, views, rows, and columns, offers a flexible programming environment. You set up rules governing the relationships between different data fields, such as one-to-one, one-to-many, unique, required or optional, and “pointers” between different tables. The database enforces these rules, so that with a well-designed database, your application never sees inconsistent, duplicate, orphan, out-of-date, or missing data.

The SQL part of “MySQL” stands for “Structured Query Language”. SQL is the most common standardized language used to access databases. Depending on your programming environment, you might enter SQL directly (for example, to generate reports), embed SQL statements into code written in another language, or use a language-specific API that hides the SQL syntax.

What is SQL?


SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard

What Can SQL do?

SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views

Example of SQL request to Data Base

The SELECT statement is used to select data from a database.

SELECT column_name,column_name
FROM table_name;

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO table_name
VALUES (value1,value2,value3,...);

The UPDATE statement is used to update existing records in a table.

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

The DELETE statement is used to delete records in a table.

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

What is PHP? Example of SQL request to Data Base in PHP.

PHP (recursive acronym for PHP: Hypertext Preprocessor) is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML.

PHP scripts are executed on the server.

Open a Connection to MySQL

Before we can access data in the MySQL database, we need to be able to connect to the server:

<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>


Select Data With MySQLi

The following example selects the id, firstname and lastname columns from the MyGuests table and displays it on the page:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

The DELETE statement is used to delete records from a table:

The following examples delete the record with id=3 in the "MyGuests" table:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// sql to delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>