CODEWITHSUNDEEP
phpmysqlfunctionpdo
Wokki
Wokki

Reputation: 157

MySQL query in PHP function

I'm attempting to use functions to get certain data from database. For example, I want to get info from an user with ID 1.

try {
    $connection = new PDO("mysql:host=localhost;dbname=database", "root", "password");
}
catch (PDOException $e) {
    die("Error: " . $e->getMessage());
}

$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

function getUser($id) {
    global $connection;
    $query = $connection->prepare("SELECT * FROM accounts WHERE ID = '$id'");
    $query->execute();

    while($row = $query->fetch()) {
        echo $row['playername'];
        $user[] = $row;
    }
}

And then in my index.php.

include 'inc/db.php';

getUser("1");
foreach($user AS $user) {
    echo $user['ID'];
}

The first echo works, I get the username displayed, but the foreach doesn't echo anything. I tried to var_dump($user); but ended up getting NULL.

Upvotes: 0

Views: 132

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157895

Your problem is that you are writing too much code. PHP can't process so much, chokes and dies.
All you actually need is

$pdo = new PDO("mysql:host=localhost;dbname=database", "root", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

function getUser($id)
{
    global $pdo;

    $query = $pdo->prepare("SELECT * FROM accounts WHERE ID = ?");
    $query->execute(array($id));
    return $query->fetch();
}

$user = getUser(1);
echo $user['playername'];

to make it little more serious, you should use prepared statement to pass variable into query and return data from the function.

Upvotes: 0

Marc B
Marc B

Reputation: 360692

You need to have:

function getUser(...) {
  ...
  $user = array();
  while(...) {
      $user[] = $row;
  }
  return $user;
}

And then in your main code:

$users = getUser(1);
foreach($users as $user) { .... }

Right now you're defining local variables and then not returning them, so they're lost when the method exits. And then not capturing any possible returned values anyways, making your code basically pointless.

Upvotes: 2

Related Questions