Sunday

PHP: MySQL in PHP ( insert, fetch, update, delete and prepared Queries )

     , , , , , ,  No comments

MySQL is the most popular database system used with PHP.

  • MySQL is a database system used on the web
  • MySQL is a database system that runs on a server
  • MySQL is ideal for both small and large applications
  • MySQL is very fast, reliable, and easy to use
  • MySQL uses standard SQL
  • MySQL compiles on a number of platforms
  • MySQL is free to download and use
  • MySQL is developed, distributed, and supported by Oracle Corporation
  • MySQL is named after co-founder Monty Widenius's daughter: My







Databases are useful for storing information categorically. A company may have a database with the following tables:

  • Employees
  • Products
  • Customers
  • Orders


If you don't have a PHP server with a MySQL Database, you can download it for free here: http://www.mysql.com

Connect to MySQL


PHP 5 and later can work with a MySQL database using:

  • MySQLi extension (the "i" stands for improved)
  • PDO (PHP Data Objects)


MySQLi Installation


For Linux and Windows: The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed.
For installation details, go to: http://php.net/manual/en/mysqli.installation.php

PDO Installation


For installation details, go to: http://php.net/manual/en/pdo.installation.php

Check Connection to MySQL


<?php
$servername = "Localhost";
$username = "realants";
$password = "realants";
// $databasename = "db_name";
// Create connection
$link = mysqli_connect($servername, $username, $password);

// Check connection
if (!$link) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully....";
?>

The connection will be closed automatically when the script ends. To close the connection before use mysqli_close($conn);


Create a MySQL Database


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

// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// Create database
$sql = "CREATE DATABASE my_db";
if (mysqli_query($conn, $sql)) {
echo "Database created successfully";
} else {
echo "Error creating database: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Create a MySQL Table


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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

if (mysqli_query($conn, $sql)) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Insert Data Into MySQL


The INSERT INTO statement is used to add new records to a MySQL table:
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘Pk’, ‘Choudhary’, 'pk@example.com')";

if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>


Get Inserted Data


<?php
$servername = "localhost";
$username = "root";
$password = "123";
$dbname = "my_db";

// Create connection
$conn = mysqli_connect($servername, $username, $password,$dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT firstname, lastname ,email FROM MyGuests";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo "Name: " . $row["firstname"]. " " . $row["lastname"]. "Email " .$row["email"] ."<br>" ;
}
} else {
echo "0 results";
}


mysqli_close($conn);
?>

Insert Multiple Records Into MySQL


<?php
$servername = "localhost";
$username = "root";
$password = "123";
$dbname = "my_db";

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

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘Pk’, ‘Choudhary’, 'pk@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘Lk’, ‘Choudhary’, 'kk@example.com');";

if (mysqli_multi_query($conn, $sql)) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

Prepared Statements and Bound Parameters


A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency.
Prepared statements basically work like this:
  • Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?"). Example: INSERT INTO MyGuests VALUES(?, ?, ?)
  • The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it
  • Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values

Compared to executing SQL statements directly, prepared statements have three main advantages:

  • Prepared statements reduce parsing time as the preparation on the query is done only once (although the statement is executed multiple times)
  • Bound parameters minimize bandwidth to the server as you need send only the parameters each time, and not the whole query
  • Prepared statements are very useful against SQL injections, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.


<?php
$servername = "localhost";
$username = "root";
$password = "123";
$dbname = "my_db";

// Create connection
$conn = mysqli_connect($servername, $username, $password,$dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}




// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();

echo "New records created successfully";

$stmt->close();

mysqli_close($conn);
?>


0 comments:

Post a Comment