Displaying table data

Question

I'm creating a todo list to add tasks, delete tasks and set the priority of each task. However, I keep getting this error:

The main problem seems to be with the while loop on line 57 but I'll put all my code on incase the root of the issue is farther up.

<?php
//Creating variables for connection parameters
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydb";

//Create connection
$connection = mysqli_connect($servername,$username, $password, $database);

//Check connection
if(!$connection){ //if the connection failed
    die("Connection failed:" . mysqli_connect_error());
}
echo "Connected successfully! </br>"; // if it connected succesfully


if(isset($_POST['submit']) ){ //if the submit button is clicked
    $name = $_POST['name']; //Creating variable $name constaining the task entered
    $priority = $_POST['priority']; //Creating variable $priority constaining the priority entered
    $stmt = mysqli_prepare($connection, "INSERT INTO todos (name, priority) VALUES(?, ?)"); //Inserting the 2 values into the todo table
    mysqli_stmt_bind_param($stmt, 'si', $name, $priority); //setting the task and priority as the 2 values mentioned above
    mysqli_stmt_execute($stmt); //executing the above
}elseif(isset($_POST['delete'])){ //if the delete button is clicked
    $id = $_POST['id']; //Creating variable $id constaining the id of the task entered
    $stmt = mysqli_prepare($connection, "DELETE FROM todos WHERE id = ?"); //deleting the task containing an ____ id
    mysqli_stmt_bind_param($stmt, 'i', $id); // setting the above ___ id to the id of the task wanting to be deleted
    mysqli_stmt_execute($stmt); //executing the above
}
?>

<!DOCTYPE HTML> <!--Starting html part-->
<html lang="en"> <!--Setting page language to english-->
<head>
    <title>Todo List</title> <!--title of webpage-->
    <!--Importing the required fonts for the page-->
    <link rel="stylesheet" href="//fonts.googleapis.com/css?family=Roboto:300,300italic,700,700italic">
    <link rel="stylesheet" href="//cdn.rawgit.com/necolas/normalize.css/master/normalize.css">
    <link rel="stylesheet" href="//cdn.rawgit.com/milligram/milligram/master/dist/milligram.min.css">
</head>

<body class="container">
    <h1>Todo List</h1> <!--Title at top of page-->
    <form method="POST" action=""> <!--Creating a form thats data is going to be sent by POST method-->
        <input type="text" name="name" value=""> <!--Task bar-->
        <label for="priority">Task Priority (between 1 and 3):</label> <!--Instructions for next input box-->
        <input type="number" name="priority" min="1" max="3" style="width: 7em"> <!--Priority box from 1-3-->
        <input type="submit" name="submit" value="Add"><!--Add button-->
    </form>
    <h2>Current Todos</h2> <!--Naming table below-->
    <table class="table table-striped">
        <thead><th>Priority</th><th>Task</th><th></th></thead> <!--Naming columns. third column header is blank because it's the delete button column-->
        <tbody>
<?php
    $stmt = mysqli_prepare($connection, "SELECT * FROM todos ORDER BY id DESC"); //Ordering table from 1st task entered to last     

    while ($row = mysqli_fetch_array($stmt)){ //Loops through all the tasks in the database
?>
            <tr>
                <td><?php echo $row['priority'] ?></td><!--printing each priority to the row -->
                <td><?php echo $row['name'] ?></td> <!--printing each task to the row -->
                <td>
                    <form method="POST"> <!--Creating a form thats data is going to be sent by POST method-->
                        <button type="submit" name="delete">Delete</button> <!--Makes the delete button-->
                        <input type="button" name="id" value="<?php echo $row ['id'] ?>"> 
                        <input type="hidden" name="delete" value="true">
                    </form>
                    
                </td>
            </tr> 
<?php 
    } //Closes while loop
?> 

        </tbody>
    </table>
</body>
</html>

Seriously consider whether you really want to hard delete data from your database in this manner — Strawberry, Jan 09 '21 at 08:08
Does this answer your question? [mysqli\_fetch\_assoc() expects parameter / Call to a member function bind\_param() errors. How to get the actual mysql error and fix it?](https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param) — Dharman, Jan 09 '21 at 14:00
If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo — Dharman, Jan 09 '21 at 14:00
You can find your answer here https://stackoverflow.com/a/65225839/1839439 — Dharman, Jan 09 '21 at 14:02
@Dharman It's for a class so unfortunately I have to use mysqli. Also I tried using foreach() to loop through it just made more errors. — MacKenzie Richards, Jan 09 '21 at 19:52
It's a shame that your teacher is demanding from you to learn mysqli. That's a terrible idea but if you must use mysqli then I just posted a solution below. I also removed the manual connection checking; never do that! I added a line to properly enable error reporting. — Dharman, Jan 09 '21 at 20:00

score 0 · Accepted Answer · answered Jan 09 '21 at 19:59

Your mistake is that you never executed the SELECT prepared statement and you are trying to fetch directly from it. Unfortunately, mysqli is not as simple as PDO. It is not designed for beginners and you have to do more work to get the result.

It's a very good idea to separate PHP code and HTML as much as possible. Don't create a prepared statement within HTML code. Take a look at the prepared statement I added at the end of PHP code which shows how to fetch the result from a prepared statement.

PHP

<?php
//Creating variables for connection parameters
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydb";

// Enable error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
//Create connection
$connection = mysqli_connect($servername, $username, $password, $database);

if (isset($_POST['submit'])) { //if the submit button is clicked
    $name = $_POST['name']; //Creating variable $name constaining the task entered
    $priority = $_POST['priority']; //Creating variable $priority constaining the priority entered
    $stmt = mysqli_prepare($connection, "INSERT INTO todos (name, priority) VALUES(?, ?)"); //Inserting the 2 values into the todo table
    mysqli_stmt_bind_param($stmt, 'si', $name, $priority); //setting the task and priority as the 2 values mentioned above
    mysqli_stmt_execute($stmt); //executing the above
} elseif (isset($_POST['delete'])) { //if the delete button is clicked
    $id = $_POST['id']; //Creating variable $id constaining the id of the task entered
    $stmt = mysqli_prepare($connection, "DELETE FROM todos WHERE id = ?"); //deleting the task containing an ____ id
    mysqli_stmt_bind_param($stmt, 'i', $id); // setting the above ___ id to the id of the task wanting to be deleted
    mysqli_stmt_execute($stmt); //executing the above
}

$stmt = mysqli_prepare($connection, "SELECT * FROM todos ORDER BY id DESC"); //Ordering table from 1st task entered to last
mysqli_stmt_execute($stmt); //executing the above
$result = mysqli_stmt_get_result($stmt);
// Optionally fetch it as an array
// $data = mysqli_fetch_all($result);
?>

HTML:

<!DOCTYPE HTML>
<!--Starting html part-->
<html lang="en">
<!--Setting page language to english-->

<head>
    <title>Todo List</title>
    <!--title of webpage-->
    <!--Importing the required fonts for the page-->
    <link rel="stylesheet" href="//fonts.googleapis.com/css?family=Roboto:300,300italic,700,700italic">
    <link rel="stylesheet" href="//cdn.rawgit.com/necolas/normalize.css/master/normalize.css">
    <link rel="stylesheet" href="//cdn.rawgit.com/milligram/milligram/master/dist/milligram.min.css">
</head>

<body class="container">
    <h1>Todo List</h1>
    <!--Title at top of page-->
    <form method="POST" action="">
        <!--Creating a form thats data is going to be sent by POST method-->
        <input type="text" name="name" value="">
        <!--Task bar-->
        <label for="priority">Task Priority (between 1 and 3):</label>
        <!--Instructions for next input box-->
        <input type="number" name="priority" min="1" max="3" style="width: 7em">
        <!--Priority box from 1-3-->
        <input type="submit" name="submit" value="Add">
        <!--Add button-->
    </form>
    <h2>Current Todos</h2>
    <!--Naming table below-->
    <table class="table table-striped">
        <thead>
            <th>Priority</th>
            <th>Task</th>
            <th></th>
        </thead>
        <!--Naming columns. third column header is blank because it's the delete button column-->
        <tbody>
            <?php
            foreach ($result as $row) : //Loops through all the tasks in the database
            ?>
                <tr>
                    <td><?php echo $row['priority'] ?></td>
                    <!--printing each priority to the row -->
                    <td><?php echo $row['name'] ?></td>
                    <!--printing each task to the row -->
                    <td>
                        <form method="POST">
                            <!--Creating a form thats data is going to be sent by POST method-->
                            <button type="submit" name="delete">Delete</button>
                            <!--Makes the delete button-->
                            <input type="button" name="id" value="<?php echo $row['id'] ?>">
                            <input type="hidden" name="delete" value="true">
                        </form>

                    </td>
                </tr>
            <?php
            endforeach;
            ?>
        </tbody>
    </table>
</body>
</html>

Displaying table data

1 Answers1