Inserting and selecting data with PHPزیادکردن و هەڵبژاردنی زانیاری بە PHP

<?php
// dbcon.php: the connection file used on this page's examples
try {
    $con = mysqli_connect("localhost", "root", "", "mydb");
    mysqli_set_charset($con, "utf8mb4");
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());
    exit("Sorry, the database is not available right now.");
}

<?php
include "dbcon.php";   // the connection file: it creates $con

$sql = "INSERT INTO `stage_4` (`name`, `age`, `salary`)
        VALUES ('Dilan', 24, 350)";

try {
    $con->query($sql);   // the same as: mysqli_query($con, $sql);
    echo "New record created successfully. Its id is " . $con->insert_id;
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());
    echo "Error: the record was not saved.";
}

<?php
// Why we never paste form values into SQL (run with SQLite)
$pdo = new PDO("sqlite::memory:");
$pdo->exec("CREATE TABLE log (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, password TEXT)");
$pdo->exec("INSERT INTO log (name, password) VALUES ('Karwan', 'k-secret-1')");

function unsafe_insert(PDO $pdo, string $name, string $pass): void {
    $sql = "INSERT INTO log (name, password) VALUES ('$name', '$pass')";
    echo "SQL: $sql\n";
    try { $pdo->exec($sql); echo "Result: inserted\n\n"; }
    catch (PDOException $e) { echo "Result: ERROR, the query is broken\n\n"; }
}

unsafe_insert($pdo, "Hana O'Brien", "1234");                       // an honest name with '
unsafe_insert($pdo, "x', (SELECT password FROM log WHERE name = 'Karwan')) -- ", "anything");

// The safe way: a prepared statement
$stmt = $pdo->prepare("INSERT INTO log (name, password) VALUES (?, ?)");
$stmt->execute(["Hana O'Brien", "1234"]);

foreach ($pdo->query("SELECT id, name, password FROM log") as $r) {
    echo $r["id"] . " | " . $r["name"] . " | " . $r["password"] . "\n";
}

<?php
include "dbcon.php";

// Imagine these three values came from a form
$name   = "Hana O'Brien";
$age    = 23;
$salary = 450;

// 1. Prepare: the SQL has ? where the values will go
$stmt = mysqli_prepare($con, "INSERT INTO stage_4 (name, age, salary) VALUES (?, ?, ?)");

// 2. Bind: "sii" = string, integer, integer (one letter per ?)
mysqli_stmt_bind_param($stmt, "sii", $name, $age, $salary);

// 3. Execute: the values travel separately from the SQL
mysqli_stmt_execute($stmt);

echo "Saved " . mysqli_stmt_affected_rows($stmt) . " row. New id: " . mysqli_insert_id($con);
mysqli_stmt_close($stmt);

<?php
// The same insert with PDO (shown here with SQLite so it can run anywhere)
$pdo = new PDO("sqlite::memory:");
$pdo->exec("CREATE TABLE stage_4 (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(100) NOT NULL, age INT NOT NULL, salary INT NOT NULL)");

$name   = "Hana O'Brien";
$age    = 23;
$salary = 450;

$stmt = $pdo->prepare("INSERT INTO stage_4 (name, age, salary) VALUES (?, ?, ?)");
$stmt->execute([$name, $age, $salary]);
echo "Saved " . $stmt->rowCount() . " row. New id: " . $pdo->lastInsertId() . "\n";

// Named markers such as :name work too
$stmt = $pdo->prepare("INSERT INTO stage_4 (name, age, salary) VALUES (:name, :age, :salary)");
$stmt->execute(["name" => "Soran", "age" => 30, "salary" => 600]);

foreach ($pdo->query("SELECT * FROM stage_4") as $row) {
    echo $row["id"] . " | " . $row["name"] . " | " . $row["age"] . " | " . $row["salary"] . "\n";
}

<?php
include "dbcon.php";
$message = "";

if (isset($_POST["save"])) {
    $name     = trim($_POST["name"]);
    $password = $_POST["password"];

    if ($name === "" || $password === "") {
        $message = "Please fill in both fields.";
    } else {
        $hash = password_hash($password, PASSWORD_DEFAULT);   // store a hash, never the password

        $stmt = mysqli_prepare($con, "INSERT INTO log (name, password) VALUES (?, ?)");
        mysqli_stmt_bind_param($stmt, "ss", $name, $hash);
        mysqli_stmt_execute($stmt);          // throws an exception if it fails
        $message = "Insert is successful.";
    }
}
?>
<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>">
    <input type="text" name="name" placeholder="your name"><br>
    <input type="password" name="password" placeholder="your password"><br>
    <input type="submit" name="save" value="go">
</form>
<p><?php echo htmlspecialchars($message); ?></p>

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>">
    Username: <input type="text" name="uname" size="30"><br><br>
    Email: <input type="email" name="email" size="30"><br><br>
    Password: <input type="password" name="pass1" size="30"><br><br>
    Confirm password: <input type="password" name="pass2" size="30"><br><br>
    <input type="submit" value="Create Account" name="submit"><br>
</form>

<?php
// connection.php
try {
    $connect = mysqli_connect("localhost", "root", "", "php_mysql");
    mysqli_set_charset($connect, "utf8mb4");
} catch (mysqli_sql_exception $e) {
    error_log($e->getMessage());
    exit("Could not connect to the database.");
}

// getting values from the form inputs
$name     = $_POST["uname"];
$email    = $_POST["email"];
$password = password_hash($_POST["pass1"], PASSWORD_DEFAULT);  // hash the password

require "./connection.php";   // connect to the database

try {
    $stmt = mysqli_prepare($connect, "INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
    mysqli_stmt_bind_param($stmt, "sss", $name, $email, $password);
    mysqli_stmt_execute($stmt);
    echo "Account created successfully, you can log in now";
} catch (mysqli_sql_exception $e) {
    if ($e->getCode() == 1062) {   // 1062 = this value already exists in a UNIQUE column
        echo "This email address already has an account";
    } else {
        error_log($e->getMessage());
        echo "An error occurred, please try again";
    }
}
// close the connection
mysqli_close($connect);

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Create an account</title>
</head>
<body>
<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>">
    Username: <input type="text" name="uname" size="30"><br><br>
    Email: <input type="email" name="email" size="30"><br><br>
    Password: <input type="password" name="pass1" size="30"><br><br>
    Confirm password: <input type="password" name="pass2" size="30"><br><br>
    <input type="submit" value="Create Account" name="submit"><br>
<?php
if (isset($_POST["submit"])) {
    if (empty($_POST["uname"])) {
        echo "Username is required";
    } elseif (empty($_POST["email"])) {
        echo "Please enter your email address";
    } elseif (!filter_var($_POST["email"], FILTER_VALIDATE_EMAIL)) {
        echo "Please enter a valid email address";
    } elseif (empty($_POST["pass1"])) {
        echo "Please enter your password";
    } elseif (empty($_POST["pass2"])) {
        echo "Please confirm your password";
    } elseif ($_POST["pass1"] != $_POST["pass2"]) {
        echo "The passwords did not match, please try again";
    } else {
        // getting values from the form inputs
        $name     = $_POST["uname"];
        $email    = $_POST["email"];
        $password = password_hash($_POST["pass1"], PASSWORD_DEFAULT);  // hash the password

        require "./connection.php";   // connect to the database

        try {
            $stmt = mysqli_prepare($connect, "INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
            mysqli_stmt_bind_param($stmt, "sss", $name, $email, $password);
            mysqli_stmt_execute($stmt);
            echo "Account created successfully, you can log in now";
        } catch (mysqli_sql_exception $e) {
            if ($e->getCode() == 1062) {   // 1062 = this value already exists in a UNIQUE column
                echo "This email address already has an account";
            } else {
                error_log($e->getMessage());
                echo "An error occurred, please try again";
            }
        }
        // close the connection
        mysqli_close($connect);
    }
}
?>
</form>
</body>
</html>

$connect = mysqli_connect('localhost','root','','ABCD');
if(!$connect){
    die ("failed your database".mysqli_error($connect));
}
$emailError = null;
$PassError = null;
$RpassError = null;

if (isset($_POST['subbtn'])){
    if (empty($_POST['email'])){
        $emailError = "Require your email";
    }
    elseif (!strstr($_POST['email'],"@") && (!strstr($_POST['email'],"."))){
        $emailError = " contain @ and .";
    }
    if (empty($_POST['pass'])){
        $PassError = "require your password";
    }
    if ($_POST['rpass'] != $_POST['pass']){
        $RpassError = "Mismatch your password";
    }
}
if(!empty($_POST['email']) && !empty($_POST['pass'])){
    $email = $_POST['email'];
    $password = md5($_POST['pass']);
    $sql_command = "insert into users (email,password) values ('$email' ,'$password') ";
    if (mysqli_query($connect,$sql_command)){
        echo "done";
    }else{
        echo"Failed".mysqli_error($connect);
    }
}

<?php
require "connection.php";   // creates $connect (database php_mysql)
$result = mysqli_query($connect, "SELECT id, name, email FROM users ORDER BY id");
?>
<table border="1">
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Email</th>
    </tr>
<?php
while ($row = mysqli_fetch_assoc($result)) {
    echo "    <tr>\n";
    echo "        <td>" . htmlspecialchars($row["id"]) . "</td>\n";
    echo "        <td>" . htmlspecialchars($row["name"]) . "</td>\n";
    echo "        <td>" . htmlspecialchars($row["email"]) . "</td>\n";
    echo "    </tr>\n";
}
?>
</table>
<p><?php echo mysqli_num_rows($result); ?> users</p>

<?php
$conn = mysqli_connect("localhost", "username", "password", "database_name");

$result = mysqli_query($conn, "SELECT * FROM users");

$num_rows = mysqli_num_rows($result);   // procedural style
// $num_rows = $result->num_rows;       // object style: the same number

echo "There are " . $num_rows . " rows in the users table.";

<?php
$db = new PDO("sqlite::memory:");
$db->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, city TEXT)");
$db->exec("INSERT INTO users (name, city) VALUES ('Dilan','Erbil'), ('Rezan','Duhok'), ('Hana','Erbil'), ('Soran','Erbil')");

// 1. Fetch the rows, then count them in PHP (like mysqli_num_rows)
$rows = $db->query("SELECT * FROM users WHERE city = 'Erbil'")->fetchAll();
echo "Rows fetched: " . count($rows) . "\n";

// 2. Let the database count: only one small number travels back
$total = $db->query("SELECT COUNT(*) FROM users")->fetchColumn();
echo "COUNT(*): " . $total . "\n";

<?php
$con = mysqli_connect("localhost", "root", "", "MyStudy");   // throws if it fails (PHP 8.1+)
mysqli_set_charset($con, "utf8mb4");

$alreadyExists = false;
$username = "";
$message = "";

if (isset($_POST["SubBtn"])) {
    $username = trim($_POST["userInput"]);

    if ($username === "") {
        $message = "Username cannot be empty";
    } else {
        // 1. Does this username already exist? (prepared SELECT)
        $stmt = mysqli_prepare($con, "SELECT id FROM users WHERE username = ?");
        mysqli_stmt_bind_param($stmt, "s", $username);
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        $alreadyExists = mysqli_num_rows($result) > 0;
    }

    if ($username !== "" && !$alreadyExists) {
        // 2. New user: check the uploaded image
        $file_name = basename($_FILES["FileName"]["name"]);   // no folder names from the visitor
        $file_tmp = $_FILES["FileName"]["tmp_name"];
        $target_file = "uploads/" . $file_name;
        $parts = explode(".", $file_name);
        $extension = strtolower(end($parts));
        $allowed = ["png", "jpg"];

        if ($file_name === "") {
            $message = "Image input cannot be empty";
        } elseif (file_exists($target_file)) {
            $message = "Image file already exists.";
        } elseif (!in_array($extension, $allowed)) {
            $message = "Only PNG and JPG images are allowed.";
        } else {
            // 3. Save the user (prepared INSERT, hashed password), then keep the file
            $hash = password_hash($_POST["passInput"], PASSWORD_DEFAULT);
            $stmt = mysqli_prepare($con, "INSERT INTO users (username, password, filename) VALUES (?, ?, ?)");
            mysqli_stmt_bind_param($stmt, "sss", $username, $hash, $file_name);
            mysqli_stmt_execute($stmt);
            move_uploaded_file($file_tmp, $target_file);
            $message = "Success: the user was added.";
        }
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Users</title>
</head>
<body>
<p><?php echo htmlspecialchars($message); ?></p>

<form method="post" enctype="multipart/form-data">
<table border="1">
    <tr>
        <th>ID</th>
        <th>Username</th>
        <th>Image</th>
    </tr>
<?php
$selectQuery = mysqli_query($con, "SELECT id, username, filename FROM users ORDER BY id");
while ($row = mysqli_fetch_assoc($selectQuery)) {
?>
    <tr>
        <td><?php echo $row["id"]; ?></td>
        <td>
            <?php echo htmlspecialchars($row["username"]);
            if ($alreadyExists && $row["username"] === $username) {
                echo ' <span style="color:red">already exists</span>';
            } ?>
        </td>
        <td><img width="50" height="60" alt="" src="uploads/<?php echo htmlspecialchars($row["filename"]); ?>"></td>
    </tr>
<?php
}
?>
    <tr>
        <td></td>
        <td>
            <input type="text" name="userInput" placeholder="username">
            <input type="password" name="passInput" placeholder="password">
        </td>
        <td>
            <input type="file" name="FileName">
            <input type="submit" name="SubBtn" value="Insert">
        </td>
    </tr>
</table>
</form>
</body>
</html>

<?php
require "dbcon.php";

$perPage = 10;                                            // rows on one page
$page = isset($_GET["page"]) ? (int) $_GET["page"] : 1;   // ?page=3 in the URL
if ($page < 1) {
    $page = 1;
}
$offset = ($page - 1) * $perPage;                         // page 3 -> skip 20 rows

$stmt = mysqli_prepare($con, "SELECT id, product FROM Orders ORDER BY id LIMIT ? OFFSET ?");
mysqli_stmt_bind_param($stmt, "ii", $perPage, $offset);   // both are integers
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row["id"] . ": " . htmlspecialchars($row["product"]) . "<br>\n";
}
echo '<a href="?page=' . ($page + 1) . '">Next page</a>';

<?php
// The same page with PDO (run here with SQLite and ?page=3)
$_GET["page"] = "3";
$pdo = new PDO("sqlite::memory:");
$pdo->exec("CREATE TABLE Orders (id INTEGER PRIMARY KEY, product TEXT)");
for ($i = 1; $i <= 50; $i++) { $pdo->exec("INSERT INTO Orders VALUES ($i, 'Item $i')"); }

$perPage = 10;
$page = isset($_GET["page"]) ? (int) $_GET["page"] : 1;
if ($page < 1) {
    $page = 1;
}
$offset = ($page - 1) * $perPage;

$stmt = $pdo->prepare("SELECT id, product FROM Orders ORDER BY id LIMIT :lim OFFSET :off");
$stmt->bindValue(":lim", $perPage, PDO::PARAM_INT);   // send as numbers, not text
$stmt->bindValue(":off", $offset, PDO::PARAM_INT);
$stmt->execute();

foreach ($stmt as $row) {
    echo $row["id"] . ": " . htmlspecialchars($row["product"]) . "<br>\n";
}
echo '<a href="?page=' . ($page + 1) . '">Next page</a>';