Using a variable in php function returning value

Question

I have a function that is supposed to give me a staff members name from his ID number so my php is like this

$staffId = $_GET['staff_id'];
$staff = staff_load($staffId);

and my function is like this

function staff_load()
{
$dbh = dbh_get(); //connects to database

$sql = 'select user_name from user_staff where user_id = ?';
$stmt = $dbh->prepare($sql);
$stmt->execute();
$staff = $stmt->fetch();

dbh_free($dbh); //disconnects from database
return $staff;
}

But when I try and use the $staff variable, it shows nothing. I can't work out what I'm doing wrong. I've tried a bunch of variants and gotten nowhere except frustrated.

<td>Book for ' . $staff . '</td>

First, `var_dump` your var `$staff` and tell what it returns. — Dmytrechko, Jan 23 '17 at 09:23
Your function definition (`function staff_load()`) needs to accept the value passed to it (`function staff_load($id)`), and bind it in the SQL query — Mark Baker, Jan 23 '17 at 09:24
`function staff_load()` takes no parameter, it should be `function staff_load($id)` and then inside your `execute()` add `execute([$id])` — Jaquarh, Jan 23 '17 at 09:24
You should be getting errors from this code. Look at your error log — RiggsFolly, Jan 23 '17 at 09:25
You `->prepare()` a query, **but you dont bind the parameter to it** — RiggsFolly, Jan 23 '17 at 09:26
Add [error reporting](http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php/845025#845025) to the top of your file(s) _while testing_ right after your opening PHP tag for example ` — RiggsFolly, Jan 23 '17 at 09:26
function staff_load() , You forgot to mention staffid in the function as parameter i guess — Maaz Rehman, Jan 23 '17 at 09:27

Barmar · Accepted Answer · 2017-01-23T20:18:58.193

3

First, you forgot to include the parameter in your function definition:

function staff_load($id) {

Then you need to bind the parameter of the prepared statement.

$sql = 'select user_name from user_staff where user_id = $1';
$stmt = $dbh->prepare($sql);
$stmt->execute(array($id));

Next, fetch() returns an array, you need to extract the user_name element from the array:

$row = $stmt->fetch();
if ($row) {
    $staff = $row['user_name'];
} else {
    $staff = false;
}

edited Jan 23 '17 at 20:18

answered Jan 23 '17 at 09:29

Barmar

741,623
53
500
612

changed function to this but still nothing function staff_load($staffId) { $dbh = dbh_get(); $sql = 'select user_name from user_staff where user_id = :staffId'; $stmt = $dbh->prepare($sql); $stmt->execute(); $stmt->bindParam(':staffId', $staffId); $row = $stmt->fetch(PDO::FETCH_ASSOC); if ($row) { $staff = $row['user_name']; } else { $staff = false; } dbh_free($dbh); return $staff; } – Kilisi Jan 23 '17 at 09:40
Are there any errors? Have you enable PDO's error signalling? Can you confirm that you're using PDO as I assumed? – Barmar Jan 23 '17 at 15:59
I assumed it was PDO because `$stmt->fetch()` is the syntax for that API. What API are you using? – Barmar Jan 23 '17 at 18:52
can it be written without api? I'm not a programmer, but doesn't have pdo in other functions. It's just php to postgresql database – Kilisi Jan 23 '17 at 19:52
this is a statement that works function user_options() { $dbh = dbh_get(); $options = ''; $sql = 'select user_id, user_name from user_staff where user_id>7 ORDER BY user_name'; $stmt = $dbh->prepare($sql); $stmt->execute(); while (true) { $r = $stmt->fetch(); if (is_bool($r)) break; $options .= ''; } dbh_free($dbh); return $options; } – Kilisi Jan 23 '17 at 20:03
@Kilisi There's always an API. Whatever function you use to make database queries is the API. – Barmar Jan 23 '17 at 20:13
well, I tried what you had, and it doesn't work, any ideas? – Kilisi Jan 23 '17 at 20:14
It's not clear what functions you're using. According to http://php.net/manual/en/book.pgsql.php, the functions for making PostgreSQL queries all begin with `pg_`, but you're not calling those functions in the code you posted. I guess it's being done in the database class you're using. – Barmar Jan 23 '17 at 20:17
I've changed my answer to use a prepared statement as described in http://php.net/manual/en/function.pg-prepare.php. – Barmar Jan 23 '17 at 20:20

Dima · Answer 2 · 2017-01-23T09:29:54.667

0

first of all you have to pass parameter to staff_load function

function staff_load($staffId)

secondly you should bind parameter

$sql = 'select user_name from user_staff where user_id = :user_id';
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':user_id', $staffId);

edited Jan 23 '17 at 09:29

answered Jan 23 '17 at 09:27

Dima

11
2

user_id should equal the $staffId value – Kilisi Jan 23 '17 at 20:07

Using a variable in php function returning value

2 Answers2