Stephane B. Stephane B. - 6 months ago 11
PHP Question

Use parameter of php function as column name in SQL query

This is for a school project. I am new in PHP and MySQL.
I am trying to write a function in PHP with one string parameter, which will become the column name of a SQL query which is used in a prepare() statement inside this function.

Here is the code:

function checkCredentials($dbFieldToCheck) {
$statement = $conn->prepare("SELECT id FROM user WHERE ".$dbFieldToCheck." = ? AND password = PASSWORD(?)");
if (!$statement) die("Prepare failed: (" . $conn->errno . ") " . $conn->error);
$statement->bind_param("ss", $_POST["username/email"], $_POST["password"]);
$statement->execute();
$result = $statement->get_result();
$row = $result->fetch_assoc();
return $row;
}


Then I call this function twice in two different variables:

$row1 = checkCredentials('email');
$row2 = checkCredentials('username');


Finally I do something with these two variables (not useful here I guess).

I have tried different ways to write the parameter in the SQL statement also different ways to write it in the function call. Now I am even starting to think that my function is not even called at all...

What I want to achieve works if I just execute the code inside the function two times with the column name hard coded. But this is not nice :)

Any help is very much appreciated. Thanks!

Answer

Add to the parameter of your function the submitted POST data and your connection variable. When you call your function, it will look like this:

checkCredentials($dbFieldToCheck, $conn, $_POST["username/email"], $_POST["password"]);

Your function will look something like this:

function checkCredentials(string $dbFieldToCheck, $conn, $username, $password) {

    $statement = $conn->prepare("SELECT id FROM user WHERE ".$dbFieldToCheck." = ? AND password = PASSWORD(?)");
    if (!$statement) die("Prepare failed: (" . $conn->errno . ") " . $conn->error);
    $statement->bind_param("ss", $username, $password);
    $statement->execute();
    $result = $statement->get_result();
    $row = $result->fetch_assoc();
    return $row;

}

You can use global to call variables inside your function (you can refer here for the cons of using global).

Comments