I am currently working on an admin module that displays all users from the database who is level 0 so the admins that are level 2 can change levels of other users on the fly. Here is what module looks like:
I have tried to research my issue but can’t really find any solution. I want to make it so if an admin clicks on the button Change to level 1 it will automatically just run the UPDATE query for the user that is in that same column. My issue is, I am not sure how to write the query to fetch the username into that column and tie an UPDATE to level 1 query / UPDATE to level 2 for each button.
I don’t see any form in your code. Make every button a form, provide userid and level via hidden input fields. Run your SQL with the values from the form.
Sorry about that, I had forms before but for some reason when providing code in the pastebin they weren’t there. I have updated my code here where I added the forms around the buttons and also created two querys for each button.
The problem I am currently having is upon clicking either Change to level 1 or Change to level 2 - it just updates the last user registered level instead of the username in the row I clicked the button on. To clerify a bit more I want it so if I hit the button Change to level 1 higlighted in yellow, it should change the username: cscodismith (same goes with the fields highlighted in purple)
The two querys that I am using on each button are as follows:
// if users hit Change to level 1 button;
if(isset($_POST['changeLevel_1'])) {
// set $username var
$username = $row['username'];
// update user in that row to level 1 in database
$updateLevel_1 = dbConnect()->prepare("UPDATE users SET level = '1' WHERE username = '$username'");
$updateLevel_1->execute();
// echo success message and redirect
echo '<p class="success">' .$username. ' has been updated to level 1!</p>';
header('refresh:3;url=index.php');
}
// if user hits Change to level 2 button;
if(isset($_POST['changeLevel_2'])) {
// set $username var
$username = $row['username'];
// update user in that row to level 2 in database
$updateLevel_2 = dbConnect()->prepare("UPDATE users SET level = '2' WHERE username = $username");
$updateLevel_2->execute();
// echo success message and redirect
echo '<p class="success">' .$username. ' has been updated to level 2!</p>';
header('refresh:3;url=index.php');
}
this should give you NULL and an array with the button-name. just have a look at your form: there is no input with the username. i quote myself, please read carefully:
provide userid and level via hidden input fields
try this with type=“text” inputs first to get a feeling
Alright after getting a view on: var_dump($_POST['changeLevel_1']); var_dump($_POST);
It shows me the following:
It seems that it has not given me an array with button-name on it though as you said it should. Sorry for asking so many questions but I also don’t understand why I should be using text inputs in my <form>'s if I am not requesting the user to give me any details but simply clicking on the button that will update the user’s level in that row that the button that was clicked is in.
Also when you say:
should each of my form look something like this then?
Both your input fields are called “level”, they need to have different names. Yes, as per your update.
I would just include the level that you wish to upgrade the user to as a hidden variable in your form, that way you can use the same submit code whether you’re upping them to level 2 or level 1. There’s no need to pass through the current permission level - you could retrieve that in the update code based on the user id if you need it.
Also this bit:
$updateLevel_1 = dbConnect()->prepare("UPDATE users SET level = '1' WHERE username = '$username'");
$updateLevel_1->execute();
could use some changes. You’ve gone to the trouble of using a prepared statement, but then not used the power and security of bound parameters. Something like
$updateLevel_1 = dbConnect()->prepare("UPDATE users SET level = ':level' WHERE username = :user ");
$updateLevel_1->execute(array(':user' => $username, ':level' => $newlevel));
or using bindParam() instead.
And another question would be where the username is coming from. You pass the id value in your form, so you would need to use this to determine which user you are updating, not the username.
Alright, thanks for all the input first off! I like to tackle tasks one by one slowly to make sure I am doing things 100% correctly (hopefully you don’t mind). So first off what you’re saying is that I need to:
include the level that you wish to upgrade the user to as a hidden variable in your form, that way you can use the same submit code whether you’re upping them to level 2 or level 1.
So when I come to trying this I have two forms still, one for each level and each look like the following:
I have also used the bindParam below my query as I am more familiar with that method, as you suggested. Not completely sure if I have done bound them correctly or not but here they are along with the prepared query:
// set variables
$username = $row['username'];
$newLevel = $_POST['user_level'];
// update user in that row to level 1 in database
$updateLevel_1 = dbConnect()->prepare("UPDATE users SET level = :level WHERE username = :username");
$updateLevel_1->bindParam(':username', $username);
$updateLevel_1->bindParam(':level', $newLevel);
$updateLevel_1->execute();
As you can see in the query I am using username from the $row[‘username’] variable to identify which user I am trying to adjust but that is also where the problem comes in (It usually selects the last user registered in the database).
Where is the code that gets the $username variable value? By selecting a $row variable, if you’re outside a while() loop retrieving records from a table, it will give the last in that record set. Can’t see where that first line of code comes from, though.
But if you’re passing the user_id through in the form, I don’t see why you need to also get the username - just change the update query to use the id instead, as long as it’s unique.
$userid = $_POST['user_id'];
$newLevel = $_POST['user_level'];
// update user in that row to level 1 in database
$updateLevel_1 = dbConnect()->prepare("UPDATE users SET level = ':level' WHERE id = :id");
$updateLevel_1->bindParam(':id', $userid);
$updateLevel_1->bindParam(':level', $newLevel);
$updateLevel_1->execute();
The two forms are fine, but point them to the same processing code. Because the query contains the new level value, there’s no need to have different processing code, just pass the new level in from the form.
Use the same name for the buttons, all you need is contained in the form variables.
Obviously at some point you need to look at various security stuff to ensure that the code checks an authorised user is making the change, not just use the form variables without any checks.
Very good point, not really sure why I was using the username instead of simply using the ID of the account. So as of now making all the changes I have updated the code on pastebin here and instead of two query’s I believe I can now run one query to update the level of a user
// if users hits a change level 1/2 button;
if(isset($_POST['changeLevel'])) {
// set variables
$userid = $_POST['user_id'];
$newLevel = $_POST['user_level'];
// update user in that row to level 1 in database
$updateLevel = dbConnect()->prepare("UPDATE users SET level = :level WHERE id = :id");
$updateLevel->bindParam(':id', $userid);
$updateLevel->bindParam(':level', $newLevel);
$updateLevel->execute();
// echo success message and redirect
echo '<p class="success">' .$username. ' has been updated to level ' .$newLevel. '!</p>';
header('refresh:3;url=index.php');
}
The code above seems to be working fine now and doing as I intend it to do but when you say
what sort of security do I need to put on this sort of form to ensure that an authorized user is making the change. I thought this would already be done and be secure with the statement:
if ($admin_level > 1) {
to ensure that the only one that can actually see the module is users that are level 2 in the database so no other unauthorized users will have any access to view the module.
I don’t honestly know - there are people on here with far more knowledge on such matters, if you have a bit of a browse further down the board I recall seeing examples and links to articles.
I think the problem there is that you’re making an assumption that the only people trying to call your update code must be authorised users, those who’ve clicked on your form, and they’re not always the problem. The problem are those attempting to pretend to be authorised users. From a security point of view you need to consider all the things that could happen if you don’t prevent them.
One way of adding protection would be to generate a unique field based on the details you retrieve from the database, store it as another hidden form variable, then as part of the update routine re-generate the field, compare it to the content of the hidden field, and see that they are the same. So you might generate a hash value based on the username, date of joining and a few other fields, which would be almost impossible for an intruder to “crack”.
That might sound a bit over the top, especially if this particular code is being used internally by a small company, won’t ever be on the net, etc. etc. But it’s possibly something worth looking into for future applications, or to consider if this particular user were to get larger, expand to multiple sites, need to access their admin from home, whatever. Keeping security in mind is a good habit to get into - and it may be that with security in mind, you can assess the potential threats and determine that you don’t need to add any more security. That’s fine too, as long as it’s considered.
might be a nice to have further down the line but i would also disable or hide the options that arent needed. If i am already level 1 i don’t need a button to change to level 1.
When you are outputting your list of users in the while loop you would just need to do a simple if/else statement.
<?php if($row['level'] =='1'){ echo '<xxxx button code to change to 2 xxx>'} elseif{$row['level'] =='2'){echo '<xxxx button code to change to 1 xxx>'}else{ echo 'could not find a level';}
You could then actually just have 1 column and be able to quickly see what levels people are set at. Easy enough to add in style="background: red;" etc to have different colors depending on the level they are currently set at. Again easier to see quickly in a list.
You can also easily add a button at the top using a GET variable to order the users by level (or anything else in your table for that matter). E.g
<a href="?order=level&dir=asc">Order by level ascending</a>
You can now just add $specify to your query at the end and it will add an ORDER BY. You can build on that to switch ASC to DESC and ORDER BY other columns you just need to set links and add the variables. It will be safe as the GET variable isn’t directly used in the query only to turn it on or off.
