In this article SELECT Query in PHP we give the information about SELECT query in PHP is used to fetch data from the database.
SELECT Query in PHP
Introduction to Databases and MySQL
Database and MySQL are used to efficiently manage web applications and various types of data. In this article we will get basic information about Databases and MySQL.
What is Database?
Database is an organized collection used to store, access and manage data. Information in the database is in tabular form, which can be relational or non-relational database.
What is MySQL?
MySQL is an open-source relational database system. It is used to store and manage data efficiently. MySQL accesses data through SQL (Structured Query Language).
Benefits of using MySQL
- Free and open-source: MySQL can be used at no cost.
- Relational Database: Data in the tables of the database are relationally linked.
- Fast and efficient: MySQL helps to save and retrieve data efficiently.
- Scalability: Ability to handle large databases and large data sets.
Installation and use of MySQL
To use MySQL you need to install MySQL Server.
On Windows: You can download the installer from the official MySQL website.
On Linux: You can install MySQL using sudo apt-get install mysql-server.
Creating a Database and Adding Data with MySQL
- Creating a Database
CREATE DATABASE mydatabase;
- Creating a table
USE mydatabase;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
password VARCHAR(100)
);
- Adding data
INSERT INTO users (name, email, password) VALUES (‘John Doe’, ‘john@example.com’, ‘password123’);
- Fetching Data
SELECT * FROM users;
Examples of working with MySQL
Accessing MySQL through PHP
- Creating a MySQL Connection
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “mydatabase”;
// Establishing MySQL connection
$conn = new mysqli($servername, $username, $password, $dbname);
// check if there is an error
if ($conn->connect_error) {
die(“Connection error: ” . $conn->connect_error);
}
echo “Connection successful!”;
?>
fetch data
<?php
$sql = “SELECT id, name, email FROM users”;
$result = $conn->query($sql);
if ($result->num_rows> 0) {
while($row = $result->fetch_assoc()) {
echo “id: “. $row[“id”]. ” – Name : ” . $row[“name”]. ” – Email : ” . $row[“email”]. “<br>”;
}
} else {
echo “No data found!”;
}
?>
Important Security Measures When Using MySQL and PHP
Secure passwords: Whenever users enter a password, it must be hashed.
Prepared Statements: Use prepared statements to transfer data securely.
Closing the connection to the database: Always close the database connection when the work is complete.
Conclusion
MySQL is a popular relational database used in web applications and other projects.
MySQL is used to manage data efficiently through SQL (Structured Query Language).
MySQL along with PHP and other languages is used to add, read, update and delete data.
SQL Queries: SELECT, INSERT, UPDATE, DELETE in PHP
When working with MySQL databases in PHP you can implement SELECT, INSERT, UPDATE, and DELETE SQL queries.
-
SELECT Query in PHP
SELECT query is used to fetch data from the database.
PHP Code Example for SELECT
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “mydatabase”;
// Create MySQL connection
$conn = new mysqli($servername, $username, $password, $dbname);
//check whether the connection is successful or not
if ($conn->connect_error) {
die(“Connection error: ” . $conn->connect_error);
}
// prepare select query
$sql = “SELECT id, name, email FROM users”;
$result = $conn->query($sql);
if ($result->num_rows> 0) {
while ($row = $result->fetch_assoc()) {
echo “ID: “. $row[“id”]. ” – Name : ” . $row[“name”]. ” – Email : ” . $row[“email”]. “<br>”;
}
} else {
echo “No results found!”;
}
$conn->close(); // close the connection
?>
-
INSERT Query in PHP
INSERT query is used to add new data to the database.
PHP Code Example for INSERT
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “mydatabase”;
// Create MySQL connection
$conn = new mysqli($servername, $username, $password, $dbname);
//check whether the connection is successful or not
if ($conn->connect_error) {
die(“Connection error: ” . $conn->connect_error);
}
// Prepare INSERT query
$sql = “INSERT INTO users (name, email, password) VALUES (‘John Doe’, ‘john@example.com’, ‘password123’)”;
if ($conn->query($sql) === TRUE) {
echo “New record added successfully!”;
} else {
echo “Error: ” .$conn->error;
}
$conn->close(); // close the connection
?>
-
UPDATE Query in PHP
UPDATE query is used to update existing data.
PHP Code Example for UPDATE
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “mydatabase”;
// Create MySQL connection
$conn = new mysqli($servername, $username, $password, $dbname);
//check whether the connection is successful or not
if ($conn->connect_error) {
die(“Connection error: ” . $conn->connect_error);
}
// prepare UPDATE query
$sql = “UPDATE users SET email=’newemail@example.com’ WHERE id=1”;
if ($conn->query($sql) === TRUE) {
echo “Record updated successfully!”;
} else {
echo “Error: ” .$conn->error;
}
$conn->close(); // close the connection
?>
-
DELETE Query in PHP
DELETE query is used to delete data from the database.
PHP Code Example for DELETE
<?php
$servername = “localhost”;
$username = “root”;
$password = “”;
$dbname = “mydatabase”;
// Create MySQL connection
$conn = new mysqli($servername, $username, $password, $dbname);
//check whether the connection is successful or not
if ($conn->connect_error) {
die(“Connection error: ” . $conn->connect_error);
}
// Prepare DELETE query
$sql = “DELETE FROM users WHERE id=5”;
if ($conn->query($sql) === TRUE) {
echo “Record deleted successfully!”;
} else {
echo “Error: ” .$conn->error;
}
$conn->close(); // close the connection
?>
Benefits of Using SQL Queries in PHP
- The combination of PHP and MySQL helps to manage data from the database easily.
- Easier to handle connection errors and display the results of SQL query execution.
- By using Prepared Statements and other security measures, the security of the database can be enhanced.
Conclusion
- SELECT, INSERT, UPDATE, DELETE queries are used to fetch, add, update and delete data from MySQL database with PHP.
- Correct SQL and PHP connection management ensures that data can be managed efficiently and securely.
Some More:
POP- Introduction to Programming Using ‘C’
OOP – Object Oriented Programming
DBMS – Database Management System
RDBMS – Relational Database Management System
Join Now: Data Warehousing and Data Mining