-2

I have a simple form that is using a dropdown list to select a team member by position from a phpmyadmin db and using php in an index.php file.

This returns the rows perfectly and works great, however, I would like to also have the option in the same form to select all records from that table regardless

Here is the html form

<form id="main_select" action="view_members.php" method="POST">
    <select name='main_select' required>
        <option value="" disabled selected>Select staff position</option>
        <option value="all">View All Members</option>
        <option value="Professor">Professor</option>
        <option value="Senior Lecturer">Senior Lecturer</option>
        <option value="Reader">Reader</option>
        <option value="Lecturer">Lecturer</option>
    </select>
    <input type="submit" value="View Selected Staff Members">
</form>

and here is the view_members.php that works perfectly when say a professor option is chosen

 <?php
if (isset($_POST['main_select'])) {
  $position = $_POST['main_select'];
  $statement = "SELECT * FROM staff_members WHERE position = '$position'";
  $result = mysqli_query($conn, $statement);
}

?>

<?php
echo '<table align="center" border="0" cellspacing="35" cellpadding="2" width="100%">';
echo "<thead><tr><th>ID</th><th>Name</th><th>Email</th><th>Position</th><th>Update</th> 
<th>Action</th></tr></thead>";
while ($row = mysqli_fetch_assoc($result)) {
  echo "<tr>";
  echo "<td>{$row['id']}</td>";
  echo "<td>{$row['name']}</td>";
  echo "<td>{$row['email']}</td>";
  echo "<td>{$row['position']}</td>";
  echo "<td><a href='edit_member.php?id={$row['id']}'>Edit</a></td>";
  echo "<td><a href='delete_member.php?id={$row['id']}'>Delete</a></td>";
  echo "</tr>";
}
echo "</table>";
echo '<p><a href="index.php">Back</a></p>';
?>

I then tried to add an else statement to look for "all" in the form and simply select all records but that returns nothing yet if I choose professor again it works ok? is there a way I can do this?

Here is the if else code I tried with

  <?php
if (isset($_POST['main_select'])) {
  $position = $_POST['main_select'];
  $statement = "SELECT * FROM staff_members WHERE position = '$position'";
  $result = mysqli_query($conn, $statement);
} else {
if (isset($_POST['main_select' == 'all'])) {
  $statement = "SELECT * FROM staff_members";
  $result = mysqli_query($conn, $statement);
}

}

any help would be greatly appreciated.

Thanks

David.

ADyson
  • 57,178
  • 14
  • 51
  • 63
d212digital
  • 385
  • 3
  • 8
  • `isset($_POST['main_select' == 'all'])` - first of all, that is not how you use isset (it checks if the variable exists, not what it contains), and second, it will of course never go into that else branch, when `if (isset($_POST['main_select']))` was already true. (Which it is, as soon as you submit that form with _any_ of the options selected.) – CBroe Nov 24 '22 at 14:07
  • I knew it wasn't right but I'm knew to php so just thought I would try it. So how do I get the all value then? or rather select all records from the staff_members table if the option is selected - which is not in the db ? – d212digital Nov 24 '22 at 14:25
  • After you check whether the value was set, you simply check `if($_POST['main_select'] == 'all')`, and if that is the case you make your query without WHERE clause, and in the `else` branch you make the query _with_ the WHERE clause. – CBroe Nov 24 '22 at 14:27
  • I've done it like this – d212digital Nov 24 '22 at 14:33
  • 1
    No, I told you in the first comment already, that `isset($_POST['main_select' == 'all'])` is anything but. – CBroe Nov 24 '22 at 14:41
  • Well it's vulnerable to SQL injection and related issue, so in that sense no it's very incorrect. But in terms of your application logic... `isset($_POST['main_select' == 'all']` is still wrong. First check the parameter is set, then check what value it's set to - these are two different `if`s. – ADyson Nov 24 '22 at 14:41
  • In general though, if you want to find out if some code is correct, you should prepare some test cases and then test it. – ADyson Nov 24 '22 at 14:42
  • OK thanks, but forgetting what I have written for selecting all then, how do I create the logic to either select all or select the chosen option? this is also just testing at this stage and it's not going to be a live application in anyway – d212digital Nov 24 '22 at 14:45
  • 1
    Follow the process I just outlined: `if (isset($_POST['main_select') { if ($_POST['main_select'] == "all") { /* code to select all without restriction */ } else { /* code to select by position */ } /* code to display the results */ }` – ADyson Nov 24 '22 at 14:52
  • `it's not going to be a live application in anyway`...regardless, you should learn how to do things correctly so you don't get into bad habits and have to unlearn them later. Read [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) – ADyson Nov 24 '22 at 14:52

1 Answers1

-1

So thank you for your input guys, here is what I put together assuming this is still open to SQL injection?

 <?php
if (isset($_POST['main_select'])) {
    $position = $_POST['main_select'];
    if ($position == "all") {
        $statement = "SELECT * FROM staff_members";
    } else {
        $statement = "SELECT * FROM staff_members WHERE position = '$position'";
    }
    $result = mysqli_query($conn, $statement);
}

echo '<table align="center" border="0" cellspacing="35" cellpadding="2" width="100%">';
echo "<thead><tr><th>ID</th><th>Name</th><th>Email</th><th>Position</th><th>Update</th><th>Action</th></tr></thead>";
while ($row = mysqli_fetch_assoc($result)) {
  echo "<tr>";
  echo "<td>{$row['id']}</td>";
  echo "<td>{$row['name']}</td>";
  echo "<td>{$row['email']}</td>";
  echo "<td>{$row['position']}</td>";
  echo "<td><a href='edit_member.php?id={$row['id']}'>Edit</a></td>";
  echo "<td><a href='delete_member.php?id={$row['id']}'>Delete</a></td>";
  echo "</tr>";
}
echo "</table>";
echo '<p><a href="index.php">Back</a></p>';
?>

However, it works and pulls in all records when "all" is selected

d212digital
  • 385
  • 3
  • 8
  • 1
    `assuming this is still open to SQL injection?`...correct. Refer to the link I provided in the other comment thread. – ADyson Nov 24 '22 at 15:11
  • Logic-wise, all the stuff to loop through the results and display them needs to be within the `if (isset($_POST['main_select'])) {` though, otherwise you're going to find it could try to run that code even when no option was selected - and therefore no query was executed. Obviously that's going to cause errors when it can't find a `$result` to loop through. – ADyson Nov 24 '22 at 15:12
  • Thanks again for your feedback, I am literally starting from the beginning with php, I worked on magento 1 many years ago and a lot has changed since then. :-) I have checked that link out and will coding using prepared statements from now on. :-) – d212digital Nov 24 '22 at 15:17