How to execute SQL query inside a PHP function

Question

I'm trying to execute this function:

<?php
function registerDevice(){
 $query = "INSERT INTO devices (device,username) VALUES (:device,:username)";
 $query_params = array(
 ':device' => $_POST['device'],
 ':username' => $_POST['username'],
 );
 try {
 $stmt = $db->prepare($query);
 $result = $stmt->execute($query_params);
 }
 catch (PDOException $ex) {
 $response["success"] = 0;
 $response["result"] = "Error154";
 die(json_encode($response));
 }
 }
registerDevice();
?>

The method is works successfully if is not called when is outside the function:

<?php
$query = "INSERT INTO devices (device,username) VALUES (:device,:username)";
 $query_params = array(
 ':device' => $_POST['device'],
 ':username' => $_POST['username'],
 );
 try {
 $stmt = $db->prepare($query);
 $result = $stmt->execute($query_params);
 }
 catch (PDOException $ex) {
 $response["success"] = 0;
 $response["result"] = "Error154";
 die(json_encode($response));
 }
?>

but when i call the function the function does not work at all. I hope you guys can help me out. Thanks

• Danyal Sandeelo: i can see an extra } there at the end of the code

Answer 1

The key is $db variable. You never show how it's initialized: I assume it's created by some external (require) file responsible for creating a connection to DB and storing it into $db var. As file boundaries do not create separate scopes in PHP, that variable is left in the global scope.

It's not a good practice, but it works when the code using $db is also placed in the global scope. But it breaks when the code is moved into a function, which introduces a new - isolated - scope.

(I'd suggest checking this question and its answers, it explains a lot about intricacies of PHP; and believe me, there are some)

One possible way out of this mess is explicitly passing the value of $db variable into registerDevice function as its param. This obviously requires changes in the signature:

function registerDevice($db) {
 // ... the rest of the code is the same 
}
registerDevice($db);

Note that $_POST variable is a different beast. Actually, there's more of them - $_GET, $_SERVER and so on, those wild things also known as PHP superglobals. You can safely (sort of) use them within any part of your code, whenever it introduces a new scope or not. That's why they're called superglobals, after all.

Still, even with all the power in your disposal it might be a great idea adjusting your function so that it doesn't depend on any magic:

function registerDevice($db, $deviceId, $username) {
 // ... the code is the same 
}
if (isset($_POST['device'], $_POST['username'])) {
 registerDevice($db, $_POST['device'], $_POST['username']);
}
else {
 // something is not right with the request
}

The change might seem insignificant, but now your function can take inputs from any source, becoming a step closer to a truly autonomous entity. That, among other things, allows you to 1) test this function in an isolation; 2) reuse this function in other parts of your application.

• user2643881: Yeap. that is definitely the right answer. Thank you so much!
• raina77ow: You're welcome. Again I'd suggest checking the docs and the linked SO material, it explains a LOT. )