mysqli
mysqli
2021/05/05
簡介
mysqli是php提供的mysql資料庫語法，i是加強版的意思。
要先新增一個practice的資料庫(schema)，編碼請選擇utf8_unicode_ci。然後，利用SQL產生以下的資料表:
CREATE TABLE `practice`.`job` (
`postid` INT NOT NULL AUTO_INCREMENT ,
`company` VARCHAR(45) NOT NULL ,
`content` TEXT NOT NULL ,
`pdate` DATE NOT NULL ,
PRIMARY KEY (`postid`));
標準的SQL語法:
CREATE TABLE practice.job (
postid INT NOT NULL AUTO_INCREMENT ,
company VARCHAR(45) NOT NULL ,
content TEXT NOT NULL ,
pdate DATE NOT NULL ,
PRIMARY KEY (postid));
建立連結
<?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";
// Close connection
mysqli_close($conn);
?>
讀取資料
<?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);
?>
新增資料
<?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);
?>
為了避免SQL Injection以及效率，比較好的寫法是透過Prepared Statement
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
// Prepare an insert statement
$sql = "INSERT INTO persons (first_name, last_name, email) VALUES (?, ?, ?)";
if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);
/* Set the parameters values and execute
the statement again to insert another row */
$first_name = "Hermione";
$last_name = "Granger";
$email = "hermionegranger@mail.com";
mysqli_stmt_execute($stmt);
/* Set the parameters values and execute
the statement to insert a row */
$first_name = "Ron";
$last_name = "Weasley";
$email = "ronweasley@mail.com";
mysqli_stmt_execute($stmt);
echo "Records inserted successfully.";
} else{
echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link);
}
// Close statement
mysqli_stmt_close($stmt);
// Close connection
mysqli_close($link);
?>
更新資料
<?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 = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
if (mysqli_query($conn, $sql)) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
為了避免SQL Injection以及效率，比較好的寫法是透過Prepared Statement
刪除資料
<?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 delete a record
$sql = "DELETE FROM MyGuests WHERE id=3";
if (mysqli_query($conn, $sql)) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . mysqli_error($conn);
}
mysqli_close($conn);
?>