PHP - จัดการ MySQL โดยใช้ mysqli prepare (select * from user)

จะมีการ bind param โดยใช้ค่าดังนี้

• i   corresponding variable has type integer 
• d  corresponding variable has type double 
• s   corresponding variable has type string 
• b  corresponding variable is a blob and will be sent in packets 

สำหรับ insert id ให้ใช้ $statement->insert_id ได้

select *

            $sql = "select * from user";
            $stmt = $conn->prepare($sql);
            if ($stmt->execute()) {
                $rows = array();
                $data = array();

                $fileds = $stmt->result_metadata()->fetch_fields();

                echo '<table><tr>';

                foreach ($fileds as $f) {
                    echo '<th>' . $f->name . '</th>';

                    $rows[] = &$data[$f->name]; // pass by reference
                }

                echo '</tr>';

                call_user_func_array(array($stmt, 'bind_result'), $rows);

                while ($stmt->fetch()) {
                    echo '<tr>';

                    foreach ($rows as $r)
                        echo '<td>' . $r . '</td>';
                    //หรือใช้
                    foreach ($data as $d)
                        echo '<td>' . $d . '</td>';
                    //หรือใช้
                    foreach ($fileds as $f)
                        echo '<td>' . $data[$f->name] . '</td>';

                    echo '</tr>';
                }
                echo '</table>';

                $stmt->free_result();
                $stmt->close();
            } else {
                echo $stmt->error;
            }

PHP - จัดการ MySQL โดยใช้ mysqli prepare (select iduser, name, lastname from user)

จะมีการ bind param โดยใช้ค่าดังนี้

• i   corresponding variable has type integer 
• d  corresponding variable has type double 
• s   corresponding variable has type string 
• b  corresponding variable is a blob and will be sent in packets 

สำหรับ insert id ให้ใช้ $statement->insert_id ได้

select 
            $sql = "select iduser,name,lastname from user";
            $stmt = $conn->prepare($sql);

            if ($stmt->execute()) {
                $stmt->bind_result($id, $name, $lname);

                $fileds = $stmt->result_metadata()->fetch_fields();

                foreach ($fileds as $f){
                    echo "$f->name :";
                }
                echo "<br>";

                while ($stmt->fetch()) {
                    echo "$id : $name : $lname <br>";
                }

                $stmt->free_result();
                $stmt->close();

            } else {
                echo $stmt->error;
            }

PHP - จัดการ MySQL โดยใช้ mysqli prepare (insert, update, delete)

จะมีการ bind param โดยใช้ค่าดังนี้

• i   corresponding variable has type integer 
• d  corresponding variable has type double 
• s   corresponding variable has type string 
• b  corresponding variable is a blob and will be sent in packets 

สำหรับ insert id ให้ใช้ $statement->insert_id ได้

insert, update, delete

            $sql = "insert into user(id,name,lastname) values (?,?,?)"; // param ให้แทนด้วย ?
            $stmt = $conn->prepare($sql);
            $stmt->bind_param("iss", $id, $name, $lastname);// bind_param ต้องใส่ชนิด param ลงไปด้วย
            $id=1;           
            $name = "abc";
            $lastname = "cde";
            if ($stmt->execute()) {
                echo $stmt->insert_id;
                $stmt->close();
            } else {
                echo $stmt->error;
            }

PHP - ดึงข้อมูลจาก database โดยใช้ query ของ mysqli

สำหรับคำสั่ง select ของ mysql นั้น query ของ mysqli จะ return เป็น mysqli_result

mysqli_result::$current_field — Get current field offset of a result pointer
mysqli_result::data_seek — Adjusts the result pointer to an arbitrary row in the result
mysqli_result::fetch_all — Fetches all result rows as an associative array(MYSQLI_ASSOC หรือ 1), a numeric array(MYSQLI_NUM หรือ 2), or both(MYSQLI_BOTH หรือ 3)
mysqli_result::fetch_array — Fetch a result row as an associative, a numeric array, or both
mysqli_result::fetch_assoc — Fetch a result row as an associative array
mysqli_result::fetch_field_direct — Fetch meta-data for a single field
mysqli_result::fetch_field — Returns the next field in the result set
mysqli_result::fetch_fields — Returns an array of objects representing the fields in a result set
mysqli_result::fetch_object — Returns the current row of a result set as an object
mysqli_result::fetch_row — Get a result row as an enumerated array
mysqli_result::$field_count — Get the number of fields in a result
mysqli_result::field_seek — Set result pointer to a specified field offset
mysqli_result::free — Frees the memory associated with a result
mysqli_result::$lengths — Returns the lengths of the columns of the current row in the result set
mysqli_result::$num_rows — Gets the number of rows in a result

fetch_fields

        $fields = $results->fetch_fields();
        foreach ($fields as $f) {
         echo "$f->name :";  
        }
        $results->free();


fetch_assoc

        $fields = $results->fetch_fields();
        echo '<table><tr>';
        foreach ($fields as $f) {
            $name = $f->name;
            echo '<th>' . $name . '</th>';
        }
        echo '</tr>';
        while ($vals = $results->fetch_assoc()) {
            echo "<tr>";
            foreach ($fields as $f) {
                echo '<td>' . $vals[$f->name] . '</td>';
            }
            echo '</tr>';
        }
        echo '</table>';
        $results->free();

fetch_array
       fetch_array ([ int $resulttype = MYSQLI_BOTH ] )

• MYSQLI_ASSOC ออกมาเป็น associative array
• MYSQLI_NUM ออกมาเป็น numeric array
• MYSQLI_BOTH ออกมาทั้ง associative และ numeric แต่ต้องระวัง length จะเพิ่มมาอีกเท่านึงเพราะออกมาทั้ง 2 แบบ ให้ระวังเวลา ใช้ for แบบกำหนด i<length

        $fields = $results->fetch_fields();
        echo '<table><tr>';
        foreach ($fields as $f) {
            $name = $f->name;
            echo '<th>' . $name . '</th>';
        }
        echo '</tr>';
        while ($vals = $results->fetch_array(MYSQLI_ASSOC)) {
            echo "<tr>";
            foreach ($fields as $f) {
                echo '<td>' . $vals[$f->name] . '</td>';
            }
            echo '</tr>';
        }
        echo '</table>';
        $results->free();

หรือ

        $fields = $results->fetch_fields();
        echo '<table><tr>';
        foreach ($fields as $f) {
            $name = $f->name;
            echo '<th>' . $name . '</th>';
        }
        echo '</tr>';
        while ($vals = $results->fetch_array(MYSQLI_NUM)) {
            echo "<tr>";
            for($i=0;$i<count($vals);$i++){
                echo '<td>' . $vals[$i] . '</td>';
            }
            echo '</tr>';
        }
        echo '</table>';
        $results->free();

fetch_all
       fetch_all ([ int $resulttype = MYSQLI_NUM ] )

• MYSQLI_ASSOC ออกมาเป็น associative array
• MYSQLI_NUM ออกมาเป็น numeric array
• MYSQLI_BOTH ออกมาทั้ง associative และ numeric แต่ต้องระวัง length จะเพิ่มมาอีกเท่านึงเพราะออกมาทั้ง 2 แบบ ให้ระวังเวลา ใช้ for แบบกำหนด i<length

        $fields = $results->fetch_fields();
        $rows = $results->fetch_all();
        echo '<table><tr>';
        foreach ($fields as $f) {
            $name = $f->name;
            echo '<th>' . $name . '</th>';
        }
        echo '</tr>';
        for ($i = 0; $i < count($rows); $i++) {
            echo "<tr>";
            for ($j = 0; $j < count($rows[$i]); $j++) {
                echo '<td>' . $rows[$i][$j] . '</td>';
            }
            echo '</tr>';
        }
        echo '</table>';
        $results->free();

PHP - Create (Databse, Table), Insert, Get last id, Update, Delete โดยใช้ query ของ mysqli

ฟังก์ชัน query ของ mysqli ถ้าใช้กับ Create, Insert, Update, Delete แล้วสำเร็จจะ retrun true
และถ้าจะเอา id ของข้อมูลที่ใส่เข้าไปใหม่ของ table ที่ AUTO_INCREMENT PRIMARY KEY
ให้ใช้  $last_id = $conn->insert_id;

Create Database

$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    echo "Database created successfully";
} else {
    echo "Error creating database: " . $conn->error;
}

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
)";

if ($conn->query($sql) === TRUE) {
    echo "Table MyGuests created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

Insert

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    $last_id = $conn->insert_id;
    echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Insert Multiple

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', 'julie@example.com')";

if ($conn->multi_query($sql) === TRUE) {
    echo "New records created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Update

$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

Delete

$sql = "DELETE FROM MyGuests WHERE id=3";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}

PHP - Database(MySQL) Connect

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

//Select database
$conn->select_db($dbname);

$conn->close();

echo "Connected successfully";

หรือ

$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);
}

$conn->close();

echo "Connected successfully";

PHP - Multidimensional arrays

Multidimensional arrays

$cars = array
  (
  array("Volvo",22,18),
  array("BMW",15,13),
  array("Saab",5,2),
  array("Land Rover",17,15)
  );

echo $cars[0][0].": In stock: ".$cars[0][1].", sold: ".$cars[0][2].".<br>";
echo $cars[1][0].": In stock: ".$cars[1][1].", sold: ".$cars[1][2].".<br>";
echo $cars[2][0].": In stock: ".$cars[2][1].", sold: ".$cars[2][2].".<br>";
echo $cars[3][0].": In stock: ".$cars[3][1].", sold: ".$cars[3][2].".<br>";

แสดงผล

Volvo: In stock: 22, sold: 18.
BMW: In stock: 15, sold: 13.
Saab: In stock: 5, sold: 2.
Land Rover: In stock: 17, sold: 15.