What are some best practices for storing and retrieving data from a MySQL table in PHP?

When storing and retrieving data from a MySQL table in PHP, it is important to use prepared statements to prevent SQL injection attacks. Additionally, it is recommended to sanitize user input before storing it in the database to ensure data integrity. When retrieving data, make sure to handle errors properly and close the database connection after use to free up resources. 

// Connect to MySQL database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
$conn = new mysqli($servername, $username, $password, $dbname);

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

// Prepare and execute a query to insert data into a table
$stmt = $conn->prepare("INSERT INTO table_name (column1, column2) VALUES (?, ?)");
$stmt->bind_param("ss", $value1, $value2);
$value1 = "value1";
$value2 = "value2";
$stmt->execute();

// Prepare and execute a query to retrieve data from a table
$stmt = $conn->prepare("SELECT column1, column2 FROM table_name WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 1;
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // Process the retrieved data
}

// Close the database connection
$conn->close();

Keywords

MySQL PHP data storage data retrieval database operations

Related Questions