Variable scope and mysqli prepared statements

[mmonaco]

I am interested in how variable scope works with mysqli prepared statements. For example:

     $link = new mysqli("localhost", "root", "password", "db");

 $stmt = $link->prepare("SELECT name, age, state FROM users WHERE id=?");

Now I know I need to use the bind_param() and bind_result() methods, but I don't know where/how...

      $stmt->bind_param("i", $id);
      $stmt->bind_result($name, $age, $state);

Should these be used every time I would like to use the statement, or only once? And what is the scope of the variables $id, $name, $age, $state?

If I want to set up the link, and prepare the statements at the begining of the document, how do I use the statements within a function, and handle the scope of the parameters and results?

[Muntjewerf]

looking at your query provided in the example it will be returning one row.
Using a "normal" query

// simple example (no error checking)
$link = new mysqli("localhost", "root", "password", "db");
$result = $link->query("SELECT name, age, state FROM users WHERE id='1'");
$row = $link->fetch_row($result);  // return row;

$name = $row[0];
$age = $row[1];
$state = $row[2];

$result->close(); // close result set
$link->close();     // close connection

if you want to retrieve the same records using a prepared statement, you'll need to do something like this:

// simple example (no error checking)
$link = new mysqli("localhost", "root", "password", "db");
$stmt = $link->prepare("SELECT name, age, state FROM users WHERE id=?");
$stmt->execute(); // execute statement
$stmt->bind_param("i", $id); //sets the "question mark" to the value of $id type int.  

$stmt->bind_result($name, $age, $state); // sets the data retrieved from the fields to var names supplied.

$row = $stmt->fetch(); // return row

$name = $row[0];
$age = $row[1];
$state = $row[2];

$stmt->close();
$link->close();