Using prepared statements in PHP with MySQL is a recommended way to avoid SQL injection, which is a common web application vulnerability.
Connecting to the database:
$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);
}
Insert Data Using Prepared Statements:
$stmt = $conn->prepare("INSERT INTO MyTable (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email); // 'sss' indicates string, string, string
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();
echo "New record created successfully";
$stmt->close();
$conn->close();
Select Data Using Prepared Statements:
$stmt = $conn->prepare("SELECT id, firstname, lastname FROM MyTable WHERE lastname=?");
$stmt->bind_param("s", $lastname);
$lastname = "Doe";
$stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
while ($row = $result->fetch_assoc()) {
echo "id: " . $row["id"] . " - Name: " . $row["firstname"] . " " . $row["lastname"] . "<br>";
}
$stmt->close();
$conn->close();
Update Data Using Prepared Statements:
$stmt = $conn->prepare("UPDATE MyTable SET email=? WHERE firstname=?");
$stmt->bind_param("ss", $email, $firstname);
$email = "john.new@example.com";
$firstname = "John";
$stmt->execute();
echo $stmt->affected_rows . " record(s) updated";
$stmt->close();
$conn->close();
Delete Data Using Prepared Statements:
$stmt = $conn->prepare("DELETE FROM MyTable WHERE lastname=?");
$stmt->bind_param("s", $lastname);
$lastname = "Doe";
$stmt->execute();
echo $stmt->affected_rows . " record(s) deleted";
$stmt->close();
$conn->close();
Another popular option is PDO (PHP Data Objects). PDO allows you to use prepared statements in a more general way and is not specific to MySQL, meaning you can use it with other databases as well.
Connecting to the database:
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
The usage of prepared statements with PDO is similar to MySQLi, but the binding of parameters and the fetching of results can be a bit different.
UPCET Exam
Click Here
SAAT Exam
Click Here
MHT CET Exam
Click Here
IPU CET Exam
Click Here
KCET Exam
Click Here
COMEDK UG Exam
Click Here
VITEEE Exam
Click Here
BITSAT
Click Here
DSAT: Dayanand Sagar Admission Test
Click Here
Career In Animation in india
Click Here
Merchant Navy Courses in india
Click Here
Interior Design Career in india
Click Here
UGC NET Exam
Click Here
B. Ed Exam
Click Here
AFCAT - Air Force Common Admission Test
Click Here
GATE Exam
Click Here
Joint Entrance Examination (JEE)
Click Here
Common Admission Test (CAT)
Click Here
CDS - Combined Defence Services Exam
Click Here