PHP執行sql

Yolololololo發表於2024-05-31
PHPSQL

PHP連線MySQL

連線資料庫

<?php
$severname='localhost';
$username='root';
$password='123456';
$dbanme='資料庫名'

//建立連線
$conn = new mysqli($servername, $username,$password, $dbname);

//連線檢測
if ($conn->connect_error) {
    die("連線失敗: " . $conn->connect_error);
} else {
    echo "連線成功";
    $conn->close();
}

PHP執行sql

規定由 mysqli_query()、mysqli_store_result() 或 mysqli_use_result() 返回的結果集識別符號

mysqli_query() 函式執行某個針對資料庫的查詢
建立資料庫
<?php
$servername='localhost';
$username='root';
$password='root';

//建立連線
$conn = new mysqli($servername,$username,$password);

//檢測連線
if ($conn->connect_error)
{
    die('連線失敗：'.$conn->connect_error);
}else{
    echo '連線成功!';
}

//建立資料庫
$sql="create database myDB";
if($conn->query($sql) === TRUE){
    echo '資料庫建立成功';
}else{
    echo '資料庫建立失敗：'.$conn->error;
}

$conn->close();

<?php
$servername = "localhost";
$username = "username";
$password = "password";
 
// 建立連線
$conn = mysqli_connect($servername, $username, $password);
// 檢測連線
if(!$conn){
    die("連線失敗: " . mysqli_connect_error());
}
 
// 建立資料庫
$sql = "CREATE DATABASE myDB";
if(mysqli_query($conn,$$sql)){
	echo "資料庫建立成功";
} else {
    echo "Error creating database: " . mysqli_error($conn);
mysqli_close($conn);
}
建立表
  • DEFAULT value - 設定預設值
  • UNSIGNED - 使用無符號數值型別，0 及正數
  • PRIMARY KEY - 設定資料表中每條記錄的唯一標識。 通常列的 PRIMARY KEY 設定為 ID 數值，與 AUTO_INCREMENT 一起使用。
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

// 建立連線
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測連線
if ($conn->connect_error) {
    die("連線失敗: " . $conn->connect_error);
}
//sql語句
$sql="create table users(
id int(6) unsigned auto_increment primary key,
firstname varchar(30) not null,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

if ($conn->query($sql)===TRUE){
	echo "表建立成功！";
} else {
    echo "建立資料表錯誤: " . $conn->error;
}

$conn->close();
插入資料
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

// 建立連線
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測連線
if ($conn->connect_error) {
    die("連線失敗: " . $conn->connect_error);
}
//sql語句
$sql = "INSERT INTO users (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql)===TRUE){
	echo "表建立成功！";
} else {
    echo "建立資料表錯誤: " . $conn->error;
}

$conn->close();
插入多條資料
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

// 建立連線
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測連線
if ($conn->connect_error) {
    die("連線失敗: " . $conn->connect_error);
}
//sql語句
$sql = "INSERT INTO users (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO users (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO users (firstname, lastname, email)
VALUES ('Julie', 'Dooley', 'julie@example.com')";

if ($conn->multi_query($sql)===TRUE){
    echo "表建立成功！";
} else {
    echo "建立資料表錯誤: " . $conn->error;
}

$conn->close();

image-20240531181119863

預處理語句

預處理：預留的值使用引數 "?" 標記

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

？的使用：可將問號替換為整型，字串，雙精度浮點型和布林值

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

bind_param() 函式：

該函式繫結了 SQL 的引數，且告訴資料庫引數的值。 "sss" 引數列處理其餘引數的資料型別。s 字元告訴資料庫該引數為字串

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

// 建立連線
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測連線
if ($conn->connect_error) {
    die("連線失敗: " . $conn->connect_error);
}

//預處理及繫結
$stmt=$conn->prepare("insert into users (firstname,lastname,email) values (?,?,?)");
$stmt->bind_param('sss',$firstanme,$lastname,$email);

$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 "新記錄插入成功";

$stmt->close();
$conn->close();
讀取資料
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

// 建立連線
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測連線
if ($conn->connect_error) {
    die("連線失敗: " . $conn->connect_error);
}

//讀取資料
$sql = "SELECT id, firstname, lastname FROM users";//讀取三個欄位，將其結果賦值給$result
$result = $conn->query($sql);

if ($result->num_rows > 0) {//函式 num_rows() 判斷返回的資料
    // 輸出資料
    while($row = $result->fetch_assoc()) {//如果返回的是多條資料，函式 fetch_assoc() 將結合集放入到關聯陣列並迴圈輸出
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 結果";
}
$conn->close();

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

// 建立連線
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測連線
if ($conn->connect_error) {
    die("連線失敗: " . $conn->connect_error);
}

//讀取資料
$sql = "SELECT id, firstname, lastname FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // 輸出資料
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 結果";
}

mysqli_close($conn);
where子句
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

// 建立連線
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測連線
if ($conn->connect_error) {
    die("連線失敗: " . $conn->connect_error);
}

//讀取資料
$sql = "SELECT * from users where firstanem=John";
$result = mysqli_query($conn, $sql);

while($row = mysqli_fetch_array($result))
{
    echo $row['FirstName'] . " " . $row['LastName'];
    echo "<br>";
}
$conn->close();
order by
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

// 建立連線
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測連線
if ($conn->connect_error) {
    die("連線失敗: " . $conn->connect_error);
}

//讀取資料
$sql = "SELECT * from users order by age";
$result = mysqli_query($conn, $sql);

while($row = mysqli_fetch_array($result))
{
    echo $row['FirstName'];
    echo " " . $row['LastName'];
    echo " " . $row['Age'];
    echo "<br>";
}
$conn->close();
update
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

// 建立連線
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測連線
if ($conn->connect_error) {
    die("連線失敗: " . $conn->connect_error);
}

//讀取資料
$sql = "update users set age=18 where firstname=’john‘ and lastname=’doe‘";
$result = mysqli_query($conn, $sql);

$conn->close();
Delete
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "myDB";

// 建立連線
$conn = new mysqli($servername, $username, $password, $dbname);
// 檢測連線
if ($conn->connect_error) {
    die("連線失敗: " . $conn->connect_error);
}

//讀取資料
$sql = "delete from users where lastname='doe'";
$result = mysqli_query($conn, $sql);

$conn->close();

相關文章