PHP - Populate a field depending on auto-populated select

Question

I'm fairly new to php and have a question. I have an HTML form that has a SELECT auto-populated from an SQL table via PHP. The dropdown is populated with all users with the level of "Admin" or "Moderator". This is the code to connect:

$con = mysqli_connect("localhost", "root", "", "database") or die("Error " . mysqli_error($con));

And the dropdown itself:

<form name="htmlform" role="form" method="POST" action="result.php">
<select id="user" name="user" required>
    <option selected disabled>User</option>
    <?php
    $result = $con->query("SELECT username FROM users WHERE level='admin' OR level='moderator' ORDER BY level");
    while ($row = $result->fetch_assoc()) 
    {
      $username = $row['username'];
      echo '<option value="'.$username.'">'.$username.'</option>'; 
    }
    ?>
</select>

This works perfectly. The problem I'm having is that I am trying to reuse the data from this form (specifically $_POST['user']) on another page to auto-populate another field in a form. I need to see if the 'user' is an Admin or not and return $other as either "y" (Admin) or "n" (not Admin), which will then be added to another table.

Here's my code on the 2nd page (result.php):

$user=$_POST['user'];
$query = $con->query("SELECT level FROM users WHERE username=$user");
$variable=mysqli_query($con, $query);
if ($variable=="admin") {
    $other = 'y';
} else {
    $other='n';
}

At the moment all output for $other is "n" regardless of anything. So, obviously I have an error in the code, but don't know enough php to be able to spot or correct it. Please could someone help point out the error?

Using bits from all the below answers I came up with this snippet for result.php which does the required job: `$query = $con->query("select level from users WHERE username='$user'"); while ($row = $query->fetch_assoc()) { $lvl = $row['level']; } if ($lvl==="Admin") { $other= 'y'; } else { $other='n'; }` A lot of people mention injection attacks. How would I alter the code to avoid this? — Josh N, Feb 03 '17 at 10:49
See the answer by @RiggsFolly. Learn to get away from using the PHP mysql and mysqli functions. Learn and use the PHP PDO stuff. Learn to use prepared statements. — Anthony Rutledge, Feb 03 '17 at 11:45

score 2 · Answer 1 · edited May 23 '17 at 12:09

text values have to be wrapped in quotes in a query

$query = $con->query("SELECT level FROM users WHERE username='$user'");

You also look like you were trying to execute that same query twice here:

$query = $con->query("SELECT level FROM users WHERE username=$user");
$variable=mysqli_query($con, $query);

this is not legal usage.

Also when you run this line

$variable=mysqli_query($con, $query);

$variable is not a value, but a mysqli_result object that will contain a resultset or FALSE if the query failed, but definitely not the content if the id column in your query.

However if you are using data got from the user, it is not safe to assume thay are not attempting a SQL Injection Attack

So you should use Prepared and Parameterised queries like this

$stmt = $con->prepare("SELECT level FROM users WHERE username=?");
$stmt->bind_param('s', $_POST['user']);
$stmt->execute();

I think you shoud start by reading the PHP manual for the mysqli extension

Anthony Rutledge · Accepted Answer · 2017-02-02T18:19:01.923

1

(Without getting into issues about best practices ...)

Your second code snippet's usage of the return value from mysql_query() is problematic.

The PHP Manual states:

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

Hence, $variable is a PHP resource and cannot ever be equal to a string. Use tripple === equals when possible. You still need to "fetch" the record from the result resource (you managed to to do this in the first code snippet).

Generally speaking ...

$result = mysqli_query($con, $query);
$record = result->fetch_assoc();  

//if(result->fetch_assoc()['level'] === 'admin') in PHP 5.4 and up.

//or

//if(mysqli_query($con, $query)->fetch_assoc()['level'] === 'admin') in PHP 5.4 and up.

if($record['level'] === 'admin')
{

}
else
{

}

Cheers!

edited Feb 02 '17 at 18:19

answered Feb 02 '17 at 18:00

Anthony Rutledge

6,980
2
39
44

Thank you for the reply. _(Without getting into issues about best practices ...)_. Please elaborate on what would be the best practice. I don't want to get into bad habits :) – Josh N Feb 03 '17 at 10:58
Learn to use PDO instead of the mysql and mysqli functions. We all go through it. It's in the PHP manual. Use prepared statements with user input. Also, in your program, sanitize and validate your user inputs before using them in a prepared statement. – Anthony Rutledge Feb 03 '17 at 11:47

score 0 · Answer 3 · answered Feb 02 '17 at 17:59

According with mysqli_query doc:

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

So don't expect to get database value directly from call, you are comparing a mysql_result object (you made a SELECT) versus a constant string. You need to get your data from mysql_result object and then you can make comparison.

score -1 · Answer 4 · answered Feb 02 '17 at 17:55

-1

This code should work for you:

$user = $_POST['user'];
$sql = "SELECT level FROM users WHERE username={$user}";

$variable = mysqli_query($con, $sql)->fetch_row();
if ($variable[0]=="admin") {
    $other = 'y';
} else {
    $other='n';
}

answered Feb 02 '17 at 17:55

Saliery

119
5

Why would this code work for the OP? A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. – Jay Blanchard Feb 06 '17 at 18:51

PHP - Populate a field depending on auto-populated select

4 Answers4