MySQL query in PHP function

Question

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.

Doesn't make much sense to do foreach($var as $var). That is, to use the same var name. — fedorqui
– fedorqui, Commented Feb 28, 2014 at 15:46
I'm not that good in PHP, still studying. What would you suggest me to use to get data from an user? — Wokki
– Wokki, Commented Feb 28, 2014 at 15:47
You need to learn about PHP variable scope. $user inside your getUser method is COMPLETELY different from the (undefined) $user you're trying to use in the foreach loop. — Marc B
– Marc B, Commented Feb 28, 2014 at 15:47

Marc B · Accepted Answer · 2014-02-28 15:49:47Z

2

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.

answered Feb 28, 2014 at 15:49

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wokki Over a year ago

I attempted to echo value with the code you provided, but I am ending up getting values such as "117744KKhhhhhhhh1111001111112211551122" which are not the values that are in the database.

Your Common Sense · Accepted Answer · 2014-02-28 15:56:06Z

0

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.

edited Feb 28, 2014 at 15:56

answered Feb 28, 2014 at 15:48

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

2 Comments

Marc B Over a year ago

"php can't process so much and dies"? Huh? HUH!?

Wokki Over a year ago

This works great for getting a single item. How about if I want to get all users and use foreach method for that?

Collectives™ on Stack Overflow

MySQL query in PHP function

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related