Member Avatar for TheFearful
TheFearful

Hey everyone, I have a question about prepared statements for PHP and mySQL. I have been able to do them for a single row such as ID and such for a particular object, but is it possible to do a SELECT all command as well? I want to be able to print out the entire table in a page, but I don't know what to do exactly. I think the issue I have is with trying to fetch it or put it in a while loop. This is what I have. What you see commented out is what I had before that works but is prone to SQL injection(so I have to replace it sadly).

 $orderBy = "LastName";
        $stmt = mysqli_stmt_init($conn);
        mysqli_stmt_prepare($stmt, "SELECT * FROM engineers ORDER BY ? desc");
        mysqli_stmt_bind_param($stmt, 's', $orderBy);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_bind_result($stmt, $result);
        $result = mysqli_stmt_fetch($stmt);
        mysqli_stmt_close($stmt);
        //The sql command to select all the users and will order
        //them in alphabetical order based off of the last name
        //$sql = "SELECT * FROM engineers ORDER BY LastName";

        //The result will be stored in this variable
        //$result = mysqli_query($conn, $sql);

        //The form creation so that the user can update the user on the next page
        //when they decided to pick a user that they will want to update.
        echo "<form action='updateEngineers.php' id='changeTable' method='POST'>";

        echo "<input type='hidden'  id='currentUser' name='currentUser' value = ". $currentUser . "  />";
        //Creates a table so that the users' information are shown for each row
        echo "<table style='width:100%'>";
        echo "<tr>";
        echo "<td>FirstName</td>";
        echo "<td>LastName</td>";
        echo "<td>Extension</td>";
        echo "<td>Office</td>";
        echo "<td>Mobile</td>";
        echo "<td>Email</td>";
        echo "<td>PersonalPhone</td>";
        echo "<td>TelephoneAgent</td>";
        echo "<td>UserID</td>";
        echo "<td>EmployeeID</td>";
        echo "<td>Department</td>";
        echo "<td>Access Code</td>";
        echo "</tr>";

        //Will go through the database from the SELECT command above
        //and print them out onto the page here.
        while($row = mysqli_fetch_assoc($result))
        {
                //Gives each row a specific id so that you can reference that in the update or delete pages
                echo "<tr id ='". $row['EmployeeID']."' >";

                //Will display the items from the database in the order presented below
                echo "<td class ='firstname'> " . htmlentities($row["FirstName"],ENT_QUOTES).  "</td>";
                echo "<td class ='lastname'> " . htmlentities($row["LastName"],ENT_QUOTES).  "</td>";
 echo "<td class ='extension'> " . htmlentities($row["Extension"],ENT_QUOTES).  "</td>";
                echo "<td class ='telephone'> " . htmlentities($row["Telephone"],ENT_QUOTES).  "</td>";
                echo "<td class ='mobile'> " . htmlentities($row["Mobile"],ENT_QUOTES).  "</td>";
                echo "<td class ='email'> " . htmlentities($row["Email"],ENT_QUOTES).  "</td>";
                echo "<td class ='personalphone'> " . htmlentities($row["PersonalPhone"],ENT_QUOTES).  "</td>";
                echo "<td class ='telephoneagent'> " . htmlentities($row["TelephoneAgent"],ENT_QUOTES).  "</td>";
                echo "<td class ='userid'> " . htmlentities($row["UserID"],ENT_QUOTES).  "</td>";
                echo "<td class ='employeeid'> " . htmlentities($row["EmployeeID"],ENT_QUOTES).  "</td>";
                echo "<td class ='department'> " . htmlentities($row["Department"],ENT_QUOTES).  "</td>";
                echo "<td class ='accesscode'> " . htmlentities($row["AccessCode"],ENT_QUOTES).  "</td>";

                //The buttons that will be used for the update or delete
                echo "<td><button type='submit' name='engineers' value='".$row['UserID']."' class='button'>Update</button></td>";
                echo "<td><button type='button' name='delete' value='".$row['UserID']."' class='delete-button'>Delete</button></td>";
                echo "</tr>";
        }
        //finishes with the table
        echo "</table>";
        //The form ends so the values in the above table will be used
        //when the user wants to update the user. Delete should be in the submitted values
        //if the user hits the delete button but the script down below will fix the problem
        echo "</form>";

        //A new form to be shown at the bottom of the page so that
        //the user can choose to add a new user if they need to add
        //a new user for the sales engineering team
        echo "<form action='addEngineers.php' method='POST'>";
        echo "<input type='hidden'  id='currentUser' name='currentUser' value = ". $currentUser . "  />";
        echo "<button type = 'submit' name = 'addUser' value='addUser' class='add-button'>Add</button>";
        echo "</form>";
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.