Adding functionality to buttons generated via a ph

问题:

I am very new to PHP/HTML/CSS programming and in the code I have attached bellow is my attempt to create the basic functionality of an administration panel for a website.

What I want to do is to print out the table with all of its rows and columns and to have an additional column with controls which would allow me to delete said row from a database. Eventually I would like to make it possible to change the name, password and administrator privileges for each user.

Additionally, I really have no idea how to make each button to hold a value connecting it to its respective row.

Perhaps due to me being an astoundingly inexperienced programmer all of my attempts have either failed or deleted the last row (perhaps as it was the last value under the $email variable name). A friend suggested to use either JavaScript or to move to a different platform (Angular JS was his suggestion) in order to achieve my goals but for now I would really like to keep it simple (if that is really the word for it) with PHP and CSS.

---

Here is an image of what the administration panel looks like:

---

Here is my table generator (or as good as I managed to get it):

<?php
    include "connection.php"; 

    $sql = "SELECT * FROM users;";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) 
    {
        echo "<table class='sqltable'>
                <tr class='sqltable'>
                    <th class='sqltable'>ID</th>
                    <th class='sqltable'>EMAIL</th>
                    <th class='sqltable'>NAME</th>
                    <th class='sqltable'>IS ADMIN</th>
                    <th class='sqltable'>PASSWORD</th>
                    <th class='sqltable'>CONTROLS</th>
                </tr>";

        // output data of each row
        while($row = $result->fetch_assoc()) 
        {
            echo "<tr class='sqltable'>                            
                    <td class='sqlcell'>".$row["ID"]."</td>
                    <td class='sqlcell'>".$row["EMAIL"]."</td>
                    <td class='sqlcell'>".$row["FIRST_NAME"]." ".$row["MID_NAME"]." ".$row["LAST_NAME"]."</td>
                    <td class='sqlcell'>".$row["IS_ADMIN"]."</td> 
                    <td class='sqlcell'>".$row["PASSWORD"]."</td>
                    <td class='sqlcell'>
                        <center>
                            <div style='border: 1px solid lightgray;' method='POST'>
                                <input type='hidden' name='ID' value='".$row['ID']." '/>
                                <input type='button' name='delete' value='DEL ".$row['ID']." '/>
                            </div>
                        </center>
                    </td>
                </tr>";
        } 
        echo "</table>";
    }

    else 
    {
        echo "DATABASE IS EMPTY!";
    }  

    $conn->close(); 

    if (isset($_POST['delete']))
    { //if a delete request received
        $id = $_POST['id']; //primary key of this row

        /////// Connectivity /////////
        $servername = "127.0.0.1";
        $username = "root";
        $password = ""; 
        $db = "myDB";    

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

        //check connection
        if ($conn)
        {
            printf("Connect failed: %s\n", mysqli_connect_error());
            exit();
        }

        //compose sql statement
        $stmt = mysqli_prepare($conn, "DELETE FROM users WHERE ID=?");

        mysqli_stmt_bind_param($stmt,'i',$id); //now add the $id to the statement 'i' stands for integer

        mysqli_stmt_execute($stmt);

        mysqli_stmt_close($stmt);
        mysqli_close($conn); //connection closed
    }
?>

---

This is what I started doing and I am already pretty sure that I have taken the wrong route to do this.

function delete()
            {
                $del = "DELETE FROM '".$table."' WHERE EMAIL='".$email."';";
                $conn->query($del);
            }

回答1:

now when I implemented your code posted in the question I've changed several things to get it work properly, all minor issues, I'll post them here with in comments elaboration.

<?php
    if (isset($_POST['delete'])) //first: test if any delete request, delete and then render the table 
    { //if a delete request received
        $id = $_POST['id']; //primary key of this row, where 'id' index must be case-sensitively equal to the hidden input name 'id'

        /////// Connectivity /////////
        $servername = "localhost";
        $username = "root";
        $password = "root"; 
        $db = "user_delete";    

        // Create connection (procedural style)
        $conn = mysqli_connect($servername, $username, $password, $db);

        //check connection
        if (!$conn) //if NOT connected
        {
            printf("Connect failed: %s\n", mysqli_connect_error()); //print error
            exit(); //exit the program 'in this case you wouldn't see the table either'
        }

        //compose sql statement
        $stmt = mysqli_prepare($conn, "DELETE FROM users WHERE ID=?"); //notice that sql statements are NOT case sensitive

        mysqli_stmt_bind_param($stmt,'i',$id); //now add the $id to the statement 'i' stands for integer

        mysqli_stmt_execute($stmt);

        mysqli_stmt_close($stmt);
        mysqli_close($conn); //connection closed, row deleted
    }

    include "connection.php"; 

    $sql = "SELECT * FROM users;";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) 
    {
        echo "<table class='sqltable'>
                <tr class='sqltable'>
                    <th class='sqltable'>ID</th>
                    <th class='sqltable'>EMAIL</th>
                    <th class='sqltable'>NAME</th>
                    <th class='sqltable'>IS ADMIN</th>
                    <th class='sqltable'>PASSWORD</th>
                    <th class='sqltable'>CONTROLS</th>
                </tr>";

        // output data of each row
        while($row = $result->fetch_assoc()) 
        {
            echo "<tr class='sqltable'>";
            echo   "<td class='sqlcell'>".$row["id"]."</td>";   //php is case-sensitive so you should use $row['ID'] according to your scheme
            echo   "<td class='sqlcell'>".$row["email"]."</td>";//php is case-sensitive so you should use $row['EMAIL'] according to your scheme
            echo   "<td class='sqlcell'>".$row["name"]."</td>";//for simplicity, I made one field, change it according to your scheme
            echo   "<td class='sqlcell'>".$row["is_Admin"]."</td>";//php is case-sensitive so you should use $row['IS_ADMIN'] according to your scheme
            echo   "<td class='sqlcell'>".$row["password"]."</td>";//same as above
            echo    "<td class='sqlcell'>
                        <center>
                            <div style='border: 1px solid lightgray;'>";
                    echo    "<form method='POST'>"; //must be added in a form with method=post
                    echo        "<input type='hidden' name='id' value='".$row['id']." '/>"; //differntiate between input name `id` and mysql field name you have `ID`, input field name is the index you will fetch in line 4: $_POST['id']
                    echo        "<input type='submit' name='delete' value='DEL ".$row['id']." '/>"; //type: submit, not button
                    echo    "</form>
                            </div>
                        </center>
                    </td>
                </tr>";
        } 
        echo "</table>";
    }

    else 
    {
        echo "DATABASE IS EMPTY!";
    }  
//all done
    $conn->close(); 
?>

update: now this is the same code, all in OOP style and reusing connection:

<?php
    include "connection.php"; 
    if (isset($_POST['delete'])) //first: test if any delete request, delete and then render the table 
    { //if a delete request received
        $id = $_POST['id']; //primary key of this row, where 'id' index must be case-sensitivly equal to the hidden input name 'id'

        //check connection
        if (mysqli_connect_errno()) //if connection error existed
        {
            printf("Connect failed: %s\n", mysqli_connect_error()); //print error
            exit(); //exit the program 'in this case you wouldn't see the table either'
        }

        //compose sql statement
        $stmt = $conn->prepare("DELETE FROM users WHERE ID=?"); //notice that sql statements are NOT case sensitive

        $stmt->bind_param('i',$id); //now add the $id to the statement 'i' stands for integer

        $stmt->execute();

        $stmt->close();
    }

    $sql = "SELECT * FROM users;";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) 
    {
        echo "<table class='sqltable'>
                <tr class='sqltable'>
                    <th class='sqltable'>ID</th>
                    <th class='sqltable'>EMAIL</th>
                    <th class='sqltable'>NAME</th>
                    <th class='sqltable'>IS ADMIN</th>
                    <th class='sqltable'>PASSWORD</th>
                    <th class='sqltable'>CONTROLS</th>
                </tr>";

        // output data of each row
        while($row = $result->fetch_assoc()) 
        {
            echo "<tr class='sqltable'>";
            echo   "<td class='sqlcell'>".$row["id"]."</td>";   //php is case-sensitive so you should use $row['ID'] according to your scheme
            echo   "<td class='sqlcell'>".$row["email"]."</td>";//php is case-sensitive so you should use $row['EMAIL'] according to your scheme
            echo   "<td class='sqlcell'>".$row["name"]."</td>";//for simplicity, I made one field, change it according to your scheme
            echo   "<td class='sqlcell'>".$row["is_Admin"]."</td>";//php is case-sensitive so you should use $row['IS_ADMIN'] according to your scheme
            echo   "<td class='sqlcell'>".$row["password"]."</td>";//same as above
            echo    "<td class='sqlcell'>
                        <center>
                            <div style='border: 1px solid lightgray;'>";
                    echo    "<form method='POST'>"; //must be added in a form with method=post
                    echo        "<input type='hidden' name='id' value='".$row['id']." '/>"; //differntiate between input name `id` and mysql field name you have `ID`, input field name is the index you will fetch in line 4: $_POST['id']
                    echo        "<input type='submit' name='delete' value='DEL ".$row['id']." '/>"; //type: submit, not button
                    echo    "</form>
                            </div>
                        </center>
                    </td>
                </tr>";
        } 
        echo "</table>";
    }

    else 
    {
        echo "DATABASE IS EMPTY!";
    }  
//all done
    $conn->close(); 
?>

Hint: in real world, never store passwords as plain text, search and read more about hashing

回答2:

Well, first of all, php scripts are server-side scripts, which means that your button is not going to provoke the delete function, alternatively, it will send a request to the server where deletion take exist.

how to do that? simply you got to render a form (which is html element), just inside the table cell where you want to show delete button, and you define method as post (read more about http request methods), and you can include the id value (or the primary key of that table what so ever).

 <form method="post">
 <input type='submit' name='delete' />
 <input type='hidden' name="id" value="$row['id']" />
 </form>

so this form tells the browser: whenever the user click 'delete' button, submit this form that includes one hidden input holds the id of the element wanted to delete.

now we go for the server side, at the beginning of your file:

<?php 
if (isset($_POST['delete'])){ //if a delete request received
$id = $_POST['id']; //primary key of this row

//establish connection to mysql
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

//check connection
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

//compose sql statement
$stmt = $mysqli->prepare("DELETE FROM users WHERE ID=?");

$stmt->bind_param('i',$id); //now add the $id to the statement 'i' stands for integer

$stmt->execute();

$stmt->close();
$mysqli->close() //connection closed
}
?>

code above written in OOP, or you may write it in Procedural style..

<?php 
if (isset($_POST['delete'])){ //if a delete request received
$id = $_POST['id']; //primary key of this row

//establish connection to mysql
$mysqli = mysqli_connect('localhost', 'my_user', 'my_password', 'world');

//check connection
if (!$mysqli) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

//compose sql statement
$stmt = mysqli_prepare($mysqli, "DELETE FROM users WHERE ID=?");

mysqli_stmt_bind_param($stmt,'i',$id); //now add the $id to the statement 'i' stands for integer

mysqli_stmt_execute($stmt);

mysqli_stmt_close($stmt);
mysqli_close($mysqli) //connection closed
}
?>

1. more about binding parameters
2. HTTP methods