Database Connectivity In PHP( Step By Step)

PHP Connect to MySQL

Prerequisite:

Before you can test your PHP and MySQL application using XAMPP, you need to ensure that both Apache and MySQL services are running.

Here are the steps:

1.   Step 1. Start XAMPP:

• Open the XAMPP Control Panel. You can do this by navigating to the directory where XAMPP is installed and running xampp-control.exe.

2.   Step 2:   Start Apache:

• In the XAMPP Control Panel, find the "Apache" module.
• Click the "Start" button next to "Apache." This will start the Apache web server.

3.   Step 3:  Start MySQL:

• In the XAMPP Control Panel, find the "MySQL" module.
• Click the "Start" button next to "MySQL." This will start the MySQL database server.

4.   Step 4: Verify Running Services:

• After starting both Apache and MySQL, you should see "Running" next to their respective modules in the XAMPP Control Panel.
• Now that Apache and MySQL are running, you can open your web browser and access your PHP application by navigating to the appropriate URL.
• If your XAMPP installation is on your local machine, the URL might be http://localhost/your-directory/index.html,   where "your-directory" is the folder where you've placed your files.

PHP Connect MySQL

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

1. MySQLi extension (the "i" stands for improved)
2. PDO (PHP Data Objects)

Should I Use MySQLi (Object-Oriented or Procedural) or PDO?

If you need a short answer, it would be "Whatever you like".

Both MySQLi and PDO have their advantages:

1. PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.
2. So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code - queries included.
3. Both are object-oriented, but MySQLi also offers a procedural API.
4. Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.

MySQL Examples in Both MySQLi and PDO Syntax

There are three ways of working with PHP and MySQL:

1. MySQLi (object-oriented- Using Query method)
2. MySQLi (object-oriented- Using Prepared statements)
3. MySQLi (procedural)
4. PDO(PHP Data Objects)

SQL connectivity in PHP

To check SQL connectivity in PHP, follow these steps:

Step 1: Set Up Your Environment

Ensure you have a PHP environment set up with a web server like Apache or Nginx, and a database server like MySQL.

Step 2: Create a PHP File

Create a PHP file where you will write the script to check the database connectivity. Name it something like check_connection.php.

Step 3: Add Connection Credentials

In your PHP file, start by defining the database connection credentials such as hostname, username, password, and database name.

<?php

 

$servername = "localhost"; // or your server name...here "localhost"

$username = "your_username"; // your database username ...here "root"

$password = "your_password"; // your database password   ...here " "

?>

Step 4: Create the Connection

Use the mysqli or PDO (PHP Data Objects) extension to create a connection to the database.

By using MySQLi (Object Oriented).

Total Code For connect to PHP:

connect_object_mysql.php

<?php $servername = "localhost"; $username = "your_username"; $password = "your_password"; // Create connection $conn = new mysqli($servername, $username, $password); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } echo "Connected successfully"; ?>

Step-by-Step Explanation

1. Define Server and Login Information:

$servername = "localhost";
$username = "your_username";
$password = "your_password";

1. $servername: This is the address of the MySQL server. localhost is typically used when the server is on the same machine as your PHP script.
2. $username: This is the username you use to log into your MySQL database.
3. $password: This is the password associated with the username.

2. Create Connection:

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

1. new mysqli(): This creates a new connection to the MySQL server using the server name, username, and password provided. The resulting connection object is stored in the $conn variable.

3. Check Connection: 

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

1. if ($conn->connect_error): This checks if there was an error connecting to the database.
2. die("Connection failed: " . $conn->connect_error): If there's an error, die() stops the script execution and outputs the error message. This helps you understand why the connection failed.

Alternatives to die()

In production applications, you might prefer more advanced error handling techniques, such as:

· Exception Handling:

$servername = "localhost";

$username = "root";

$password = "";

$dbname = "myDatabase";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

throw new Exception("Connection failed: " . $conn->connect_error);

}

· Logging Errors:

$logFile = '/path/to/error.log';

$errorMessage = "Connection failed: " . $conn->connect_error;

error_log($errorMessage, 3, $logFile);

exit($errorMessage); // or redirect to an error page

Using die() for simple scripts and debugging is common, but for robust applications, consider using more comprehensive error handling strategies.

4. Success Message:

echo "Connected successfully";

1. echo "Connected successfully": If the connection is successful (no error), this line outputs the message "Connected successfully".

By Using MySQLi( Procedural)

Total Code :

connect_procedural_mysql.php

<?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()); } echo "Connected successfully"; ?>

Code Explanation

1. Define Server and Login Information: 

$servername = "localhost";

$username = "username";
$password = "password";

o $servername: This is the address of the MySQL server. localhost is used when the server is on the same machine as your PHP script.

o $username: This is the username you use to log into your MySQL database.

o $password: This is the password associated with the username.

2. Create Connection: 

$conn = mysqli_connect($servername, $username, $password);

o mysqli_connect(): This function attempts to establish a connection to the MySQL server using the provided server name, username, and password. The resulting connection object is stored in the $conn variable.

3. Check Connection:

if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

o if (!$conn): This checks if the connection was not successful. The mysqli_connect function returns false if the connection fails.

o die("Connection failed: " . mysqli_connect_error()): If the connection fails, the die() function stops the script execution and outputs an error message that includes the error returned by mysqli_connect_error(). This helps you understand why the connection failed.

4. Success Message:

echo "Connected successfully";

o echo "Connected successfully": If the connection is successful (meaning $conn is not false), this line outputs the message "Connected successfully".

By Using PDO

connect_PDO_mysql.php

<?php $servername = "localhost"; $username = "username"; $password = "password"; try { $conn = new PDO("mysql:host=$servername;", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo "Connected successfully"; } catch(PDOException $e) { echo "Connection failed: " . $e->getMessage(); } ?>

Code Explanation

1. Define Server and Login Information:

$servername = "localhost";

$username = "username";
$password = "password";

1. $servername: The address of the MySQL server. localhost is used when the server is on the same machine as your PHP script.
2. $username: The username you use to log into your MySQL database.
3. $password: The password associated with the username.

2. Create Connection with PDO:

try {

$conn = new PDO("mysql:host=$servername;", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}

Try Block: 

The try block contains the code that attempts to establish a connection to the MySQL server.

1. new PDO("mysql:host=$servername;", $username, $password): This creates a new PDO instance to connect to the MySQL server. The dsn (Data Source Name) specifies the driver (mysql) and the server host (host=$servername).
2. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION): This line sets the error mode for the PDO instance to throw exceptions. This means any errors that occur will generate a PDOException.
3. echo "Connected successfully": If the connection is successful, this message is displayed.

Catch Block: 

The catch block handles any exceptions thrown during the connection attempt.

1. catch(PDOException $e): This catches any PDOException that is thrown.
2. echo "Connection failed: " . $e->getMessage(): If an exception is caught, this line outputs an error message that includes the exception's message.

Tip: A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block.
Step 5: Test Your Connection

Open your file where you write the code in a web browser. For example, if your file is named connect_php.php and located in the root directory of your web server, navigate to http://localhost/ connect_php.php, check connected or not .

Explanation of PDO

PDO (PHP Data Objects)

1. PDO stands for PHP Data Objects. It's a PHP extension that provides a consistent interface for accessing databases.
2. PDO is a database access layer providing a uniform method of access to multiple databases. It does not provide a database abstraction but offers a data-access abstraction layer. This means, regardless of the database you're using (MySQL, PostgreSQL, SQLite, etc.), you use the same functions to issue queries and fetch data.

Key Points about PDO

1. Database Abstraction Layer: PDO serves as a database abstraction layer, meaning it provides a unified interface to interact with various database management systems (DBMS) such as MySQL, PostgreSQL, SQLite, SQL Server, etc. This allows developers to write code that works across different databases without needing to change the underlying SQL queries significantly.

2. Object-Oriented Interface: PDO uses an object-oriented approach to database access. Connections, queries, and results are represented as objects, making it easier to work with and manage database operations within PHP scripts.

3. Prepared Statements: PDO supports prepared statements, which help prevent SQL injection attacks by separating SQL logic from the data being supplied to it. This improves security by automatically escaping parameters and reducing the risk of malicious SQL code execution.

4. Error Handling: PDO provides robust error handling capabilities. It allows developers to set error handling modes (such as throwing exceptions), which simplifies debugging and improves code reliability by catching database-related errors programmatically.

5. Performance: While PDO adds a layer of abstraction, it generally performs well because it leverages the native database drivers provided by each DBMS. This means PDO doesn’t sacrifice performance significantly compared to using native extensions like mysqli.

Benefits of Using PDO

Security: Helps prevent SQL injection attacks through prepared statements and parameter binding.
Flexibility: Works across multiple database types, reducing the need for extensive code changes when switching databases.
Consistency: Provides a consistent API for database interactions, improving code maintainability and readability.
Error Handling: Offers robust error handling mechanisms, enhancing application reliability and ease of debugging.
Conclusion

PDO is a powerful and flexible extension in PHP for database access. It simplifies database interactions, enhances security, and promotes code portability across different DBMS platforms. Its object-oriented nature and support for prepared statements make it a preferred choice for many PHP developers when working with databases.

Explain this :

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

in PHP using PDO (PHP Data Objects) is used to set the error handling mode for the PDO connection object.

setAttribute Method

The setAttribute method is used to set an attribute on a PDO instance. It allows you to configure various settings related to how PDO operates.
Parameters Explained

1. First Parameter (PDO::ATTR_ERRMODE):

1. This specifies the attribute you want to set. PDO::ATTR_ERRMODE is a constant in PHP PDO that determines how PDO should handle errors.

2. Second Parameter (PDO::ERRMODE_EXCEPTION):

1. This specifies the value or mode for the attribute. PDO::ERRMODE_EXCEPTION is another constant that tells PDO to throw exceptions when errors occur.

Explanation
Error Modes in PDO:

1. PDO::ERRMODE_SILENT: This mode makes PDO silent about errors and does not throw exceptions. Instead, error codes can be retrieved using errorCode() or errorInfo().
2. PDO::ERRMODE_WARNING: In this mode, PDO will issue warnings but not throw exceptions. Useful for scenarios where you want errors to be logged but not stop script execution.
3. PDO::ERRMODE_EXCEPTION: This mode instructs PDO to throw PDOException exceptions when errors occur. This is particularly useful for error handling because it allows you to catch and handle exceptions in a structured manner within your PHP code.

Practical Use

Setting PDO::ERRMODE_EXCEPTION is a common practice because it:

Ensures that any database-related errors (like SQL syntax errors or connection failures) will trigger exceptions.

Allows you to use try-catch blocks to gracefully handle these exceptions, providing more control over error handling and debugging.

Example

Here’s how you typically set ERRMODE_EXCEPTION in a PDO connection setup:

 

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDatabase"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Set PDO to throw exceptions on error $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Other database operations can follow here } catch(PDOException $e) { // Handle PDO exceptions here echo "Connection failed: " . $e->getMessage(); } ?>

Benefits

1. Improved Debugging: Exceptions provide more detailed error messages, making it easier to identify and fix issues.
2. Consistent Error Handling: Using exceptions ensures a consistent approach to error management throughout your application.
3. Security: Helps prevent sensitive information leaks that might occur with other error handling modes.

In summary, setting PDO::ERRMODE_EXCEPTION ensures that PDO throws exceptions for database errors, facilitating better error handling and debugging in your PHP applications.

Close the Connection

The connection will be closed automatically when the script ends. To close the connection before, use the following:

MySQLi Object-Oriented: $conn->close(); 

 

MySQLi Procedural: mysqli_close($conn);

 

PDO: $conn = null;

Step 6: Handle Errors (Optional)

For better error handling and debugging, you can add more detailed error messages and handle exceptions.

<?php $servername = "localhost"; $username = "your_username"; $password = "your_password"; $dbname = "your_database"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } echo "Connected successfully"; // Close the connection (optional but recommended) $conn->close(); ?>

Using PDO (Alternative Method)

Alternatively, you can use PDO for connecting to the database. Here's how:

<?php $servername = "localhost"; $username = "your_username"; $password = "your_password"; $dbname = "your_database"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); echo "Connected successfully"; } catch(PDOException $e) { echo "Connection failed: " . $e->getMessage(); } // Close the connection (optional but recommended) $conn = null; ?>

Explanation:

1. new PDO(...): Creates a new PDO instance representing a connection to the database.

2. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);: Sets the error reporting mode to throw exceptions.

3. try...catch block: Attempts to create the connection and set the attribute. If an error occurs, it catches the PDOException and prints an error message.

Benefits of Using PDO::ERRMODE_EXCEPTION

1. Immediate Feedback: Errors are immediately thrown and can be caught, making debugging easier.
2. Consistent Error Handling: Using exceptions allows for consistent error handling practices.
3. Improved Code Readability: Makes the code more readable and maintainable by avoiding the need to manually check for errors after every database operation.
4. Control Flow Management: Exceptions can be used to control the flow of your application, allowing you to handle errors gracefully.

Step 7: Secure Your Credentials

By following these steps, you can check the connectivity in PHP effectively.

PHP Create a MySQL Database

Create A Database:

Creating a database can be done in two ways:

1. dynamically using a scripting language like PHP

2. via a graphical user interface tool like phpMyAdmin.

Choose What type and Why?

Choosing between dynamically creating a database using PHP and using phpMyAdmin depends on various factors such as your project requirements, your familiarity with tools, and the level of automation needed.

Dynamically Creating a Database Using PHP

Advantages:

1. Automation: Useful for applications that require dynamic database creation based on user input or other runtime conditions.

2. Scalability: Ideal for scalable applications where new databases need to be created programmatically as part of the application's workflow.

3. Integration: Easily integrates with other parts of the application, allowing for seamless database management within your codebase.

Disadvantages:

1. Complexity: Requires knowledge of PHP and SQL, which might be complex for beginners.

2. Error Handling: Errors in SQL syntax or connection issues must be handled programmatically, which can add complexity to the code.

Creating a Database Using phpMyAdmin

Advantages:

1. User-Friendly: Provides a graphical interface that is intuitive and easy to use, making it accessible for beginners and those who prefer a visual approach.

2. Quick Setup: Allows for quick database creation without writing code, which can be faster for simple tasks.

3. Administration Tools: Includes various tools for managing databases, tables, and data, making it a powerful tool for database administration.

Disadvantages:

1. Manual Process: Not suitable for scenarios where databases need to be created dynamically or programmatically.

2. Limited Automation: Requires manual intervention, which might not be ideal for automated workflows or large-scale applications.

Which is Better?

1. For Beginners and Simple Tasks: phpMyAdmin is better due to its ease of use and graphical interface. It's perfect for those who need to quickly set up and manage databases without diving into code.
2. For Advanced Users and Dynamic Applications: Creating databases using PHP is better for applications that require automation and dynamic database creation. It provides more control and can be integrated into the application's workflow, making it suitable for complex and scalable projects.

Conclusion

1. The choice between dynamically creating a database using PHP and using phpMyAdmin depends on your specific needs:
2. Use phpMyAdmin for straightforward, manual database management tasks and when ease of use is a priority.
3. Use PHP scripts for more advanced, automated, and dynamic database management as part of your application's codebase.

A database consists of one or more tables.

Type 1: Creating a Database Using phpMyAdmin

phpMyAdmin is a web-based tool that provides a graphical interface to manage MySQL databases.

Steps to create a database using phpMyAdmin:

1. Access phpMyAdmin: Open phpMyAdmin in your web browser (typically accessed via http://localhost/phpmyadmin).

2. Select the "Databases" Tab: Click on the "Databases" tab at the top of the page.

3. Create a Database:

o In the "Create database" field, enter the name of the database you want to create.

o Click the "Create" button.

Once you click "Create," phpMyAdmin will execute the necessary SQL command to create the database.

Type 2: Create a database dynamically using a scripting language like PHP

Create a MySQL Database Using MySQLi and PDO

The CREATE DATABASE statement is used to create a database in MySQL.

The steps 1 -4 are same as in Privious.

Step 5:Create A Database:

Modify the previous connect_php.php or You can create a file as connect_db.php

The following examples create a database named "myDB":

Example (MySQLi Object-oriented)

connect_object_db.php

<?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); } // Create database $sql = "CREATE DATABASE myDB"; if ($conn->query($sql) === TRUE) { echo "Database created successfully"; } else { echo "Error creating database: " . $conn->error; } $conn->close(); ?>

Note: When you create a new database, you must only specify the first three arguments to the mysqli object (servername, username and password).

Tip: If you have to use a specific port, add an empty string for the database-name argument, like this: new mysqli("localhost", "username", "password", "", port)

Example (MySQLi Procedural)

connect_procedure_db.php

<?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 myDB"; if (mysqli_query($conn, $sql)) { echo "Database created successfully"; } else { echo "Error creating database: " . mysqli_error($conn); } mysqli_close($conn); ?>

   

Example (PDO)

connect_pdo_db.php

<?php $servername = "localhost"; $username = "username"; $password = "password"; try { $conn = new PDO("mysql:host=$servername", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "CREATE DATABASE myDBPDO"; // use exec() because no results are returned $conn->exec($sql); echo "Database created successfully<br>"; } catch(PDOException $e) { echo $sql . "<br>" . $e->getMessage(); } $conn = null; ?>

 

Tip: A great benefit of PDO is that it has exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try{ } block, the script stops executing and flows directly to the first catch(){ } block. In the catch block above we echo the SQL statement and the generated error message.

Step 6: Test Your db Creation

Open your file where you write the code in a web browser. For example, if your file is named connect_php.php or connect_db.php and located in the root directory of your web server, navigate to http://localhost/ your_filename.php, check database created or  not .

PHP MySQL Create Table

Create A Table:

A database table has its own unique name and consists of columns and rows.

Creating tables in a database can also be done by these following two ways :

1. via the phpMyAdmin graphical interface.

2. dynamically using PHP scripts

Summary

1. Using PHP: You can create tables dynamically by writing PHP scripts that execute SQL commands. This is useful for applications that need to create tables based on user input or other runtime conditions.
2. Using phpMyAdmin: You can create tables using a graphical interface, which is often simpler and more intuitive for beginners or quick setups.

Which is Better?

1. For Beginners and Simple Tasks: phpMyAdmin is better due to its ease of use and graphical interface. It’s perfect for those who need to quickly set up and manage tables without writing code.
2. For Advanced Users and Dynamic Applications: Creating tables using PHP is better for applications that require automation and dynamic table creation. It provides more control and can be integrated into the application's workflow, making it suitable for complex and scalable projects.

Dynamically Creating Tables Using PHP

Advantages:

1. Automation: Ideal for applications requiring dynamic table creation based on user input or runtime conditions.

2. Integration: Seamlessly integrates with the application’s codebase, allowing for programmatic control over table management.

3. Scalability: Useful for scalable applications that need to create and manage tables on-the-fly.

Disadvantages:

1. Complexity: Requires knowledge of PHP and SQL, which can be complex for beginners.

2. Error Handling: Needs robust error handling for SQL syntax errors and connection issues, adding to the complexity of the code.

3. Debugging: Troubleshooting issues can be more challenging compared to using a graphical interface.

Creating Tables Using phpMyAdmin

Advantages:

1. User-Friendly: Provides an intuitive graphical interface, making it accessible and easy for beginners to use.

2. Quick Setup: Allows for fast table creation without writing any code, which is efficient for simple tasks.

3. Visual Feedback: Offers immediate visual feedback on the table structure and data, aiding in quick validation and adjustments.

4. Comprehensive Tools: Includes various tools for managing databases, tables, and data, making it a powerful tool for database administration.

Disadvantages:

1. Manual Process: Not suitable for scenarios requiring automated or dynamic table creation.

2. Limited Automation: Requires manual intervention, which may not be ideal for large-scale applications needing frequent table adjustments.

3. Dependence on Interface: Relies on access to phpMyAdmin, which may not always be available or practical in all environments.

Which is Better?

1. For Beginners and Simple Tasks: phpMyAdmin is typically better due to its ease of use and graphical interface. It allows for quick setup and management without requiring coding skills.
2. For Advanced Users and Dynamic Applications: Creating tables using PHP is better for applications that require automation and dynamic table creation. It offers greater control and flexibility, making it suitable for complex and scalable projects.

Conclusion

The choice between using PHP scripts and phpMyAdmin to create tables depends on your specific needs and level of expertise:

1. Use phpMyAdmin for straightforward, manual table management tasks and when ease of use and visual feedback are priorities.
2. Use PHP scripts for advanced, automated, and dynamic table management as part of your application's codebase, especially when integration and scalability are critical.

Type 1 : Creating Tables Using phpMyAdmin

phpMyAdmin provides a user-friendly interface to create tables. The steps are :

1. Access phpMyAdmin: Open phpMyAdmin in your web browser (typically accessed via http://localhost/phpmyadmin). 

2. Select the Database: Click on the database in which you want to create the table.

3. Create a Table:

o Click on the "Structure" tab if you’re not already there.

o Enter the table name and the number of columns in the "Create table" section, then click "Go".

Define the columns: name, type, length, and other attributes for each column.

o Click "Save" to create the table.

Type 2: Dynamically Creating Tables Using PHP

To create a table dynamically using PHP, you use SQL commands within your PHP script to define the table structure.

The steps are: 

1. Connect to the Database: Use the mysqli_connect() or PDO to connect to the database.

2. Create Table: Use the CREATE TABLE SQL command to create the table.

Create a MySQL Table Using MySQLi and PDO

The CREATE TABLE statement is used to create a table in MySQL.

We will create a table named "MyGuests", with five columns: "id", "firstname", "lastname", "email" and "reg_date":

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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

)

Notes on the table above:

The data type specifies what type of data the column can hold.

After the data type, you can specify other optional attributes for each column:

1. NOT NULL - Each row must contain a value for that column, null values are not allowed
2. DEFAULT value - Set a default value that is added when no other value is passed
3. UNSIGNED - Used for number types, limits the stored data to positive numbers and zero
4. AUTO INCREMENT - MySQL automatically increases the value of the field by 1 each time a new record is added
5. PRIMARY KEY - Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT

Each table should have a primary key column (in this case: the "id" column). Its value must be unique for each record in the table.

The following examples shows how to create the table in PHP:

Example (MySQLi Object-oriented)

create_object_table.php

<?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 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )"; if ($conn->query($sql) === TRUE) { echo "Table MyGuests created successfully"; } else { echo "Error creating table: " . $conn->error; } $conn->close(); ?>

 

Example (MySQLi Procedural)

create_procedure_table.php

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )"; if (mysqli_query($conn, $sql)) { echo "Table MyGuests created successfully"; } else { echo "Error creating table: " . mysqli_error($conn); } mysqli_close($conn); ?>

 

Example (PDO)
create_pdo_table.php

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )"; // use exec() because no results are returned $conn->exec($sql); echo "Table MyGuests created successfully"; } catch(PDOException $e) { echo $sql . "<br>" . $e->getMessage(); } $conn = null; ?>

 Step 6: Test Your table Creation

Open your file where you write the code in a web browser. For example, if your file is named connect_php.php or create_table.php and located in the root directory of your web server, navigate to http://localhost/your_filename.php, check table created or not .

PHP MySQL Insert Data

After a database and a table have been created, we can start adding data in them.

Here are some syntax rules to follow:

1. The SQL query must be quoted in PHP
2. String values inside the SQL query must be quoted
3. Numeric values must not be quoted
4. The word NULL must not be quoted

Inserting data into tables

Inserting data into tables can be done again in two ways :

1. dynamically using PHP scripts or

2. manually through tools like phpMyAdmin.

Summary

1. Using PHP: You can insert data dynamically by writing PHP scripts that execute SQL commands. This method is useful for applications that need to insert data based on user input or other runtime conditions.
2. Using phpMyAdmin: You can insert data using a graphical interface, which is simpler and more intuitive for beginners or quick data entry tasks.

Which is Better?

1. For Beginners and Simple Tasks: phpMyAdmin is better due to its ease of use and graphical interface. It allows for quick data entry without writing code.
2. For Advanced Users and Dynamic Applications: Inserting data using PHP is better for applications that require automation and dynamic data insertion. It provides more control and can be integrated into the application's workflow.

Conclusion

The choice between using PHP scripts and phpMyAdmin to insert data depends on your specific needs:

1. Use phpMyAdmin for straightforward, manual data entry tasks and when ease of use and visual feedback are priorities.
2. Use PHP scripts for advanced, automated, and dynamic data insertion as part of your application's codebase, especially when integration and scalability are critical.

Inserting Data Using phpMyAdmin

phpMyAdmin provides a graphical interface for inserting data into tables. Here’s how to do it:

1. Access phpMyAdmin: Open phpMyAdmin in your web browser (typically accessed via http://localhost/phpmyadmin).

2. Select the Database and Table: Click on the database and then the table into which you want to insert data.

3. Insert Data:

o Click on the "Insert" tab.

o Fill in the values for each column in the form provided.

o Click "Go" to execute the insert operation.

Dynamically Inserting Data Using PHP

To insert data into a table dynamically using PHP, you use SQL INSERT statements within your PHP script.

Steps are:

1. Connect to the Database: Use the mysqli_connect() or PDO to connect to the database.

2. Prepare and Execute the SQL INSERT Statement: Use SQL to insert data into the table.


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,...)

In the previous chapter we created an empty table named "MyGuests" with five columns: "id", "firstname", "lastname", "email" and "reg_date". Now, let us fill the table with data.

Note: If a column is AUTO_INCREMENT (like the "id" column) or TIMESTAMP with default update of current_timesamp (like the "reg_date" column), it is no need to be specified in the SQL query; MySQL will automatically add the value.

The following examples add a new record to the "MyGuests" table:

insert_table.php

Example (MySQLi Object-oriented)

<?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 = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')"; if ($conn->query($sql) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $sql . "<br>" . $conn->error; } $conn->close(); ?>

 

Example (MySQLi Procedural)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // 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 ('John', 'Doe', 'john@example.com')"; if (mysqli_query($conn, $sql)) { echo "New record created successfully"; } else { echo "Error: " . $sql . "<br>" . mysqli_error($conn); } mysqli_close($conn); ?>

 

Example (PDO)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john@example.com')"; // use exec() because no results are returned $conn->exec($sql); echo "New record created successfully"; } catch(PDOException $e) { echo $sql . "<br>" . $e->getMessage(); } $conn = null; ?>

 

Example:

Using mysqli extension with prepare statemnets:

<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "myDatabase"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->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.doe@example.com"; $stmt->execute(); echo "New records created successfully"; $stmt->close(); $conn->close(); ?>

 

Explanation:
Key Components

1. $conn->prepare(): This method prepares an SQL statement for execution. It takes an SQL string with placeholders (?) for the values that will be bound later.

2. $stmt->bind_param(): This method binds variables to the placeholders in the prepared statement. The first argument "sss" specifies the types of the variables (s for string in this case).

3. Setting Parameters: The variables $firstname, $lastname, and $email are set with the values you want to insert into the table.

4. $stmt->execute(): This method executes the prepared statement with the bound variables.




mysqli vs. PDO

· mysqli: The provided code uses the mysqli extension, which is specific to MySQL databases. It supports both object-oriented and procedural programming paradigms. The prepare, bind_param, and execute methods are part of the mysqli extension's API.

· PDO (PHP Data Objects): PDO is a database access layer providing a uniform method of access to multiple databases. Unlike mysqli, PDO supports a variety of databases such as MySQL, PostgreSQL, SQLite, and more. PDO uses a different API for prepared statements, binding parameters, and executing queries.

PDO Example Of above example:

For comparison, here’s how the same operation would look using PDO: 

<?php $dsn = 'mysql:host=localhost;dbname=myDatabase'; $username = 'root'; $password = ''; try { $pdo = new PDO($dsn, $username, $password); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Prepare the SQL statement $stmt = $pdo->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (:firstname, :lastname, :email)"); // Bind parameters $stmt->bindParam(':firstname', $firstname); $stmt->bindParam(':lastname', $lastname); $stmt->bindParam(':email', $email); // Set parameters and execute $firstname = "John"; $lastname = "Doe"; $email = "john.doe@example.com"; $stmt->execute(); echo "New record created successfully"; } catch (PDOException $e) { echo "Error: " . $e->getMessage(); } $pdo = null; ?>

 

In the PDO example:

:firstname, :lastname, and :email are named placeholders.

bindParam is used to bind variables to the placeholders.

execute is used to execute the prepared statement.

Conclusion

The code snippet you provided is using the mysqli extension. It’s suitable for MySQL-specific projects. If you need to support multiple database types or prefer a more consistent API, you might consider using PDO instead.

Step 6: Test Your table Insertion

Open your file where you write the code in a web browser. For example, if your file is named insert_table.php p and located in the root directory of your web server, navigate to http://localhost/ insert_table.php, check connected or not .

PHP MySQL Prepared Statements

Prepared statements are very useful against SQL injections.

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.

Prepared Statements in MySQLi

Example (MySQLi with Prepared Statements)

<?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); } // 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(); $conn->close(); ?>

Explanation:

"INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"

In our SQL, we insert a question mark (?) where we want to substitute in an integer, string, double or blob value.

Then, have a look at the bind_param() function:

$stmt->bind_param("sss", $firstname, $lastname, $email);

This function binds the parameters to the SQL query and tells the database what the parameters are. The "sss" argument lists the types of data that the parameters are. The s character tells mysql that the parameter is a string.

The argument may be one of four types:

i - integer

d - double

s - string

b - BLOB

We must have one of these for each parameter.

By telling mysql what type of data to expect, we minimize the risk of SQL injections.

Note: If we want to insert any data from external sources (like user input), it is very important that the data is sanitized and validated.

Prepared Statements in PDO

Example (PDO with Prepared Statements)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // prepare sql and bind parameters $stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (:firstname, :lastname, :email)"); $stmt->bindParam(':firstname', $firstname); $stmt->bindParam(':lastname', $lastname); $stmt->bindParam(':email', $email); // insert a row $firstname = "John"; $lastname = "Doe"; $email = "john@example.com"; $stmt->execute(); // insert another row $firstname = "Mary"; $lastname = "Moe"; $email = "mary@example.com"; $stmt->execute(); // insert another row $firstname = "Julie"; $lastname = "Dooley"; $email = "julie@example.com"; $stmt->execute(); echo "New records created successfully"; } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; ?>

 

PHP MySQL Select Data

Select Data From a MySQL Database

The SELECT statement is used to select data from one or more tables:

SELECT column_name(s) FROM table_name

or we can use the * character to select ALL columns from a table:

SELECT * FROM table_name

Select Data With MySQLi

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

Example (MySQLi Object-oriented)

<?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(); ?>

   Explanation

Code lines to explain from the example above:

1. First, we set up an SQL query that selects the id, firstname and lastname columns from the MyGuests table. The next line of code runs the query and puts the resulting data into a variable called $result.
2. Then, the function num_rows() checks if there are more than zero rows returned.
3. If there are more than zero rows returned, the function fetch_assoc() puts all the results into an associative array that we can loop through. The while() loop loops through the result set and outputs the data from the id, firstname and lastname columns.

Example (MySQLi Procedural)

<?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "SELECT id, firstname, lastname 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 "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>"; } } else { echo "0 results"; } mysqli_close($conn); ?>

   You can also put the result in an HTML table:

Example (MySQLi Object-oriented)

<?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) { echo "<table><tr><th>ID</th><th>Name</th></tr>"; // output data of each row while($row = $result->fetch_assoc()) { echo "<tr><td>".$row["id"]."</td><td>".$row["firstname"]." ".$row["lastname"]."</td></tr>"; } echo "</table>"; } else { echo "0 results"; } $conn->close(); ?>

 

Select Data With PDO (+ Prepared Statements)

The following example uses prepared statements.

It selects the id, firstname and lastname columns from the MyGuests table and displays it in an HTML table:

Example (PDO)

<?php echo "<table style='border: solid 1px black;'>"; echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>"; class TableRows extends RecursiveIteratorIterator { function __construct($it) { parent::__construct($it, self::LEAVES_ONLY); } function current() { return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>"; } function beginChildren() { echo "<tr>"; } function endChildren() { echo "</tr>" . "\n"; } } $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDBPDO"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyGuests"); $stmt->execute(); // set the resulting array to associative $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { echo $v; } } catch(PDOException $e) { echo "Error: " . $e->getMessage(); } $conn = null; echo "</table>"; ?>

 

Save the data from a form 

in database

To save data from a form into a database in PHP, follow these steps:

Step 1: Set Up Your Environment

Ensure you have a PHP environment set up with a web server like Apache or Nginx, and a database server like MySQL.

Step 2: Create a Database and Table

Create a database and a table where you will store the form data. 

CREATE DATABASE my_database;
USE my_database;

CREATE TABLE my_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
message TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Create an HTML Form

Create an HTML form where users can input their data.

form.html 

<!DOCTYPE html> <html> <head> <title>Form to Database</title> </head> <body> <form action="submit_form.php" method="post"> <label for="name">Name:</label><br> <input type="text" id="name" name="name" required><br><br> <label for="email">Email:</label><br> <input type="email" id="email" name="email" required><br><br> <label for="message">Message:</label><br> <textarea id="message" name="message" required></textarea><br><br> <input type="submit" value="Submit"> </form> </body> </html>

 Step 4: Handle Form Submission in PHP

Create a PHP script (submit_form.php) to handle the form submission and save the data to the database.

Using Prepared Statements

submit_form.php 

<?php $servername = "localhost"; $username = "your_username"; $password = "your_password"; $dbname = "my_database"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Get form data and sanitize $name = htmlspecialchars(trim($_POST['name'])); $email = htmlspecialchars(trim($_POST['email'])); $message = htmlspecialchars(trim($_POST['message'])); // Validate form data if (empty($name) || empty($email) || empty($message)) { die("All fields are required."); } if (!filter_var($email, FILTER_VALIDATE_EMAIL)) { die("Invalid email format."); } // Prepare and bind $stmt = $conn->prepare("INSERT INTO my_table (name, email, message) VALUES (?, ?, ?)"); $stmt->bind_param("sss", $name, $email, $message); // Execute the statement if ($stmt->execute()) { echo "New record created successfully"; } else { echo "Error: " . $stmt->error; } // Close connections $stmt->close(); $conn->close(); ?>

 Using mysqli with query() Method

While not recommended due to security risks, here is how you can use the query() method.

Create a PHP script (submit_form.php) to handle the form submission and save the data to the database.

Using query() Method

submit_form.php 

<?php $servername = "localhost"; $username = "your_username"; $password = "your_password"; $dbname = "my_database"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Get form data and sanitize $name = htmlspecialchars(trim($_POST['name'])); $email = htmlspecialchars(trim($_POST['email'])); $message = htmlspecialchars(trim($_POST['message'])); // Validate form data if (empty($name) || empty($email) || empty($message)) { die("All fields are required."); } if (!filter_var($email, FILTER_VALIDATE_EMAIL)) { die("Invalid email format."); } // Create SQL query $sql = "INSERT INTO my_table (name, email, message) VALUES ('$name', '$email', '$message')"; // Execute the query if ($conn->query($sql) === TRUE) { echo "New record created successfully"; } else { echo "Error: " . $conn->error; } // Close connection $conn->close(); ?>

 

Important Note: 

Using the query() method with user inputs directly, as shown above, is vulnerable to SQL injection. Always prefer using prepared statements to ensure the security of your application.

Step 5: Test Your Form

Open your form in a web browser and submit it. For example, if your form file is named form.html and located in the root directory of your web server, navigate to http://localhost/form.html, fill in the form, and submit it.

Step 6: Validate Form Data 

(Optional but Recommended)

For security and data integrity, validate the form data before saving it to the database. 

$name = trim($_POST['name']); $email = trim($_POST['email']); $message = trim($_POST['message']); if (empty($name) || empty($email) || empty($message)) { die("All fields are required."); } if (!filter_var($email, FILTER_VALIDATE_EMAIL)) { die("Invalid email format."); }

 

Step 7: Sanitize Data 

(Optional but Recommended)

Sanitize the data to prevent SQL injection and other security issues. 

$name = htmlspecialchars($name); $email = htmlspecialchars($email); $message = htmlspecialchars($message);

 

Step 8: Secure Your PHP Script

Ensure your PHP script is secure. 

Using prepared statements as shown helps prevent SQL injection or Avoid exposing sensitive information.
If you prefer using mysqli with the query() method instead of prepared statements, here's how you can do it. 

Note that using query() directly with user inputs is not recommended due to the risk of SQL injection.

It is always better to use prepared

How to retrieve data from database

To retrieve data from a database in PHP using the mysqli extension, follow these steps:

using the mysqli extension.

Step 1: Set Up Your Environment

Ensure you have a PHP environment set up with a web server like Apache or Nginx, and a database server like MySQL.

Step 2: Create a Database and Table

Create a database and a table where you will store the data. (Assuming this step is already done from previous steps)

Step 3: Insert Sample Data (Optional)

Insert some sample data into your table to retrieve. INSERT INTO my_table (name, email, message) VALUES
('John Doe', 'john@example.com', 'Hello world!'),
('Jane Smith', 'jane@example.com', 'PHP is great!'),
('Bob Brown', 'bob@example.com', 'I love coding!');

Step 4: Create a PHP Script to Retrieve Data

Create a PHP script (retrieve_data.php) to connect to the database and retrieve the data. 

<?php $servername = "localhost"; $username = "your_username"; $password = "your_password"; $dbname = "my_database"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Create SQL query $sql = "SELECT id, name, email, message, created_at FROM my_table"; $result = $conn->query($sql); // Check if there are results if ($result->num_rows > 0) { // Output data of each row while($row = $result->fetch_assoc()) { echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. " - Message: " . $row["message"]. " - Created At: " . $row["created_at"]. "<br>"; } } else { echo "0 results"; } // Close connection $conn->close(); ?>

 Step 5: Test Your Script

Open your script in a web browser to see the retrieved data.

For example, if your script file is named retrieve_data.php and located in the root directory of your web server, navigate to http://localhost/retrieve_data.php.

Optional: Format Output in HTML Table

For better readability, you can format the output in an HTML table. 

<?php $servername = "localhost"; $username = "your_username"; $password = "your_password"; $dbname = "my_database"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Create SQL query $sql = "SELECT id, name, email, message, created_at FROM my_table"; $result = $conn->query($sql); echo "<table border='1'> <tr> <th>ID</th> <th>Name</th> <th>Email</th> <th>Message</th> <th>Created At</th> </tr>"; // Check if there are results if ($result->num_rows > 0) { // Output data of each row while($row = $result->fetch_assoc()) { echo "<tr> <td>" . $row["id"]. "</td> <td>" . $row["name"]. "</td> <td>" . $row["email"]. "</td> <td>" . $row["message"]. "</td> <td>" . $row["created_at"]. "</td> </tr>"; } } else { echo "<tr><td colspan='5'>0 results</td></tr>"; } echo "</table>"; // Close connection $conn->close(); ?>

 Summary

By following these steps, you can retrieve and display data from a MySQL database in PHP using the mysqli extension. This process includes connecting to the database, executing a SQL SELECT query, and outputting the results. For enhanced security and functionality, consider using prepared statements and proper error handling in your scripts.

using prepared statements

Step 1: Set Up Your Environment

Ensure you have a PHP environment set up with a web server like Apache or Nginx, and a database server like MySQL.

Step 2: Create a Database and Table

Create a database and a table where you will store the data. (Assuming this step is already done from previous steps)

Step 3: Insert Sample Data (Optional)

Insert some sample data into your table to retrieve. INSERT INTO my_table (name, email, message) VALUES
('John Doe', 'john@example.com', 'Hello world!'),
('Jane Smith', 'jane@example.com', 'PHP is great!'),
('Bob Brown', 'bob@example.com', 'I love coding!');

Step 4: Create a PHP Script to Retrieve Data

Create a PHP script (retrieve_data.php) to connect to the database and retrieve the data using prepared statements and proper error handling.

<?php $servername = "localhost"; $username = "your_username"; $password = "your_password"; $dbname = "my_database"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Prepare SQL statement $sql = "SELECT id, name, email, message, created_at FROM my_table"; $stmt = $conn->prepare($sql); if ($stmt === false) { die("Error preparing statement: " . $conn->error); } // Execute the statement if (!$stmt->execute()) { die("Error executing statement: " . $stmt->error); } // Bind result variables $stmt->bind_result($id, $name, $email, $message, $created_at); // Fetch values and output results echo "<table border='1'> <tr> <th>ID</th> <th>Name</th> <th>Email</th> <th>Message</th> <th>Created At</th> </tr>"; while ($stmt->fetch()) { echo "<tr> <td>$id</td> <td>$name</td> <td>$email</td> <td>$message</td> <td>$created_at</td> </tr>"; } echo "</table>"; // Close statement and connection $stmt->close(); $conn->close(); ?>

 

Step 5: Test Your Script

Open your script in a web browser to see the retrieved data.

For example, if your script file is named retrieve_data.php and located in the root directory of your web server, navigate to http://localhost/retrieve_data.php.

Summary

By using prepared statements and proper error handling, you can securely retrieve and display data from a MySQL database in PHP.

Prepared statements help prevent SQL injection attacks, while proper error handling ensures that any issues are reported and managed correctly.


Open your file where you write the code in a web browser. For example, if your file is named connect_php.php or create_table.php and located in the root directory of your web server, navigate to http://localhost/ create_table.php, check connected or not .